MyBatis-动态SQL

说明

本文以orcl.scott.emp为例。

源码

MyBatis

if

需求

当empno不为空时，使用empno查询，当ename不为空时，使用ename模糊查询。

接口

    /**
     * 通过JavaBean、动态if获取员工信息
     * 如果empno不为空，那么以empno为准查询
     * 如果ename不为空，那么同时以ename字段进行模糊查询
     */
    Emp selectByDynParamsIf(Emp emp);

实现

<!-- if -->
  <select id="selectByDynParamsIf" resultMap="BaseResultMap" parameterType="com.yan.po.Emp">
    select 
        <include refid="Base_Column_List"/>
        from emp where 1=1
    <if test="empno != null and empno != ''">
        and empno=#{empno,jdbcType=DECIMAL}
    </if>
    <if test="ename != null and ename != ''">
        and ename like ('%'||#{ename}||'%') 
    </if>
  </select>

测试

    /**
     * 动态SQL-if
     */
    emp = new Emp("SCOTT");
    emp=empMapper.selectByDynParamsIf(emp);
    System.out.println("================以下语句通过动态SQL生成================");
    System.out.println(emp.getEname()+"的工作是："+emp.getJob());

结果

2017-01-05 23:08:18,717 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - ==>  Preparing: select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp where 1=1 and ename like ('%'||?||'%') 
2017-01-05 23:08:18,718 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - ==> Parameters: SCOTT(String)
2017-01-05 23:08:18,722 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsIf] - <==      Total: 1
================以下语句通过动态SQL生成================
SCOTT的工作是：ANALYST

choose、when、otherwise

需求

当empno不为空时，使用empno查询，当empno为空而ename不为空时，使用ename模糊查询,否则只取全部结果集的第一行（保证所取得的结果能够包装成Emp）。这里逻辑不严谨，意思一下而已。

接口

 /**
     * 通过JavaBean、动态choose获取员工信息
     * 若empno不为空，则只以empno为准查询
     * 若empno为空但ename不为空，则以ename为准模糊查询
     * 否则只取第一行记录
     * @param emp
     * @return
     */
    Emp selectByDynParamsChoose(Emp emp);

实现

<!-- choose、when、otherwise -->
  <select id="selectByDynParamsChoose" resultMap="BaseResultMap" parameterType="com.yan.po.Emp">
    select * from emp 
    <trim prefix="where" prefixOverrides="and" suffixOverrides=",">
    <choose>
        <when test="empno != null and empno != ''">
            and empno = #{empno},
        </when>
        <when test="empno == null and ename != null and ename != ''">
            and ename like ('%'||#{ename}||'%') 
        </when>
        <otherwise>
            and rownum = 1
        </otherwise>
    </choose>
    </trim>
  </select>

测试

   /**
     * 动态SQL-choose
     */
    System.out.println("=========empno\\ename 均不为空,但ename是错误的==========");
    emp = new Emp((short) 7369,"HAHA");
    emp = empMapper.selectByDynParamsChoose(emp);
    System.out.println(emp.getEname());
    System.out.println("=========empno为空，ename不为空==========");
    emp = new Emp(null,"ADAMS");
    emp = empMapper.selectByDynParamsChoose(emp);
    System.out.println(emp.getHiredate());
    System.out.println("=========没有参数==========");
    emp = new Emp();
    emp = empMapper.selectByDynParamsChoose(emp);
    System.out.println(emp.getEname());

结果

=========empno\ename 均不为空,但ename是错误的==========
2017-01-05 23:08:18,723 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==>  Preparing: select * from emp where empno = ? 
2017-01-05 23:08:18,723 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters: 7369(Short)
2017-01-05 23:08:18,728 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <==      Total: 1
SMITH
=========empno为空，ename不为空==========
2017-01-05 23:08:18,740 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==>  Preparing: select * from emp where ename like ('%'||?||'%') 
2017-01-05 23:08:18,740 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters: ADAMS(String)
2017-01-05 23:08:18,744 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <==      Total: 1
Sat May 23 00:00:00 CDT 1987
=========没有参数==========
2017-01-05 23:08:18,749 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==>  Preparing: select * from emp where rownum = 1 
2017-01-05 23:08:18,749 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - ==> Parameters: 
2017-01-05 23:08:18,755 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsChoose] - <==      Total: 1
SMITH

foreach

需求

查询部门编号在给定集合（数组，list，map等）范围内的员工的集合。

接口

 /**
     * 通过foreach获取部门id在一定范围内的员工信息
     * 除了例子中的数组类型参数，也可以遍历List、Set、Map等集合
     */
    List<Emp> selectByDynParamsForeach(List<Short> deptnos);

实现

<!-- collection指集合类名，数组则为array List则为list或collection
   -->
  <select id="selectByDynParamsForeach" resultType="emp" >
    select * from emp where deptno in
    <foreach collection="collection" index="index" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
  </select>

测试

    /**
     * 动态SQL-foreach
     */
//    short[] deptnos=new short[]{10,30};
    List<Short> deptnos = new ArrayList<Short>();
    List<Emp> empList=new ArrayList<Emp>();
    deptnos.add((short) 10);
    deptnos.add((short) 30);
    empList=empMapper.selectByDynParamsForeach(deptnos);
    for(Emp e:empList){
        System.out.println(e.getEname());
    }

结果

2017-01-05 23:08:18,757 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - ==>  Preparing: select * from emp where deptno in ( ? , ? ) 
2017-01-05 23:08:18,758 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - ==> Parameters: 10(Short), 30(Short)
2017-01-05 23:08:18,773 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsForeach] - <==      Total: 9
ALLEN
WARD
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
MILLER

特别说明

Map类型请参考

bind

需求

对输入的ename自动添加模糊查询使用的通配符%，对输入的工资加1000再查询。

接口

   /**
     * 通过bind元素预处理参数来查询员工信息
     */
    Emp selectByDynParamsBind(@Param("ename") String ename,@Param("sal")BigDecimal sal );

实现

<select id="selectByDynParamsBind" resultType="emp">
    <bind name="p_ename" value="'%'+ename+'%'"/>
    <bind name="p_sal" value="sal+1000.00"/>
    select * from emp 
    <trim prefix="where" prefixOverrides="and">
        and ename like (#{p_ename})
    </trim>
        and sal = #{p_sal}
  </select>

测试

    /**
     * 动态SQL-bind
     */
    BigDecimal bd = new BigDecimal(4000.00);
    emp = empMapper.selectByDynParamsBind("KING",bd);
    System.out.println(emp.getJob());

结果

2017-01-05 23:08:18,783 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - ==>  Preparing: select * from emp where ename like (?) and sal = ? 
2017-01-05 23:08:18,786 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - ==> Parameters: %KING%(String), 5000.0(BigDecimal)
2017-01-05 23:08:18,791 DEBUG [com.yan.dao.mapper.EmpMapper.selectByDynParamsBind] - <==      Total: 1
PRESIDENT

trim

属性	实例	作用
prefix	<trim prefix="where"> and empno =#{empno} </trim>	当语句中没有where时，自动添加
prefixOverrides	<trim prefixOverrides="and">and empno =#{empno} </trim>	当因为前缀多余and而发生错误时，将其覆盖或删除
suffix	略	后缀，与prefix刚好相反，比如update语句中的逗号
suffixOverrides	略	后缀，与prefixOverrides刚好相反，比如update语句中的逗号