Mapper, 在接口和接口的方法上加上相应的注解
import org.apache.ibatis.annotations.SelectProvider;
import org.mybatis.spring.annotation.MapperScan;
import yycg.base.domain.vo.SysuserCustom;
import yycg.base.domain.vo.SysuserQueryVo;
import yycg.base.mapper.sql.SysuserCustomSql;
import java.util.List;
/**
* Created with IDEA
* author:bigStone
* Date:2019/5/2
**/
@MapperScan
public interface SysuserMapperCustom {
@SelectProvider(type = SysuserCustomSql.class, method = "findSysuserCustomList")
List<SysuserCustom> findSysuserCustomList(SysuserQueryVo sysuserQueryVo) throws Exception;
}
生成动态SQL的类
import org.apache.ibatis.jdbc.SQL;
import yycg.base.domain.vo.SysuserQueryVo;
/**
* 多表嵌套查询
* Created with IDEA
* author:bigStone
* Date:2019/5/2
**/
public class SysuserCustomSql {
public String findSysuserCustomList(SysuserQueryVo sysuserQueryVo) {
SQL sql1 = new SQL();
sql1.SELECT("SYSUSER.id, SYSUSER.userid,SYSUSER.username," +
" SYSUSER.groupid, SYSUSER.sysid, sysuser.USERSTATE, " +
"decode(SYSUSER.Groupid, '1', " +
"(select mc from userjd where id = sysuser.sysid), '2', " +
"(select mc from userjd where id = sysuser.sysid), '3', " +
"(select mc from useryy where id = sysuser.sysid), '4'," +
"(select mc from usergys where id = sysuser.sysid)) sysmc");
sql1.FROM("sysuser");
SQL sql2 = new SQL() {{
SELECT("SYSUSER2.*");
//把sql1的查询语句作为sql2的查询对象,别名为SYSUSER2
FROM("(" + sql1.toString() + ") SYSUSER2");
if (sysuserQueryVo.getSysuserCustom() != null) {
if (sysuserQueryVo.getSysuserCustom().getUserid() != null && sysuserQueryVo.getSysuserCustom().getUserid() != "") {
WHERE("SYSUSER2.userid = #{sysuserCustom.userid}");
}
if (sysuserQueryVo.getSysuserCustom().getUsername() != null && sysuserQueryVo.getSysuserCustom().getUsername() != "") {
WHERE("SYSUSER2.username = #{sysuserCustom.username}");
}
if (sysuserQueryVo.getSysuserCustom().getGroupid() != null && sysuserQueryVo.getSysuserCustom().getGroupid() != "") {
WHERE("SYSUSER2.groupid = #{sysuserCustom.groupid}");
}
if (sysuserQueryVo.getSysuserCustom().getSysmc() != null && sysuserQueryVo.getSysuserCustom().getSysmc() != "") {
WHERE("SYSUSER2.sysmc like '%' || #{sysuserCustom.sysmc} || '%' ");
}
}
}};
SQL sql3 = new SQL() {{
SELECT("page_1.*, rownum page_num");
//把sql2的查询语句作为sql3的查询对象,并给别名page_1
FROM("(" + sql2.toString() + ") page_1");
if (sysuserQueryVo.getPageBean() != null) {
WHERE("rownum <= #{pageBean.endIndex}");
}
}};
SQL sql4 = new SQL() {{
SELECT("page_2.*");
//把sql3的查询语句作为sql4的查询对象, 并给别名page_2
FROM("(" + sql3.toString() + ") page_2");
if (sysuserQueryVo.getPageBean() != null) {
WHERE("page_2.page_num >= #{pageBean.startIndex}");
}
}};
return sql4.toString();
}
}
参数为自定义查询类
/**
*自定义查询类,包含分页信息和实体类信息
* Created with IDEA
* author:bigStone
* Date:2019/5/2
**/
public class SysuserQueryVo {
//自定义PageBean类,包含分页的相关信息
private PageBean pageBean;
//用于映射查询结果的实体类
private SysuserCustom sysuserCustom;
public PageBean getPageBean() {
return pageBean;
}
public void setPageBean(PageBean pageBean) {
this.pageBean = pageBean;
}
public SysuserCustom getSysuserCustom() {
return sysuserCustom;
}
public void setSysuserCustom(SysuserCustom sysuserCustom) {
this.sysuserCustom = sysuserCustom;
}
}
虽然一个sql调了大半天,但还是有收获的,主要是当时思路不够清晰,传参的时候比较头痛,后来把每个查询都分离出来,再再原来的基础上一步一步嵌套,还是比较简单的,虽然费了老大事,但感觉还是比配置文件好用。
传参问题: 如果传递的是一个对象,直接 #{属性} 即可取值, 如果对象属性依然是包装类,可以用 #{属性.下一级属性} 来取值。