动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
所谓的动态sql其实本质还是sql语句,只是我们可以在sql层面,去执行一个逻辑代码
动态sql就是在拼接sql语句,只要我们保证sql的准确性,按照sql的格式,去排列组合就可以了
一、动态SQL环境搭建
- 实体类
- Mapper配置文件
- mybatis-config.xml配置文件
- 测试
二、动态SQL之If语句
在查询语句中我们经常会用到条件查询
:我们可以通过if标签判断,动态的加入sql,如下所示
这段代码是通过if标签判断是否满足判断条件,如果是,则加上sql语句,如果不是则不会加,where 1=1必须加上,否则会报错。
UserMapper.xml:
<select id="queryUserIf" parameterType="map" resultType="user">
select * from user where 1=1
<if test="id!=null">
and id=#{id}
</if>
</select>
测试:
通过map传值我们可以控制判断条件,如果不传值,则查询所有user,如果传值,则查询满足判断条件的user。
@Test
public void queryUserIfTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//加入条件
Map map =new HashMap();
map.put("id",4);
List<User> users = mapper.queryUserIf(map);
for (User user:users){
System.out.println(user);
}
sqlSession.close();
}
三、trim、where、set
where:
如上代码所示,我们想用 标签就需要在前面加入一个 where 1=1的语句,否则他的语句将会变成这样:
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
很显然这种语句是错误的,也是不安全的,为了解决这一问题,我们可以适当的使用元素
where 元素()只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。这样就解决了上面这种麻烦。
<select id="queryUserWhere" parameterType="map" resultType="user">
select * from user
<where>
<if test="id!=null">
and id=#{id}
</if>
</where>
</select>
set:
用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列,忽略其它不更新的列
在update用户数据时我们会用到set语句,如:
update user set username =?,password=?,phone=?
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
<!-- set-->
<update id="UpdateUserSet" parameterType="map">
update user
<set>
<if test="username!=null">
username = #{username},
</if>
<if test="name!=null">
name=#{name}
</if>
</set>
where id=#{id}
</update>
测试:
@Test
public void UpdateUsersetTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map =new HashMap();
map.put("id",5);
map.put("username","lpm555");
map.put("name","aaa");
int i = mapper.UpdateUserSet(map);
System.out.println(i>0);
sqlSession.commit();
sqlSession.close();
}
trim:
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
实用度不高,不推荐使用
四、choose、when、otherwise
这三个标签看着停牛逼的,其实就相当于Java中的swiswitch、case、default
choose里面的条件如果都满足,则只会查询符合第一个条件的内容,都不满足则执行otherwise里的内容,相当于swiswitch、case、default
将choose嵌套在where里面,可以更好的拼凑查询语句
<select id="queryUserChoose" parameterType="map" resultType="user">
select * from user
<where>
<choose>
<when test="id!=null">
and id=#{id}
</when>
<when test="username!=null">
and username=#{username}
</when>
<otherwise>
and name=#{name}
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void queryUserchooseTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map =new HashMap();
// map.put("id",4);
// map.put("username","lpm666");
// map.put("name","admin");
List<User> users = mapper.queryUserChoose(map);
for (User user:users){
System.out.println(user);
}
sqlSession.close();
}
五、sql片段
我们可以将一些公共的sql片段集合起来,放在一个元素中,这样我们就可以在其他片段中引用这个sql片段,实现代码复用
步骤:
1.使用sql标签抽取公共部分
2.在需要引用的地方使用include引用进去
例如:
<sql id="if-username-name">
<if test="username!=null">
username = #{username},
</if>
<if test="name!=null">
name=#{name}
</if>
</sql>
<update id="UpdateUserSet" parameterType="map">
update user
<set>
<include refid="if-username-name"></include>
</set>
where id=#{id}
</update>
注意事项
1.最好基于单表来定义sql片段
2.sql片段不要包含where标签
六、foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="user">
SELECT *
FROM user id
WHERE 1=1 and
<foreach item="id" index="index" collection="idlist"
open="(" separator="or" close=")">
#{id}
</foreach>
</select>
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!
foreach标签中collection代表传递过来的集合,item代表集合中的值,open为开头,separator为分隔符,close代表结尾。
以下代码相当于: select * from user where id=1 or id=4 or id=5
由于where标签的存在,会使得and 被去除。
Mapper.xml:
<!-- foreach-->
<!-- select * from user where id=1 or id=4 or id=5-->
<!-- 我们可以传递一个map。这个map可以存在一个集合-->
<select id="queryUserforeach" parameterType="map" resultType="user">
select * from user
<where>
<foreach collection="ids" item="id" open="and (" separator="or" close=")">
id=#{id}
</foreach>
</where>
</select>
测试:
@Test //查询id为1,4,5的用户
public void queryUserforeachTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map =new HashMap();
//使用list包含要查询的id,查询id为1,4,5的用户
List ids =new ArrayList();
ids.add(1);
ids.add(4);
ids.add(5);
map.put("ids",ids);
List<User> users = mapper.queryUserforeach(map);
for (User user:users){
System.out.println(user);
}
sqlSession.close();
}