失败,是正因你在距成功一步之遥的时候停住了脚步。
9、动态SQL
Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。
9.1、if
if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会拼接到SQL中。
反之标签中的内容不会拼接到SQL中。
1 DynamicSQLMapper.java
// 根据条件查询
List<Emp> getEmpByCondition(Emp emp);
2 DynamicSQLMapper.xml
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select emp_id, emp_name, age, gender, dept_id *
from t_emp
where
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
3 测试
@Test
public void testGetEmpByCondition() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp(null, "张三", 20, "男", null);
List<Emp> list = mapper.getEmpByCondition(emp);
sqlSession.close;
list.forEach(System.out::println);
}
9.2、where
where和if一般结合使用。
若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字。
若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件最前方多余的and或or去掉。
注意:where标签不能去掉条件最后多余的and或or。
1 DynamicSQLMapper.java
// 根据条件查询
List<Emp> getEmpByConditionWhere(Emp emp);
2 DynamicSQLMapper.xml
<!--List<Emp> getEmpByConditionWhere(Emp emp);-->
<select id="getEmpByConditionWhere" resultType="emp">
select emp_id, emp_name, age, gender, dept_id
from t_emp
<where>
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</where>
</select>
3 测试
@Test
public void testGetEmpByCondition() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp();
emp.setAge(20);
List<Emp> list = mapper.getEmpByConditionWhere(emp);
sqlSession.close();
list.forEach(System.out::println);
}
9.3、trim
trim用于去掉或添加标签中的内容
标签中有内容:
- prefix:在trim标签中的内容的开始添加指定内容
- prefixOverrides:在trim标签中的内容的开始去掉指定内容
- suffix:在trim标签中的内容的结束添加指定内容
- suffixOverrides:在trim标签中的内容的结束去掉指定内容
标签中没有内容,trim标签也没有效果
1 DynamicSQLMapper.java
// 根据条件查询trim
List<Emp> getEmpByConditionTrim(Emp emp);
2 DynamicSQLMapper.xml
<!--List<Emp> getEmpByConditionTrim(Emp emp);-->
<select id="getEmpByConditionTrim" resultType="emp">
select emp_id, emp_name, age, gender, dept_id
from t_emp
<trim prefix="where" suffixOverrides="and"><!--在前面添加where,在后面去掉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="gender != null and gender != ''">
gender = #{gender} and
</if>
</trim>
;
</select>
3 测试
@Test
public void testGetEmpByConditionTrim() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp();
emp.setAge(20);
emp.setGender("男");
List<Emp> list = mapper.getEmpByConditionTrim(emp);
sqlSession.close();
list.forEach(System.out::println);
}
9.4、choose、when、otherwise
choose、when、 otherwise相当于if…else if…else
when至少设置一个,otherwise最多设置一个
1 DynamicSQLMapper.java
// 根据条件查询
List<Emp> getEmpByConditionChoose(Emp emp);
2 DynamicSQLMapper.xml
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select emp_id, emp_name, age, gender, dept_id
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="gender != null and gender != ''">
gender = #{gender}
</when>
<otherwise>
dept_id = 1
</otherwise>
</choose>
</where>
</select>
3 测试
@Test
public void testGetEmpByConditionChoose() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp();
emp.setAge(20);
emp.setGender("男");
List<Emp> list = mapper.getEmpByConditionChoose(emp);
sqlSession.close();
list.forEach(System.out::println);
}
传输两个条件,当第一个条件满足就结束。
9.5、foreach
- collection:设置要循环的数组或集合。
- item:表示数组或者集合中的每一个数据。
- separator:设置每次循环的数据之间的分隔符,分隔符前后自动带空格。
- open:循环的所有内容以什么开始。
- close:循环的所有内容以什么结束。
- index 是当前迭代的序号。
- 当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
1 批量添加
DynamicSQLMapper.java
// 批量添加员工
void insertMoreEmp(@Param("emps") List<Emp> emps);
DynamicSQLMapper.xml
<!--void insertMoreEmp(@Param("emps") List<Emp> emps);-->
<insert id="insertMoreEmp">
insert into t_emp (emp_id, emp_name, age, gender, dept_id)
values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.gender},null)
</foreach>
</insert>
测试
@Test
public void testInsertMoreEmp() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp1 = new Emp(null, "测试1", 23, "男", null);
Emp emp2 = new Emp(null, "测试2", 23, "男", null);
Emp emp3 = new Emp(null, "测试3", 23, "男", null);
Emp emp4 = new Emp(null, "测试4", 23, "男", null);
Emp emp5 = new Emp(null, "测试5", 23, "男", null);
List<Emp> list = Arrays.asList(emp1, emp2, emp3, emp4, emp5);
int i = mapper.insertMoreEmp(list);
sqlSession.commit();
sqlSession.close();
System.out.println("结果为:" + 1);
}
2 批量删除
DynamicSQLMapper.java
// 批量删除员工
int deleteMoreByArray(@Param("eids") List list);
DynamicSQLMapper.xml
<!--第一种写法-->
<!--void deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
delete
from t_emp
where
<foreach collection="eids" item="eid" separator="or">
emp_id = #{eid}
</foreach>
</delete>
<!--第二种写法-->
<!--void deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
delete
from t_emp
where emp_id in
<foreach collection="eids" item="eid" open="(" separator="," close=")">
#{eid}
</foreach>
</delete>
测试
@Test
public void testDeleteMoreByArray() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List list = new ArrayList();
list.add(10);
list.add(11);
list.add(12);
list.add(13);
list.add(14);
int i = mapper.deleteMoreByArray(list);
sqlSession.commit();
sqlSession.close();
System.out.println("结果为:" + i);
}
9.6、SQL标签
- sql片段,可以记录一段公共sql片段,在使用的地方通过include标签进行引入
- 设置sql片段:<sql id=“empColumns”></sql>
- 引用sql片段:<include refid=“empColumns”></include>
1 DynamicSQLMapper.xml
<!--sql片段-->
<sql id="empColumns">
emp_id, emp_name, age, gender, dept_id
</sql>
在需要使用的地方使用Include标签引用即可
<!--List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByCondition" resultType="Emp">
select
<include refid="empColumns"></include>
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="gender != null and gender != ''">
gender = #{gender}
</when>
</choose>
</where>
</select>
9.6、set
- set和if一般结合使用。
- 若set标签中的if条件都不满足,则where标签没有任何功能,即不会添加set关键字。
- 若set标签中的if条件满足,则set标签会自动添加set关键字,并删除无关的逗号。
- 只能删除多余逗号,不能添加逗号,在写sql语句的时候必须在结尾添加逗号。
1 DynamicSQLMapper.java
// 更新员工信息
int updateEmpById(Emp Emp);
2 DynamicSQLMapper.xml
<!--int updateEmpById(Emp Emp);-->
<update id="updateEmpById">
update t_emp
<set>
<if test="empName != null and empName != ''">
emp_name = #{empName},
</if>
<if test="age != null and age != ''">
age = #{age},
</if>
<if test="gender != null and gender != ''">
gender = #{gender},
</if>
</set>
<where>
emp_id = #{empId}
</where>
</update>
3 测试
@Test
public void testUpdateEmpById() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
Emp emp = new Emp(4, "田七", null, "女", null);
int i = mapper.updateEmpById(emp);
sqlSession.commit();
sqlSession.close();
System.out.println("结果为:" + i);
}