IDEA+Mybatis(七)动态SQL
动态SQL就是根据不同的条件生成不同的SQL语句
一、if语句
mapper :
<select id="queryUserIf" resultType="entity.User" parameterType="map">
select * from user where 1=1
<if test="username != null">
and username=#{username}
</if>
<if test="email != null">
and username=#{username}
</if>
</select>
接口:
List<User> queryUserIf(Map map);
测试:
@Test
public void testQueryUserIf(){
SqlSession sqlSession= MybatisUtils.sqlSession();
try {
UserDao DAO=sqlSession.getMapper(UserDao.class);
Map map=new HashMap<String,Object>();
map.put("username","user1");
map.put("email","23423@qq.com");
List<User> stu = DAO.queryUserIf(map);
for (User user : stu) {
System.out.println(user);
}
} finally {
sqlSession.close();
}
}
结果:
注意:
if标签是判断传过来的参数中是否有对应的属性,有则将其包含的SQL追加进去,有多少符合条件的追加多少;
传递的参数不一定用Map,也可以用对应的实体类,如上面的user类。
二、choose(when、otherwise)
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句
mapper :
<select id="queryUserChoose" resultType="entity.User" parameterType="map">
select * from user where
<choose>
<when test="username != null">
username=#{username}
</when>
<when test="email != null">
email=#{email}
</when>
<otherwise>
pwd=#{pwd}
</otherwise>
</choose>
</select>
接口:
List<User> queryUserChoose(Map map);
测试:
@Test
public void testQueryUserChoose(){
SqlSession sqlSession= MybatisUtils.sqlSession();
try {
UserDao DAO=sqlSession.getMapper(UserDao.class);
Map map=new HashMap<String,Object>();
//map.put("username","user1");
map.put("email","23423@qq.com");
map.put("pwd","123456");
List<User> stu = DAO.queryUserChoose(map);
for (User user : stu) {
System.out.println(user);
}
} finally {
sqlSession.close();
}
}
结果:
注意:
choose标签中只要有一个条件成立,这个条件后面的条件自动忽略,和if不一样;
传递的参数不一定用Map,也可以用对应的实体类,如上面的user类。
三、trim、where、set
在select语句中使用SQL拼接时通常容易在where语句后遇到加不加and的问题,如果不想使用where 1=1这种方法,可以使用where标签,where标签会自动的审查代码,如果出现不必要的 “and” ,“OR”等词语,会将其删除。
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
mapper :
<select id="queryUserIf" resultType="entity.User" parameterType="map">
select * from user
<where>
<if test="username != null">
and username=#{username}
</if>
<if test="email != null">
and email=#{email}
</if>
</where>
</select>
测试:
public void testQueryUserIf(){
SqlSession sqlSession= MybatisUtils.sqlSession();
try {
UserDao DAO=sqlSession.getMapper(UserDao.class);
Map map=new HashMap<String,Object>();
//map.put("username","user1");
map.put("email","23423@qq.com");
List<User> stu = DAO.queryUserIf(map);
for (User user : stu) {
System.out.println(user);
}
} finally {
sqlSession.close();
}
}
结果:
同理,set是作用在Update标签中的,用于拼接更新语句时自动删除“,”号
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
set:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
这里不做拓展
所谓的动态SQL,本质还是SQL语句,知识我们可以在SQL层面执行逻辑代码
四、foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="ids"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!
提示 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
使用示例:
SqlSession sqlSession= MybatisUtils.sqlSession();
try {
UserDao DAO=sqlSession.getMapper(UserDao.class);
Map map=new HashMap<String,Object>();
ArrayList<Integer> ids=new ArrayList<Integer>();
ids.add(1);
map.put("ids",ids);
List<User> stu = DAO.queryUserIf(map);
for (User user : stu) {
System.out.println(user);
}
} finally {
sqlSession.close();
}
五、SQL片段
我们可以将一些公用的SQL片段抽取出来方便复用
1、使用sql标签抽取公共部分的sql
<sql id="user-name-email" >
<if test="username != null">
and username=#{username}
</if>
<if test="email != null">
and email=#{email}
</if>
</sql>
2、在需要使用的地方使用Include标签引用
<select id="queryUserIf" resultType="entity.User" parameterType="map">
select * from user
<where>
<include refid="user-name-email"></include>
</where>
</select>
注意:
最好基于单表定义SQL片段
最好不用包含where标签