目录
5.1.1、方法一:使用 wher .. in(?,?,?)
5.1.2、方法二:使用 where .. or .. or ..
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题
1、if 标签
-
if标签可通过test属性(即传递过来的数据)的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
① 创建 DynamicSQLMapper 接口,并添加方法
public interface DynamicSQLMapper {
/**
* 多条件查询
*/
List<Emp> getEmpByCondition(Emp emp);
}
② 创建 DynamicSQLMapper.xml,添加 SQL 语句
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zyj.mybatis.mapper.DynamicSQLMapper">
<!-- List<Emp> getEmpByCondition(Emp emp) -->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp where 1 = 1 <!-- 1 = 1 是为了防止第一个if标签的SQL语句不拼接但拼接后面的if标签时,会产生where and …… 的错误 -->
<!-- if:根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中,若不符合test中的表达式,则不拼接 -->
<if test="empName != null and empName != ''">
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 void testGetEmpByCondition(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> emp = mapper.getEmpByCondition(new Emp(null, null, null, "男", "zhangsan@qq.com"));
System.out.println(emp);
}
④ 输出结果
DEBUG 03-18 20:54:13,567 ==> Preparing: select * from t_emp where 1 = 1 and sex = ? and email = ? (BaseJdbcLogger.java:137)
DEBUG 03-18 20:54:13,595 ==> Parameters: 男(String), zhangsan@qq.com(String) (BaseJdbcLogger.java:137)
DEBUG 03-18 20:54:13,615 <== Total: 1 (BaseJdbcLogger.java:137)
[Emp{eid=1, empName='张三', age=33, sex='男', email='zhangsan@qq.com', dept=null}]
2、where 标签
-
where 和 if一般结合使用:
-
若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
-
若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and 和 or去掉
-
-
注意:where标签不能去掉条件最后多余的and
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<!--
where:
当where标签中有内容时(有if标签中的SQL语句执行),会自动生成where关键字,并且将if标签中前面的and / or关键字舍弃
当where标签中没有内容时(if标签的SQL语句都不执行),此时where没有任何效果
注意:where标签不能将其内容后面多余的and / or去掉
-->
<where>
<!-- if:根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到SQL中,若不符合test中的表达式,则不拼接 -->
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
or age = #{age}
</if>
<if test="sex != null and sex != ''">
or sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</where>
</select>
3、trim 标签
-
trim用于去掉或添加标签中的内容
-
常用属性:
-
prefix:在trim标签中的内容的前面添加某些内容
-
suffix:在trim标签中的内容的后面添加某些内容
-
prefixOverrides:在trim标签中的内容的前面去掉某些内容
-
suffixOverrides:在trim标签中的内容的后面去掉某些内容
-
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<!--
trim:若标签中没有内容,trim标签也没有任何效果
prefix / suffix:将trim标签中内容前面 / 后面添加指定内容
prefixOverrides / suffixOcerrides:将trim标签中内容前面 / 后面去掉指定内容
-->
<trim prefix="where" prefixOverrides="and|or" >
<if test="empName != null and empName != ''">
emp_name = #{empName} or
</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>
4、choose、when、otherwise 标签
choose、when、otherwise 相当于 if …… else if …… else ……
① 在 DynamicSQLMapper 添加方法
/**
* 测试choose、when、otherwise
*/
List<Emp> getEmpByChoose(Emp emp);
② 在 DynamicSQLMapper.xml 添加 SQL 语句
<!-- List<Emp> getEmpByChoose(Emp emp) -->
<!--
choose、when、otherwise 相当于 if …… else if …… else …… 。其中when至少要有一个,otherwise最多只能有一个
when、otherwise 中的SQL语句只能生效一个,因为是 if …… else if …… else ……
-->
<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
public void testGetEmpByChoose(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> empByChoose = mapper.getEmpByChoose(new Emp(null, "", 16, "女", "zhangsan@qq.com"));
System.out.println(empByChoose);
}
④ 输出结果
DEBUG 03-18 23:34:31,140 ==> Preparing: select * from t_emp WHERE age = ? (BaseJdbcLogger.java:137)
DEBUG 03-18 23:34:31,179 ==> Parameters: 16(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-18 23:34:31,200 <== Total: 1 (BaseJdbcLogger.java:137)
[Emp{eid=3, empName='王五', age=16, sex='女', email='wangwu@qq.com', dept=null}]
5、foreach 标签
foreach 的相关属性:
collection:设置需要循环的数组或元素,需要从@Param获取
item:表示数组或集合中的一个数据
separator:循环体之间的分隔符
open:foreach标签所循环的所有内容的开始符
close:foreach标签所循环的所有内容的结束符
5.1、批量删除
5.1.1、方法一:使用 wher .. in(?,?,?)
① 在 DynamicSQLMapper 添加方法
/**
* 通过数组实现批量删除
*/
int deleteMoreByArray(@Param("eids") Integer[] eids);
② 在 DynamicSQLMapper.xml 添加 SQL 语句
<!-- 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>
③ 测试方法
@Test
public void testDeleteMoreByArray(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
int i = mapper.deleteMoreByArray(new Integer[]{9,10,11});
System.out.println(i);
}
④ 输出结果
DEBUG 03-18 23:54:44,653 ==> Preparing: delete from t_emp where eid in ( ? , ? , ? ) (BaseJdbcLogger.java:137)
DEBUG 03-18 23:54:44,684 ==> Parameters: 9(Integer), 10(Integer), 11(Integer) (BaseJdbcLogger.java:137)
DEBUG 03-18 23:54:44,686 <== Updates: 3 (BaseJdbcLogger.java:137)
3
5.1.2、方法二:使用 where .. or .. or ..
<!-- 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 from t_emp where
<foreach collection="eids" item="eid" separator="or"> <!-- 由于拼接后分隔符的前后会自动添加空格,所以不用写成" or ",即空格+or+空格 -->
eid = #{eid}
</foreach>
</delete>
5.2、批量添加
① 在 DynamicSQLMapper 添加批量添加的方法
/**
* 通过List集合实现批量添加
*/
int insertMoreByList(@Param("emps") List<Emp> emps);
② 在 DynamicSQLMapper.xml 添加 SQL 语句
<!-- 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
public void testInsertMoreByList(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null, "a1", 23, "男", "123@qq.com");
Emp emp2 = new Emp(null, "a1", 23, "男", "123@qq.com");
Emp emp3 = new Emp(null, "a1", 23, "男", "123@qq.com");
Emp emp4 = new Emp(null, "a1", 23, "男", "123@qq.com");
List<Emp> emps = Arrays.asList(emp1, emp2, emp3, emp4);
int i = mapper.insertMoreByList(emps);
System.out.println(i);
}
④ 输出结果
DEBUG 03-19 13:00:16,425 ==> Preparing: insert into t_emp values (null, ?, ?, ?, ?, null) , (null, ?, ?, ?, ?, null) , (null, ?, ?, ?, ?, null) , (null, ?, ?, ?, ?, null) (BaseJdbcLogger.java:137)
DEBUG 03-19 13:00:16,453 ==> Parameters: a1(String), 23(Integer), 男(String), 123@qq.com(String), a1(String), 23(Integer), 男(String), 123@qq.com(String), a1(String), 23(Integer), 男(String), 123@qq.com(String), a1(String), 23(Integer), 男(String), 123@qq.com(String) (BaseJdbcLogger.java:137)
DEBUG 03-19 13:00:16,460 <== Updates: 4 (BaseJdbcLogger.java:137)
4
6、SQL 片段
-
sql片段,可以记录一段公共sql片段,在使用的地方通过include标签进行引入
① 声明 sql 片段
<sql id="empColumns">eid, emp_name, age, sex, email</sql>
② 引用 sql 片段
select <include refid="empColumns"></include> from t_emp