阅读完需:约 9 分钟
动态 SQL
动态 SQL 是 MyBatis 中非常强大的一个功能。例如一些常见的查询场景:
- 查询条件不确定
- 批量插入
- ….
这些类似需求,我们都可以通过 MyBatis 提供的动态 SQL 来解决。
1.if
if 是一个判断节点,如果满足某个条件,节点中的 SQL 就会生效。例如分页查询,要传递两个参数,页码和查询的记录数,如果这两个参数都为 null,那我就查询所有。
MyBatis 中提供的动态 SQL 节点非常多。
我们首先来定义接口方法:
List<User> getUserByPage(@Param("start") Integer start, @Param("count") Integer count);
接口定义成功后,接下来在 XML 中定义 SQL:
<select id="getUserByPage" resultType="org.javaboy.mybatis.model.User">
select * from user
<if test="start !=null and count!=null">
limit #{start},#{count}
</if>
</select>
if 节点中,test 表示判断条件,如果判断结果为 true,则 if 节点的中的 SQL 会生效,否则不会生效。也就是说,在方法调用时,如果分页的两个参数都为 null,则表示查询所有数据:
public class Main2 {
public static void main(String[] args) {
SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
SqlSession sqlSession = instance.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = mapper.getUserByPage(null, null);
System.out.println(list);
list = mapper.getUserByPage(2, 2);
System.out.println(list);
sqlSession.commit();
}
}
2.where
where 用来处理查询参数。例如我存在下面一个查询函数:
List<User> getUserByUsernameAndId(@Param("id") Integer id, @Param("name") String name);
这个查询的复杂之处在于:每个参数都是可选的,如果 id 为 null,则表示根据 name 查询,name 为 null,则表示根据 id 查询,两个都为 null,表示查询所有。
<select id="getUserByUsernameAndId" resultType="org.javaboy.mybatis.model.User">
select * from user
<where>
<if test="id!=null">
and id>#{id}
</if>
<if test="name!=null">
and username like concat('%',#{name},'%')
</if>
</where>
</select>
用 where 节点将所有的查询条件包起来,如果有满足的条件,where 节点会自动加上,如果没有,where 节点也将不存在,在有满足条件的情况下,where 还会自动处理 and 关键字。
public class Main2 {
public static void main(String[] args) {
SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
SqlSession sqlSession = instance.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = mapper.getUserByUsernameAndId(2, "java");
System.out.println(list);
list = mapper.getUserByUsernameAndId(null, "javaboy");
System.out.println(list);
list = mapper.getUserByUsernameAndId(5, null);
System.out.println(list);
list = mapper.getUserByUsernameAndId(null, null);
System.out.println(list);
}
}
3.foreach
foreach 用来处理数组/集合参数。
例如,我们有一个批量查询的需求:
List<User> getUserByIds(@Param("ids")Integer[] ids);
对应的 XML 如下:
<select id="getUserByIds" resultType="org.javaboy.mybatis.model.User">
select * from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
在 mapper 中,通过 foreach 节点来遍历数组,collection 表示数组变量,open 表示循环结束后,左边的符号,close 表示循环结束后,右边的符号,item 表示循环时候的单个变量,separator 表示循环的元素之间的分隔符。
注意,默认情况下,无论你的数组/集合参数名字是什么,在 XML 中访问的时候,都是 array,开发者可以通过 @Param 注解给参数重新指定名字。
例如我还有一个批量插入的需求:
Integer batchInsertUser(@Param("users") List<User> users);
然后,定义该方法对应的 mapper:
<insert id="batchInsertUser">
insert into user (username,address) values
<foreach collection="users" separator="," item="user">
(#{user.username},#{user.address})
</foreach>
</insert>
然后,在 Main 方法中进行测试:
public class Main2 {
public static void main(String[] args) {
SqlSessionFactory instance = SqlSessionFactoryUtils.getInstance();
SqlSession sqlSession = instance.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = new ArrayList<>();
User u1 = new User();
u1.setUsername("zhangsan");
u1.setAddress("shenzhen");
users.add(u1);
User u2 = new User();
u2.setUsername("lisi");
u2.setAddress("广州");
users.add(u2);
mapper.batchInsertUser(users);
sqlSession.commit();
}
}
例2:
将数据库中前三个数据的id修改为1,2,3;
需求:我们需要查询 blog 表中 id 分别为1,2,3的博客信息
1、编写接口
List<Blog> queryBlogForeach(Map map);
2、编写SQL语句
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
3、测试
@Test
public void testQueryBlogForeach(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}
4.sql 片段
大家知道,在 SQL 查询中,一般不建议写 *
,因为 select *
会降低查询效率。但是,每次查询都要把字段名列出来,太麻烦。这种使用,我们可以利用 SQL 片段来解决这个问题。
例如,我们先在 mapper 中定义一个 SQL 片段:
<sql id="Base_Column">
id,usename,address
</sql>
然后,在其他 SQL 中,就可以引用这个变量:
<select id="getUserByIds" resultType="org.javaboy.mybatis.model.User">
select <include refid="Base_Column" /> from user where id in
<foreach collection="ids" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</select>
5.set
set 关键字一般用在更新中。因为大部分情况下,更新的字段可能不确定,如果对象中存在该字段的值,就更新该字段,不存在,就不更新。例如如下方法:
Integer updateUser(User user);
现在,这个方法的需求是,根据用户 id 来跟新用户的其他属性,所以,user 对象中一定存在 id,其他属性则不确定,其他属性要是有值,就更新,没值(也就是为 null 的时候),则不处理该字段。
我们结合 set 节点,写出来的 sql 如下:
<update id="updateUser" parameterType="org.javaboy.mybatis.model.User">
update user
<set>
<if test="username!=null">
username = #{username},
</if>
<if test="address!=null">
address=#{address},
</if>
<if test="favorites!=null">
favorites=#{favorites},
</if>
</set>
where id=#{id};
</update>
6.trim标签
事实上trim标签有点类似于replace效果。
trim 属性
prefix:表示在trim标签内sql语句加上前缀xxx
suffix:表示在trim标签内sql语句加上后缀xxx
suffixOverrides:表示去除最后一个后缀xxx
prefixOverrides:去掉指定的前缀
<insert id="insertUserInfoByTrim" parameterType="com.cckj.bean.UserInfo">
INSERT into userinfo
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username !=null and username != ''">
username,
</if>
<if test="sex !=null and sex != ''">
sex,
</if>
<if test="age !=null">
age,
</if>
<if test="phone !=null and phone != ''">
phone,
</if>
<if test="address !=null and address != ''">
address,
</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test="username !=null and username != ''">
#{username,jdbcType=VARCHAR},
</if>
<if test="sex !=null and sex != ''">
#{sex,jdbcType=CHAR},
</if>
<if test="age !=null">
#{age,jdbcType=INTEGER},
</if>
<if test="phone !=null and phone != ''">
#{phone,jdbcType=VARCHAR},
</if>
<if test="address !=null and address != ''">
#{address,jdbcType=VARCHAR},
</if>
</trim>
</insert>
7. choose
有时候,我们不想用到所有的查询条件,只想选择其中的一个,查询条件有一个满足即可,使用 choose 标签可以解决此类问题,类似于 Java 的 switch 语句
1、编写接口方法
List<Blog> queryBlogChoose(Map map);
2、sql配置文件
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
3、测试类
@Test
public void testQueryBlogChoose(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("title","Java如此简单");
map.put("author","狂神说");
map.put("views",9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
System.out.println(blogs);
session.close();
}