目录
MyBatis源码及资料: https://github.com/coderZYGui/MyBatis-Study
MyBatis系列
一、 动态SQL
- MyBatis的强大特性之一便是它的
动态SQL。 - 动态sql是mybatis中的一个核心，什么是动态sql？
动态sql即对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。 - 如果你有使用JDBC或其他类似框架的经验,你就能体会到根据不同条件拼接SQL语句有多么痛苦。
- 拼接的时候要确保不能忘了必要的空格, 还要注意省掉列名列表最后的逗号。利用
动态SQL这一特性可以彻底摆脱这种痛苦。 - 通常使用动态SQL不可能是独立的一部分, MyBatis当然使用一种强大的动态SQL语言来改进这种情形，这种语言可以被用在任意的SQL映射语句中。
- 和标签库
JSTL很像
1、if 标签
跳转到目录
<if test="boolean表达式"></if> —> test: 判断表达式 (采用OGNL表达式)
<!-- 调用了trim()方法 -->
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
- if 元素用于判断,一般用作
是否应该包含某一个查询条件 (传递过来的对象是否为 null 或 '')
测试类
/**
* 查询工资大于等于1000的员工
*/
@Test
public void test1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
BigDecimal minSalary = new BigDecimal("1001");
List<Employee> employees = mapper.query1(minSalary);
for (Employee employee : employees) {
System.out.println(employee);
}
sqlSession.close();
}
/**
* 查询工资在1000-2000之间的员工
*/
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
BigDecimal minSalary = new BigDecimal("1000");
BigDecimal maxSalary = new BigDecimal("2000");
List<Employee> employees = mapper.query2(minSalary, maxSalary);
for (Employee employee : employees) {
System.out.println(employee);
}
sqlSession.close();
}
Employee接口
@Param注解 : 为了解决参数个数问题, 因为Mapper接口, 底层只允许传递一个参数
- 解决方法
- 1、将多个参数, 封装为一个VO, 进行传递
- 2、使用Map来存储多个参数, 通过map的key, 传递给sql
- 3、使用@Param注解, 底层仍然使用的是Map方式, @Param注解中的内容, 就充当了key
/**
* 查询工资大于1000的员工
*/
List<Employee> query1(@Param("minSalary") BigDecimal minSalary);
/**
* 查询工资在1000-2000之间
* @param minSalary
* @param maxSalary
* @return
*/
List<Employee> query2(
@Param("minSalary") BigDecimal minSalary,
@Param("maxSalary") BigDecimal maxSalary
);
EmployeeMapper.xml
<!--查询工资大于等于1000的员工-->
<select id="query1" resultType="Employee">
SELECT * FROM employee
<if test="minSalary != null and minSalary != ''">
WHERE salary >= #{minSalary}
</if>
</select>
<!--查询工资在1000-2000之间的员工-->
<select id="query2" resultType="Employee">
SELECT * FROM employee WHERE 1 = 1
<if test="minSalary != null and minSalary != ''">
AND salary >= #{minSalary}
</if>
<if test="maxSalary != null and maxSalary != ''">
AND salary <= #{maxSalary};
</if>
</select>
注意: 如果minSalary和maxSalary条件是可选择的,也就是说当minSalary传入null时,SQL就会出现问题; 就不确定使用WHERE还是AND来连接查询条件.
解决方案: 使用WHERE 1 = 1方式,其他的查询条件都使用AND或OR连接,但是 WHERE 1 = 1 会影响查询性能.
2、choose、when、otherwise 标签
- 相当于switch判断
测试方法
/**
* 查询指定部门的员工信息
*/
@Test
public void test3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
BigDecimal minSalary = new BigDecimal("100");
BigDecimal maxSalary = new BigDecimal("1000");
List<Employee> employees = mapper.query3(minSalary, maxSalary, 20L);
for (Employee employee : employees) {
System.out.println(employee);
}
sqlSession.close();
}
Employee接口中的方法
List<Employee> query3(
@Param("minSalary") BigDecimal minSalary,
@Param("maxSalary") BigDecimal maxSalary,
@Param("deptId") Long deptId
);
EmployeeMapper.xml
<!--查询指定部门的员工信息-->
<select id="query3" resultType="Employee">
SELECT * FROM employee WHERE 1 = 1
<if test="minSalary!=null and minSalary != ''">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null and maxSalary != ''">
AND salary <= #{maxSalary}
</if>
<!--假如下拉列表获取的部门id,"所在部门"这个要排除,设为-1-->
<!--<if test="deptId > 0">
AND deptId = #{deptId}
</if>-->
<choose> <!--相当于switch判断-->
<when test="deptId > 0">AND deptId = #{deptId}</when>
<otherwise>AND deptId IS NOT NULL</otherwise>
</choose>
</select>
和上面例子无关
<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="lastName!=null">
last_name like #{lastName}
</when>
<when test="email!=null">
email = #{email}
</when>
<!-- 上面选择都没进, 才执行otherwise -->
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
3、trim(where,set) 标签
3.1、where
where元素: 使用<where>标签首先会判断查询条件是否有WHERE关键字,如果没有,则在第一个查询条件之前,插入一个WHERE关键字, 如果发现查询条件AND 或者 OR开头,也会把第一个查询条件前的AND/OR 去掉- 这种方式避免了
WHERE 1 = 1的形式
查询指定部门的员工信息
<select id="query3" resultType="Employee">
SELECT * FROM employee
<where>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<choose> <!--相当于switch判断-->
<when test="deptId > 0">AND deptId = #{deptId}</when>
<otherwise>AND deptId IS NOT NULL</otherwise>
</choose>
</where>
</select>
SELECT * FROM employee WHERE 条件....
3.2、set
set元素和where元素相似,也能根据set中的sql动态的去掉最后的逗号,并在前面添加set关键字,如果没有内容,也会选择忽略set语句.
应用场景
- 因为如果修改用户信息的时候, 当password没有设置值, 所以在#{password}时从getPassword()中就获取的为NULL,所以就要采用
if来动态判断password是否为空,如果为空,则不拼接,但是此时会出现问题,上面拼接的语句最后会存在一个,. 此时就会出现sql语法错误
- 这个时候就采用
set元素来操作了,可以去掉后面的,
测试方法
/**
* 更新指定id的员工信息
*/
@Test
public void test4(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setId(6L);
employee.setSn("6238");
// employee.setName("guizy");
employee.setSalary(new BigDecimal("8888"));
int update = mapper.update(employee);
if (update > 0){
System.out.println("成功修改"+update+"条用户信息!");
}
sqlSession.commit();
sqlSession.close();
}
EmployeeMapper接口
int update(Employee employee);
EmployeeMapper.xml
<update id="update">
UPDATE employee
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="sn != null and sn != ''">
sn = #{sn},
</if>
<if test="salary != null and salary != ''">
salary = #{salary},
</if>
</set>
WHERE id = #{id};
</update>
UPDATE employee SET name ...
3.3、trim
- trim是更强大的
格式化SQL的标签:
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
<!--trim包含的动态SQL-->
</trim>
前提如果trim元素包含内容返回一个字符串,则在返回之后的字符串
prefix:在trim标签中的内容的 前面添加某些内容prefixOverrides:在trim标签中的内容的 前面去掉某些内容suffix:在trim标签中的内容的 后面添加某些内容suffixOverrides:在trim标签中的内容的 后面去掉某些内容
<!--public List<Employee> getEmpsByConditionTrim(Employee employee); -->
<select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and或者or where标签不能解决
prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
prefix给拼串后的整个字符串加一个前缀
prefixOverrides="":
前缀覆盖： 去掉整个字符串前面多余的字符
suffix="":后缀
suffix给拼串后的整个字符串加一个后缀
suffixOverrides=""
后缀覆盖：去掉整个字符串后面多余的字符
-->
<!-- 自定义字符串的截取规则
prefix="where"表示: 在trim标签内拼接好的字符串前面加上 where
suffixOverrides="and"表示: 在trim标签内拼接好的字符串后面 去掉 and
-->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=""">
email=#{email} and
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
-
使用where等价于
<!-- 相当于使用 WHERE 来替换 AND 或者 OR--> <trim prefix="WHERE" prefixOverrides="AND |OR "> </trim><select id="query3" resultType="Employee"> SELECT * FROM employee <!-- 如果trim标签里面的字符串, 以prefixOverrides中的值打头, 就用prefix来替代--> <!--和使用where标签效果一样 因为以AND打头, 和prefixOverrides中的一样, 所以用prefix的内容WHERE替换AND --> <!-- trim标签内字符串前面加上 where, 然后再将trim标签内前面的AND或OR去除 --> <trim prefix="WHERE" prefixOverrides="AND|OR"> <if test="minSalary!=null"> AND salary >= #{minSalary} </if> <if test="maxSalary!=null"> AND salary <= #{maxSalary} </if> <choose> <!--相当于switch判断--> <when test="deptId > 0">AND deptId = #{deptId}</when> <otherwise>AND deptId IS NOT NULL</otherwise> </choose> </trim> </select>注意: 此时AND后面有一个空格
-
使用set等价于
<!-- 使用suffix的空格, 替换末尾的 ,--> <trim prefix="SET" suffixOverrides=","> </trim><!-- 因为最后以 , 结尾, 和suffixOverrides中相同, 所以suffix替代去掉逗号, 相当于标签set操作 --> <!-- 在trim标签内前面加上SET, 在后面去除掉, --> <trim prefix="SET" suffix="" suffixOverrides=","> <if test="name!=null"> name = #{name}, </if> <if test="sn!=null"> sn = #{sn}, </if> <if test="salary!=null"> salary = #{salary}, </if> </trim>
相关trim的介绍： https://www.cnblogs.com/wx60079/p/13212333.html
4、foreach
- SQL中有时候使用
IN关键字,如WHERE id IN(10,20,30),此时可以使用${ids}直接拼接SQL ,但是会导致SQL注入问题,要避免SQL注入,只能使用#{}方式,此时就可以配合使用foreach元素了。foreach元素用于迭代一个集合/数组， 通常是构建在IN运算符条件中。
foreach元素:collection属性:表示对哪一个集合或数组做迭代- 如果参数是
数组类型,此时Map的key为array; - 如果参数是
List类型,此时Map的key为list;
- 如果参数是
open属性:在迭代集合之前,拼接什么符号close属性:在迭代集合之后,拼接什么符号separactor属性:在迭代元素时,每一个元素之间使用什么符号分割开来item属性:被迭代的每一个元素的变量index属性:迭代的索引
需求: 删除ID为10,20,30的数据
需求: 批量插入语法
注意: 当传递一个List对象或Array对象参数给MyBatis时,(这里可以看前面讲MyBatis参数处理的部分),MyBatis会自动把它包装到一个Map中,当是List对象时会以list作为key, 数组对象会以array作为key. 一般使用Param注解设置key名.
EmployeeMapperTest测试类
/**
* 批量删除指定id的员工信息
*/
@Test
public void test5(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
mapper.batchDelete(new Long[]{10L,20L,30L});
sqlSession.commit();
sqlSession.close();
}
/**
* 批量插入员工信息
*/
@Test
public void test6(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> list = new ArrayList<>();
list.add(new Employee(null, "周", "10001", new BigDecimal("5555.00"), 50L));
list.add(new Employee(null, "吴", "10002", new BigDecimal("6666.00"), 60L));
list.add(new Employee(null, "郑", "10003", new BigDecimal("7777.00"), 70L));
int count = mapper.batchInsert(list);
if (count > 0){
System.out.println("成功插入了:" + count + "条用户信息!");
}
sqlSession.commit();
sqlSession.close();
}
EmployeeMapper接口
/**
* 使用foreach元素批量删除
* @param ids
* param注解原理还是Map,Map的key
*/
void batchDelete(@Param("ids") Long[] ids);
/**
* 批量插入用户信息
* @param list
* @return
* 当参数是数组或集合时,一般要加上@Param注解,写死
*/
int batchInsert(@Param("emps") List<Employee> emps);
EmployeeMapper.xml
<!--使用foreach元素_完成批量删除-->
<delete id="batchDelete">
DELETE FROM employee WHERE id IN
<!--
foreach元素:
collection属性:表示对哪一个集合或数组做迭代
如果参数是数组类型,此时Map的key为array;
如果参数是List类型,此时Map的key为list;
open属性:在迭代集合之前,拼接什么符号
close属性:在迭代集合之后,拼接什么符号
separactor属性:在迭代元素时,每一个元素之间使用什么符号分割开来
item属性:被迭代的每一个元素的变量
index属性:迭代的索引
-->
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<!--使用foreach元素_完成批量插入-->
<insert id="batchInsert">
INSERT INTO employee(id, name, sn, salary, deptId) VALUES
<foreach collection="emps" separator="," item="e">
(#{e.id}, #{e.name}, #{e.sn}, #{e.salary}, #{e.deptId})
</foreach>
</insert>
两个内置参数:
<!-- 两个内置参数：
不只是方法传递过来的参数可以被用来判断，取值。。。
mybatis默认还有两个内置参数：
_parameter:代表整个参数
单个参数：_parameter就是这个参数
多个参数：参数会被封装为一个map；_parameter就是代表这个map
_databaseId:如果配置了databaseIdProvider标签。
_databaseId就是代表当前数据库的别名oracle
-->
<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="Employee">
<!--
_parameter就代表着传递过来的参数, 当前指 Employee对象
_databaseId的使用要在mybatis-config中配置多数据源信息(databaseIdProvider标签)
-->
<if test="_databaseId=='mysql'">
select * from tbl_employee
<if test="_parameter!=null">
where last_name like #{lastName}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
<if test="_parameter!=null">
where last_name like #{_parameter.lastName}
</if>
</if>
</select>
5、sql、include、bind 标签
- 使用sql可以把相同的
sql片段起一个名字,并使用include在sql任意位置使用. - bind:
使用OGNL表达式创建一个变量,并将其绑定在上下文中.
需求: 按照员工的关键字、工资范围、所属部门来查询
需求: 按照查询条件查询员工的人数
sql标签:使用<sql>片段来封装表的全部字段, 然后通过<include>来引入;
注意: 要封装一个查询条件类,用来设置条件使用
EmployeeQueryObject 封装查询条件的
package com.sunny.query;
import lombok.Data;
import java.math.BigDecimal;
/**
* 封装员工的高级查询信息--->封装查询条件
*/
@Data
public class EmployeeQueryObject {
private String keyword; // 根据keyword来查询,员工名字或编号
private BigDecimal minSalary; // 最低工资
private BigDecimal maxSalary; // 最高工资
private Long deptId = -1L; // 部门ID,缺省为-1;表示所有部门
/**
* 重写keyword的get方法,如果
* @return
*/
public String getKeyword(){
// 防止传的条件是空或空字符
return empty2null(keyword);
}
// 如果字符串为空串,也应该设置为null
private String empty2null(String str){
return hasLength(str) ? str : null;
}
// 判断这个字符串是否有数据
private boolean hasLength(String str){
// str不为空 并且 str trim()后不和""相等
/**
* 判断非空,假如str为zy
* zy!=null ---> true
* "".equals(zy.trim()) --> false
* !"".equals(zy.trim()) ---> !false ---> true
* 所以
* true && true ---> true 不为空
*/
return str!=null && !"".equals(str.trim());
}
}
EmployeeMapper接口
public interface EmployeeMapper {
/**
* 根据查询条件来查询员工
* @param qo 封装查询条件的类对象
* @return
*/
List<Employee> queryForList(EmployeeQueryObject qo);
/**
* 根据查询条件来查询员工人数
* @param qo 封装查询条件的类对象
* @return
*/
int queryForEmpCount(EmployeeQueryObject qo);
}
EmployeeMapper.xml
<?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">
<!--命名空间,类似包的概念: namespace:绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.sunny.dao.EmployeeMapper">
<!--多个查询共同使用的sql-->
<sql id="Base_where">
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%' + keyword +'%'"/>
<!-- 下面 #{KeywordLike} 用 %keyword%来替代, 这个keyword是实体的属性 -->
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
<!--AND (name LIKE concat('%', #{keyword}, '%') OR sn LIKE concat('%', #{keyword}, '%'))-->
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <=#{maxSalary}
</if>
<if test="deptId!=null">
AND deptId = #{deptId}
</if>
</where>
</sql>
<!--根据查询条件来查询符合条件的查询-->
<select id="queryForList" resultType="Employee">
SELECT * FROM employee
<include refid="Base_where"></include>
</select>
<!--查询符合条件的员工数量-->
<select id="queryForEmpCount" resultType="int">
SELECT count(*) FROM employee
<include refid="Base_where"></include>
</select>
</mapper>
EmployeeMapperTest测试类
public class EmployeeMapperTest {
/**
* 按照员工的关键字、工资范围、所属部门来查询
*/
@Test
public void test1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
EmployeeQueryObject qo = new EmployeeQueryObject();
qo.setKeyword("2");
qo.setMinSalary(new BigDecimal("1000"));
qo.setMaxSalary(new BigDecimal("9000"));
qo.setDeptId(30L);
List<Employee> employees = mapper.queryForList(qo);
for (Employee employee : employees) {
System.out.println(employee);
}
sqlSession.close();
}
/**
* 按照查询条件了查询员工的人数
*/
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
EmployeeQueryObject qo = new EmployeeQueryObject();
qo.setKeyword("2");
qo.setMinSalary(new BigDecimal("1000"));
qo.setMaxSalary(new BigDecimal("9000"));
qo.setDeptId(30L);
int i = mapper.queryForEmpCount(qo);
if (i > 0) {
System.out.println("符合条件的一共有:"+i+"人!");
}
sqlSession.close();
}
}
434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
