MyBatis动态SQL的拼接

一、多条件查询

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决 拼接SQL语句字符串时的痛点问题。

1.if

接口

 /**
     * 查询员工信息
     * @param emp
     * @return
     */
    List<Emp> getEmpByCondition(Emp emp);

SQL

if标签可通过test属性的表达式进行判断，若表达式的结果为true，则标签中的内容会执行；反之标签中的内容不会执行

在SQL语句后面 加上 1 = 1 恒等式就可以完美解决 SQL语句拼接过程中出现的末尾where 和直接拼接and的问题

<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 empName != '' ">
            and age = #{age}
        </if>
        <if test="gender != null and empName != '' ">
            and gender = #{gender}
        </if>

    </select>

测试

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

    }

2.where

where和if一般结合使用：
a>若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字
b>若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的and去掉
注意：where标签不能去掉条件最后多余的and

接口

 /**
     * 查询员工信息
     * @param emp
     * @return
     */
    List<Emp> getEmpByCondition(Emp emp);

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

测试

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

    }

3.trim

trim用于去掉或添加标签中的内容
prefix， suffix:在标签中内容前面或后面添加指定内容
prefixoverrides.suffixoverrides:在标签中内容前面或后面去掉指定内容

接口

 /**
     * 查询员工信息
     * @param emp
     * @return
     */
    List<Emp> getEmpByCondition(Emp emp);

SQL

 <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <trim prefix="where" suffixOverrides="and">
            <if test="empName != null and empName != '' ">
                emp_name = #{empName} and
            </if>
            <if test="age != null and empName != '' ">
                age = #{age} and
            </if>
            <if test="gender != null and empName != '' ">
                gender = #{gender}
            </if>
        </trim>

    </select>

测试

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

    }

4.choose、when、otherwise

相当于java中的if…else if…else
when至少设置一个,otherwise最多设置一个

接口

/**
     * 使用choose查询员工信息
     * @param emp
     * @return
     */
    List<Emp> getEmpByChoose(Emp emp);

SQL

 <!--  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 empName != '' ">
                    age = #{age}
                </when>
                <when test="gender != null and empName != '' ">
                    gender = #{gender}
                </when>
            </choose>
        </where>
    </select>

测试

@Test
    public void testGetEmpByChoose(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp = new Emp(null,"张三",20,"");
        List<Emp> list = mapper.getEmpByChoose(emp);
        list.forEach(System.out::println);

    }

5、foreach

标签注解：
collection:foreach从哪里获取数据
item :表示循环集合中的每一个数据
separator:每个数据间用什么分隔符
open:当前循环的内容以什么开始
close：当前循环的内容以什么结束

接口

/**
     * 批量添加员工信信息
     * @param emps
     */
    void insertMoreEmp(@Param("emps") List<Emp> emps);

/**
     * 批量删除的功能
     */
    void deleteMoreEmp(@Param("empIds") Integer[] empIds);

SQL

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

<!-- void deleteMoreEmp(@Param("empIds") Integer[] empIds); -->
    <delete id="deleteMoreEmp">
        delete from t_emp where emp_id in
            <foreach collection="empIds" item="empId" separator="," open="(" close=")">
                #{empId}
            </foreach>
    </delete>

第二种批量删除写法

<delete id="deleteMoreEmp">
        delete from t_emp where
        <foreach collection="empIds" item="empId" separator="or">
            emp_id  = #{empId}
        </foreach>
    </delete>

测试

@Test
    public void testInsertMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"小明1",20,"男");
        Emp emp2 = new Emp(null,"小明2",20,"男");
        Emp emp3 = new Emp(null,"小明3",20,"男");
        List<Emp> list = Arrays.asList(emp1,emp2,emp3);
        mapper.insertMoreEmp(list);

    }

@Test
    public void testDeleteMoreEmp(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Integer[] empIds = new Integer[]{6,7};
        mapper.deleteMoreEmp(empIds);

    }

6.SQL片段

sql片段，可以记录一段公共sql片段，在使用的地方通过include标签进行引入

创建

<sql id="empColumns">
        emp_Id,emp_name,age,gender,dept_id
        </sql>

引用

select <include refid="empColumns"></include> from t_emp