这里有三张表,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>