Mybatis-dynamic-SQL

Mybatis dynamic SQL

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

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

if & where & trim & set

if 、 where 主要用于简单的判断

<!-- 方式一 -->
<mapper namespace="com.shufang.mapper.EmployeeMapper">

    <!-- public List<Employee> getEmpsByMultiFeature(Emmployee emp);   -->
    <select id="getEmpsByMultiFeature" resultType="Employee">
        select empid,name,gender,age,deptid
        from hr_emp
        where 1 = 1
        <if test="empId != null"> and empid = #{empId}</if>
        <if test="name != null and name != ''"> and name = #{name} </if>
        <if test="gender == 1 or gender == 2">and gender = #{gender} </if>
        <if test="age != null and age &lt; 100"> and age = #{age} </if>
    </select>
</mapper>


<!--方式二-->
<!-- public List<Employee> getEmpsByMultiFeature(Emmployee emp);   -->
<select id="getEmpsByMultiFeature" resultType="Employee">
        select empid,name,gender,age,deptid
        from hr_emp
        <where>
          <if test="empId != null"> empid = #{empId}</if>
          <if test="name != null and name != ''"> and name = #{name} </if>
          <if test="gender == 1 or gender == 2">and gender = #{gender} </if>
          <if test="age != null and age &lt; 100"> and age = #{age} </if>
        </where>
</select>

<!--方式三-->
<!-- public List<Employee> getEmpsByMultiFeature(Emmployee emp);   -->
    <select id="getEmpsByMultiFeature" resultType="Employee">
        select empid,name,gender,age,deptid
        from hr_emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empId != null"> empid = #{empId} and </if>
            <if test="name != null and name != ''">  name = #{name} and</if>
            <if test="gender == 1 or gender == 2"> gender = #{gender} or</if>
            <if test="age != null and age &lt; 100"> age = #{age} </if>
        </trim>
    </select>

public class TestSQL {
    /**
     * 1.<if test=""><if/>: 根据传入的参数条件，筛选不同的SQL语句进行拼接
     * 2.<where> ... demo block such as <if></if> ...</where> 给SQL加上where 并且去掉where后面多余的 and
     * 3.<trim prefix ="where" suffixOverrides = "and|or">... demo block such as <if></if> ...</trim>  给SQL添加前缀，并且去掉后缀多余的and 或者 or
     * 4.<set></set>: 主要是解决update 在某些参数没有传入的时候，去掉多余的·,· 以及加上 set关键字
     * 总结：-----> 实际工作中还是<trim></trim>用得最多
     * @throws IOException
     */
    @Test
    public  void testSQL() throws IOException {

        SqlSessionFactory sqlSessionFactory = MybatisUtil.getSqlSession("mybatis-config.xml");
        //TODO 这里就是测试的方法的调用
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

        Employee employee = new Employee();
//        employee.setEmpId(1010);
        employee.setName("SuperMan");
        employee.setGender(2);
//        employee.setAge(26);
        List<Employee> emps = mapper.getEmpsByMultiFeature(employee);

        for (Employee emp : emps) {
            System.out.println(emp);
        }

        MybatisUtil.closeSession(sqlSession);
    }

}

Choose & when & otherwise

when 和 otherwise久类似于java中的if else if else…的逻辑，可以根据传入的参数不同，查询不同的SQL,

只有一个条件的会执行

    <!-- public List<Employee> getEmpsByOneFeature(Employee emp);   -->
    <select id="getEmpsByOneFeature" resultType="Employee">
        select empid,name,gender,age,deptid
        from hr_emp
        <where>
            <choose>
                <when test="empId != null">
                    empid = #{empId}
                </when>
                <when test="name != null and name != ''">
                    name = #{name}
                </when>
                <when test="gender == 1 or gender == 2">
                    gender = #{gender}
                </when>
                <otherwise>
                    age = #{age}
                </otherwise>
            </choose>
        </where>
    </select>

foreach

相关参数解读

<foreach collection="ids" open="empid" close="" separator="," item="" index="">
</foreach>
collection: 代表传过来的参数集合或者数组
item： 集合中每个元素的别名
open：设置循环体的开始内容
close： 设置循环体的结束内容
index： 若遍历的是list，那么index代表下标，如果便利的是map，那么index代表的是key
separator：设置每一个循环之间元素的分隔符

如何实现批量删除，其实有2种方式

方式一

//通过一个集合传入id，实现批量删除
void deleteBatch(@Param("ids") String ids);

<delete id="deleteBatch">
        delete from hr_emp where empid in (${ids})
</delete>

方式二

    <!--    void deleteMoreByList(List<String> ids);-->
    <delete id="deleteMoreByList">
        delete from hr_emp where empid in 
        <foreach collection="ids"  separator="," item="empid" open="(" close=")"> 
                #{empid}
        </foreach>
    </delete>

方式三

<!--    void deleteByOr(@Param("ids") Array<String> ids);-->
    <delete id="deleteByOr">
        delete from hr_emp
        <where>
            <foreach collection="ids" item="empid" separator="or">
                empid = #{empid}
            </foreach>
        </where>

    </delete>

sql&include

<!--定义公用的SQL片段-->
<sql id="empColumns"> select empid,name,gender,age,deptid from hr_emp</sql>

<!--通过include标签引用SQL的id，进行SQL的拼接-->
<select id="getEmpsByMultiFeature" resultType="Employee">
    <include refid="empColumns"/>
    <trim prefix="where" suffixOverrides="and|or">
      <if test="empId != null">empid = #{empId} and</if>
      <if test="name != null and name != ''">name = #{name} and</if>
      <if test="gender == 1 or gender == 2">gender = #{gender} or</if>
      <if test="age != null and age &lt;= 100">age = #{age}</if>
    </trim>
</select>

mybatis批量操作

delete

delete  from hr_emp where empid in ()
delete from hr_emp where empid = 1 or empid = 2....

select

select xxx from hr_emp where empid in ()
select xxx from hr_emp where empid = 1 or empid = 2 .......

update

-- 1、把每条数据修改成相同内容
update hr_emp set xx= ... where empid = 1 or empid = 2 .......
update hr_emp set xx= ... where empid in ()

-- 2、把每条数据修改成相同的数据
update hr_emp set xx= .... where empid = ?;
update hr_emp set xx= .... where empid = ?;
update hr_emp set xx= .... where empid = ?;
.....

insert

insert into hr_emp values (),(),(),().....

    <!-- void insertBatch(List<Employee> emps);批量插入   -->
    <insert id="insertBatch">
        insert into hr_emp(name,gender,age) values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.name},#{emp.gender},${emp.age})
        </foreach>
    </insert>

    <!-- void updateBatch(@Param("emps) Employee[] emps); 批量修改 
    jdbc.username=root
    jdbc.password=888888
    #允许JDBC在预编译的过程中能执行多个SQL语句，只有update的时候才有可能会用到
    jdbc.url=jdbc:mysql://localhost:3306/hello?allowMutilQueries=true
    jdbc.driver=com.mysql.cj.jdbc.Driver
		-->

    <update id="updateBatch">
        <foreach collection="emps" item="emp" >
            update hr_emp set name = #{emp.name} ,gender = #{emp.gender} ,age = #{emp.age}
            where empid = #{emp.empId};
        </foreach>
    </update>