动态SQL
mybatis
框架的动态sql
技术是一种根据特定条件动态拼装sql
语句的功能,它存在单方是为了解决拼接sql
语句字符串时痛点问题
动态sql
就是解决我们拼接sql
语句的麻烦
1.if
标签【重点】
标签属性test
所对应的表达式决定在标签中的内容是否需要拼接到SQL
中,并且在where
之后加入一个恒成立条件1=1
在where
的后面加入恒成立条件1=1
的原因是,防止后面所有的条件都不成功,where
多余后报错,同时还防止第一个条件不成立,在where
的后面直接跟着and
造成报错
注意是:在if
标签中可以直接使用传入对象中的属性
例如:
<if test="empName!=null and empName!=''">
and 字段名 = #{传入值}【字段名是对应表中字段名否则报错】
</if>
案例:
Mapper
接口
/**
* if语句,根据传入条件查询员工信息
*/
// 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
Emp getEmpByDynamics(Emp emp);
Mapper.xml
文件
<!--
if条件动态
if:根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中
-->
<select id="getEmpByDynamics" resultType="Emp">
select * from 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
类
// if标签
@Test
public void testGetEmpByDynamics(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
Emp emp = mapper.getEmpByDynamics(new Emp(null, "海康", 21, "男", ""));
System.out.println(emp);
}
2.where
标签【重点】
where
和if
一般结合使用:
- 若
where
标签中的if
条件都不满足,则where
标签没有任何功能,即不会添加where
关键字 - 若
where
标签中的if
条件满足,则where
标签自动添加where
关键字,并将条件最前面多余的and
或or
去掉
注意是:where
标签不能去掉最后多余的and
或or
,所以不能把and
或or
写到后面
案例:
Mapper
接口
// 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
List<Emp> getEmpByDynamics(Emp emp);
Mapper.xml
文件
<!--
where标签一般与IF标签一起使用
当if标签中条件满足时,则自动生成where,并且自动拼接sql语句,将多余的and或or去掉
当if标签中条件不满足时,则不会生成where关键字
-->
<select id="getEmpByDynamics" resultType="Emp">
select * from 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>
test
类
@Test
public void testGetEmpByDynamics(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
// 返回sql:select * from emp WHERE age = ? and sex = ?
List<Emp> list = mapper.getEmpByDynamics(new Emp(null, "", 21, "男", ""));
list.forEach(emp -> System.out.println(emp));
}
3.trim
标签【重点】
trim
用去掉或添加标签中的内容
常用属性:
prefix
:在trim
标签中的内容前面添加某些内容
prefixOverrides
:在trim
标签中的内容的前面去掉某些内容
suffix
:在trim
标签中的内容后面添加某些内容
suffixOverrides
:在trim
标签中的内容后面去掉某些内容
例如:
prefix="where" 表示当`if`成功立时,则在内容前面添加`where`,如果`if`条件都不成立则不添加`where`关键字
suffixOverrides="and|or" 表示将内容中后面多余的`and`或`or`去掉
案例:
Mapper
接口
// 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
List<Emp> getEmpByDynamics(Emp emp);
Mapper.xml
文件
<!--
3、trim:
若标签中有内容时:
prefix|suffix:将trim标签中内容前面或后面添加指定内容
suffixOverrides|prefixOverrides:将trim标签中内容前面或后面去掉指定内容
若标签中没有内容时,trim标签也没有任何效果
-->
<select id="getEmpByDynamics" resultType="Emp">
select * from 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} and
</if>
<if test="email!=null and email!=''">
email=#{email}
</if>
</trim>
</select>
test
类
@Test
public void testGetEmpByDynamics(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
// 返回的sql:select * from emp where age=? and sex=?
List<Emp> list = mapper.getEmpByDynamics(new Emp(null, "", 21, "男", ""));
list.forEach(emp -> System.out.println(emp));
}
4.choose when otherwise
标签
choose
是父级标签,将when
和otherwise
嵌套着
when
:只有一个时,相当于if
,当when
有多个时,相当于if ...else if...else if
,并且when
至少要有一个
otherwise
相当于else
,并且最多只能有一个,也可以没有,就是所有条件都不满足时,则执行otherwise
中的内容
相当于if ... else ... if ... else ...
语句,只要有一个条件满足,则就是跳转该语句,如果不满足则执行最后的else
中的内容
例如:
<select id="getEmpListByChoose" resultType="Emp">
select <include refid="empColumns"></include> from t_emp
<where>
<choose>
<when test="ename != '' and ename != null">
ename = #{ename}
</when>
<when test="age != '' and age != null">
age = #{age}
</when>
<when test="sex != '' and sex != null">
sex = #{sex}
</when>
<when test="email != '' and email != null">
email = #{email}
</when>
</choose>
</where>
</select>
案例:
Mapper
接口
/**
* choose when otherwise标签,根据传入条件查询员工信息
* 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
*/
List<Emp> getEmpByChoose(Emp emp);
Mapper.xml
文件
<!--
getEmpByChoose(Emp emp);
choose when otherwise标签
-->
<select id="getEmpByChoose" resultType="Emp">
select * from 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">
email = #{email}
</when>
<otherwise>
<!--表示上述所有when都不满足时,则执行该语句-->
did = 1
</otherwise>
</choose>
</where>
</select>
test
类
// `choose when otherwise`标签
@Test
public void testGetEmpChoose(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
// 返回sql: select * from emp WHERE age = ? ,说明只要有一个条件when时,当执行when中内容,
// 并跳转该语句,如果都不满足,则执行otherwise中的内容
List<Emp> list = mapper.getEmpByChoose(new Emp(null, "", 21, "男", ""));
//
list.forEach(emp -> System.out.println(emp));
}
5.foreach
标签【非常重点】
属性:
collection
:设置要循环的数组或集合
item
:表示集合或数组中每个数据
separator
:设置循环体之间的分隔符
open
:设置foreach
标签中的内容的开始符
close
:设置foreach
标签中的内容的结束符
<foreach collection="eids" item="eid" separator="," open="(" close=")"></foreach>
collection表示集合或数组
item表示集合或数组中的每个元素
open表示以'('开始【应用在需要使用括号的sql语句中】
close表示以')'结束【应用在需要使用括号的sql语句中】
open 与 close搭配使用,表示在循环最外添加一个括号 例如:只在where ... in 中使用,不建议使用,需要括号自己手动添加
delete from emp where eid in
( <foreach collection="eids" item="eid" separator=",">
#{eid}
</foreach>
)
与下面的等价的
delete from emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
open
与 close
搭配使用,表示在循环最外添加一个括号 例如:只在where ... in
中使用,不建议使用,需要括号自己手动添加
案例:动态删除多条数据【批量删除】
Mapper
接口
/**
* 动态删除多条数据
*/
int deleteMoreByArray(@Param("eids")Integer[] eids);
Mapper.xml
文件
<!--
deleteMoreByArray(@Param("eids")Integer[] eids);批量删除多条数据
使用foreach标签
-->
<delete id="deleteMoreByArray">
<!--
方式二:批量删除
返回sql:
delete from emp where eid=? or eid=? or eid=?
-->
delete from emp where
<foreach collection="eids" item="eid" separator="or">
eid=#{eid}
</foreach>
<!--方式一:批量删除
下面代码返回sql:delete from emp where eid in ( ? , ? , ? )
delete from emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
或
delete from emp where eid in
(
<foreach collection="eids" item="eid" separator=",">
#{eid}
</foreach>
)
-->
</delete>
test
类
// 批量删除多条数据
@Test
public void testDeleteMoreByArray(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
Integer[] eids = {9,10,20};
int result = mapper.deleteMoreByArray(eids);
System.out.println(result);
}
案例2:批量添加
注意是由于在List
中存放的是每个Emp
对象,所以需要获取Emp
中的属性需要使用emp
对象名.
的形式访问
Mapper
接口
// 动态添加多条数据
int insertMoreByList(@Param("emps") List<Emp> emps);
Mapper.xml
文件
<!--
动态添加多条数据:就是批量插入多条数据
insertMoreByList(@Param("emps") List<Emp> emps);
-->
<insert id="insertMoreByList" >
insert into emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
<!--
一定不能使用 open和close搭配添加括号,因为是open和close代表整个foreach结束
添加的,就是在最外层添加的,一般只能在where ... in 情况下使用,所以在所有情况下
都不建议使用,需要括号我们自己手动添加
-->
</foreach>
</insert>
test
类
// 批量添加多条数据
@Test
public void testInsertMoreByList(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
Emp emp1 = new Emp(null, "a1", 22, "男", "123@qq.com");
List<Emp> list = Arrays.asList(emp1, emp1, emp1);
int result = mapper.insertMoreByList(list);
//返回sql: insert into emp values (null,?,?,?,?,null) , (null,?,?,?,?,null) , (null,?,?,?,?,null)
System.out.println(result);
}
6.sql
标签
sql
片段,可以记录一段公共的sql
片段,在使用的地方通过include
标签进行引入
使用的步骤是:
1.先声明sql
片段
<sql id="empColumns">
eid,ename,age,sex,did
</sql>
2.引用sql
片段
select <include refid="empColumns"></include> from t_emp
综合
Mapper
接口
/**
* 演示动态sql
*/
public interface DynamicsMapper {
/**
* if与where与trim语句,根据传入条件查询员工信息
*/
// 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
List<Emp> getEmpByDynamics(Emp emp);
/**
* choose when otherwise标签,根据传入条件查询员工信息
* 由于不知道传入是邮箱还是年龄还是性别等,所以直接将传入的信息封装成对象
*/
List<Emp> getEmpByChoose(Emp emp);
/**
* 动态删除多条数据
*/
int deleteMoreByArray(@Param("eids")Integer[] eids);
// 动态添加多条数据
int insertMoreByList(@Param("emps") List<Emp> emps);
}
Mapper.xml
文件
<mapper namespace="com.haikang.mybatis.mapper.DynamicsMapper">
<!--
动态添加多条数据:就是批量插入多条数据
insertMoreByList(@Param("emps") List<Emp> emps);
-->
<insert id="insertMoreByList" >
insert into emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
<!--
一定不能使用 open和close搭配添加括号,因为是open和close代表整个foreach结束
添加的,就是在最外层添加的,一般只能在where ... in 情况下使用,所以在所有情况下
都不建议使用,需要括号我们自己手动添加
-->
</foreach>
</insert>
<!--
deleteMoreByArray(@Param("eids")Integer[] eids);批量删除多条数据
使用foreach标签
-->
<delete id="deleteMoreByArray">
<!--
方式二:批量删除
返回sql:
delete from emp where eid=? or eid=? or eid=?
-->
delete from emp where
<foreach collection="eids" item="eid" separator="or">
eid=#{eid}
</foreach>
<!--方式一:批量删除
下面代码返回sql:delete from emp where eid in ( ? , ? , ? )
delete from emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
或
delete from emp where eid in
(
<foreach collection="eids" item="eid" separator=",">
#{eid}
</foreach>
)
-->
</delete>
<!--
getEmpByChoose(Emp emp);
choose when otherwise标签
-->
<select id="getEmpByChoose" resultType="Emp">
select * from 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">
email = #{email}
</when>
<otherwise>
<!--表示上述所有when都不满足时,则执行该语句-->
did = 1
</otherwise>
</choose>
</where>
</select>
<!--
3、trim:
若标签中有内容时:
prefix|suffix:将trim标签中内容前面或后面添加指定内容
suffixOverrides|prefixOverrides:将trim标签中内容前面或后面去掉指定内容
若标签中没有内容时,trim标签也没有任何效果
-->
<select id="getEmpByDynamics" resultType="Emp">
select * from 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} and
</if>
<if test="email!=null and email!=''">
email=#{email}
</if>
</trim>
</select>
<!--
where标签一般与IF标签一起使用
当if标签中条件满足时,则自动生成where,并且自动拼接sql语句,将多余的and或or去掉
当if标签中条件不满足时,则不会生成where关键字
-->
<select id="getEmpByDynamicsTwo" resultType="Emp">
select * from 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>
<!--
if条件动态
if:根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中
-->
<select id="getEmpByDynamicsOnd" resultType="Emp">
select * from 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>
</mapper>
test
类
public class TestDynamicsMapper {
// `choose when otherwise`标签
@Test
public void testGetEmpChoose(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
// 返回sql: select * from emp WHERE age = ? ,说明只要有一个条件when时,当执行when中内容,
// 并跳转该语句,如果都不满足,则执行otherwise中的内容
List<Emp> list = mapper.getEmpByChoose(new Emp(null, "", 21, "男", ""));
//
list.forEach(emp -> System.out.println(emp));
}
// if标签与where标签与trim标签
@Test
public void testGetEmpByDynamics(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
// 返回的sql:select * from emp where age=? and sex=?
List<Emp> list = mapper.getEmpByDynamics(new Emp(null, "", 21, "男", ""));
list.forEach(emp -> System.out.println(emp));
}
// 批量删除多条数据
@Test
public void testDeleteMoreByArray(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
Integer[] eids = {9,10,20};
int result = mapper.deleteMoreByArray(eids);
System.out.println(result);
}
// 批量添加多条数据
@Test
public void testInsertMoreByList(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicsMapper mapper = sqlSession.getMapper(DynamicsMapper.class);
Emp emp1 = new Emp(null, "a1", 22, "男", "123@qq.com");
List<Emp> list = Arrays.asList(emp1, emp1, emp1);
int result = mapper.insertMoreByList(list);
//返回sql: insert into emp values (null,?,?,?,?,null) , (null,?,?,?,?,null) , (null,?,?,?,?,null)
System.out.println(result);
}
}