MyBatis动态SQL(Dynamic SQL)

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。
实际开发中可以通过以下几种标签进行动态SQL拼接。

1 if标签

根据test属性所对应的表达式计算结果决定标签中的内容是否拼接到sql中。

@Test
public void dynamicSqlMapperTest(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    DynamicSqlMapper dynamicSqlMapper = sqlSession.getMapper(DynamicSqlMapper.class);
    List<Emp> empList = dynamicSqlMapper.queryEmpList(new Emp(null, "张三", "", "男", null));
    System.out.println(empList);
}

List<Emp> queryEmpList(Emp emp);

<select id="queryEmpList" resultType="Emp">
    select * from t_emp
    where 1=1
    <if test="userName != null and userName != ''">
        and user_name = #{userName}
    </if>
    <if test="passWord != null and passWord != ''">
        and pass_word = #{passWord}
    </if>
    <if test="sex != null and sex != ''">
        and sex = #{sex}
    </if>
</select>

2 where标签

• 当where标签中有内容时，会自动生成where关键字，并将内容前多余的and或or去掉；
• 当where标签中没有内容时，不会生成where关键字，也即不会拼接条件语句；
• 注意：where标签不能去掉内容后多余的and或or

List<Emp> queryEmpListUsingWhereTag(Emp emp);

<select id="queryEmpListUsingWhereTag" resultType="Emp">
    select * from t_emp
    <where>
        <if test="userName != null and userName != ''">
            and user_name = #{userName}
        </if>
        <if test="passWord != null and passWord != ''">
            and pass_word = #{passWord}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
    </where>
</select>

3 trim标签

<trim prefix="where" suffix="" prefixOverrides="" suffixOverrides="and|or"> </trim>

• 若标签中有内容，则

  •  prefix|suffix用于设定在trim标签中的内容前面或后面添加指定的内容
    

  •  prefixOverrides|suffixOverrides: 用于设定将trim标签中的内容前面或后面去掉指定的内容
    

• 若标签中没有内容，则trim标签没有任何效果

List<Emp> empList = dynamicSqlMapper.queryEmpList(new Emp(null, "张三丰", "", "男", null));

List<Emp> queryEmpListUsingTrimTag(Emp emp);

<select id="queryEmpListUsingTrimTag" resultType="Emp">
    select * from t_emp
    <trim prefix="where" suffixOverrides="and|or">
        <if test="userName != null and userName != ''">
            user_name = #{userName} and
        </if>
        <if test="passWord != null and passWord != ''">
            pass_word = #{passWord} and
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex} and
        </if>
    </trim>
</select>

4 choose-when-otherwise标签

相当于if-else if-else，when至少有一个，otherwise最多有一个成立。

List<Emp> empList = dynamicSqlMapper.queryEmpListUsingChooseWhenTag(new Emp(null, "张三丰", "", "男", null));

List<Emp> queryEmpListUsingChooseWhenTag(Emp emp);

<select id="queryEmpListUsingChooseWhenTag" resultType="Emp">
    select * from t_emp
    <where>
        <choose>
            <when test="userName != null and userName != ''">
                user_name = #{userName}
            </when>
            <when test="passWord != null and passWord != ''">
                pass_word = #{passWord}
            </when>
            <when test="sex != null and sex != ''">
                sex = #{sex}
            </when>
            <otherwise>
                dept_id = 1
            </otherwise>
        </choose>
    </where>
</select>

5 foreach标签

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

• 批量新增

Emp emp1 = new Emp(null, "张三1", "123", "男", null);
Emp emp2 = new Emp(null, "张三2", "123", "男", null);
Emp emp3 = new Emp(null, "张三3", "123", "男", null);
Emp emp4 = new Emp(null, "张三4", "123", "男", null);
Emp emp5 = new Emp(null, "张三5", "123", "男", null);
Emp emp6 = new Emp(null, "张三6", "123", "男", null);
List<Emp> empList = Arrays.asList(emp1, emp2, emp3, emp4, emp5, emp6);
int i = dynamicSqlMapper.batchInsertEmpUsingForeach(empList);

int batchInsertEmpUsingForeach(@Param("empList") List<Emp> empList);

<insert id="batchInsertEmpUsingForeach">
    insert into t_emp values
    <foreach collection="empList" item="emp" separator=",">
        (null, #{emp.userName}, #{emp.passWord}, #{emp.sex}, null)
    </foreach>
</insert>

• 批量删除

@Test
public void batchDeleteEmpUsingForeachTest(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    DynamicSqlMapper dynamicSqlMapper = sqlSession.getMapper(DynamicSqlMapper.class);
    int i = dynamicSqlMapper.batchDeleteEmpUsingForeach(Arrays.asList("6","7","8"));
    System.out.println(i);
}

int batchDeleteEmpUsingForeach(@Param("empIdList") List<String> empIdList);

<delete id="batchDeleteEmpUsingForeach">
    delete from t_emp where id in
      <foreach collection="empIdList" item="empId" separator="," open="(" close=")">
            #{empId}
      </foreach>
</delete>

6 sql标签

6.1 使用方式一

定义一：

<sql id="commonColumnId">id, user_name, pass_word, sex, dept_id</sql>

使用方式一：

<include refid="commonColumnId"></include>

@Test
public void sqlSegmentTest(){
    SqlSession sqlSession = SqlSessionUtil.getSqlSession();
    DynamicSqlMapper dynamicSqlMapper = sqlSession.getMapper(DynamicSqlMapper.class);
    List<Emp> empList = dynamicSqlMapper.queryAllEmpInfo();
    System.out.println(empList);
}

List<Emp> queryAllEmpInfo();

<sql id="commonColumnId">id, user_name, pass_word, sex, dept_id</sql>
<select id="queryAllEmpInfo" resultType="Emp">
    select
        <include refid="commonColumnId"></include>
    from
        t_emp
</select>

6.2 使用方式二

定义二：

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

使用方式二：

<include refid="userColumns"><property name="alias" value="t1"/></include>