learn from:http://www.mybatis.org/mybatis-3/dynamic-sql.html
mybatis支持动态拼接sql语句。主要有:
首先看基本实例:
<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对象来作为查询条件。
所以,很简单很容易理解。
首先看期望的结果,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匹配。
对应的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'}]