MyBatis学习(四)
前言
最近比较松懈，下班回家后也懒得学习了。今晚实在是看不下去了，争取时间学习。社会上有这么多的资源，就看谁能抢的多吧。今天就说说MyBatis的动态SQL吧
正文
动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:
<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 BLOG
WHERE
这会导致查询失败。如果仅仅第二个条件匹配又会怎样？这条 SQL 最终会是这样:
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
这个查询也会失败。这个问题不能简单的用条件句式来解决，如果你也曾经被迫这样写过，那么你很可能从此以后都不想再这样去写了。
MyBatis 有一个简单的处理，这在90%的情况下都会有用。而在不能使用的地方，你可以自定义处理方式来令其正常工作。一处简单的修改就能得到想要的效果：
<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 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。
好了，上面都是摘自MyBatis官网，说明了动态sql在使用中存在的问题，那我就以where为例，跑一个小程序，还是基于前面几节中使用到的表。
<select id="getUserByObjInDynamicMethod" parameterType="org.tonny.entity.User" resultType="org.tonny.entity.User"> SELECT * FROM users <where> <if test="id != null"> id=#{id} </if> <if test="name != null"> and name like #{name} </if> </where> </select> <select id="getUserByMapInDynamicMethod" parameterType="org.tonny.entity.User" resultType="org.tonny.entity.User"> SELECT * FROM users <where> <if test="id != null"> id=#{id} </if> <if test="name != null"> and name like #{name} </if> </where> </select>
这部分配置与上面的一样，使用了where标签。对应的Java代码如下：
@Test public void getUserByObjInDynamicMethod() { SqlSession sqlSession = sqlSessionFactory.openSession(); // 映射sql的标识字符串 String sql = "org.tonny.mapper.UsersMapper.getUserByObjInDynamicMethod"; User user = new User(); user.setId(1); user.setName("%nny%"); user.setAge(30); List<User> userList = sqlSession.selectList(sql, user); sqlSession.close(); System.out.println(userList); } @Test public void getUserByMapInDynamicMethod() { SqlSession sqlSession = sqlSessionFactory.openSession(); // 映射sql的标识字符串 String sql = "org.tonny.mapper.UsersMapper.getUserByObjInDynamicMethod"; Map<String,Object> map = new HashMap<String, Object>(); map.put("id", 1); map.put("name", "%nny%"); List<User> userList = sqlSession.selectList(sql, map); sqlSession.close(); System.out.println(userList); }
这样就可以查出来了。对了，为了可以在控制台查看MyBatis的SQL，需要在mybatis.xml增加配置信息：
<!-- 设置日志打印 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings>
这样就可以看到输出的sql语句了。测试结果：
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. Opening JDBC Connection Created connection 25193812. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54] ==> Preparing: SELECT * FROM users WHERE id=? and name like ? ==> Parameters: 1(Integer), %nny%(String) <== Columns: id, NAME, age <== Row: 1, Tonny Chien, 25 <== Total: 1 Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1806d54] Returned connection 25193812 to pool. [User [id=1, name=Tonny Chien, age=25]]
还要介绍一下set标签，它对应update中的set
<update id="updateUserByMapInDynamicMethod" parameterType="java.util.Map"> UPDATE users <set> <if test="name != null"> name = #{name}, </if> <if test="age != null"> age = #{age} </if> </set> <where> <if test="id != null"> id=#{id} </if> <if test="age != null"> <![CDATA[ and age > #{age} ]]> </if> </where> </update>
Java测试代码如下：
@Test public void updateUserByMapInDynamicMethod() { SqlSession sqlSession = sqlSessionFactory.openSession(); // 映射sql的标识字符串 String sql = "org.tonny.mapper.UsersMapper.updateUserByMapInDynamicMethod"; Map<String,Object> map = new HashMap<String, Object>(); map.put("id", 1); map.put("age", 20); map.put("name", "北堂一刀"); int result = sqlSession.update(sql, map); sqlSession.commit(); sqlSession.close(); System.out.println(result); }
执行结果：
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. Opening JDBC Connection Created connection 30587315. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3] ==> Preparing: UPDATE users SET name = ?, age = ? WHERE id=? and age > ? ==> Parameters: 北堂一刀(String), 20(Integer), 1(Integer), 20(Integer) <== Updates: 1 Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3] Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3] Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1d2b9b3] Returned connection 30587315 to pool. 1
后记
就写到这儿吧，下次再详细些，休息了。
