MyBatis中动态sql的实现方法示例

1. 动态sql

动态sql是mybatis中的一个核心,什么是动态sql?

动态sql即对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接、组装。

MyBatis的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。有些时候,SQL语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息。使用Oracle的序列、mySQL的函数生成Id。这时我们可以使用动态SQL。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

MyBatis中用于实现动态SQL的元素主要有:

1、if和where

2、choose(when,otherwise)

3、trim

4、set

5、foreach

就拿上一篇博文中对用户的综合查询一例来说:

select * from user where user.sex = #{user.sex} and user.username like '%${user.username}%'

假如这个user是null咋整?或者user.sex或者user.username为null呢?所以更严谨的做法应该是在执行这个语句之前要先进行判断才对,确保都不为空,那么我再去查询。这就涉及到了mybatis中的动态sql了。

在mybatis中,动态sql可以使用标签来表示,这很类似于jstl表达式,我们可以将上面的sql语句改成动态sql,如下:

<select id="findUserList" parameterType="mybatis.po.UserQueryVo" resultType="mybatis.po.User">
 select * from user <!-- where可以自动去掉条件中的第一个and -->
 <where>
 <if test="user!=null">
 <if test="user.sex!=null and user.sex!=''">
 and user.sex = #{user.sex} </if>
 <if test="user.username!=null and user.username!=''">
 and user.username like '%${user.username}%' </if>
 </if>
 </where>
</select>

上面的代码很好理解,主要就是加了一些判断,条件不为空,才进行查询条件的拼接,让mybatis动态的去执行。那么在测试代码中,我们可以故意的将user.sex不赋初值,就可以看到查询的结果是不一样的。

2. sql片段

那么现在还有个问题,如果好几个statement都需要这样做,而且动态sql部分都一样,这就会导致一些代码的重复,所以如果遇到这种情况,我们就应该抽取,动态sql也可以抽取,我们可以将动态的这部分sql抽取成sql片段,然后在具体的statement中引用进来即可。如下:

<sql id="query_user_where">
 <if test="user!=null">
 <if test="user.sex!=null and user.sex!=''">
 and user.sex = #{user.sex} </if>
 <if test="user.username!=null and user.username!=''">
 and user.username like '%${user.username}%' </if>
 </if>
</sql>

id是给该sql片段起个名字而已,内部就是上面的where动态部分,然后我们将上面原来的动态部分改成对这个sql片段的引用,如下:

<select id="findUserList" parameterType="mybatis.po.UserQueryVo" resultType="mybatis.po.User">
 select * from user <where>
 <!-- 引用sql片段的id,如果refid指定的id不在本mapper文件中,需要在前面加上namespace -->
 <include refid="query_user_where"></include>
 <!-- 还可以引用其他sql片段 -->
 </where>
</select>

3. foreach

还有个问题:如果我们要向sql传递数组或List该咋整呢?mybatis使用的是foreach解析。为了模拟这个场景,我们将上面的查询改成多个id查询,有两种查询方式:

SELECT * FROM USER WHERE id=1 OR id=12 OR id=17SELECT * FROM USER WHERE id IN(1,12,17)

首先有一点很明确,既然要使用多个id进行查询,那么多个id肯定要作为参数传进来,所以存储多个id的List需要放到UserQueryVo中作为一个属性,这点很好理解,所以我们先在UserQueryVo中增加这个属性:

//传入多个id
private List<Integer> ids;

然后我们修改UserMapper.xml中的sql片段(还是写在sql片段中),如下:

<sql id="query_user_where">
 <if test="user!=null">
 <if test="user.sex!=null and user.sex!=''">
 and user.sex = #{user.sex} </if>
 <if test="user.username!=null and user.username!=''">
 and user.username like '%${user.username}%' </if>
 </if>
 <if test="ids!=null">
 <!-- 使用右边的sql拼接:AND (id=1 OR id=12 OR id=17) -->
 <foreach collection="ids" item="user_id" open="AND (" close=")" separator="OR">
 id=#{user_id}
  </foreach>
 </if>
</sql>

下面简单介绍一下这个foreach中相关属性的作用:

collection:指定输入对象中的集合属性,这里就是这个ids。 item:表示每个遍历生成的对象,自己起个名儿,在foreach体中使用。 open:开始遍历时拼接的sql串。 close:结束遍历时拼接的sql串。 separator:遍历的两个对象中需要拼接的sql串。

我们测试一下,然后看下控制台打印出来的sql就很容易理解了。测试程序:

@Testpublic void testFindUserList() throws Exception {

 SqlSession sqlSession = sqlSessionFactory.openSession();
 //创建UserMapper对象,mybatis自动生成mapper代理对象
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

 //创建包装对象,设置查询条件
 UserQueryVo userQueryVo = new UserQueryVo();
 User user = new User();
 //由于这里使用动态sql,如果不设置某个值,条件不会拼接在sql中
 user.setSex("男");
 user.setUsername("倪升武");

 //传入多个id
 List<Integer> ids = new ArrayList<Integer>();
 ids.add(1);
 ids.add(12);
 ids.add(17);
 userQueryVo.setIds(ids);

 userQueryVo.setUser(user);
 //调用userMapper的方法
 List<User> list = userMapper.findUserList(userQueryVo);
 System.out.println(list);}

看下控制台打印出的sql:

select * from user WHERE user.sex = ? and user.username like '%倪升武%' AND ( id=? OR id=? OR id=? ) 

注意一个细节:在mybatis中,如果输入的是Integer或者int类型的0,上面那个if判断标签返回的是false,也就是说,即使非空非'',也不会拼接标签体中的sql。

所以mybatis自动的将多个id拼接到了sql中。那么另外一个sql的实现就不再赘述了,跟上面的一样,唯一不同的就是sql片段部分,如下:

<!-- 使用右边的sql拼接:AND id IN(1,12,17) -->
<foreach collection="ids" item="user_id" open="AND id IN(" close=")" separator=",">
 #{user_id}</foreach>

总结:

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对我们的支持。

时间: 2018-11-08

详解Mybatis逆向工程中使用Mysql8.0版本驱动遇到的问题

前言 今天在使用 8.0.12 版的 mysql 驱动时遇到了各种各样的坑,在使用 JDBC 连接上遇到的问题可以参考我的上一篇博客.我在使用 mybatis 逆向工程生成各种 mapper , pojo , dao 时,遇到了一个困惑我好几个小时的错误,这个错误是 Result Maps collection already contains value for BaseResultMap 产生这个错误可能有各种原因.但是这里我只说我的原因及解决过程. 初步探索 我在网上查阅了大量的博客文章,

Mybatis核心组成部分之SQL映射文件揭秘详解

前言 Mybatis真正强大的地方在于SQL映射语句,这也是它的魅力所在. 相对于它强大的功能,SQL映射文件的配置却非常简单,我上篇文章语句讲了Mybatis的搭建以及核心配置的讲解,接下来咱们就一起来看看Mybatis另一个重要的元素-SQL映射文件 首先先介绍一下SQL映射文件的几个顶级元素配置 顶级元素配置 1.Mapper:映射文件的根元素节点,只有一个属性namespace(命名空间),其作用如下 用于区分不同的mapper,全局唯一 绑定DAO接口,即面向接口编程.当namespa

mybatis连接MySQL8出现的问题解决方法

使用MySQL8,在整合ssm框架,用mybatis逆向工程生成的代码测试时,执行到数据库查询前均正常,但进行查询时,便卡主没有反应了,设置了日志.try catch等也不报错,页面就在那一直转,之前mybatis自动生成代码都是正常的,然后在测试类中,使用Connection进行连接测试并查询数据库,也是能够正常查询到数据的: Connection conn = null; try { String userName = "root"; String password = "

MyBatis直接执行SQL的工具SqlMapper

可能有些人也有过类似需求,一般都会选择使用其他的方式如Spring-JDBC等方式解决. 能否通过MyBatis实现这样的功能呢? 为了让通用Mapper更彻底的支持多表操作以及更灵活的操作,在2.2.0版本增加了一个可以直接执行SQL的新类SqlMapper. 我们来了解一下SqlMapper. SqlMapper提供的方法 SqlMapper提供了以下这些公共方法: Map<String,Object> selectOne(String sql) Map<String,Object&

SQL 正则表达式及mybatis中使用正则表达式

mysql 提供的模式匹配的其他类型是使用扩展正则表达式. 当你对这类模式进行匹配测试时,使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE,它们是同义词). 扩展正则表达式的一些字符是: "."匹配任何单个的字符. 一个字符类"[...]"匹配在方括号内的任何字符.例如,"[abc]"匹配"a"."b"或"c".为了命名字符的一个范围,使用一个"-

如何批量测试Mybatis项目中的Sql是否正确详解

去Oracle行动 最近公司要发展海外项目,所以要将现有的系统全部平移过去,另外数据库也要从原来的Oracle变为Mysql.公司的数据库交互层面使用的是Mybatis,而Oracle与Mysql也有一些语法上的不同.所以在项目中的Sql要改动,但是多个项目中涉及到的Sql非常多,如果仅凭人工一条一条辨别的话,工作量有点大.所以就萌发出了直接将数据源变为Mysql,利用反射批量执行Mapper中的方法,然后如果有参数的话,就设置为默认的初始值,然后记录下来成功的数据和失败的数据,这样就可以根据失

Mybatis学习笔记之动态SQL揭秘

前言 MyBatis 的强大特性之一便是它的动态 SQL.所以今天小编在这里为大家介绍一下Mybatis的一个强大功能-动态SQL 动态SQL是Mybatis的一个强大的特性,在使用JDBC操作数据时,如果查询条件特别多,将条件串联成SQL字符串是一件非常痛苦的事情,通常的解决方法使写很多的if-else条件语句去判断和拼接,并确保不能忘了空格或在字段的最后省略逗号.Mybatis使用一种强大的动态SQL语言来改善这种情况 动态SQL基于OGNL的表达式,可使我们能方便地在SQL语句中实现某些逻

详解mybatis.generator配上最新的mysql 8.0.11的一些坑

一.简介 mybatis-geneator是一款mybatis自动代码生成工具,可以通过配置,自动生成Entity.mapper和xml文件. 二.配置(配置的话  按着我这个来配置吧 !  ) 在pom文件的<build>下的<plugins>添加以下配置 <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plu

MyBatis执行动态SQL的方法

大家基本上都知道如何使用 MyBatis 执行任意 SQL,使用方法很简单,例如在一个 XXMapper.xml 中: <select id="executeSql" resultType="map"> ${_parameter} </select> 你可以如下调用: sqlSession.selectList("executeSql", "select * from sysuser where enabled

mybatis mysql delete in操作只能删除第一条数据的方法

出现的Bug 如图,我开始复制delete语句和参数到数据库执行,删除两条数据,但是后台执行确只删除一条数据,当时表示一脸懵逼  分析原因 分析原因 如图,正确的参数传值应该是这样的,聪明的同学,应该就知道哪里错了 解决问题  解决问题 我就不贴开始的代码了,直接贴解决bug的代码 mybatis中的代码 <!-- 批量删除--> <delete id="deleteByIds" parameterType="int[]"> <![CD

php+mysqli预处理技术实现添加、修改及删除多条数据的方法

本文实例讲述了php+mysqli预处理技术实现添加.修改及删除多条数据的方法.分享给大家供大家参考.具体分析如下: 首先来说说为什么要有预处理(预编译)技术?举个例子:假设要向数据库添加100个用户,按常规思路,就是向数据库发送100个执行请求,此时,按照 mysql 数据库的工作原理,它需要对每一条执行语句进行编译(这里就有100次).所以,这里的效率是非常低的. 预处理(预编译)技术的作用,就是减少编译的次数和时间,以提高效果.通过一个案例来说明,预处理(预编译)技术是如何做到的(好吧,先

mysql实现合并同一ID对应多条数据的方法

本文实例讲述了mysql实现合并同一ID对应多条数据的方法.分享给大家供大家参考,具体如下: 如 : CREATE TABLE `c_classuser_tab` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Classid` int(11) DEFAULT NULL, `Username` varchar(100) DEFAULT NULL, `studentid` varchar(100) DEFAULT NULL, `College` varchar(1

SQL语句分组获取记录的第一条数据的方法

使用Northwind 数据库 首先查询Employees表 查询结果: city列里面只有5个城市 使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2) 先进行分组 注:根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). sql语句为: select EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,City,ROW_NUM

mysql delete 多表连接删除功能

单个表的删除: DELETE FROM tableName WHERE columnName = value; 删除表内的所有行: 即:保留表的结构.属性.索引 DELETE FROM tablename; DELETE * FROM tablename; 删除同一张表内的所有内容(删除数据.表结构) TRUNCATE customer; 无法报告删除了几行,且只能用于单一表 多表连接删除: DELETE orders,itrms FROM orders,items WHERE orders.u

SQL获取第一条记录的方法(sqlserver、oracle、mysql数据库)

Sqlserver 获取每组中的第一条记录 在日常生活方面,我们经常需要记录一些操作,类似于日志的操作,最后的记录才是有效数据,而且可能它们属于不同的方面.功能下面,从数据库的术语来说,就是查找出每组中的一条数据.下面我们要实现的就是在sqlserver中实现从每组中取出第一条数据. 例子 我们要从上面获得的有效数据为: 对应的sql语句如下所示: select * from t1 t where id = (select top 1 id from t1 where grp = t.grp o

mysql为字段添加和删除唯一性索引(unique) 的方法

1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

PHP使用PDO创建MySQL数据库、表及插入多条数据操作示例

本文实例讲述了PHP使用PDO创建MySQL数据库.表及插入多条数据操作.分享给大家供大家参考,具体如下: 创建 MySQL 数据库: <?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername", $username, $pas

Yii+MYSQL锁表防止并发情况下重复数据的方法

本文实例讲述了Yii+MYSQL锁表防止并发情况下重复数据的方法.分享给大家供大家参考,具体如下: lock table 读锁定 如果一个线程获得在一个表上的read锁,那么该线程和所有其他线程只能从表中读数据,不能进行任何写操作. lock tables user read;//读锁定表 unlock tables;//解锁 lock tables user read local;//本地读锁定表,其他线程的insert未被阻塞,update操作被阻塞 lock table 写锁定 如果一个线

MySQL 多表关联一对多查询实现取最新一条数据的方法示例

本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法.分享给大家供大家参考,具体如下: MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据,数据出现重复 由于历史原因,表结构设计不合理:产品告诉我说需要导出客户信息数据,需要导出客户的 所属行业,纳税性质 数据:但是这两个字段却在订单表里面,每次客户下单都会要求客户填写:由此可知,客户数据和订单数据是一对多的关系:那这样的话,问题就来了,我到底以订单中的哪一条数据为准呢?经过协商后一致同