干货 | MyBatis的动态SQL

子乾建建-Jeff

发布于 2020-06-29 14:56:53

9710

发布于 2020-06-29 14:56:53

文章被收录于专栏：iBroPro

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日。

（以上内容来自百度百科，所以说百度是一个好东西）

通常包含在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元素自动将他们去除。

如果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>

注意：

prefix：在trim标签内sql语句加上前缀。
suffix：在trim标签内sql语句加上后缀。
suffixOverrides：指定去除多余的前缀内容。
prefixOverrides：指定去除多余的后缀内容。

在更新语句中，动态更新语句的标签元素使用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="">

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...)

再唠叨一句：很多sql语句我并没有写入参类型，是因为官方文档告诉我们它很多情况下其实可以自动识别，所以，这就不用我们担心了。不信，你去官网看～

整体项目结构：

码云链接地址： https://gitee.com/JeffBro/MyBatisTest

来都原创不易，点赞分享~~~

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-09-08，如有侵权请联系 cloudcommunity@tencent.com 删除

mybatis

本文分享自 iBroPro 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度

干货 | MyBatis的动态SQL

干货 | MyBatis的动态SQL

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐