提示:这是MyBatis的第四篇,有兴趣的话可以看下:
二: MyBatis的resultMap标签 自定义封装返回值类型
文章目录
MyBatis动态SQL
1、where标签和if标签组合使用
- 实现根据条件,进行灵活拼接SQL语句,如果条件不成立,则不加入SQL查询语句中。
编写EmployeeDynamicSQLMapper
接口
public interface EmployeeDynamicSQLMapper {
//通过条件自动拼接查询条件(为空的条件则不拼接)
public List<Employee> getEmpsByConditions(Employee emp);
}
创建EmployeeDynamicSQLMapper.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">
<mapper namespace="com.atguigu.dao.EmployeeDynamicSQLMapper">
</mapper>
写select
标签,使用where
标签组合if
标签,对条件进行判断:
<select id="getEmpsByConditions" resultType="com.atguigu.bean.Employee">
select * from tbl_employee
<where>
<if test="id != null">
id = #{id}
</if>
<if test="lastName != null and lastName != ''">
and last_name like #{lastName}
</if>
<if test="email != null">
and email = #{email}
</if>
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</where>
</select>
写Java测试:
@Test
public void test11() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeDynamicSQLMapper mapper = openSession.getMapper(EmployeeDynamicSQLMapper.class);
Employee employee = new Employee();
// employee.setId(12);
employee.setLastName("%王%");
employee.setGender("0");
List<Employee> empByConditions = mapper.getEmpsByConditions(employee);
for (Employee emp : empByConditions) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
输出:
2021-07-04 21:03:02,670 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditions] - => Preparing: select * from tbl_employee WHERE last_name like ? and gender = ?
2021-07-04 21:03:02,717 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditions] - => Parameters: %王%(String), 0(String)
2021-07-04 21:03:02,744 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditions] - <= Total: 1
Employee [id=12, lastName=王五, email=wangwu@qq.com, gender=0]
实现了条件的自动拼接,如果不符合判断规则,则不加入SQL语句中进行判断。
2、trim标签手动处理SQL语句
trim
标签可以手动的在SQL语句开头或者结尾 增加或者删除指定的字符。
prefix
:在前缀添加指定字符prefixOverrides
:删除开头的指定字符suffix
:在结尾添加指定字符suffixOverrides
:删除结尾的指定字符
在接口中新增方法:
public interface EmployeeDynamicSQLMapper {
//通过条件自动拼接查询条件(为空的条件则不拼接)
public List<Employee> getEmpByConditions(Employee emp);
//通过条件手动trim拼接查询条件(为空的条件则不拼接)
public List<Employee> getEmpsByConditionsTrim(Employee emp);
}
写select
标签:
<select id="getEmpsByConditionsTrim" resultType="com.atguigu.bean.Employee">
select * from tbl_employee
where
<trim prefixOverrides="and" suffixOverrides="and">
<!-- trim去除掉where后面的SQL语句的开头的and -->
<!-- 和结尾的最后一个and 但是我结尾没有and 所以大家就当无事发生 -->
<!-- 也可以通过prefix suffix属性在开头或结尾加上指定的字符串 -->
<if test="id != null">
and id = #{id}
</if>
<if test="lastName != null and lastName != ''">
and last_name like #{lastName}
</if>
<if test="email != null">
and email = #{email}
</if>
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</trim>
</select>
上代码跑一下:
@Test
public void test12() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeDynamicSQLMapper mapper = openSession.getMapper(EmployeeDynamicSQLMapper.class);
Employee employee = new Employee();
// employee.setId(12);
employee.setLastName("%王%");
employee.setGender("0");
List<Employee> empByConditions = mapper.getEmpsByConditionsTrim(employee);
for (Employee emp : empByConditions) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
输出结果:
2021-07-04 21:50:41,543 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditionsTrim] - => Preparing: select * from tbl_employee where last_name like ? and gender = ?
2021-07-04 21:50:41,593 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditionsTrim] - => Parameters: %王%(String), 0(String)
2021-07-04 21:50:41,628 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpByConditionsTrim] - <= Total: 1
Employee [id=12, lastName=王五, email=wangwu@qq.com, gender=0]
完成
3、choose分支条件查询
- 需求:从多个分支中选择一个进行查询:
当有id时,则按id查询;
当有姓名时,则按姓名查询;
当有邮箱时,则按邮箱查询;
当没有上述三项时,查询性别是女生的。
使用choose
、when
、otherwise
标签实现,类似与Java中的switch
、case
、default
,和SQL中的case
、when
、else
接口中新增方法:
public interface EmployeeDynamicSQLMapper {
//通过条件自动拼接查询条件(为空的条件则不拼接)
public List<Employee> getEmpsByConditions(Employee emp);
//通过条件手动trim拼接查询条件(为空的条件则不拼接)
public List<Employee> getEmpsByConditionsTrim(Employee emp);
//通过条件判断,选择其中的一个条件进行查找
public List<Employee> getEmpsByChoose(Employee emp);
}
写select
标签:
<select id="getEmpsByChoose" resultType="com.atguigu.bean.Employee">
select * from tbl_employee
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="lastName != null and lastName != ''">
last_name like #{lastName}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
写Java代码测试:
@Test
public void test13() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeDynamicSQLMapper mapper = openSession.getMapper(EmployeeDynamicSQLMapper.class);
Employee employee = new Employee();
employee.setId(12);
employee.setLastName("%王%");
employee.setGender("0");
List<Employee> empByConditions = mapper.getEmpsByChoose(employee);
for (Employee emp : empByConditions) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
输出结果:
2021-07-04 22:18:15,841 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByChoose] - => Preparing: select * from tbl_employee WHERE id = ?
2021-07-04 22:18:15,879 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByChoose] - => Parameters: 12(Integer)
2021-07-04 22:18:15,901 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByChoose] - <= Total: 1
Employee [id=12, lastName=王五, email=wangwu@qq.com, gender=0]
4、set标签动态更新
- 需求:通过条件判断更新数据(有数据才更新,无数据的字段不更新)。
话不多说直接贴update
标签代码:
<update id="updateEmpByConditions">
update tbl_employee
<set>
<!-- set标签可自动去除最后一个set项结尾的逗号 -->
<if test="lastName != null">
last_name = #{lastName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender},
</if>
</set>
<where>
id = #{id}
</where>
</update>
成功修改
5、用trim手动实现更新
使用trim
标签在拼接的SQL语句开头增加set
,并去除最后一个set项
结尾的逗号。
update
标签代码:
<update id="updateEmpByTrim">
update tbl_employee
<trim prefix="set" suffixOverrides=",">
<!-- trim标签在拼接的SQL语句开头增加set,并去除最后一个set项结尾的逗号 -->
<if test="lastName != null">
last_name = #{lastName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender == 0 or gender == 1">
gender = #{gender},
</if>
</trim>
<where>
id = #{id}
</where>
</update>
完成更新,但是我不贴测试代码
哎 就是玩
5、foreach标签遍历元素
需求:查找id在所给的集合内的数据
先编写接口方法:
//通过id的集合查找数据(@Param注解 给参数取名字 在xml中可以使用)
public List<Employee> getEmpsByIds(@Param("ids")List<Integer> ids);
编写xml中的select标签
<select id="getEmpsByIds" resultType="com.atguigu.bean.Employee">
select * from tbl_employee
where id in
<foreach collection="ids" item="id"
open="(" separator="," close=")">
#{id}
<!-- 使用#{item属性定义名}就能取出变量的值 -->
</foreach>
</select>
collection
:指定要遍历的集合:
list
类型的参数会特殊处理封装在map
中,map
的key
就叫list
item
:将当前遍历到的元素赋值给指定的变量separator
:每个元素之间的分隔符open
:遍历出所有结果拼接一个开始的字符close
:遍历出所有结果拼接一个结束的字符index
:索引。遍历list的时候index
就是索引,item
就是当前值。
遍历map
的时候index
表示的就是map
的key
,item
就是map
的值。
Java测试代码:
@Test
public void test16() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeDynamicSQLMapper mapper = openSession.getMapper(EmployeeDynamicSQLMapper.class);
List<Employee> empsByIds = mapper.getEmpsByIds(Arrays.asList(1,7,8,9));
for(Employee emp : empsByIds) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
输出!
2021-07-05 20:42:27,095 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByIds] - => Preparing: select * from tbl_employee where id in ( ? , ? , ? , ? )
2021-07-05 20:42:27,143 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByIds] - => Parameters: 1(Integer), 7(Integer), 8(Integer), 9(Integer)
2021-07-05 20:42:27,175 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.getEmpsByIds] - <= Total: 4
Employee [id=1, lastName=tom, email=tom@atguigu.com, gender=1]
Employee [id=7, lastName=李思, email=lisi@qq.com, gender=0]
Employee [id=8, lastName=张三, email=zhangsan@qq.com, gender=0]
Employee [id=9, lastName=赵六, email=zhaoliu@qq.com, gender=1]
遍历完成
6、foreach遍历对象集合
需求:批量插入一组员工信息(List
集合)到数据库
先打个草稿写一下SQL
语句:
insert into tbl_employee (last_name,email,gender,dept_id)
values ('allen','allen@qq.com','1',1),
('mary','mary@qq.com','0',2)
老规矩三步走 先定义接口方法:
//批量插入员工信息到数据库
public void addEmps(@Param("emps")List<Employee> emps);
xml中配置insert标签:
<insert id="addEmps">
insert into tbl_employee (last_name,email,gender,dept_id)
values
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
写Java测试代码:
@Test
public void test17() {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeDynamicSQLMapper mapper = openSession.getMapper(EmployeeDynamicSQLMapper.class);
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(null, "allen", "allen@qq.com", "1", new Department(1)));
emps.add(new Employee(null, "mary", "mary@qq.com", "0", new Department(2)));
mapper.addEmps(emps);
openSession.commit();
} finally {
openSession.close();
}
}
输出结果:
2021-07-05 21:21:10,020 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.addEmps] - => Preparing: insert into tbl_employee (last_name,email,gender,dept_id) values (?,?,?,?) , (?,?,?,?)
2021-07-05 21:21:10,066 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.addEmps] - => Parameters: allen(String), allen@qq.com(String), 1(String), 1(Integer), mary(String), mary@qq.com(String), 0(String), 2(Integer)
2021-07-05 21:21:10,067 [main] DEBUG [com.atguigu.dao.EmployeeDynamicSQLMapper.addEmps] - <= Updates: 2