Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题
一、if
if标签可通过test属性(即传递过来的数据)的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
- mapper接口中(DynamicSQLMapper)
返回类型为Emp
//多条件查询
List<Emp> getEmpByCondition(Emp emp);
- mapper映射文件中(DynamicSQLMapper.xml)
<!--List<Emp> getEmpByCondition(Emp emp);-->
<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="sex != null and sex !=''">
and sex = #{sex}
</if>
<if test="email != null and email !=''">
and email = #{email}
</if>
</select>
- test测试文件中(ResultMapTest)
//多条件查询
@Test
public void testGetEmpByCondition(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> list = mapper.getEmpByCondition(new Emp(null,"张三",23,"男","12@qq.com"));
System.out.println(list);
}
二、where
- where和if一般结合使用:
- 若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
- 若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and/or去掉
- mapper映射文件中(DynamicSQLMapper.xml)
<!--List<Emp> getEmpByCondition(Emp emp);-->
<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="sex != null and sex !=''">
and sex = #{sex}
</if>
<if test="email != null and email !=''">
and email = #{email}
</if>
</where>
</select>
效果:
拼接sql语句时第一个and被去掉
Preparing: select * from t_emp WHERE emp_name = ? and age = ? and sex = ? and email = ?
三、trim
- trim用于去掉或添加标签中的内容
- 常用属性
- prefix:在trim标签中的内容的前面添加某些内容
- suffix:在trim标签中的内容的后面添加某些内容
- prefixOverrides:在trim标签中的内容的前面去掉某些内容
- suffixOverrides:在trim标签中的内容的后面去掉某些内容
- 若trim中的标签都不满足条件,则trim标签没有任何效果,也就是只剩下
select * from t_emp
- mapper映射文件中(DynamicSQLMapper.xml)
<!--List<Emp> getEmpByConditionTrim(Emp emp);-->
<select id="getEmpByConditionTrim" 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} and
</if>
<if test="sex != null and sex !=''">
sex = #{sex} or
</if>
<if test="email != null and email !=''">
email = #{email} and
</if>
</trim>
</select>
效果:
拼接sql语句时最后一个and被去掉
Preparing: select * from t_emp where emp_name = ? and age = ? and sex = ? or email = ?
四、choose、when、otherwise
choose、when、otherwise
相当于if...else if..else
- when至少要有一个,otherwise至多只有一个
- mapper接口中(DynamicSQLMapper)
返回类型为Emp
//测试choose、when、otherwise
List<Emp> getEmpByChoose(Emp emp);
- mapper映射文件中(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 = 1
</otherwise>
</choose>
</where>
</select>
- test测试文件中(ResultMapTest)
//测试choose、when、otherwise
@Test
public void getEmpByChooseTest() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> list = mapper.getEmpByChoose(new Emp(null,"张三",23,"男","12@qq.com"));
System.out.println(list);
}
效果:
拼接sql语句时,依次执行,直到满足when条件才停止拼接。否则默认拼接otherwise语句
Preparing: select * from t_emp WHERE emp_name = ?
五、foreach(数据和集合)
- 属性:
- collection:设置要循环的数组或集合
- item:表示集合或数组中的每一个数据
- separator:设置循环体之间的分隔符,分隔符前后默认有一个空格,如
,
- open:设置foreach标签中的内容的开始符
- close:设置foreach标签中的内容的结束符
5.1 批量添加
- mapper接口中(DynamicSQLMapper)
返回类型为int,注意要使用@Param标识参数
//通过list集合来实现批量添加
int insertMoreByList(@Param("emps") List<Emp> emps);
- mapper映射文件中(DynamicSQLMapper.xml)
- separator:分割符
<!--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>
- test测试文件中(ResultMapTest)
//通过list集合来实现批量添加
@Test
public void insertMoreByList() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null,"a",1,"男","123@321.com");
Emp emp2 = new Emp(null,"b",1,"男","123@321.com");
Emp emp3 = new Emp(null,"c",1,"男","123@321.com");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
int result = mapper.insertMoreByList(emps);
System.out.println(result);
}
效果:
DEBUG 04-20 14:11:37,853 ==> Preparing: insert into t_emp values (null,?,?,?,?,null) , (null,?,?,?,?,null) , (null,?,?,?,?,null) (BaseJdbcLogger.java:137)
DEBUG 04-20 14:11:37,907 ==> Parameters: a(String), 1(Integer), 男(String), 123@321.com(String), b(String), 1(Integer), 男(String), 123@321.com(String), c(String), 1(Integer), 男(String), 123@321.com(String) (BaseJdbcLogger.java:137)
DEBUG 04-20 14:11:38,011 <== Updates: 3 (BaseJdbcLogger.java:137)
3
5.2 批量删除
- mapper接口中(DynamicSQLMapper)
返回类型为int,注意要使用@Param标识参数
//通过数组来实现批量删除
int deleteMoreByArray(@Param("eids")Integer[] eids);
- mapper映射文件中(DynamicSQLMapper.xml)
- separator:分割符
<!--int deleteMoreByArray(@Param("eids")Integer[] eids);-->
<delete id="deleteMoreByArray">
delete from t_emp where eid in (
<foreach collection="eids" item="eid" separator=",">
#{eid}
</foreach>
)
</delete>
- test测试文件中(ResultMapTest)
//测试deleteMoreByArray
@Test
public void TestDeleteMoreByArray() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
int result = mapper.deleteMoreByArray(new Integer[]{6,7,8});
System.out.println(result);
}
效果:
DEBUG 04-20 13:53:21,816 ==> Preparing: delete from t_emp where eid in ( ? , ? , ? ) (BaseJdbcLogger.java:137)
DEBUG 04-20 13:53:21,864 ==> Parameters: 6(Integer), 7(Integer), 8(Integer) (BaseJdbcLogger.java:137)
DEBUG 04-20 13:53:21,976 <== Updates: 3 (BaseJdbcLogger.java:137)
3
六、SQL片段
- sql片段,可以记录一段公共sql片段,在使用的地方通过include标签进行引入
- 声明sql片段:
<sql>
标签
- 比如引入sql字段:
<sql id="empColumns">eid,emp_name,age,sex,email</sql>
- 引用sql片段:
<include>
标签
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
</select>
本文主要参考:
【尚硅谷】2022版MyBatis教程(细致全面,快速上手)