MyBatis动态sql
MyBatis框架有一个强大的特性就是它的动态sql能力。我们在使用JDBC或其他相似框架时，经常会碰到串联sql字符串，但这是一件十分痛苦的事情，总是要去考虑关键字后面跟什么内容、不能忘记空格、在列表的最后要省略逗号等等。下面我用一个条件查询的案例让大家知道我们为什么需要动态sql。
案例准备
1.新建数据库表t_user
CREATE TABLE `t_user` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT'用户id',
`name` VARCHAR(10) DEFAULT NULL COMMENT'用户姓名',
`age` INT DEFAULT NULL COMMENT'用户年龄',
`birthday` DATE DEFAULT NULL COMMENT'用户生日',
PRIMARY KEY (`id`)
)
2.新建类User.java
package com.cwd.mybatis.bean;
import org.apache.ibatis.type.Alias;
import java.util.Date;
@Alias("User")
public class User {
private Integer id;
private String name;//姓名
private Integer age;//年龄
private Date birthday;//生日
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}
3.新建接口UserDao.java
package com.cwd.mybatis.dao;
import com.cwd.mybatis.bean.User;
public interface UserDao {
}
4.新建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">
<!--namespace:接口存放路径 类路径-->
<mapper namespace="com.cwd.mybatis.dao.UserDao">
</mapper>
为什么需要动态sql？
如上图，当我们需要按照姓名/年龄/生日这些数据进行查询时，客户端会将数据发送到处理层，处理层将数据封装到User类的对象中传给数据持久层，我们需要使用sql语句操作数据库进行查询，这时的sql语句就会出现问题，比如where后的条件，如果传入的数据为null，岂不是where后面没有数据，这时就会出错。
我们以案例来说话
接口UserDao.java中的方法为：
//为什么需要动态sql user中封装的是查询条件
List<User> findUserList(User user);
UserMapper.xml中的配置为
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
where true
<if test="name != null">
and name = #{name}
</if>
<if test="age != null">
and age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</select>
我们可以想象，如果where后面没有true，那么会出现什么样的问题？where and …或者直接就是where后面什么都没有，这显然不符合sql语句的语法。并且这里的if标签本就是MyBatis中用于实现动态sql的主要元素之一，我们可以想象如果没有这个if标签，这条查询sql该如何写？这就是我们动态sql存在的意义。
动态sql的使用
MyBatis中用于实现动态sql的元素主要有：
if | where | trim | set | choose（when，otherwise） | foreach
if元素（标签）
if元素可以对传入的条件进行判断，还是上面的案例，我们依照上面案例的代码进行测试。
@Test
public void test1() {
try {
Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
//处理,假设从客户端传递过来一个查询条件(姓名/年龄/生日)封装到User中
User user = new User();
user.setAge(20);
List<User> list = userDao.findUserList(user);
System.out.println(list);
//提交事务
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
我们可以看到，当查询的数据不确定时，使用if元素的动态sql功能可以使我们的查询更加准确。
where标签
对于查询条件个数不确定的情况，我们还可以使用where标签。where标签会进行判断，如果它包含的标签中有返回值的话，它就插入一个where。另外，如果标签返回的内容是以AND或OR开头，它就会剔除掉AND或OR。使用方法如下：
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
<!--当下面if标签有返回值时,where就会出现-->
<where>
<!--第一个if中有返回值,第二个if返回的值就会带AND;如果第一个没有返回值,第二个就会剔除AND-->
<if test="name != null">
name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</where>
</select>
trim标签
where标签也可以使用trim标签表示，当where后紧随AND或OR的时候，就去除AND或着OR。trim标签中有两个属性，prefix表示前缀，prefixOverrides表示覆盖首部指定的内容。
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
<trim prefix="where" prefixOverrides="and">
<if test="name != null">
name = #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</trim>
</select>
小案例–模糊查询
当我们输入一个姓氏或者一个字时，查询与之相匹配的数据时，我们通常使用sql语句中的like关键字加%进行模糊查询。当我们在MyBatis中进行模糊查询时，我们不能使用#{}进行查询，或者说不能单独使用，对于这个问题，我们有三种解决方式。
1.使用’%${}’
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
<!--当下面if标签有返回值时,where就会出现-->
<trim prefix="where" prefixOverrides="and">
<if test="name != null">
name like '%${name}'
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</trim>
</select>
2.使用concat(’%’,’${}’)拼接字符串
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
<!--当下面if标签有返回值时,where就会出现-->
<trim prefix="where" prefixOverrides="and">
<if test="name != null">
name like concat('%','${name}')
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</trim>
</select>
3.在处理层对客户端输入的数据直接加%，一起传给sql语句当参数。
<select id="findUserList" parameterType="User" resultType="User">
select id,name,age,birthday from t_user
<!--此时传入的参数为%name-->
<trim prefix="where" prefixOverrides="and">
<if test="name != null">
name like #{name}
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</trim>
</select>
choose标签
choose标签可以代替if标签进行判断，但是只能进行二选一的判断，一般用于条件不为空时的固定选项。
1.接口UserDao.java中的方法为：
//当查询条件为空时，查询所有的数据
List<User> findUserListNotNull(@Param("name") String name);
2.UserMapper.xml中的配置为：
<select id="findUserListNotNull" parameterType="string" resultType="User">
select id,name,age,birthday from t_user
<trim prefix="where" prefixOverrides="and">
<choose>
<when test="name!=null">
and name like '%${name}'
</when>
<otherwise>
true
</otherwise>
</choose>
</trim>
</select>
3.测试：
@Test
public void test2() {
try {
Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
//处理
String name = "";
List<User> list = userDao.findUserListNotNull(name);
System.out.println(list);
//提交事务
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
set标签
set标签用于去除最后一个逗号，比如当用户需要从客户端修改数据时，只需要修改一部分数据，另一部分不变时，我们使用update进行数据库修改时，就无法准确确定要修改的参数，这时就需要使用set标签来做update的动态SQL，完成确定修改哪些参数的步骤。我们以下面的修改案例为例：
1.接口UserDao.java中的方法为：
//修改User,可以修改一条数据的任意一列
void updateUser(User user);
2.UserMapper.xml中的配置为：
<!--
修改User,当要修改的列不确定时,使用set标签可以去除最后一个逗号
-->
<update id="updateUser" parameterType="User">
UPDATE t_user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</set>
where id = #{id}
</update>
3.测试：
/**
* 测试修改update,使用set
*/
@Test
public void test3() {
try {
Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
//处理,假设从客户端传递过来要修改的参数(姓名/年龄/生日+id)封装到User中
User user = new User();
user.setId(5);
user.setName("vim");
userDao.updateUser(user);
//提交事务
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
小案例–trim实现
trim标签中除了有两个属性，prefix表示前缀，prefixOverrides表示覆盖首部指定的内容。还有两个属性即suffixOverrides表示覆盖尾部指定的内容，suffix表示尾部。
UserMapper.xml中的配置为：
<update id="updateUser" parameterType="User">
UPDATE t_user
<trim prefix="set" suffixOverrides=",">
<if test="name != null">
name = #{name},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
</trim>
where id = #{id}
</update>
foreach标签
foreach标签主要在in(a,b)（判断某字段的值是否属于in列表中的某一项条件）中使用，它可以在 SQL 语句中进行迭代一个集合。多用于delete语句中的批量删除，foreach 标签的属性主要有 item，index，collection，open，separator，close。
各个属性的功能分别为：item 表示集合中每一个元素进行迭代时的别名；index 指定一个名字，用于表示在迭代过程中每次迭代到的位置；open 表示该语句以什么开始； separator 表示在每次进行迭代之间以什么符号作为分隔符；close 表示以什 么结束。
在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的。如果传入的是单参数且类型为List时，collection属性值为list，如果传入的参数类型为一个array数组时，collection属性值为array。
我们以一个批量查询和删除的案例为大家展示：
1.接口UserDao.java中的方法为：
//删除User
void deleteUser(List<Integer> list);
//迭代查询user信息
List<User> findUser(List<Integer> list);
2.UserMapper.xml中的配置为：
<!--
批量删除User
对传递过来的数组参数进行迭代
-->
<delete id="deleteUser">
DELETE FROM t_user WHERE id in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<!--按照学号迭代查询-->
<select id="findUser" resultType="User">
SELECT * FROM t_user WHERE id in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
3.测试：
/**
* 删除User，一次删除多条记录，循环迭代参数查询
*/
@Test
public void test4() {
try {
Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession sqlSession = sessionFactory.openSession();
UserDao userDao = sqlSession.getMapper(UserDao.class);
//处理,假设从客户端传递过来要删除的多个参数为一个集合
List<Integer> list = new ArrayList<>();
list.add(6);
list.add(7);
list.add(8);
//查询
System.out.println("现在id=6,7,8的数据为"+userDao.findUser(list));
System.out.println("---------------");
//删除
userDao.deleteUser(list);
//删除后
System.out.println("---------------");
System.out.println("删除后id=6,7,8的数据为"+userDao.findUser(list));
//提交事务
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
特殊符号处理
在MyBtais中的xml文件中，存在一些特殊符号，比如：<、>、"、&、<> 等，正常书写MyBatis会报错，所以需要对这些特殊符号进行转义。具体转义如下所示：
< <
> >
" "
' '
& &
除了转义字符外，我们还可以使用<![CDATA[ 特殊符号 ]]>来包裹特殊字符。
比如下面的xml文件语句：
<!--
xml文件中的特殊符号转义
比如这条查询语句SELECT * FROM t_user WHERE id < #{id}
这里使用<号就会报错
解决方法:
第一种:转义字符 如: < <
第二种:使用<![CDATA[ 特殊符号 ]]>进行包裹
-->
<select id="findUser" resultType="User">
SELECT * FROM t_user WHERE id <![CDATA[ < ]]> #{id}
</select>
转载：https://blog.csdn.net/Lotus_dong/article/details/116310487