Mybatis入门-动态sql及示例(七)

if（如果ename有值则加上条件查询，反之不加）

<select id="selectEmp" parameterType="emp" resultType="emp">
    select * from emp where sal >#{sal}
    <if test="ename != null">
        and ename = #{ename}
    </if>
</select>

---

like（这里拼接还可以用concat函数）

<select id="selectEmpLike" parameterType="emp" resultType="emp">
    select * from emp where sal >#{sal}
    <if test="ename != null">
        and ename like '%'||#{ename}||'%'
    </if>
</select>

---

CDATA函数（这里’<'会被识别为标签起始符,所以需要用该函数进行拼接）

<select id="selectEmpCdata" parameterType="emp" resultType="emp">
    select * from emp where sal <![CDATA[<=]]>#{sal}
</select>

---

if+where（这里没有使用where标签,在查询条件后加where 1=1是为了避免sql语句直接拼接上and条件查询后报错）

<select id="selectEmpWhere" parameterType="emp" resultType="emp">
    select * from emp where 1=1
    <if test="ename != null">
        and ename like '%'||#{ename}||'%'
    </if>
</select>

---

choose+when+otherwise(按顺序判断when标签中if是否成立,如果有一个成立则choose结束,当when中所有条件都不满足时，执行otherwise中的sql）

<select id="selectEmpChoose" parameterType="emp" resultType="emp">
    select * from emp where 1=1
    <choose>
        <when test="sal != null">
            and sal <![CDATA[<=]]>#{sal}
        </when>
        <when test="ename !=null">
            and ename = #{ename}
        </when>
        <otherwise>
            and deptno=20
        </otherwise>
    </choose>
</select>

---

trim（执行insert操作）

<insert id="InsertDept" parameterType="dept">
    <!--insert into 表名(字段) values(值)-->
    insert into dept
    <!--prefix:以(开头,suffix:以)结尾，suffixOverrides:忽略最后一个,-->
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="deptno !=null">
            deptno,
        </if>
        <if test="dname !=null">
            dname,
        </if>
        <if test="loc !=null">
            loc,
        </if>
    </trim>
    values
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="deptno !=null">
            #{deptno},
        </if>
        <if test="dname !=null">
            #{dname},
        </if>
        <if test="loc !=null">
            #{loc},
        </if>
    </trim>
</insert>

---

update+set+if+where

<update id="UpdateDeptWithIf" parameterType="dept">
    <!--update 表名 set 列名=值 where 过滤条件-->
    update dept
    <set>
        <if test="loc != null">
            loc = #{loc},
        </if>
    </set>
    <where>
        <if test="deptno != null">
            and deptno=#{deptno}
        </if>
    </where>
</update>

---

foreach（collection=“list”）

<select id="SelectForEach" parameterType="list" resultType="dept">
    <!--select * from dept where (deptno=10 or deptno=20)-->
    select * from dept
    <where>
        <!--如果传入的是单参数且参数类型是一个List,collection="list"-->
        <!--item:集合中每一个元素进行迭代时的别名 open:以(开头 close:以）结尾 separator:每次进行迭代直接的分隔符-->
        <foreach collection="list" open="(" close=")" item="no" separator="or">
            deptno = #{no}
        </foreach>
    </where>
</select>

---

foreach+in（collection=“array”）

<select id="SelectForEachArray" parameterType="list" resultType="dept">
    <!--select * from dept where deptno in (10,20)-->
    <!--如果传入的是单参数且参数类型是一个数组,collection="array",parameterType="list"-->
    select * from dept where deptno in
    <foreach collection="array" open="(" close=")" item="no" separator=",">
        #{no}
    </foreach>
</select>

---

foreach+in（collection=“map的key值”）

public interface DeptMapper {
List<Dept> SelectForEachMap(Map<String,List<Integer>> map) throws Exception;
}

--mapper映射文件
<select id="SelectForEachMap" parameterType="map" resultType="dept">
    <!--select * from dept where deptno in (10,20,30)-->
    <!--如果传入的参数是多个的时候,需要把他们封装成一个map,collection="map的key值",parameterType="map"-->
    select * from dept where deptno in
    <foreach collection="depts" open="(" close=")" item="no" separator=",">
        #{no}
    </foreach>
</select>

--测试类
@Test
public void testMap() throws Exception {
    SqlSession sqlSession = factory.openSession();
    //传入参数为想获得接口的实现类
    DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
    Map<String,List<Integer>> map = new HashMap<String, List<Integer>>();
    List<Integer> list = new ArrayList<Integer>();
    list.add(10);
    list.add(20);
    list.add(30);
    map.put("depts",list);
    List<Dept> ListDept= mapper.SelectForEachMap(map);
    for (Dept d: ListDept) {
        System.out.println(d.getDeptno()+";"+d.getDname()+";"+d.getLoc());
    }
    sqlSession.close();
}

---

sql+include

<!--sql片段,用于提取公共sql-->
<sql id="sqlid">
    select * from dept
</sql>
<select id="SelectForEachMap" parameterType="map" resultType="dept">
    <!--select * from dept where deptno in (10,20)-->
    <!--如果传入的是多个的时候,需要把他们封装成一个map,collection="map的key值",parameterType="map"-->
    <include refid="sqlid"></include>
    where deptno in
    <foreach collection="depts" open="(" close=")" item="no" separator=",">
        #{no}
    </foreach>
</select>