7_动态SQL

目录

1_if标签

 2_where标签

3_choose标签

4_set标签

5_trim标签

 6_bind标签

 7_sql标签

 8_foreach标签

---

1_if标签

接口
 

public interface EmpMapper2 {
   List<Emp> findByCondition(Emp emp);
}

映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msb.mapper.EmpMapper2">
<!--List<Emp> findByCondition(Emp emp);-->
    <select id="findByCondition" resultType="emp">
        select * from emp where 1=1
        <if test="empno != null">
            and empno =#{empno}
        </if>
        <if test="ename != null and ename != ''">
            and ename like concat('%',#{ename},'%')
        </if>
        <if test="job != null and job != ''">
            and job =#{job}
        </if>
        <if test="mgr != null">
            and mgr =#{mgr}
        </if>
        <if test="hiredate != null">
            and hiredate =#{hiredate}
        </if>
        <if test="sal != null">
            and sal =#{sal}
        </if>
        <if test="comm != null">
            and comm =#{comm}
        </if>
        <if test="deptno != null">
            and deptno =#{deptno}
        </if>
    </select>
</mapper>

 测试代码

public static void main(String[] args) {
    SqlSession sqlSession = MyBatisUtil.getSqlSession(false);
    EmpMapper2 mapper = sqlSession.getMapper(EmpMapper2.class);
    Emp condition =new Emp();
   /* condition.setDeptno(20);*/
   /* condition.setSal(3000.0);*/
   /*condition.setHiredate(new java.sql.Date(81,1,22));*/
   condition.setComm(0.0);
   condition.setDeptno(20);
    List<Emp> emps = mapper.findEmpByCondition(condition);
    for (Emp e:emps
         ) {
        System.out.println(e);
    }
}

 2_where标签

用于处理where关键字和and

<select id="findEmpByCondition" resultType="emp">
    select * from emp
    <where>
        <if test="empno != null">
            and empno= #{empno}
        </if>
        <if test="ename != null and ename != ''">
            and ename= #{ename}
        </if>
        <if test="job != null and job != ''">
            and job= #{job}
        </if>
        <if test="mgr != null ">
            and mgr= #{mgr}
        </if>
        <if test="hiredate != null ">
            and hiredate= #{hiredate}
        </if>
        <if test="sal != null">
            and sal= #{sal}
        </if>
        <if test="comm != null ">
             and comm =#{comm}
        </if>
        <if test="deptno != null ">
            and deptno= #{deptno}
        </if>
    </where>
</select>

3_choose标签

前面的when条件成立 后面的 when就不再判断了

<select id="findEmpByCondition2" resultType="emp">
    select * from emp
    <where>
        <choose>
            <when test="empno != null">
                and empno= #{empno}
            </when>
            <when test="ename != null and ename != ''">
                and ename= #{ename}
            </when>
            <when test="job != null and job != ''">
                and job= #{job}
            </when>
            <when test="mgr != null ">
                and mgr= #{mgr}
            </when>
            <when test="hiredate != null ">
                and hiredate= #{hiredate}
            </when>
            <when test="sal != null">
                and sal= #{sal}
            </when>
            <when test="comm != null ">
                and comm =#{comm}
            </when>
            <when test="deptno != null ">
                and deptno= #{deptno}
            </when>
        </choose>
    </where>
</select>

4_set标签

接口

int updateEmpByCondtion(Emp emp);

 映射文件

<!--int updateEmpByCondtion(Emp emp);-->
<update id="updateEmpByCondtion" >
    update emp
    <set>
        <if test="ename != null and ename != '' ">
            , ename =#{ename}
        </if>
        <if test="job != null and ename != '' ">
            , job =#{job}
        </if>
        <if test="mgr != null ">
            , mgr =#{mgr}
        </if>
        <if test="hiredate != null ">
            , hiredate =#{hiredate}
        </if>
        <if test="sal != null ">
            , sal =#{sal}
        </if>
        <if test="comm != null ">
            , comm =#{comm}
        </if>
        <if test="deptno != null ">
            , deptno =#{deptno}
        </if>
    </set>
    where empno =#{empno}
</update>

5_trim标签

Trim 标签处理 set 

<update id="updateEmpByCondition2" >
    update emp
    <!--prefix 要增加什么前缀
    prefixOverrides 要去除什么前缀
    suffix 要增加什么后缀
    suffixOverrides 要去除什么后缀
    set 是trim的一种特殊情况
    -->
    <trim prefix="set"  suffixOverrides="," >
        <if test="ename != null and ename != ''">
            ename= #{ename},
        </if>
        <if test="job != null and job != ''">
            job= #{job},
        </if>
        <if test="mgr != null ">
            mgr= #{mgr},
        </if>
        <if test="hiredate != null ">
            hiredate= #{hiredate},
        </if>
        <if test="sal != null">
            sal= #{sal},
        </if>
        <if test="comm != null ">
            comm =#{comm},
        </if>
        <if test="deptno != null ">
            deptno= #{deptno},
        </if>
    </trim>
    where  empno = #{empno}
</update>

Trim标签  处理where

<select id="findEmpByCondition" resultType="emp">
    select * from emp
        <trim prefix="where" prefixOverrides="and">
            <if test="empno != null">
                and empno= #{empno}
            </if>
            <if test="ename != null and ename != ''">
                and ename= #{ename}
            </if>
            <if test="job != null and job != ''">
                and job= #{job}
            </if>
            <if test="mgr != null ">
                and mgr= #{mgr}
            </if>
            <if test="hiredate != null ">
                and hiredate= #{hiredate}
            </if>
            <if test="sal != null">
                and sal= #{sal}
            </if>
            <if test="comm != null ">
                and comm =#{comm}
             </if>
            <if test="deptno != null ">
                and deptno= #{deptno}
            </if>
        </trim>
</select>

 6_bind标签

一般用于处理模糊查询的模板

接口

 SQL语句

 7_sql标签

<sql id="empColumn">empno,ename,job,mgr,hiredate,sal,comm,deptno</sql>
<sql id="baseSelect">select <include refid="empColumn"></include> from emp</sql>
<!--List<Emp> findByCondition(Emp emp);-->
<select id="findByCondition" resultType="emp">
    <include refid="baseSelect"></include>
    <trim prefix="where" prefixOverrides="and">
        <if test="empno != null">
            and empno =#{empno}
        </if>
        <if test="ename != null and ename != ''">
            <bind name="likePattern" value="'%'+ename+'%'"/>
            and ename like #{likePattern}
        </if>
        <if test="job != null and job != ''">
            and job =#{job}
        </if>
        <if test="mgr != null">
            and mgr =#{mgr}
        </if>
        <if test="hiredate != null">
            and hiredate =#{hiredate}
        </if>
        <if test="sal != null">
            and sal =#{sal}
        </if>
        <if test="comm != null">
            and comm =#{comm}
        </if>
        <if test="deptno != null">
            and deptno =#{deptno}
        </if>
    </trim>
</select>

 8_foreach标签

<!--List<Emp> findByEmpnos1(int[] empnos);
 collection=""  遍历的集合或者是数组
                 参数是数组,collection中名字指定为array
                 参数是List集合,collection中名字指定为list
 separator=""   多个元素取出的时候 用什么文字分隔
 open=""        以什么开头
 close=""       以什么结尾
 item=""        中间变量名
 for(Person per:PersonList)
 -->
 <select id="findByEmpnos1" resultType="emp">
     select * from emp  where empno in
     <foreach collection="array" separator="," open="(" close=")" item="deptno">
         #{deptno}
     </foreach>
 </select>
<!-- List<Emp> findByEmpnos2(List<Integer> empnos);-->
 <select id="findByEmpnos2" resultType="emp">
     select * from emp  where empno in
     <foreach collection="list" separator="," open="(" close=")" item="deptno">
         #{deptno}
     </foreach>
 </select>