Mybatis系列(五)动态SQL
前言
动态 SQL
是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。下面介绍Mybatis各个标签的使用。
一、if
SQL文件:
/*
Navicat Premium Data Transfer
Source Server : mysql
Source Server Type : MySQL
Source Server Version : 50529
Source Host : localhost:3306
Source Schema : mybatis
Target Server Type : MySQL
Target Server Version : 50529
File Encoding : 65001
Date: 17/02/2021 21:48:04
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birth` date NULL DEFAULT NULL,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of t_teacher
-- ----------------------------
INSERT INTO `t_teacher` VALUES (1, '张三', '语文', '2021-01-01', '北京市');
INSERT INTO `t_teacher` VALUES (2, '李四', '数学', '2021-02-17', '上海市');
INSERT INTO `t_teacher` VALUES (3, '王五', '英语', '2021-02-17', '河南省');
SET FOREIGN_KEY_CHECKS = 1;
TeacherMapper.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.cetc.dao.TeacherMapper">
<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
select * from t_teacher where
<if test="name != null && !name.equals('')">
name = #{name} and
</if>
<if test="className != null and className != """>
class_name = #{className} and
</if>
<if test="address != null and address != ''">
address like #{address}
</if>
</select>
</mapper>
TeacherMapper.java:
package com.cetc.dao;
import com.cetc.bean.Teacher;
import java.util.List;
public interface TeacherMapper {
List<Teacher> getTeacherListByCondition(Teacher teacher);
}
Test:
@Test
public void testMybatisSelect(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
Teacher teacher = new Teacher();
teacher.setName("张三");
teacher.setClassName("语文");
teacher.setAddress("%北%");
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
if (teachers != null && teachers.size() > 0 ){
for (Teacher t : teachers) {
System.out.println(t);
}
}
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-17 21:46:52,623 ==> Preparing: select * from t_teacher where name = ? and class_name = ? and address like ? (BaseJdbcLogger.java:145)
DEBUG 02-17 21:46:52,815 ==> Parameters: 张三(String), 语文(String), %北%(String) (BaseJdbcLogger.java:145)
DEBUG 02-17 21:46:52,898 <== Total: 1 (BaseJdbcLogger.java:145)
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)
二、where、choose、when
- 上面的示例存在一个问题,就是当所有的条件
都为空
时,SQL语句会多出来一个where,造成了SQL语法错误
,mtabtis
提供了where标签
来解决此问题,它可以将条件中多出来的and自动去除,但条件是多出的and必须在每个条件sql前面
。- 上面的示例也无法实现非if时执行哪一句SQL,
when...otherwise...
解决了这个问题,它可以otherWise
可以指定非if情况下按照谁执行。
请参照下面的示例
mapper.xml:
<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
select * from t_teacher
<where>
<choose>
<!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
<when test="name != null && !name.equals('')">
and name = #{name}
</when>
<when test="className != null and className != """>
and class_name = #{className}
</when>
<!--以上条件都不符合时,按照地址来查-->
<otherwise>
address like #{address}
</otherwise>
</choose>
</where>
</select>
Test.java:
@Test
public void testMybatisSelect01(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
Teacher teacher = new Teacher();
teacher.setName("张三");
teacher.setClassName("语文");
teacher.setAddress("%北%");
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
if (teachers != null && teachers.size() > 0 ){
for (Teacher t : teachers) {
System.out.println(t);
}
}
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-17 22:13:52,177 ==> Preparing: select * from t_teacher WHERE name = ? (BaseJdbcLogger.java:145)
DEBUG 02-17 22:13:52,588 ==> Parameters: 张三(String) (BaseJdbcLogger.java:145)
DEBUG 02-17 22:13:52,662 <== Total: 1 (BaseJdbcLogger.java:145)
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)
三、trim
针对以
where标签
去除不了SQL后面
多余的and
,trim
标签很好的解决了这种问题。
TeacherMapper.xml:
<select id="getTeacherListByTrimCondition" resultType="com.cetc.bean.Teacher">
select * from t_teacher
<!--
prefix="":指定在sql语句的前面加上哪个前缀
prefixOverrides="":去除掉前缀以指定内容的值
suffix="":指定在sql语句的前面加上哪个后缀
suffixOverrides="":去除掉后缀以指定内容开头的值
-->
<trim prefix="where" prefixOverrides="and | or" suffixOverrides="and | or" suffix=";">
<if test="name != null && !name.equals('')">
name = #{name} and
</if>
<if test="className != null and className != """>
class_name = #{className} and
</if>
<if test="address != null and address != ''">
address like #{address}
</if>
</trim>
</select>
TeacherMapper.java:
List<Teacher> getTeacherListByTrimCondition(Teacher teacher);
Test.java:
@Test
public void testMybatisSelect01(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
Teacher teacher = new Teacher();
teacher.setName("张三");
teacher.setAddress("%北%");
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = teacherMapper.getTeacherListByTrimCondition(teacher);
if (teachers != null && teachers.size() > 0 ){
for (Teacher t : teachers) {
System.out.println(t);
}
}
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-17 22:32:08,223 ==> Preparing: select * from t_teacher where name = ? and address like ? ; (BaseJdbcLogger.java:145)
DEBUG 02-17 22:32:08,454 ==> Parameters: 张三(String), %北%(String) (BaseJdbcLogger.java:145)
DEBUG 02-17 22:32:08,539 <== Total: 1 (BaseJdbcLogger.java:145)
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)
四、set
针对于
update
,我们可以动态的根据条件改变某个字段。
TeacherMapper.xml:
<update id="updateTeacherById">
update t_teacher
<set>
<if test="name != null && !name.equals('')">
name = #{name} ,
</if>
<if test="className != null and className != """>
class_name = #{className} ,
</if>
</set>
<where>
id = #{id}
</where>
</update>
TeacherMapper.java:
int updateTeacherById(Teacher teacher);
Test.java:
@Test
public void testMybatisSelect02(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
Teacher teacher = new Teacher();
teacher.setId(1);
teacher.setName("张三Test");
teacher.setClassName("生物学");
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
int i = teacherMapper.updateTeacherById(teacher);
System.out.println(i > 0 ? "修改成功" : "修改失败");
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-17 22:51:43,021 ==> Preparing: update t_teacher SET name = ? , class_name = ? WHERE id = ? (BaseJdbcLogger.java:145)
DEBUG 02-17 22:51:43,109 ==> Parameters: 张三Test(String), 生物学(String), 1(Integer) (BaseJdbcLogger.java:145)
DEBUG 02-17 22:51:43,111 <== Updates: 1 (BaseJdbcLogger.java:145)
修改成功
五、foreach
动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建
IN
语句的时候。
当迭代列表、集合等可迭代
对象
或者数组
时—
index
是当前迭代的次数,item
的值是本次迭代获取的元素当使用字典(或者Map.Entry对象的集合)时
—
index
是键,index
是值
- 当迭代列表、集合等可迭代
对象
或者数组
时
TeacherMapper.xml
<select id="getTeacherListByIds" resultType="com.cetc.bean.Teacher">
select * from t_teacher
<where>
id in
</where>
<foreach collection="ids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
TeacherMapper.java:
List<Teacher> getTeacherListByIds(@Param("ids") List<Integer> ids);
Test.java:
@Test
public void testMybatisSelect03(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Integer> ids = Arrays.asList(1, 2, 3, 4, 5);
List<Teacher> teachers = teacherMapper.getTeacherListByIds(ids);
if (teachers != null && teachers.size() > 0){
for (Teacher teacher : teachers) {
System.out.println(teacher);
}
}
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-17 23:13:39,502 ==> Preparing: select * from t_teacher WHERE id in ( ? , ? , ? , ? , ? ) (BaseJdbcLogger.java:145)
DEBUG 02-17 23:13:39,571 ==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer) (BaseJdbcLogger.java:145)
DEBUG 02-17 23:13:39,598 <== Total: 3 (BaseJdbcLogger.java:145)
Teacher(id=1, name=张三Test, className=生物学, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)
Teacher(id=2, name=李四, className=数学, birth=Wed Feb 17 00:00:00 CST 2021, address=上海市)
Teacher(id=3, name=王五, className=英语, birth=Wed Feb 17 00:00:00 CST 2021, address=河南省)
- 当使用字典(或者Map.Entry对象的集合)时:
TeacherMapper.xml:
<update id="batchUpdateByMapParam">
update t_teacher
<set>
<foreach collection="map" index="key" item="value" >
${key} = #{value},
</foreach>
</set>
<where>
id in
</where>
<foreach collection="ids" item="value" open="(" separator="," close=")">
#{value}
</foreach>
</update>
TeacherMapper.java:
int batchUpdateByMapParam(@Param("map") Map<String , Object> map , @Param("ids") List<Integer> ids);
Test.java:
@Test
public void testMybatisSelect04(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Map<String , Object> map = new HashMap<>();
map.put("name" , "test01");
map.put("class_name" , "地理");
map.put("address" , "郑州市");
List<Integer> ids = Arrays.asList(1, 2);
int i = teacherMapper.batchUpdateByMapParam(map, ids);
System.out.println(i > 0 ? "修改成功" : "修改失败");
}finally {
sqlSession.close();
}
}
六、bind
bind
元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文
。
TeacherMapper.xml:
<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
<bind name="myAddress" value="'%'+address+'%'"/>
select * from t_teacher
<where>
<choose>
<!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
<when test="name != null && !name.equals('')">
and name = #{name}
</when>
<when test="className != null and className != """>
and class_name = #{className}
</when>
<!--以上条件都不符合时,按照地址来查-->
<otherwise>
address like #{myAddress}
</otherwise>
</choose>
</where>
</select>
TeacherMapper.java:
List<Teacher> getTeacherListByCondition(Teacher teacher);
Test.java:
@Test
public void testMybatisSelect(){
//获取与数据库的一次会话
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
Teacher teacher = new Teacher();
teacher.setAddress("郑");
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
if (teachers != null && teachers.size() > 0 ){
for (Teacher t : teachers) {
System.out.println(t);
}
}
}finally {
sqlSession.close();
}
}
测试结果:
DEBUG 02-18 10:41:39,988 ==> Preparing: select * from t_teacher WHERE address like ? (BaseJdbcLogger.java:145)
DEBUG 02-18 10:41:40,075 ==> Parameters: %郑%(String) (BaseJdbcLogger.java:145)
DEBUG 02-18 10:41:40,114 <== Total: 2 (BaseJdbcLogger.java:145)
Teacher(id=1, name=test01, className=地理, birth=Fri Jan 01 00:00:00 CST 2021, address=郑州市)
Teacher(id=2, name=test01, className=地理, birth=Wed Feb 17 00:00:00 CST 2021, address=郑州市)
七、sql
抽取
可重用
的sql片段
接着上个例子进行扩展,如下:
TeacherMapper.xml:
<!--抽取可重用的sql-->
<sql id="commonSql">
select * from t_teacher
</sql>
<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
<bind name="myAddress" value="'%'+address+'%'"/>
<!--引用重用的sql片段-->
<include refid="commonSql"/>
<where>
<choose>
<!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
<when test="name != null && !name.equals('')">
and name = #{name}
</when>
<when test="className != null and className != """>
and class_name = #{className}
</when>
<!--以上条件都不符合时,按照地址来查-->
<otherwise>
address like #{myAddress}
</otherwise>
</choose>
</where>
</select>
八、OGNL表达式
OGNL
( Object Graph Navigation Language )对象图导航语言,这是一种强大的
表达式语言,通过它可以非常方便的来操作对象属性。 类似于我们的EL,SpEL等
九、多数据库支持
若在 mybatis 配置文件中配置了
databaseIdProvider
, 则可以使用 “_databaseId”变量,这样就可以根据不同的数据库厂商构建特定的语句
扩展:
mybatis还提供两个参数可用于判断:
- _parameter:可以代表传入的参数,可以代表传入的单个参数或者多个参数,当要判断所有的参数不为空时,可以使用下面的语句判断:
<if test="_parameter != null">
.......
</if>
- 另一个就是_databaseId,作用就像上面多数据库支持时使用那样