MyBatis使用@Select注解写动态SQL语句

MyBatis使用@Select注解写动态SQL语句

这里有三张表，teacher，organization，sysuser

目的：使用分页查询三表联查，并且对username的模糊查询，和对tecauditstate进行判断查询

teacher表字段（teacher as t）

t.tecid,t.tecorgid,t.tectitle,t.teclabel,t.tecauditstate

organization表字段（organization as o）

o.orgname,o.orgid

sysuser表字段（sysuser as u）

u.username,u.userid

查询语句：

@Select("<script>"+" SELECT t.tecid,t.tecorgid,t.tectitle,t.teclabel,t.tecauditstate,u.username,u.userid,o.orgname,o.orgid FROM teacher AS t"+" JOIN sysuser AS u ON t.userid=u.userid"+" JOIN organization AS o ON t.tecorgid=o.orgid"+" <where>"+" <if test=' state != null'> AND tecauditstate = #{state}</if>"+" <if test=' username != null and username != \"\" '> AND u.username LIKE #{username}</if>"+" </where>"+" </script> ")

注： where 后面条件需要放在<where></where>标签

if语句的语法为  <if test =' [字段名] !=null '> AND [字段名]=#{参数} </if>

MyBatis使用@Select注解写动态SQL语句

这里有三张表，teacher，organization，sysuser

目的：使用分页查询三表联查，并且对username的模糊查询，和对tecauditstate进行判断查询

teacher表字段（teacher as t）

t.tecid,t.tecorgid,t.tectitle,t.teclabel,t.tecauditstate

organization表字段（organization as o）

o.orgname,o.orgid

sysuser表字段（sysuser as u）

u.username,u.userid

查询语句：

@Select("<script>"+" SELECT t.tecid,t.tecorgid,t.tectitle,t.teclabel,t.tecauditstate,u.username,u.userid,o.orgname,o.orgid FROM teacher AS t"+" JOIN sysuser AS u ON t.userid=u.userid"+" JOIN organization AS o ON t.tecorgid=o.orgid"+" <where>"+" <if test=' state != null'> AND tecauditstate = #{state}</if>"+" <if test=' username != null and username != \"\" '> AND u.username LIKE #{username}</if>"+" </where>"+" </script> ")

注： where 后面条件需要放在<where></where>标签

if语句的语法为  <if test =' [字段名] !=null '> AND [字段名]=#{参数} </if>