百度360必应搜狗淘宝本站头条
mqtt服务器xftp6mybatiscollectionkeyerrorc#var
当前位置：网站首页 > 热门文章 > 正文

MyBatis 动态 SQL 详解(以后写 SQL 爽多了)

bigegpt 2024-08-21 12:03 2 浏览

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
01. 环境搭建
在正式开始学习之前，我们先搭建一下此次项目的环境，首先创建数据库和数据表：
CREATE DATABASE mybatis;

CREATE TABLE tbl_employee(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
 last_name VARCHAR(255),
 gender CHAR(1),
 email VARCHAR(255)
);

INSERT INTO tbl_employee VALUES('tom',0,'tom@qq.com');
接下来我们创建一个maven项目，引入MyBatis的依赖：
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<!-- 数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.37</version>
</dependency>
接着创建数据表对应的Bean类：
@Data
@ToString
public class Employee {

private Integer id;
private String lastName;
private char gender;
private String email;
}
在类路径下编写一个XML文件(mybatis-config.xml)来完成SqlSessionFactory的构建：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>

<!-- 注册Mapper映射文件 -->
<mappers>
<mapper resource="EmployeeMapper.xml"/>
</mappers>
</configuration>
最后创建 Mapper 接口和接口对应的映射文件。
02. if
先来看看第一个动态SQL标签——if，通过if标签，我们能够根据我们传递的参数进行动态查询，比如：根据id查询员工信息，若是携带了哪个参数sql的条件就要带上这个参数对应的字段的值。
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
 select *
 from tbl_employee
where
 <if test="id != ">
 id=#{id}
 </if>
 <if test="lastName != and lastName != ''">
 and last_name like #{lastName}
 </if>
 <if test="email != and email.trim() != ''">
 and email = #{email}
 </if>
 <if test="gender == 0 or gender == 1">
 and gender = #{gender}
 </if>
</select>
测试代码：
EmployeeDynamicSqlMapper mapper = sqlSession.getMapper(EmployeeDynamicSqlMapper.class);
Employee employee = new Employee(1, "%c%", , "jack@qq.com");
List<Employee> emps = mapper.getEmpsByConditionIf(employee);
for (Employee emp : emps) {
 System.out.println(emp);
}
我们来看看它生成的sql：
==> Preparing: select * from tbl_employee where id = ? and last_name like ? and email = ? 
==> Parameters: 1(Integer), %c%(String), jack@qq.com(String)
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
Employee(id=1, lastName=, gender=1, email=jack@qq.com)
然而这样的代码是有问题的，如果我们的条件是这样的：
Employee employee = new Employee(, "%c%", , "jack@qq.com");
来看看生成的sql：
==> Preparing: select * from tbl_employee where and last_name like ? and email = ? 
==> Parameters: %c%(String), jack@qq.com(String)
因为id为，所以后面的and被直接拼在了where的后面，此时sql语法就会出错，为此我们需要使用另一个标签——where，它能够完美地解决这一问题。
03. where
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
 select *
 from tbl_employee
 <where>
 <if test="id != ">
 id = #{id}
 </if>
 <if test="lastName != and lastName != ''">
 and last_name like #{lastName}
 </if>
 <if test="email != and email.trim() != ''">
 and email = #{email}
 </if>
 <if test="gender == 0 or gender == 1">
 and gender = #{gender}
 </if>
 </where>
</select>
只需将判断条件全部放在where标签内，MyBatis就会自动将where标签拼接的sql中多出来的and和or删掉，然而where标签依然有弊端，比如这样拼接sql：
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
 select *
 from tbl_employee
 <where>
 <if test="id != ">
 id = #{id} and
 </if>
 <if test="lastName != and lastName != ''">
 last_name like #{lastName} and
 </if>
 <if test="email != and email.trim() != ''">
 email = #{email} and
 </if>
 <if test="gender == 0 or gender == 1">
 gender = #{gender}
 </if>
 </where>
</select>
并且传递的参数条件是这种情况：
Employee employee = new Employee(, "%c%", , );
此时last_name属性后面跟着一个and，但是接下来的属性都是，所以得到了这样的一条sql：
==> Preparing: select * from tbl_employee WHERE last_name like ? and 
==> Parameters: %c%(String)
可前面我们已经说到，where标签会自动去除多余的and和or呀，为什么还是报错呢？原来，where标签只能去除属性前多余的and和or，至于属性后的，它就无能为力了，所以要么就将and和or都写在属性的前面，要么就使用trim标签来解决这一问题。
04. trim
<select id="getEmpsByConditionIf" resultType="com.wwj.mybatis.bean.Employee">
 select *
 from tbl_employee
 <trim prefix="where" suffixOverrides="and">
 <if test="id != ">
 id = #{id} and
 </if>
 <if test="lastName != and lastName != ''">
 last_name like #{lastName} and
 </if>
 <if test="email != and email.trim() != ''">
 email = #{email} and
 </if>
 <if test="gender == 0 or gender == 1">
 gender = #{gender}
 </if>
 </trim>
</select>
trim标签中共有四个属性，分别介绍一下作用：

  • prefix：指定sql的前缀，该前缀会被放在trim标签拼接好的sql前面

  • suffix：指定sql的后缀，该后缀会被放在trim标签拼接好的sql后面

  • prefixOverrides：前缀覆盖，MyBatis会自动去除属性前的指定字符串

  • suffixOverrides：后缀覆盖，MyBatis会自动去除属性后的指定字符串

在这里我们将前缀指定为where，并指定后缀覆盖为and，就能解决之前的问题了，执行结果：
==> Preparing: select * from tbl_employee where last_name like ? 
==> Parameters: %c%(String)
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
Employee(id=1, lastName=, gender=1, email=jack@qq.com)
05. choose
该标签用于分支操作，与if不同的是，choose只有一个会被执行，若有一个choose满足条件，则其它choose都将失效，比如：根据传递过来的参数进行员工信息查询，如果携带id，则根据id查询，如果携带lastName，则根据lastName查询，实现如下：
List<Employee> getEmpsByConditionChoose(Employee employee);
<select id="getEmpsByConditionChoose" resultType="com.wwj.mybatis.bean.Employee">
 select *
 from tbl_employee
<where>
<choose>
<when test="id != ">
 id = ${id}
</when>
<when test="lastName != ">
 last_name like #{lastName}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
choose标签类似于带break的switch语句，otherwise标签表示其它条件都不满足则执行该标签内容，编写业务代码：
Employee employee = new Employee(1, "%c%", , );
List<Employee> emps = mapper.getEmpsByConditionChoose(employee);
for (Employee emp : emps) {
 System.out.println(emp);
}
这里同时携带了id和lastName，sql语句会如何拼接呢？因为id的判断在lastName之前，所以id分支已经满足条件，其它分支都会失效，故sql语句如下：
==> Preparing: select * from tbl_employee WHERE id = 1 
==> Parameters: 
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, jack, 1, jack@qq.com, 1
<== Total: 1
若是什么参数都不带，则执行otherwise，sql什么都不拼接，故查询所有数据。
06. set
学会了如何动态查询之后，我们来尝试一下动态更新，即：根据传递过来的参数进行员工信息更新，携带了什么参数就更新什么参数，实现如下：
void updateEmp(Employee employee);
<update id="updateEmp">
 update tbl_employee
set
 <if test="lastName != ">
 last_name = #{lastName},
 </if>
 <if test="email != ">
 email = #{email},
 </if>
 <if test="gender != ">
 gender = #{gender}
 </if>
 <where>
 id = #{id}
 </where>
</update>
这样实现的话，当参数均携带的时候不会出现问题，然而这种情况：
Employee employee = new Employee(1, "tom", , );
mapper.updateEmp(employee);
因为lastName后面的参数都为，导致 被直接拼在了id的前面，造成语法错误，来看看生成的sql：
==> Preparing: update tbl_employee set last_name = ?, WHERE id = ? 
==> Parameters: tom(String), 1(Integer)
为此，我们可以使用set标签，将这些动态更新的内容放在set标签内就能够解决这一问题：
<update id="updateEmp">
 update tbl_employee
 <set>
 <if test="lastName != ">
 last_name = #{lastName},
 </if>
 <if test="email != ">
 email = #{email},
 </if>
 <if test="gender != ">
 gender = #{gender}
 </if>
 </set>
 <where>
 id = #{id}
 </where>
</update>
这里也可以使用trim标签解决逗号拼接的问题，方式如下：
<update id="updateEmp">
 update tbl_employee
 <trim prefix="set" suffixOverrides=",">
 <if test="lastName != ">
 last_name = #{lastName},
 </if>
 <if test="email != ">
 email = #{email},
 </if>
 <if test="gender != ">
 gender = #{gender}
 </if>
 </trim>
 <where>
 id = #{id}
 </where>
</update>

07. foreach

有时候我们需要同时查询多个值的数据，为此，可以使用foreach实现，比如 查询id为1、2、3的员工信息，实现如下：
List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

<select id="getEmpsByConditionForeach" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
where id in(
 <foreach collection="ids" item="id" separator=",">
 #{id}
 </foreach>
)
</select>
首先collection属性指定需要遍历的集合，item属性指定遍历出的每个元素的名字，separator属性指定的是每个遍历出的元素的分隔符，一定不要忘记in后面的小括号，foreach也支持将小括号写在标签内：
<select id="getEmpsByConditionForeach" resultType="com.wwj.mybatis.bean.Employee">
select *
from tbl_employee
where id in
 <foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
 </foreach>
</select>
编写业务代码：
List<Employee> emps = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3));
for (Employee emp : emps) {
 System.out.println(emp);
}
执行结果：
==> Preparing: select * from tbl_employee where id in ( ? , ? , ? ) 
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<== Columns: id, last_name, gender, email, d_id
<== Row: 1, tom, 1, jack@qq.com, 1
<== Row: 3, tom, 1, tom@qq.com, 2
<== Total: 2
Employee(id=1, lastName=, gender=1, email=jack@qq.com)
Employee(id=3, lastName=, gender=1, email=tom@qq.com)
借助foreach标签我们还能够实现批量插入，实现如下：
void addEmpBatch(@Param("emps") List<Employee> emps);
<insert id="addEmpBatch">
insert into tbl_employee(last_name,gender,email)
 values
 <foreach collection="emps" item="emp" separator=",">
 (#{emp.lastName},#{emp.gender},#{emp.email})
 </foreach>
</insert>
编写业务代码：
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(, "jack", '1', "jack@qq.com"));
emps.add(new Employee(, "jerry", '1', "jerry@qq.com"));
mapper.addEmpBatch(emps);
执行结果：
==> Preparing: insert into tbl_employee(last_name,gender,email) values (?,?,?) , (?,?,?) 
==> Parameters: jack(String), 1(String), jack@qq.com(String), jerry(String), 1(String), jerry@qq.com(String)
<== Updates: 2
也可以通过执行多条sql来实现批量插入：
<insert id="addEmpBatch">
 <foreach collection="emps" item="emp" separator=";">
insert into tbl_employee(last_name, gender, email)
values (#{emp.lastName}, #{emp.gender}, #{emp.email})
 </foreach>
</insert>
sql语句之间用 ; 分隔，但是MySQL默认是不支持这种语法的，为此，需要设置一个参数使其支持该语法：
jdbc.url=jdbc:mysql:///mybatis?allowMultiQueries=true
MyBatis提供了两个内置参数用于辅助操作，也就是说，即使方法没有传递任何参数，我们也能够在操作标签中取出这两个参数进行处理：

  • _parameter：所有参数，单个参数情况下，_parameter就是该参数；多个参数情况下，_parameter就是封装所有参数的Map集合

  • _databaseId：若配置了databaseIdProvider 标签，则该参数就是当前数据库的别名

08. bind
MyBatis提供了bind标签用于将表达式的值绑定到一个变量中，用法如下：
<select id="getEmpsByConditionChoose" resultType="com.wwj.mybatis.bean.Employee">
<bind name="_lastName" value="'%'+ lastName + '%'"/>
 select *
 from tbl_employee
<where>
<choose>
<when test="id != ">
 id = ${id}
</when>
<when test="lastName != ">
 last_name like #{_lastName}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
这里使用bind标签定义了一个变量_lastName，其值为lastName值前后拼接 % ，接下来就可以引用该变量，此时调用方法进行模糊查询就不需要自己拼接 %c% 了，而是直接传入字符串 c 即可。
09. sql
该标签能够抽取可重用的sql片段，使Mapper文件更加简洁：
<sql id="insertColumn">
 last_name,gender,email
</sql>
接下来若是想使用这一字符串，直接用include标签包含进来即可：
<insert id="addEmpBatch">
 insert into tbl_employee(
<include refid="insertColumn"></include>
 )
 values
<foreach collection="emps" item="emp" separator=",">
 (#{emp.lastName},#{emp.gender},#{emp.email})
</foreach>
</insert>

相关推荐

悠悠万事，吃饭为大（悠悠万事吃饭为大,什么意思）

新媒体编辑：杜岷赵蕾初审：程秀娟审核：汤小俊审签：周星...

高铁扒门事件升级版!婚宴上‘冲喜’老人团:我们抢的是社会资源

凌晨两点改方案时，突然收到婚庆团队发来的视频——胶东某酒店宴会厅，三个穿大红棉袄的中年妇女跟敢死队似的往前冲，眼瞅着就要扑到新娘的高额钻石项链上。要不是门口小伙及时阻拦，这婚礼造型团队熬了三个月的方案...

微服务架构实战:商家管理后台与sso设计，SSO客户端设计

SSO客户端设计下面通过模块merchant-security对SSO客户端安全认证部分的实现进行封装，以便各个接入SSO的客户端应用进行引用。安全认证的项目管理配置SSO客户端安全认证的项目管理使...

还在为 Spring Boot 配置类加载机制困惑?一文为你彻底解惑

在当今微服务架构盛行、项目复杂度不断攀升的开发环境下，SpringBoot作为Java后端开发的主流框架，无疑是我们手中的得力武器。然而，当我们在享受其自动配置带来的便捷时，是否曾被配置类加载...

Seata源码—6.Seata AT模式的数据源代理二

大纲1.Seata的Resource资源接口源码2.Seata数据源连接池代理的实现源码3.Client向Server发起注册RM的源码4.Client向Server注册RM时的交互源码5.数据源连接...

30分钟了解K8S（30分钟了解微积分）

微服务演进方向o面向分布式设计(Distribution):容器、微服务、API驱动的开发;o面向配置设计(Configuration):一个镜像，多个环境配置;o面向韧性设计(Resista...

SpringBoot条件化配置(@Conditional)全面解析与实战指南

一、条件化配置基础概念1.1什么是条件化配置条件化配置是Spring框架提供的一种基于特定条件来决定是否注册Bean或加载配置的机制。在SpringBoot中，这一机制通过@Conditional...

一招解决所有依赖冲突（克服依赖）

背景介绍最近遇到了这样一个问题，我们有一个jar包common-tool，作为基础工具包，被各个项目在引用。突然某一天发现日志很多报错。一看是NoSuchMethodError，意思是Dis...

你读过Mybatis的源码?说说它用到了几种设计模式

学习设计模式时，很多人都有类似的困扰——明明概念背得滚瓜烂熟，一到写代码就完全想不起来怎么用。就像学了一堆游泳技巧，却从没下过水实践，很难真正掌握。其实理解一个知识点，就像看立体模型，单角度观察总...

golang对接阿里云私有Bucket上传图片、授权访问图片

1、为什么要设置私有bucket公共读写：互联网上任何用户都可以对该Bucket内的文件进行访问，并且向该Bucket写入数据。这有可能造成您数据的外泄以及费用激增，若被人恶意写入违法信息还可...

spring中的资源的加载（spring加载原理）

最近在网上看到有人问@ContextConfiguration("classpath:/bean.xml")中除了classpath这种还有其他的写法么，看他的意思是想从本地文件...

Android资源使用（android资源文件）

Android资源管理机制在Android的开发中，需要使用到各式各样的资源，这些资源往往是一些静态资源，比如位图，颜色，布局定义，用户界面使用到的字符串，动画等。这些资源统统放在项目的res/独立子...

如何深度理解mybatis?（如何深度理解康乐服务质量管理的5个维度）

深度自定义mybatis回顾mybatis的操作的核心步骤编写核心类SqlSessionFacotryBuild进行解析配置文件深度分析解析SqlSessionFacotryBuild干的核心工作编写...

@Autowired与@Resource原理知识点详解

springIOCAOP的不多做赘述了，说下IOC：SpringIOC解决的是对象管理和对象依赖的问题，IOC容器可以理解为一个对象工厂，我们都把该对象交给工厂，工厂管理这些对象的创建以及依赖关系...

java的redis连接工具篇（java redis client）

在Java里，有不少用于连接Redis的工具，下面为你介绍一些主流的工具及其特点：JedisJedis是Redis官方推荐的Java连接工具，它提供了全面的Redis命令支持，且...

一周热门
最近发表
标签列表