动态SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
数据库环境准备
-- 动态SQL
USE mybatis;
CREATE TABLE IF NOT EXISTS `user` (
uid INT NOT NULL AUTO_INCREMENT,
uname VARCHAR(50) NOT NULL,
upassword VARCHAR(50) NOT NULL,
u_nickname VARCHAR(50) NOT NULL,
PRIMARY KEY (uid)
);
INSERT INTO `user` VALUES (NULL, '赵云', '123456', '常山赵子龙'), (NULL, '张飞', 'asdfff', '燕人张飞'), (NULL, '关羽', 'ghhjkl', '关云长');
sql标签
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
private static final long serialVersionUID = -5970028623059313270L;
private Integer uid;
private String uname;
private String upassword;
private String unickname;
}
public interface UserMapper {
User selectByPrimaryKey(Integer uid);
}
<?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="org.westos.mapper.UserMapper">
<sql id="baseColumn">
uid, uname, upassword, u_nickname
</sql>
<select id="selectByPrimaryKey" resultType="User">
select
<include refid="baseColumn"/>
from
user
where
uid = #{uid}
</select>
</mapper>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.selectByPrimaryKey(1);
System.out.println(JSON.toJSONString(user, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user where uid = ?
==> Parameters: 1(Integer)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 1, 赵云, 123456, 常山赵子龙
<== Total: 1
{
"uid":1,
"uname":"赵云",
"unickname":"常山赵子龙",
"upassword":"123456"
}
if标签
//根据条件查询
//使用if标签
List<User> selectAll(User user);
<select id="selectAll" resultType="User">
select
<include refid="baseColumn"/>
from
user
where 1 = 1
<if test="uid != null">
and uid = #{uid}
</if>
<if test="uname != null and uname != ''">
and uname = #{uname}
</if>
<if test="upassword != null and upassword != ''">
and upassword = #{upassword}
</if>
</select>
如果传递的User对象属性都为null,那么就是查询全部User。
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(2);
user.setUname("张飞");
List<User> users = mapper.selectAll(user);
System.out.println(JSON.toJSONString(users, true));
} catch (IOException e) {
e.printStackTrace();
}
}
User对象的uid和uname是有值的。
==> Preparing: select uid, uname, upassword, u_nickname from user where 1 = 1 and uid = ? and uname = ?
==> Parameters: 2(Integer), 张飞(String)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 2, 张飞, asdfff, 燕人张飞
<== Total: 1
[
{
"uid":2,
"uname":"张飞",
"unickname":"燕人张飞",
"upassword":"asdfff"
}
]
where标签
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
//根据条件查询
//使用where标签
List<User> selectAll2(User user);
<!--where标签 标签的特点是只有满足if判断条件,才输出where,而且可以去除 第一个 and / or-->
<select id="selectAll2" resultType="User">
select
<include refid="baseColumn"/>
from
user
<where>
<if test="uid != null">
and uid = #{uid}
</if>
<if test="uname != null and uname != ''">
and uname = #{uname}
</if>
<if test="upassword != null and upassword != ''">
and upassword = #{upassword}
</if>
</where>
</select>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(2);
user.setUname("张飞");
List<User> users = mapper.selectAll2(user);
System.out.println(JSON.toJSONString(users, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user WHERE uid = ? and uname = ?
==> Parameters: 2(Integer), 张飞(String)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 2, 张飞, asdfff, 燕人张飞
<== Total: 1
[
{
"uid":2,
"uname":"张飞",
"unickname":"燕人张飞",
"upassword":"asdfff"
}
]
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("");
List<User> users = mapper.selectAll2(user);
System.out.println(JSON.toJSONString(users, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user
==> Parameters:
<== Columns: uid, uname, upassword, u_nickname
<== Row: 1, 赵云, 123456, 常山赵子龙
<== Row: 2, 张飞, asdfff, 燕人张飞
<== Row: 3, 关羽, ghhjkl, 关云长
<== Total: 3
[
{
"uid":1,
"uname":"赵云",
"unickname":"常山赵子龙",
"upassword":"123456"
},
{
"uid":2,
"uname":"张飞",
"unickname":"燕人张飞",
"upassword":"asdfff"
},
{
"uid":3,
"uname":"关羽",
"unickname":"关云长",
"upassword":"ghhjkl"
}
]
set标签
void updateUser(User user);
<!--set标签-->
<update id="updateUser">
update
user
<set>
<if test="uname != null and uname != ''">
uname = #{uname},
</if>
<if test="upassword != null and upassword != ''">
upassword = #{upassword},
</if>
<if test="unickname != null and unickname != ''">
u_nickname = #{unickname},
</if>
</set>
where uid = #{uid}
</update>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(3);
user.setUname("黄忠");
user.setUpassword("huang");
user.setUnickname("汉升");
mapper.updateUser(user);
sqlSession.commit();
System.out.println(JSON.toJSONString(mapper.selectByPrimaryKey(3), true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: update user SET uname = ?, upassword = ?, u_nickname = ? where uid = ?
==> Parameters: 黄忠(String), huang(String), 汉升(String), 3(Integer)
<== Updates: 1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@10a035a0]
==> Preparing: select uid, uname, upassword, u_nickname from user where uid = ?
==> Parameters: 3(Integer)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 3, 黄忠, huang, 汉升
<== Total: 1
{
"uid":3,
"uname":"黄忠",
"unickname":"汉升",
"upassword":"huang"
}
trim标签
<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 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
查询
//使用trim标签
List<User> selectAll3(User user);
<!--trim标签的属性,prefix增加一个前缀,prefixOverrides去除一个前缀-->
<!--suffix增加一个后缀,suffixOverrides去除一个后缀-->
<!--主要是在插入、更新时使用-->
<select id="selectAll3" resultType="User">
select
<include refid="baseColumn"/>
from
user
<trim prefix="where" prefixOverrides="and |or ">
<if test="uid != null">
and uid = #{uid}
</if>
<if test="uname != null and uname.trim != ''">
and uname = #{uname}
</if>
</trim>
</select>
prefix:增加一个前缀
prefixOverrides:去除一个前缀。
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(3);
user.setUname("黄忠");
System.out.println(JSON.toJSONString(mapper.selectAll3(user), true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user where uid = ? and uname = ?
==> Parameters: 3(Integer), 黄忠(String)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 3, 黄忠, huang, 汉升
<== Total: 1
[
{
"uid":3,
"uname":"黄忠",
"unickname":"汉升",
"upassword":"huang"
}
]
更新
//使用trim标签
void updateUser2(User user);
<!--prefix增加一个前缀,suffixOverrides去除一个后缀-->
<update id="updateUser2">
update
user
<trim prefix="set" suffixOverrides=",">
<if test="uname != null and uname.trim != ''">
uname = #{uname},
</if>
<if test="upassword != null and upassword.trim != ''">
upassword = #{upassword},
</if>
<if test="unickname != null and unickname.trim != ''">
u_nickname = #{unickname},
</if>
</trim>
where uid = #{uid}
</update>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(3);
user.setUname("马超");
user.setUpassword("ma");
user.setUnickname("马儿");
mapper.updateUser2(user);
sqlSession.commit();
System.out.println(JSON.toJSONString(mapper.selectAll3(user), true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: update user set uname = ?, upassword = ?, u_nickname = ? where uid = ?
==> Parameters: 马超(String), ma(String), 马儿(String), 3(Integer)
<== Updates: 1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@481a15ff]
==> Preparing: select uid, uname, upassword, u_nickname from user where uid = ? and uname = ?
==> Parameters: 3(Integer), 马超(String)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 3, 马超, ma, 马儿
<== Total: 1
[
{
"uid":3,
"uname":"马超",
"unickname":"马儿",
"upassword":"ma"
}
]
插入
//使用trim标签
void insertUser(User user);
<insert id="insertUser">
insert into user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="uname != null">
uname,
</if>
<if test="upassword != null">
upassword,
</if>
<if test="unickname != null">
u_nickname,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="uname != null">
#{uname},
</if>
<if test="upassword != null">
#{upassword},
</if>
<if test="unickname != null">
#{unickname},
</if>
</trim>
</insert>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUname("黄忠");
user.setUpassword("huang");
user.setUnickname("汉升");
mapper.insertUser(user);
sqlSession.commit();
System.out.println(JSON.toJSONString(mapper.selectAll(null), true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: insert into user ( uname, upassword, u_nickname ) values ( ?, ?, ? )
==> Parameters: 黄忠(String), huang(String), 汉升(String)
<== Updates: 1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@706a04ae]
==> Preparing: select uid, uname, upassword, u_nickname from user where 1 = 1
==> Parameters:
<== Columns: uid, uname, upassword, u_nickname
<== Row: 1, 赵云, 123456, 常山赵子龙
<== Row: 2, 张飞, asdfff, 燕人张飞
<== Row: 3, 马超, ma, 马儿
<== Row: 4, 黄忠, huang, 汉升
<== Total: 4
[
{
"uid":1,
"uname":"赵云",
"unickname":"常山赵子龙",
"upassword":"123456"
},
{
"uid":2,
"uname":"张飞",
"unickname":"燕人张飞",
"upassword":"asdfff"
},
{
"uid":3,
"uname":"马超",
"unickname":"马儿",
"upassword":"ma"
},
{
"uid":4,
"uname":"黄忠",
"unickname":"汉升",
"upassword":"huang"
}
]
foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
//使用foreach标签
List<User> select(List<Integer> list);
<select id="select" resultType="User">
select
<include refid="baseColumn"/>
from
user
<trim prefix="where">
uid in
<foreach collection="list" open="(" close=")" separator="," index="index" item="item">
#{item}
</foreach>
</trim>
</select>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
ArrayList<Integer> arrayList = new ArrayList<>(Arrays.asList(1, 2, 3, 4));
List<User> users = mapper.select(arrayList);
System.out.println(JSON.toJSONString(users, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user where uid in ( ? , ? , ? , ? )
==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 1, 赵云, 123456, 常山赵子龙
<== Row: 2, 张飞, asdfff, 燕人张飞
<== Row: 3, 马超, ma, 马儿
<== Row: 4, 黄忠, huang, 汉升
<== Total: 4
[
{
"uid":1,
"uname":"赵云",
"unickname":"常山赵子龙",
"upassword":"123456"
},
{
"uid":2,
"uname":"张飞",
"unickname":"燕人张飞",
"upassword":"asdfff"
},
{
"uid":3,
"uname":"马超",
"unickname":"马儿",
"upassword":"ma"
},
{
"uid":4,
"uname":"黄忠",
"unickname":"汉升",
"upassword":"huang"
}
]
choose…when…otherwise
//使用choose...when...otherwise标签
User select2(User user);
<select id="select2" resultType="User">
select
<include refid="baseColumn"/>
from
user
where
<choose>
<when test="uid != null">
uid = #{uid}
</when>
<when test="uname != null">
uname = #{uname}
</when>
<otherwise>
upassword = #{upassword}
</otherwise>
</choose>
</select>
@Test
public void test() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUid(1);
User user1 = mapper.select2(user);
System.out.println(JSON.toJSONString(user1, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select uid, uname, upassword, u_nickname from user where uid = ?
==> Parameters: 1(Integer)
<== Columns: uid, uname, upassword, u_nickname
<== Row: 1, 赵云, 123456, 常山赵子龙
<== Total: 1
{
"uid":1,
"uname":"赵云",
"unickname":"常山赵子龙",
"upassword":"123456"
}