Mybatis - 动态sql

learn from:http://www.mybatis.org/mybatis-3/dynamic-sql.html

mybatis支持动态拼接sql语句。主要有:

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

1.if

首先看基本实例:

<select id="findActiveBlogWithNameLike"  resultType="Blog">
        SELECT * FROM blog
        WHERE state = 'active'
        <if test="name != null">
            AND name LIKE #{name}
        </if>
</select>
List<Blog> findActiveBlogWithNameLike(String name);

这里遇到一个问题:

 There is no getter for property named 'name' in 'class java.lang.String'### 
Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'name' in 'class java.lang.String'

也就是说,mybatis将name当做输入参数的一个属性,并且期望通过getter方法来获取它的值。很容易想到,将输入参数改成Blog就可以了。

然而,这并不符合我们的查询习惯,比如,如果是Blog就必须这样查询:

@Test
    public void testFindActiveBlogWithNameLike() throws Exception{
        Blog key = new Blog();
        key.setName("test%");
        List<Blog> blogs = mapper.findActiveBlogWithNameLike(key);
        System.out.println(blogs);
        return;
    }

为了一个String字段而创建一个类,看着要多别扭有多别扭。当然,前提是我们仅仅是查询name条件,所以会觉得其他属性冗余。如果是多条件查询,那么Blog必然是最好的选择。那么,仅仅传入String的话应该也是可以的。参考1,参考2

第一种做法是简单类型都是使用_parameter来代替。

<select id="findActiveBlogWithNameLikeByString" parameterType="java.lang.String" resultType="Blog">
        SELECT * FROM blog
        WHERE name LIKE '%${_parameter}%'
</select>

第二种做法比较容易理解,在方法参数前添加@Param(value="xxx")注解来使用xxx作为传入参数。

<select id="findActiveBlogWithNameLikeByString" parameterType="java.lang.String" resultType="Blog">
        SELECT * FROM blog
        WHERE name LIKE '%${key}%'
</select>

List<Blog> findActiveBlogWithNameLikeByString(@Param(value = "key") String key);

两种做法均可,所以,看你喜欢了,是想要省事简洁还是通俗易读。在这里,还是选择第0种方案,即传入Blog对象来作为查询条件。

下面简单介绍if的语法:

  • if节点中,属性test是一个boolean值,为true的时候将拼接if里的sql语句。
  • 参数值是可以包含一些掩码或通配符的.比如通配符%和占位符_

所以,很简单很容易理解。

 场景一: 查询blog的名字name  like %Insert and 作者author的username like Ryan%.

首先看期望的结果,blog表中有三条满足name like:

mysql> select * from blog;
+----+------------+-----------+--------------+--------+
| id | name       | author_id | co_author_id | state  |
+----+------------+-----------+--------------+--------+
|  1 | test       |         3 |            4 | active |
|  8 | testInsert |         4 |            5 | active |
|  9 | testInsert |         5 |            6 | active |
| 10 | testInsert |         6 |            7 | active |
| 12 | testA      |        50 | NULL         | active |
| 13 | testA      |        51 | NULL         | active |
| 14 | testInsert | NULL      | NULL         | active |
+----+------------+-----------+--------------+--------+
7 rows in set

这三条中,满足author的username like的有两条:

mysql> select author.id, author.username from author where id in (4,5,6);
+----+----------+
| id | username |
+----+----------+
|  4 | Ryan     |
|  5 | Ryan0    |
|  6 | Leslie   |
+----+----------+
3 rows in set

也就是我们最终希望结果是blog id为8 和 9。

mybatis的sql语句如下:

<select id="findActiveBlogLike" resultType="Blog">
        SELECT * FROM blog b, author a
        WHERE state = 'active'
        <if test="name != null">
            AND name LIKE #{name}
        </if>
        AND b.author_id = a.id
        <if test="author != null and author.name != null">
            AND a.username  LIKE #{author.username}
        </if>
</select>

当blog的name不为null的时候查询name匹配,当author的username不为null的时候,查询author的username匹配。

  • 第一个if节点的test为name,这个会查找Blog的name字段,如果传入参数Blog没有name字段,那么就会像我们开始那样报错。所以,name必须是blog的一个字段。同理,#{name}这个也要和blog字段字面量的值匹配。
  • 第二个if节点的test里看到了and,and就是并且。首先判断author是否为null,就是判断Blog对象的author属性是否为null。接着判断author.name是否为null,这里就有点问题了。因为我的Author类中并没有name字段,对应的字段字面量是username。也就是说这里应该是author.username。但我粗心写成了author.name(所以为每段代码编写unit test是多么的重要)。更奇葩的是,这条test通过了判断为真,这里先不讲,后面测试的时候再分析原因。不过这里一定要改成author.username才是正确的做法。

对应的java接口

List<Blog> findActiveBlogLike(Blog blog);

下面开始测试:

    @Test
    public void testFindActiveBlogLike() throws Exception{
        Blog blog = new Blog();
        blog.setName("%Insert");
        Author author = new Author();
        author.setUsername("Ryan%");
        blog.setAuthor(author);
        List<Blog> blogs = mapper.findActiveBlogLike(blog);
        System.out.println(blogs);
        assertTrue(blogs.size()==2);
        return;
    }

先看结果对不对:

2016-08-06 16:20:19,264 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2016-08-06 16:20:19,740 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 1150284200.
2016-08-06 16:20:19,742 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@448ff1a8]
2016-08-06 16:20:19,745 DEBUG [com.test.mapper.dao.BlogMapper.findActiveBlogLike] - ==>  Preparing: SELECT * FROM blog b, author a WHERE state = 'active' AND name LIKE ? AND b.author_id = a.id AND a.username LIKE ? 
2016-08-06 16:20:19,888 DEBUG [com.test.mapper.dao.BlogMapper.findActiveBlogLike] - ==> Parameters: %Insert(String), Ryan%(String)
2016-08-06 16:20:19,978 DEBUG [com.test.mapper.dao.BlogMapper.findActiveBlogLike] - <==      Total: 2
[Blog{id=8, name='testInsert', author=null, coAuthor=null, posts=null, state='active'}, Blog{id=9, name='testInsert', author=null, coAuthor=null, posts=null, state='active'}]

test通过了,blog也确实是我们想要的两条。但仔细观察结果就会发现几个问题。第一个问题是author为null,这个我们等下再解决。第二问题是sql查询语句查询了author.username like,也就是说我们第二个if节点的test 为true。难道出了问题?我们的Author类明明没有name字段。所以,这里要跟踪下代码。

好吧,跟踪了半天一直到ognl内部,还是没追踪到为什么name翻译成username了。下面还是搞定第一个问题,author为null。

查询的结果映射到Blog,但blog的author字段并没有初始化。很容易就猜测到结果集的字段和blog的author不匹配。这个就用到resultMap而不是resultType。在上一遍博文中记录了下来。

<select id="findBlogMap" resultMap="findBlogResultMap">
        SELECT b.id AS id,
                b.name AS name,
                b.state AS state,
                a.id        as author_id,
                a.username  as author_username,
                a.password  as author_password,
                a.email     as author_email,
                a.bio       as author_bio
        FROM blog b, author a
        WHERE state = 'active'
        <if test="name != null">
            AND name LIKE #{name}
        </if>
        AND b.author_id = a.id
        <if test="author != null and author.username != null">
            AND a.username  LIKE #{author.username}
        </if>
    </select>
    <resultMap id="findBlogResultMap" type="Blog">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="state" column="state"/>
        <association property="author" column="author_id" javaType="Author">
            <id property="id" column="author_id"/>
            <result property="username" column="author_username"/>
            <result property="password" column="author_password"/>
            <result property="email" column="author_email"/>
            <result property="bio" column="author_bio"/>
        </association>
    </resultMap>

这样测试结果:

2016-08-15 22:42:10,994 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Opening JDBC Connection
2016-08-15 22:42:11,567 DEBUG [org.apache.ibatis.datasource.pooled.PooledDataSource] - Created connection 1627428162.
2016-08-15 22:42:11,569 DEBUG [org.apache.ibatis.transaction.jdbc.JdbcTransaction] - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@61009542]
2016-08-15 22:42:11,573 DEBUG [com.test.mapper.dao.BlogMapper.findBlogMap] - ==>  Preparing: SELECT b.id AS id, b.name AS name, b.state AS state, a.id as author_id, a.username as author_username, a.password as author_password, a.email as author_email, a.bio as author_bio FROM blog b, author a WHERE state = 'active' AND name LIKE ? AND b.author_id = a.id AND a.username LIKE ? 
2016-08-15 22:42:11,674 DEBUG [com.test.mapper.dao.BlogMapper.findBlogMap] - ==> Parameters: %Insert(String), Ryan%(String)
2016-08-15 22:42:11,725 DEBUG [com.test.mapper.dao.BlogMapper.findBlogMap] - <==      Total: 2
[Blog{id=8, name='testInsert', author=Author{id=4, username='Ryan', password='123456', email='qweqwe@qq.com', bio='this is a blog'}, coAuthor=null, posts=null, state='active'}, Blog{id=9, name='testInsert', author=Author{id=5, username='Ryan0', password='123456', email='qweqwe@qq.com', bio='this is a blog'}, coAuthor=null, posts=null, state='active'}]

2.choose

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL 集合的初始化与赋值

    对于集合类型,与单一的数据类型相比较而言,应该以一个整体的观念来考虑集合,即是一批类型相同的数据组合而非单一的数据。因此集 合类型集合的声明、赋值、初...

1005
来自专栏醉生梦死

Mysql中的运算符 原

    SELECT 10 % 3, 10 MOD 3, MOD(10,3);

1144
来自专栏Python爬虫实战

MySQL从零开始:05 MySQL数据类型

距离上次更新 MySQL 从零开始系列,已经过去了十几天,时间隔得有点长,由于我选用的是 MySQL 的最新版本,网上的教程大多停留在 MySQL 5.x,所以...

1293
来自专栏听雨堂

使用正则表达式求完整路径中的文件名

      以前都是比较恶心的算法,找字符串中的最后一个"\",再求出末尾的文件名。现在好啦,用.net中的正则表达式,可以非常漂亮的完成。    usin...

2308
来自专栏前端儿

一种排序

现在有很多长方形,每一个长方形都有一个编号,这个编号可以重复;还知道这个长方形的宽和长,编号、长、宽都是整数;现在要求按照一下方式排序(默认排序规则都是从小到大...

852
来自专栏一个爱吃西瓜的程序员

学习SQL【7】-函数

终于可以开原创标识和留言功能了,开心。我坚信努力总会有收获的。 不仅SQL, 对所有的编程语言来说,函数都起着至关重要的作用。函数就像是编程语言的“道具箱”...

34012
来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组...

863
来自专栏抠抠空间

MySQL之表的数据类型

一 介绍 存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的 详细参考: http://www.runoob....

3428
来自专栏机器学习从入门到成神

Hibernate查询技术之HQL语句

1、session中的get( )和load( )方法来查询对象。但其查询功能有限。

2071
来自专栏祥子的故事

sql | 基础总结 | 思维导图

3566

扫码关注云+社区