MyBatis动态SQL
本文主要根据自己demo案例,详细介绍动态SQL的使用。基于官网,但比官网更详细。
版本MyBatis3.5.2。
动态SQL,可被应用于任意SQL映射语句中。常见的元素为if、choose(when,otherwise)、trim(where,set)、foreach。
先来认识/回顾一下MyBatis:
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。
iBATIS一词来源于“internet”和“abatis”的组合,是一个基于Java的持久层框架。
iBATIS提供的持久层框架包括SQL Maps和Data Access Objects(DAOs)
当前,最新版本是MyBatis 3.5.2 ,其发布时间是2019年7月15日。
(以上内容来自百度百科,所以说百度是一个好东西)
if
通常包含在where条件语句中,用作判断。如果需要多个条件,并列书写、添加逻辑运算符都可以。
<select id="findActiveBlogWithTitleLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
</select>
choose,when,otherwise
不需要应用到所有条件,只需从中选择一项的情况,类似Java中提供的switch语句。在查询中、更新时,给到了什么条件,就按照什么条件进行。比如在博客表中,提供了“title”就按“title”查找,提供了“author”就按“author”查找,二者都有时,都会进行。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<choose>
<when test='title != null'>
AND title like #{title}
</when>
<when test='author != null and author.name != null'>
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
trim,where,set
分析前面两种情况,可方便解决了大部分问题。但如果where后面active也是需要判断的,该如何处理?
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
如果上述条件均未匹配,最终sql变为:
SELECT * FROM BLOH WHERE
查询失败;仅匹配第二个,sql为:
SELECT * FROM BLOG WHERE
AND title like 'xxx'
where后面直接接了and,查询失败;之前可能你会用
where 1 = 1
来解决。但在MyBatis中,有一个简单的处理:
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
where元素只会在至少有一个子元素符合条件的情况下才去插入“where”子句。如果开头为“AND”或“OR”,where元素自动将他们去除。
1
如果where元素还有其他特殊情况,可以通过自定义trim元素来定制where元素的功能。
trim元素的主要功能是可以在自己包含的内容钱加上某些前缀,也可以在其后加上某写后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides。
例如,和where元素等价的自定义trim元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR">
...
</trim>
prefixOverrides作用是移除所有指定在prefixOverrides属性中的内容(中间的空格不可少),并且插入prefix属性中指定的内容。举一个常见的例子,往Blog中添加Blog的标题和描述:
<insert id="insertBlog" paramenterType="xxx.xxx.Blog">
INSERT INTO Blog
(
<if test="title != null">
title,
</if>
<if test="description != null">
description
</if>
)
VALUES
(
<if test="title != null">
#{title},
</if>
<if test="description != null">
#{description}
</if>
)
</insert>
那么如果第二个字段为空了,if条件不成立,sql语句变为:
INSERT INTO Blog(title,) VALUES(title,)
这种格式会报错。改用trim标签,则可以避免这种错误:
<insert id="insertBlog" paramenterType="xxx.xxx.Blog">
INSERT INTO Blog
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="title != null">
title,
</if>
<if test="description != null">
description
</if>
)
</trim>
<trim prefix="VALUES(" suffix=")" suffixOverrides=",">
<if test="title != null">
#{title},
</if>
<if test="description != null">
#{description}
</if>
</trim>
</insert>
1
注意:
在更新语句中,动态更新语句的标签元素使用set。更新满足条件的字段,其他不满足条件的不处理:
<update id="updateUserInfo">
update User
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email}></if>
</set>
where id=#{id}
</update>
set元素会动态前置关键字set,同时也能够自动删除无关的逗号。使用trim元素定义等价set元素:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
foreach
动态SQL中的另外一个常用操作需求是对一个集合进行遍历,通常是在构建IN条件语句的时候。
<foreach item="" index="" collection="" open="" separator="" close="">
1
foreach元素允许你指定一个集合,声明可以在元素内使用的集合项item和索引index变量。同时可以指定开头与结尾的字符串以及在迭代结果之间放置分隔符。
你可以将任何可迭代对象(如List、Set等)、Map对象或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时,index是当前迭代的次数,item的值是本次迭代获取的元素。当使用Map对象(或者Map.Entry对象的集合)时,index是键,item是值。
一.传入一个List进行查询
方法1
BlogMapper.xml文件内容:
<select id="selectBlogList" resultType="test.Blog">
select * from Blog where id IN
<foreach collection="blogIds" item="item" index="index" separator="," close=")" open="(">
#{item}
</foreach>
</select>
BlogMapper接口:
public interface BlogMapper {
List<Blog> selectBlogList(@Param("blogIds")List<String> blogIds);
}
测试类(启动类):
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
List<String> ids = new ArrayList<>();
ids.add("1");
ids.add("3");
ids.add("4");
try (SqlSession session = sqlSessionFactory.openSession()) {
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<Blog> blogList = mapper.selectBlogList(ids);
}catch (Exception e){
e.printStackTrace();
}
}
}
控制台结果:
Preparing: select * from Blog where id IN ( ? , ? , ? )
Parameters: 1(String), 3(String), 4(String)
Total: 3
使用这种方法时,BlogMapper.xml里面foreach中collection的值必须和BlogMapper接口中@Param指定的名字保持一致。在测试类中传入一个list即可。item中的值可以随便写,#{}中的值必须和item的值保持一致。item="item"、item="id"都可以!
方法2
BlogMapper.xml文件:
<select id="selectBlogList" resultType="test.Blog">
select * from Blog where id IN
<foreach collection="list" item="id" index="index" separator="," close=")" open="(">
#{id}
</foreach>
</select>
此时collection使用list。item中和值和#{}保持一致。
BlogMapper接口:
public interface BlogMapper {
List<Blog> selectBlogList(List<String> blogIds);
}
在xml里面collection使用了list,则在接口文件中直接出入List类型的变量即可。
测试类中的书写方式和上面的一样。同样,结果也是一样的。
二、传入Map进行查询
注意:map类型的,在collection中不可以直接写map!!!
原因在于:"你可以传递一个 List 实例或者数组作为参数对象传给 MyBatis。当你这么做的时 候,MyBatis 会自动将它包装在一个 Map 中,用名称在作为键。List 实例将会以“list” 作为键,而数组实例将会以“array”作为键。"---官方文档解释。
所以当你传入map时,会被认为是一个没有值的变量。null value。
BlogMapper.xml:
<select id="selectBlogByMap" resultType="test.Blog">
select * from Blog where
<foreach collection="mapByIdTitle" open="" close="" separator="AND" index="key" item="value">
${key} = #{value}
</foreach>
</select>
BlogMapper接口文件:
public interface BlogMapper {
List<Blog> selectBlogByMap(@Param("mapByIdTitle") Map<String,Object> map);
}
测试类:
public class Demo {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
Map<String,Object> map = new HashMap<>();
map.put("id",1);
map.put("title","ABC");
try (SqlSession session = sqlSessionFactory.openSession()) {
BlogMapper mapper = session.getMapper(BlogMapper.class);
List<Blog> blogListMap = mapper.selectBlogByMap(map);
for(Blog b : blogListMap){
System.out.println("来了 老弟:" + b.getTitle());
}
}catch (Exception e){
e.printStackTrace();
}
}
}
结果:
019-08-28 10:53:41,561 [main] DEBUG [test.BlogMapper.selectBlogByMap] - ==> Preparing: select * from Blog where id = ? AND title = ?
2019-08-28 10:53:41,561 [main] DEBUG [test.BlogMapper.selectBlogByMap] - ==> Parameters: 1(Integer), ABC(String)
2019-08-28 10:53:41,562 [main] DEBUG [test.BlogMapper.selectBlogByMap] - <== Total: 1
来了 老弟:ABC
注意:接口文件中@Param和collection中的保持一致
三、传入数组进行查询
和list极其相似,推而广之。
BlogMapper.xml:
<select id="selectBlogByArray" resultType="test.Blog">
select * from Blog where id IN
<foreach collection="array" item="item" separator="," index="index" open="(" close=")">
#{item}
</foreach>
</select>
BlogMapper接口文件:
public interface BlogMapper {
List<Blog> selectBlogByArray(int[] ids);
}
测试类:
public class Demo {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
int[] blogIds = new int[]{1,3};
try (SqlSession session = sqlSessionFactory.openSession()) {
BlogMapper mapper = session.getMapper(BlogMapper.class);
//array单参数
List<Blog> blogListByArray = mapper.selectBlogByArray(blogIds);
for(Blog b1 : blogListByArray){
System.out.println("来了数组:" + b1.getTitle());
}
}catch (Exception e){
e.printStackTrace();
}
}
}
结果:
2019-08-28 11:11:42,282 [main] DEBUG [test.BlogMapper.selectBlogByArray] - ==> Preparing: select * from Blog where id IN ( ? , ? )
2019-08-28 11:11:42,282 [main] DEBUG [test.BlogMapper.selectBlogByArray] - ==> Parameters: 1(Integer), 3(Integer)
2019-08-28 11:11:42,284 [main] DEBUG [test.BlogMapper.selectBlogByArray] - <== Total: 2
来了数组:ABC
来了数组:E
四、使用List进行插入
按照原始想法,直接书写mapper.xml文件:
<insert id="insertBlog">
insert into Blog(title) values
<foreach collection="list" item="title" index="index" open="(" close=")" separator=",">
#{title}
</foreach>
</insert>
报错1:
### The error occurred while setting parameters
### SQL: insert into Blog(title) values(?,?,?)
### Cause: java.sql.SQLException: Column count doesn't match value count at row 1
明显插入格式不对,想要插入三条记录。但他把这三条记录插入到了一条,当成了3个字段。
报错2:
[main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a9273a8]
解决:单独使用mybatis,需要自己手动commit。
完整的正确写法:
BlogMapper.xml:
<insert id="insertBlog">
insert into Blog(title) values
<foreach collection="list" item="title" index="index" open="" close="" separator=",">
(#{title})
</foreach>
</insert>
BlogMapper接口文件:
public interface BlogMapper {
void insertBlog(List<String> titles);
}
测试类:
public class Demo {
public static void main(String[] args) throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
List<String> insertTitle = new ArrayList<>();
insertTitle.add("X");
insertTitle.add("Y");
insertTitle.add("Z");
try (SqlSession session = sqlSessionFactory.openSession()) {
BlogMapper mapper = session.getMapper(BlogMapper.class);
mapper.insertBlog(insertTitle);
session.commit();
session.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
注意:查询可以不执行commit,但更新/插入必须执行commit。
结果:
[main] DEBUG [test.BlogMapper.insertBlog] - ==> Preparing: insert into Blog(title) values (?) , (?) , (?)
[main] DEBUG [test.BlogMapper.insertBlog] - ==> Parameters: X(String), Y(String), Z(String)
[main] DEBUG [test.BlogMapper.insertBlog] - <== Updates: 3
[main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a9273a8]
[main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a9273a8]
[main] DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7a9273a8]
[main] DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Returned connection 2056418216 to pool.
总结:在使用foreach进行批量存储时,较方便的sql格式为
insert into TableName(clo1,clo2..) values(val1,val2...),(val1,val2...)
1
再唠叨一句:很多sql语句我并没有写入参类型,是因为官方文档告诉我们它很多情况下其实可以自动识别,所以,这就不用我们担心了。不信,你去官网看~
整体项目结构:
码云链接地址: https://gitee.com/JeffBro/MyBatisTest
来都原创不易,点赞分享~~~