视频教程链接:https://www.bilibili.com/video/BV1VP4y1c7j7?p=56&spm_id_from=pageDriver
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。
1、if
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
应用场景:多条件查询
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 多条件查询
*/
List<Emp> getEmpByCondition(Emp emp);
}
DynamicSqlMapper.xml
<!-- List<Emp> getEmpByCondition(Emp emp);-->
<!-- 加上1=1使得:即使emp_name为空,也不会导致sql语句变成:where and xxx-->
<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="email != null and email != ''">
and email = #{email}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</select>
测试类:
/**
* 动态sql
* 1: if: 根据标签中test属性所对应的内容决定标签中的内容是否拼接在sql语句中
*/
@Test
public void testGetEmpByCondition(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
// 各信息都不为null/空字符串
List<Emp> emp1 = mapper.getEmpByCondition(new Emp(null, "Apple", 22, "女", "123@gmail.com"));
// 中间存在查询出来是空,可能导致"select * from t_emp where emp_name= ? and and sex = ?..."的and和and在一起的情况
List<Emp> emp2 = mapper.getEmpByCondition(new Emp(null, "Apple", null, "女", "123@gmail.com"));
// 第一个查询条件为空字符串,可能导致"select * from t_emp where and age = ? and ..."的where和and在一起的情况
List<Emp> emp3 = mapper.getEmpByCondition(new Emp(null, null, null, "女", "123@gmail.com")); System.out.println(emp1);
System.out.println(emp1);
System.out.println(emp2);
System.out.println(emp3);
}
2、where
应用场景:多条件查询
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 多条件查询
*/
List<Emp> getEmpByCondition(Emp emp);
}
DynamicSqlMapper.xml
<!-- where标签中,如果有内容,则添加关键字,如果没有内容,则把and/or去掉-->
<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="email != null and email != ''">
and email = #{email}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
测试类:
/**
* 2、where:
* 当where标签中有内容时,会自动生成where关键字,并将内容前多余的and或or去掉
* 当where标签中没有内容时,此时where标签没有任何效果
* 注意:where标签不能将其中内容后面多余的and或or去掉
*/
@Test
public void testGetEmpByCondition2(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
// 各信息都不为null/空字符串
List<Emp> emp1 = mapper.getEmpByCondition(new Emp(null, "Apple", 22, "女", "123@gmail.com"));
// 中间存在查询出来是空,可能导致"select * from t_emp where emp_name= ? and and sex = ?..."的and和and在一起的情况
List<Emp> emp2 = mapper.getEmpByCondition(new Emp(null, "Apple", null, "女", "123@gmail.com"));
// 第一个查询条件为空字符串,可能导致"select * from t_emp where and age = ? and ..."的where和and在一起的情况
List<Emp> emp3 = mapper.getEmpByCondition(new Emp(null, null, null, "女", "123@gmail.com")); System.out.println(emp1);
System.out.println(emp1);
System.out.println(emp2);
System.out.println(emp3);
}
3、trim
应用场景:多条件查询
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 多条件查询
*/
List<Emp> getEmpByCondition(Emp emp);
}
DynamicSqlMapper.xml
<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} or
</if>
<if test="email != null and email != ''">
email = #{email} and
</if>
<if test="sex != null and sex != ''">
sex = #{sex}
</if>
</trim>
</select>
测试类:
/**
* 3、trim
* 若标签中有内容时:
* prefix/suffix 在trim标签中内容前面或后面 去添加指定内容
* prefixOverrides/suffixOverrides 在trim标签中内容前面或后面 去删掉指定内容
* 若标签中没有内容时:trim标签也没有任何效果
*/
@Test
public void testGetEmpByCondition3(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
// 各信息都不为null/空字符串
List<Emp> emp1 = mapper.getEmpByCondition(new Emp(null, "Apple", 22, "女", "123@gmail.com"));
// 中间存在查询出来是空,可能导致"select * from t_emp where emp_name= ? and and sex = ?..."的and和and在一起的情况
List<Emp> emp2 = mapper.getEmpByCondition(new Emp(null, "Apple", null, "女", "123@gmail.com"));
// 第一个查询条件为空字符串,可能导致"select * from t_emp where and age = ? and ..."的where和and在一起的情况
List<Emp> emp3 = mapper.getEmpByCondition(new Emp(null, null, null, "女", "123@gmail.com"));
System.out.println(emp1);
System.out.println(emp2);
System.out.println(emp3);
}
4、choose-when-otherwise
choose、when、otherwise相当于if…else if…else
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 测试choose when otherwise
*/
List<Emp> getEmpByChoose(Emp emp);
}
DynamicSqlMapper.xml
<!-- 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 = 2
</otherwise>
</choose>
</where>
</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);
// 各信息都不为null/空字符串
List<Emp> emp1 = mapper.getEmpByChoose(new Emp(null, "Apple", 22, "女", "123@gmail.com"));
// 中间存在查询出来是空,可能导致"select * from t_emp where emp_name= ? and and sex = ?..."的and和and在一起的情况
List<Emp> emp2 = mapper.getEmpByChoose(new Emp(null, "Apple", null, "女", "123@gmail.com"));
// 第一个查询条件为空字符串,可能导致"select * from t_emp where and age = ? and ..."的where和and在一起的情况
List<Emp> emp3 = mapper.getEmpByChoose(new Emp(null, null, null, "", ""));
System.out.println(emp1);
System.out.println(emp2);
System.out.println(emp3);
}
5、foreach
应用场景1: 通过数组实现批量删除
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 通过数组实现批量删除
*/
int deleteMoreByArray(@Param("eids") Integer[] eids);
}
DynamicSqlMapper.xml
方法一:
<!-- int deleteMoreByArray(Integer[] eids);-->
<!-- 没加@Param时,
报错:Parameter 'eids' not found. Available parameters are [array, arg0]
因此最好都加上@Param-->
<!-- 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>
方法二:
<!-- int deleteMoreByArray(Integer[] eids);-->
<delete id="deleteMoreByArray">
<!--方法2:-->
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
测试类:
/**
* 5、foreach
*/
@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);
}
应用场景2: 通过list集合实现批量添加
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 通过list集合实现批量添加
*/
int insertMoreByList(@Param("emps") List<Emp> emps);
}
DynamicSqlMapper.xml
<!-- int insertMoreByList(List<Emp> emps);-->
<!-- 不加注解会报错:Parameter 'emps' not found. Available parameters are [arg0, collection, list]-->
<!-- 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>
测试类:
/**
* 5、foreach
* collection 需要循环的数组或集合
* item 表示数组或集合中的每一个数据
* separator 循环体之间的分隔符
* open foreach标签所循环的所有内容的开始符
* close foreach标签所循环的所有内容的结束符
*/
@Test
public void testInsertMoreByList(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null, "Mary", 23, "女", "11111@qq.com");
Emp emp2 = new Emp(null, "Linda", 23, "女", "1144111@qq.com");
Emp emp3 = new Emp(null, "Jackoline", 23, "女", "1122111@qq.com");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
System.out.println(mapper.insertMoreByList(emps));
}
6、sql片段 include
应用场景:获取所有员工的某些信息
DynamicSqlMapper接口
public interface DynamicSQLMapper {
/**
* 获取所有员工的某些信息
*/
List<Emp> getAllEmpNameAndAge();
}
DynamicSqlMapper.xml
<!-- sql片段-->
<!-- List<Emp> getAllEmpNameAndAge();-->
<sql id="empColumns">emp_name, age</sql>
<select id="getAllEmpNameAndAge" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
</select>
测试类:
/**
* 6、sql片段
* 设置:
* <sql id="empColumns">emp_name, age</sql>
* 使用:
* select <include refid="empColumns"></include> from t_emp
*/
@Test
public void testGetAllEmp(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
System.out.println(mapper.getAllEmpNameAndAge());
}