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_empwhere 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>fromt_emp
</select>

6.2 使用方式二

定義二：

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

使用方式二：

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