动态SQL

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

mybatis学习记录
09. MyBatis 动态SQL（常用 1，2，5）

---

提示：以下是本篇文章正文内容，下面案例可供参考

动态SQL（常用 1，2，5）

1,2,3,6点的测试方法和mapper接口都是一样的

测试方法：(DynamicSQLMapperTest)

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

DynamicSQLMapper接口：

public interface DynamicSQLMapper {
	List<Emp> getEmpByCondition(Emp emp);
}

1.if

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

 映射文件：(DynamicSQLMapper.xml)

    <!--List<Emp> getEmpByCondition(Emp emp);-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp where 1=1
        <if test="empName != null and empName != ''">
            and 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>

2.where

$where$ :当where标签中有内容时，会生成where关键字，并且将内容前多余的and或or去掉；
当where标签中没有内容时，不会生成where关键字
注意：不能将内容后面多余的and或or去掉

 映射文件：

    <!--List<Emp> getEmpByCondition(Emp emp);-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <where>
            <if test="empName != null and empName != ''">
                and 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>

3.trim

trim：用于去掉或添加标签中的内容
常用属性：(标签内有内容时生效，没有内容时trim标签也不生效)
prefix：在trim标签中的内容的前面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffix：在trim标签中的内容的后面添加某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容

 映射文件：

    <!--List<Emp> getEmpByCondition(Emp emp);-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_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>

4.choose、when、otherwise

相当于if…else if…else；when至少有一个，otherwise最多只能有一个

 测试方法：

	@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);
    }

 mapper接口：

List<Emp> getEmpByChoose(Emp emp);

 映射文件：

    <!--List<Emp> getEmpByChoose(Emp emp);-->
    <select id="getEmpByChoose" resultType="Emp">
        select * from t_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>

5.foreach

$foreach$ ：可实现批量删除或添加
属性:
collection：设置要循环的数组或集合
item：表示集合或数组中的每一个数据
separator：设置循环体之间的分隔符
open：设置foreach标签中的内容的开始符
close：设置foreach标签中的内容的结束符

• a>通过数组实现批量删除

 测试方法：

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

 mapper接口：

int deleteMoreByArray(@Param("eids") Integer[] eids);

 映射文件：
第一种方式：where eid in ( ? , ? , ? )

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

第二种方式：where eid = ? or eid = ? or eid = ?

     <!--int deleteMoreByArray(@Param("eids") Integer[] eids);-->
    <delete id="deleteMoreByArray">
        delete from t_emp where
        <foreach collection="eids" item="eid" separator="or">
            eid = #{eid}
        </foreach>
    </delete>

• b>通过list集合实现批量添加

 测试方法：

	@Test
    public void testInsertMoreByList(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"a",23,"男","22555633@qq.com");
        Emp emp2 = new Emp(null, "c", 15, "女", "1546789@qq.com");
        Emp emp3 = new Emp(null, "f", 20, "女", "5891239@qq.com");
        List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
        int result = mapper.insertMoreByList(emps);
        System.out.println(result);
    }

 mapper接口：

int insertMoreByList(@Param("emps") List<Emp> emps);

 映射文件：

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

6.sql标签

定义：<sql id="empColumns">eid,emp_name,age,sex,email</sql>
引用：<include refid="empColumns"></include>
(测试方法和mapper接口同1)

 映射文件：

    <!--sql标签定义-->
    <sql id="empColumns">eid,emp_name,age,sex,email</sql>
    <!--sql标签使用，以where为例-->
    <select id="getEmpByConditionTwo" resultType="Emp">
         select <include refid="empColumns"></include> from t_emp
        <where>
            <if test="empName != null and empName != ''">
                and 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>

---

总结