mybatis从入门到精通(四) 动态SQL
一丶简介
Mybatis的强大特性之一是动态SQL， 它可以动态拼接sql语句， 减轻开发的工作量。
Mybatis的动态sql标签如下4种类型
1. if
2. choose (when, otherwise)
3. trim (where, set)
4. foreach
二丶<if/>
<if/>标签相当于java语言中的if语句， 通过判断是否符合预置条件来拼接sql语句。其中判断条件可以使用ongl表达式， 如e.method(args)调用对象方法,e.property访问对象属性, ongl可以参考此博文
DynamicSqlUserMapper.java
User selectUserByUserNameAndAge(@Param("userName") String userName, @Param("age") Integer age);
DynamicSqlUserMapper.xml
<select id="selectUserByUserNameAndAge" resultMap="BaseResultMap"> select * from user where user_name=#{userName} <if test="age != null"> and age=#{age} </if> </select>
DynamicSqlTests.java
@Test public void ifTest(){ // 测试if动态sql User user=dynamicSqlUserMapper.selectUserByUserNameAndAge("tim", null); Assert.assertTrue(user!= null); user=dynamicSqlUserMapper.selectUserByUserNameAndAge("tim", 999); Assert.assertTrue(user == null); }
三丶<choose/>
<choose/>类似于java中的 if ... else if ... else ... 语句
DynamicSqlUserMapper.java
List<User> selectUserLikeByChoose(@Param("userName") String userName, @Param("age") Integer age, @Param("country") String country);
DynamicSqlUserMapper.xml
<!-- 2. choose(when, otherwise) 类似于 if, else --> <select id="selectUserLikeByChoose" resultMap="BaseResultMap"> select * from user where user_name like '${userName}' <choose> <when test="age != null"> and age = #{age} </when> <when test="country != null "> and country like '${country}' </when> <otherwise> and user_id > 10 </otherwise> </choose> </select>
DynamicSqlTests.java
@Test public void chooseTest(){ // 测试choose动态sql List<User> users=dynamicSqlUserMapper.selectUserLikeByChoose("t%", 11, null); // 注意， like xx% , 其中%由参数带入控制 sqlSession.commit(); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==1); users=dynamicSqlUserMapper.selectUserLikeByChoose("t%", null, "%国"); // 注意， like xx% , 其中%由参数带入控制 sqlSession.commit(); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()>1); }
四丶<where/>
<where/>标签在于标签内的条件不成立时， 会去掉不适合的sql关键字。 如<where/>里面的条件一个也不成立，则 where关键字不会被添加拼接， <where/>里面的条件中的第一个不成立， 第二个成立，则将第二个条件中的sql语句去掉 "and"关键字。<where/> 和 <set/> 其实是<trim/>的特殊实现。
DynamicSqlUserMapper.java
List<User> selectUserLikeByWhere(@Param("userName") String userName, @Param("country") String country);
DynamicSqlUserMapper.xml
<!-- 3. trim(where, set) -->
<select id="selectUserLikeByWhere" resultMap="BaseResultMap">
select * from user
<where>
<if test="userName !=null and userName.trim().length()>0">
user_name like #{userName}
</if>
<if test="country !=null and country.trim().length()>0">
and country like #{country}
</if>
</where>
</select>
DynamicSqlTests.java
@Test public void whereTest(){ // 使用where 元素 List<User> users=dynamicSqlUserMapper.selectUserLikeByWhere("t%", " "); //country参数中的空格会被过滤， select * from user WHERE user_name like ? Assert.assertTrue(users!=null); Assert.assertTrue(users.size()>1); // select * from user WHERE country like ? // 第一个为null, 拼接时， 会去掉 第二个符合条件的 and , 之后再拼接 users=dynamicSqlUserMapper.selectUserLikeByWhere(null , "中国"); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==1); // select * from user // 没有符合的条件， 不会拼接sql, "where" 关键字不会被拼接 users=dynamicSqlUserMapper.selectUserLikeByWhere(null , null); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==5); }
五丶<trim/>
由前面可知， <where/>标签是<trim/>标签的特殊实现。属性prefix用于在<trim/>标签内的sql前添加前缀, 属性prefixOverrides表示将会使用prefix属性前缀替换掉对应的值.
除此之外, 还有suffix后缀属性, suffixOverrides重写值.
下面我们将用<trim/>标签替换<where/>。
DynamicSqlUserMapper.java
List<User> selectUserLikeByTrim(@Param("userName") String userName, @Param("country") String country);
DynamicSqlUserMapper.xml
<!-- 使用trim元素自定义方法，替换where --> <select id="selectUserLikeByTrim" resultMap="BaseResultMap"> select * from user <trim prefix="where" prefixOverrides="AND |OR "> <!-- prefix字符会替换prefixOverrides对应的字符， prefixOverrides中的空格是必须的 --> <if test="userName !=null and userName.trim().length()>0"> user_name like #{userName} </if> <if test="country !=null and country.trim().length()>0"> and country like #{country} </if> </trim> </select>
DynamicSqlTests.java
@Test public void trimTest(){ // 使用trim 元素, 达到和where一样的效果 List<User> users=dynamicSqlUserMapper.selectUserLikeByTrim("t%", " "); //country参数中的空格会被过滤， select * from user WHERE user_name like ? Assert.assertTrue(users!=null); Assert.assertTrue(users.size()>1); // select * from user WHERE country like ? // 第一个为null, 拼接时， 会去掉 第二个符合条件的 and , 之后再拼接 users=dynamicSqlUserMapper.selectUserLikeByTrim(null , "中国"); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==1); // select * from user // 没有符合的条件， 不会拼接sql, "where" 关键字不会被拼接 users=dynamicSqlUserMapper.selectUserLikeByTrim(null , null); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==5); }
六丶<foreach/>
<foreach/>标签相当于java中的for语句, collection属性标明需要遍历的集合对象, index属性表示遍历时的下标, item属性表示遍历时获得的对象,
具体用法如下
DynamicSqlUserMapper.java
List<User> selectUserByUserIdsByForeach(@Param("userIds") List<Integer> userIds);
DynamicSqlUserMapper.xml
<!-- 4. foreach --> <!-- select * from user where user_id in ( ? , ? , ? ) --> <select id="selectUserByUserIdsByForeach" resultMap="BaseResultMap"> select * from user where user_id in <foreach collection="userIds" index="index" item="item" open="(" separator="," close=")"> <!-- 注意，当collection为map时，index为key, item为value --> #{item} </foreach> </select>
DynamicSqlTests.java
@Test public void foreachTest(){ List<Integer> userIds= Arrays.asList(1,2,3); // select * from user where user_id in ( ? , ? , ? ) List<User> users=dynamicSqlUserMapper.selectUserByUserIdsByForeach(userIds); Assert.assertTrue(users!=null); Assert.assertTrue(users.size()==3); }
学习资料: