Mybatis10-Mybatis的动态SQL

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

1、if

if：根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中

1.1、Mapper接口中的方法

/**
 * 多条件查询
 */
List<Emp> getEmpByCondition(Emp emp);

1.2、Mapper接口对应的映射文件

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp where 
    <if test="empName != null and empName != ''">
        emp_name = #{empName}
    </if>
    <if test="age != null and age != ''">
        and age = #{age}
    </if>
    <if test="sex != null and sex != ''">
        and sex = #{sex}
    </if>
    <if test="email != null and email != ''">
        and email = #{email}
    </if>
</select>

1.3、测试方法及其运行结果

@Test
public void testGetEmpByCondition() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByCondition(new Emp(null, "张三", 23, "男", "123@qq.com"));
    list.forEach(System.out :: println);
}

• 正常运行
  
• 修改代码

List<Emp> list = mapper.getEmpByCondition(new Emp(null, "", 23, "男", "123@qq.com"));

报错：

观察SQL得出，出现由于empName为""，导致出现多余的and。
解决方案：
修改映射文件

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp where 1 = 1
    <if test="empName != null and empName != ''">
        emp_name = #{empName}
    </if>
    <if test="age != null and age != ''">
        and age = #{age}
    </if>
    <if test="sex != null and sex != ''">
        and sex = #{sex}
    </if>
    <if test="email != null and email != ''">
        and email = #{email}
    </if>
</select>

• 再次修改测试方法

List<Emp> list = mapper.getEmpByCondition(new Emp(null, "", null, "", ""));

运行结果

2、where

where：
  当where标签中有内容时，会自动生成where关键字，并且将内容前多余的and或or去掉
  当where标签中没有内容时，此时where标签没有任何效果
  注意：where标签不能将其中内容后面多余的and或or去掉

2.1、Mapper接口中的方法

/**
 * 多条件查询
 */
List<Emp> getEmpByCondition(Emp emp);

2.2、Mapper接口对应的映射文件

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp
    <where>
        <if test="empName != null and empName != ''">
            emp_name = #{empName}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
    </where>
</select>

2.3、测试方法及其运行结果

• 输入全为null或者“”

@Test
public void testGetEmpByCondition() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByCondition(new Emp(null, "", null, "", ""));
    list.forEach(System.out :: println);
}

• 修改映射文件

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp
    <where>
        <if test="empName != null and empName != ''">
            emp_name = #{empName}
        </if>
        <if test="age != null and age != ''">
            or age = #{age}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
    </where>
</select>

• 后面存在多余的and或or

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp
    <where>
        <if test="empName != null and empName != ''">
            emp_name = #{empName} and
        </if>
        <if test="age != null and age != ''">
            age = #{age} or
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
    </where>
</select>

3、trim

trim：
若标签中有内容时：
  prefix | suffix：将trim标签中内容前面或后面添加指定内容
  prefixOverrides | suffixOverrides：将trim标签中内容前面或后面去掉指定内容

3.1、Mapper接口中的方法

/**
 * 多条件查询
 */
List<Emp> getEmpByCondition(Emp emp);

3.2、Mapper接口对应的映射文件

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select * from mybatis.emp
    <trim prefix="where" suffixOverrides="and|or">
        <if test="empName != null and empName != ''">
            emp_name = #{empName} and
        </if>
        <if test="age != null and age != ''">
            age = #{age} and
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex} and
        </if>
        <if test="email != null and email != ''">
            email = #{email}
        </if>
    </trim>
</select>

3.3、测试方法及其运行结果

• 正常运行

@Test
public void testGetEmpByCondition() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByCondition(new Emp(null, "张三", 23, "", ""));
    list.forEach(System.out :: println);
}

• 输入全为null或“”时

List<Emp> list = mapper.getEmpByCondition(new Emp(null, "", null, "", ""));

4、choose、when、otherwise

choose、when、otherwise，相当于if…else if…else
  when至少要有一个，otherwise最多只能有1个

4.1、Mapper接口中的方法

/**
 * 测试choose、when、otherwise
 */
List<Emp> getEmpByChoose(Emp emp);

4.2、Mapper接口对应的映射文件

<!-- List<Emp> getEmpByChoose(Emp emp); -->
<select id="getEmpByChoose" resultType="Emp">
    select * from mybatis.emp
    <where>
        <choose>
            <when test="empName != null and empName != ''">
                emp_name = #{empName}
            </when>
            <when test="age != null and age != ''">
                age = #{age}
            </when>
            <when test="sex != null and sex != ''">
                sex = #{sex}
            </when>
            <when test="email != null and email != ''">
                email = #{email}
            </when>
            <otherwise>
                did = 1
            </otherwise>
        </choose>
    </where>
</select>

4.3、测试方法及其运行结果

• 测试方法

@Test
public void testGetEmpByChoose() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByChoose(new Emp(null, "张三", 23, "男", "123@qq.com"));
    System.out.println(list);
}

运行结果

• 当输入全为null或者“”

@Test
public void testGetEmpByChoose() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByChoose(new Emp(null, "", null, "", ""));
    System.out.println(list);
}

运行结果

5、foreach

foreach
  collection：设置需要循环的数组或集合
  item：表示数组或集合中的每一个数据
  separator：循环体之间的分隔符
  open：foreach标签所循环的所有内容的开始符
  close：foreach标签所循环的所有内容的结束符

5.1、foreach（一）：批量删除

5.1.1、Mapper接口中的方法

/**
 * 通过数组实现批量删除
 */
int deleteMoreByArray(@Param("eids") Integer[] eids);

5.1.2、Mapper接口对应的映射文件

• 通过where id in (x,x,x)的形式

<!-- int deleteMoreByArray(@Param("eids") Integer[] eids); -->
<delete id="deleteMoreByArray">
    delete from mybatis.emp where eid in
    <foreach collection="eids" item="eid" separator="," open="(" close=")">
        #{eid}
    </foreach>
</delete>

• 通过where id = xxx or id =xxx的形式

<!-- int deleteMoreByArray(@Param("eids") Integer[] eids); -->
<delete id="deleteMoreByArray">
    delete from mybatis.emp where
    <foreach collection="eids" item="eid" separator="or">
        eid = #{eid}
    </foreach>
    <!--delete from mybatis.emp where eid in
    <foreach collection="eids" item="eid" separator="," open="(" close=")">
        #{eid}
    </foreach>-->
</delete>

5.1.3、测试方法及其运行结果

• 测试代码

@Test
public void testDeleteMoreByArray() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    int result = mapper.deleteMoreByArray(new Integer[]{6,7,8});
    System.out.println(result);
}

• 运行结果
  
  

5.2、foreach（二）：批量添加

5.2.1、Mapper接口中的方法

/**
 * 通过list集合实现批量添加
 */
int insertMoreByList(@Param("emps") List<Emp> emps);

5.2.2、Mapper接口对应的映射文件

<!-- int insertMoreByList(@Param("emps") List<Emp> emps); -->
<insert id="insertMoreByList">
    insert into mybatis.emp values 
    <foreach collection="emps" item="emp" separator=",">
        (null, #{emp.empName}, #{emp.age}, #{emp.sex}, #{emp.email}, null)
    </foreach>
</insert>

5.2.3、测试代码及其运行结果

• 测试代码

@Test
public void testInsertMoreByList() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    Emp emp1 = new Emp(null, "a1", 23, "男", "123@qq.com");
    Emp emp2 = new Emp(null, "a1", 23, "男", "123@qq.com");
    Emp emp3 = new Emp(null, "a1", 23, "男", "123@qq.com");
    List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
    System.out.println(mapper.insertMoreByList(emps));
}

• 运行结果
  

6、SQL片段

sql标签
  设置SQL片段：< sql id=“empColumns”>eid, emp_name, age, sex, email< /sql>
  引用SQL片段：< include refid=“empColumns”>< /include>

6.1、Mapper接口中的方法

/**
 * 多条件查询
 */
List<Emp> getEmpByCondition(Emp emp);

6.2、Mapper接口对应的映射文件

<sql id="empColumns">eid, emp_name, age, sex, email</sql>

<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
    select <include refid="empColumns"></include> from mybatis.emp
    <trim prefix="where" suffixOverrides="and|or">
        <if test="empName != null and empName != ''">
            emp_name = #{empName} and
        </if>
        <if test="age != null and age != ''">
            age = #{age} and
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex} and
        </if>
        <if test="email != null and email != ''">
            email = #{email}
        </if>
    </trim>
</select>

6.3、测试方法及其运行结果

• 测试方法

@Test
public void testGetEmpByCondition() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    List<Emp> list = mapper.getEmpByCondition(new Emp(null, "", null, "", ""));
    list.forEach(System.out :: println);
}

• 运行结果