动态SQL
什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句.
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
if
choose (when, otherwise)
trim (where, set)
foreach
搭建测试环境
新建一个用户表
CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`pwd` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(3) NULL DEFAULT NULL,
`role` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8;
-- 插入数据;
INSERT INTO `test`.`user`(`id`, `name`, `pwd`, `age`, `role`) VALUES (1, '小明', '123456', 18, '管理员');
INSERT INTO `test`.`user`(`id`, `name`, `pwd`, `age`, `role`) VALUES (2, '张三', '321321', 16, '普通用户');
INSERT INTO `test`.`user`(`id`, `name`, `pwd`, `age`, `role`) VALUES (3, '李四', '321123', 16, 'VIP用户');
User.java
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
private int id; //id
private String name; //姓名
private String pwd; //密码
private int age;
private String role;
}
UserMapper.java
public interface UserMapper {}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sin.mapper.UserMapper">
</mapper>
mybatis-config.xml
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
if标签
需求：页面上，有三个筛选条件，如下图所示：那个值不为空就查询那个值。例如：用户名不为名，就查询用户名，年龄不为空就查询年龄，如果用户名和年龄都不为空，就用户名加年龄加权限。
UserMapper.java
List<User> selectUser(Map map);
UserrMapper.xml
<!--
查询用户
常规sql语句：select * from user where name = #{name} and age = #{age} and role = #{role}
-->
<select id="selectUser" resultType="com.sin.pojo.User" parameterType="map">
select * from user where
<if test="name != null">
name = #{name}
</if>
<if test="age !=null">
and age = #{age}
</if>
<if test="role !=null">
and role=#{role}
</if>
</select>
测试
@Test
public void testSelectUser(){
SqlSession session = MyBatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("name","小明");
map.put("age","18");
map.put("role","管理员");
List<User> users = mapper.selectUser(map);
System.out.println(users);
session.close();
}
测试结果
如果用户名不传，会不会出问题？
肯定会的。可以通过测试查看日志的输出。
发现SQL变成了，select * from user where and age = ? and role = ?
解决办法
方法一： 在where 后面添加 1 or 1。 (不推荐这样的写法)
<!--
查询用户
常规sql语句：select * from user where name = #{name} and age = #{age} and role = #{role}
-->
<select id="selectUser" resultType="com.sin.pojo.User" parameterType="map">
select * from user where 1 = 1
<if test="name != null">
name = #{name}
</if>
<if test="age !=null">
and age = #{age}
</if>
<if test="role !=null">
and role=#{role}
</if>
</select>
测试
@Test
public void testSelectUser(){
SqlSession session = MyBatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
//map.put("name","小明");
map.put("age","18");
map.put("role","管理员");
List<User> users = mapper.selectUser(map);
System.out.println(users);
session.close();
}
测试结果
方法二：采用mybatis 里的 <where> 标签。 (推荐)
<!--
查询用户
常规sql语句：select * from user where name = #{name} and age = #{age} and role = #{role}
-->
<select id="selectUser" resultType="com.sin.pojo.User" parameterType="map">
select * from user
<where>
<if test="name != null">
name = #{name}
</if>
<if test="age !=null">
and age = #{age}
</if>
<if test="role !=null">
and role=#{role}
</if>
</where>
</select>
测试
@Test
public void testSelectUser(){
SqlSession session = MyBatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
//map.put("name","小明");
map.put("age","18");
map.put("role","管理员");
List<User> users = mapper.selectUser(map);
System.out.println(users);
session.close();
}
测试结果
如果 <where> 标签里有返回值的话，它就插入一个where。如果标签里的内容是以AND 或OR 开头的，会忽略掉。
set标签
如果我用户名，年龄，权限，其中任意一个值有值，就更新该值。
UserMapper.java
int updateUser(Map map);
UserMapper.xml
<!--
更新用户
常规语句 ：UPDATE `user` SET `name` = #{name}, `age` = #{age}, `role` = #{role} WHERE `id` = #{id}
-->
<update id="updateUser" parameterType="map">
update user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age !=null">
age = #{age},
</if>
<if test="role !=null">
role = #{role}
</if>
</set>
where id = #{id};
</update>
测试
@Test
public void testUpdateUser(){
SqlSession session = MyBatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("id","1");
map.put("name","管理员");
map.put("age","22");
map.put("role","VIP管理员");
// 返回执行成功条数
mapper.updateUser(map);
session.close();
}
测试结果
choose语句
有时候，我们会根据不同的用户权限给不同的查询条件，查询条件有一个满足即可，这时候我们使用 choose 标签可以解决这个问题了，类似于 Java 的 switch 语句
需求：如果用户名和权限同时有值，只能查询其中一个。
UserMapper.java
List<User> selectUserChoose(Map map);
UserMapper.xml
<select id="selectUserChoose" resultType="com.sin.pojo.User">
select * from user
<where>
<choose>
<when test="name != null">
and name = #{name}
</when>
<when test="age != null">
and age = #{age}
</when>
<when test="role !=#{role}">
and role = #{role}
</when>
</choose>
</where>
</select>
测试
@Test
public void testSelectUserChoose(){
SqlSession session = MyBatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("name","小明");
map.put("age","18");
map.put("role","普通用户");
List<User> users = mapper.selectUserChoose(map);
System.out.println(users);
session.close();
}
测试结果
SQL片段
有时候可能某个 sql 语句我们会反复用到，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。
提取SQL片段：
<sql id="userSql">
select * from user
</sql>
<sql id="userSql">
select * from user
</sql>
<select id="selectUser" resultType="com.sin.pojo.User" parameterType="map">
<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
<include refid="userSql"></include>
<!-- 在这里还可以引用其他的 sql 片段 -->
<where>
<if test="name != null">
name = #{name}
</if>
<if test="age !=null">
and age = #{age}
</if>
<if test="role !=null">
and role=#{role}
</if>
</where>
</select>
Foreach语句
常用于：批量添加，批量删除，in 查询。
批量添加
UserMapper.java
// 批量添加
int batchAdd(List<Map> users);
UserMapper.xml
<insert id="batchAdd">
insert into user(id,name,pwd,age,role)
values
<foreach collection="list" item="item" separator=",">
(#{item.id},#{item.name},#{item.pwd},#{item.age},#{item.role})
</foreach>
</insert>
测试
// 测试批量添加方法
@Test
public void testBatchAdd() {
SqlSession session = MyBatisUtil.getSession(); //获取SqlSession连接
UserMapper mapper = session.getMapper(UserMapper.class);
List<Map> mapList = new ArrayList<Map>();
for (int i = 0; i < 5; i++) {
String[] names = new String[]{"刘备","张飞","关羽","诸葛亮","赵云"};
Map<String,String> map = new HashMap<String, String>();
map.put("id",1+""+i);
map.put("name",names[i]);
map.put("pwd", String.format("%06d", new Random().nextInt(999999)));
map.put("age",2+""+i);
map.put("role",i+"号管理员");
mapList.add(map);
}
mapper.batchAdd(mapList);
session.close();
}
测试结果
批量删除
UserMappe.java
// 接口添加方法
int batchDel(List<Integer> users);
UserMapper.xml
<delete id="batchDel">
delete from user where id
in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
测试
// 测试批量删除方法
@Test
public void testBatchDel() {
SqlSession session = MyBatisUtil.getSession(); //获取SqlSession连接
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> mapList = new ArrayList<Integer>();
mapList.add(1);
mapList.add(2);
mapList.add(3);
mapper.batchDel(mapList);
session.close();
}
测试结果
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
