Mybatis 动态 SQL 可以帮助我们减轻根据不同条件下拼接 SQL 语句的痛苦
用SpringBoot和Mybatis来测试动态SQL的功能
1、环境的准备 使用IDEA工具可以快速创建SpringBoot的应用
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 引入 MyBatis 场景启动器,包含其自动配置类及 MyBatis 3 相关依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
2、开始准备测试的代码
创建数据库
create table user(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
sex varchar(10),
age int,
phone varchar(20),
address varchar(20));
加入准备数据
INSERT INTO `user` VALUES ('1', 'Tom', '123456', 'male', '18', '18200123456', 'chengdu');
INSERT INTO `user` VALUES ('2', 'JACK', '123456', 'male', '18', '18200123456', 'chengdu');
INSERT INTO `user` VALUES ('3', 'ccc', '123456', 'male', '18', '18200123456', 'chongqing');
INSERT INTO `user` VALUES ('4', 'bbb', '123456', 'male', '19', '18200123456', 'chongqing');
INSERT INTO `user` VALUES ('5', 'ccc', '123456', 'male', '20', '18200123456', 'chongqing');
INSERT INTO `user` VALUES ('6', 'xiao xi', '123456', 'male', '18', '18200123456', 'xi an');
INSERT INTO `user` VALUES ('7', 'xiao yang', '123456', 'male', '20', '18200123456', 'gui yang');
实体类
package com.example.dynamicsql.entity;
/**
* @author pangxie
* @data 2020/10/15 11:45
*/
public class User {
private Integer id; // id,主键
private String username; // 用户名
private String password; // 密码
private String sex; // 性别
private Integer age; // 年龄
private String phone; // 电话
private String address; // 地址
public User() {
}
public User(Integer id, String username, String password, String sex, Integer age, String phone, String address) {
this.id = id;
this.username = username;
this.password = password;
this.sex = sex;
this.age = age;
this.phone = phone;
this.address = address;
}
//.... getter setter ....
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
'}'+"\n";
}
}
UserDao.java
@Repository
@Mapper
public interface UserDao {
/**
* 查询全部
* @return
*/
List<User> findAllUsers();
/**
* 测试 if
*/
List<User> conditionQuery(User user);
}
对呀的mapper文件建在 resources 文件下面建一个mapper文件
注:要在配置文件application.properties中 注明该文件的位置
spring.datasource.url=jdbc:mysql://localhost:3306/dynamicsql?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=xmyabc
mybatis.mapper-locations=classpath:mapper/*Dao.xml
UserDao.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.example.dynamicsql.dao.UserDao">
<resultMap type="com.example.dynamicsql.entity.User" id="UserResult">
<result property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
</resultMap>
<select id="findAllUsers" resultMap="UserResult">
select id,username,sex,age,phone,address from user
order by id desc
</select>
</mapper>
if
动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分
if 在 where 子句中做简单的条件判断
<select id="conditionQuery" resultMap="UserResult">
select id,username,sex,age,phone,address from user
where age > 18
<if test="address != null ">
and address = #{address}
</if>
</select>
这个语句的意思是 如果没有提供参数 address 语句相当于
select id,username,sex,age,phone,address from user
where age > 18
如果提供了参数 address 那么就会查询年龄>18 且传入内容的用户信息
如果想可选两个条件进行查询只需要加入另外一个条件就可以
测试代码
/**
* 测试 if
* 传入参数 address 为成都 就是查询 age >18 的 且address 为 chongqing 的
*/
@Test
public void conditionQuery(){
User user = new User(null,null,null,null,null,null,"chongqing");
List<User> users = userDao.conditionQuery(user);
System.out.println(users);
}
测试结果
choose
如果我们只想从所有条件中择其中一个,可选择 choose
元素
choose 就和 java 语言中的 switch 类似,按照顺序执行当when中有条件满足时,则跳出 choose 所以在when 和 otherwise 中只会输出一个,当所有的when的条件都不满足时就输出 otherwise 的内容
<select id="queryChoose" resultMap="UserResult">
select id,username,sex,age,phone,address from user
where age > 18
<choose>
<when test="phone != null">
and phone like #{phone}
</when>
<when test="username != null">
and username like #{username}
</when>
<otherwise>
and address = 'chongqing'
</otherwise>
</choose>
</select>
//整个的意思就是 查询 age>18 的
//如果第一个 when 满足的话,则查询 age>18 且电话包含所传入的内容的用户信息
//如果第一个不满足,第二个满足,则查询 age>18 且用户名包含所传入的内容的用户信息
//如果两个都不满足,则查询 age>18 地址为 chongqing的用户的信息
测试
/**
* 测试 choose
*/
@Test
public void queryChoose(){
User user = new User(null,null,null,null,null,"%182%",null);
List<User> users = userDao.queryChoose(user);
System.out.println(users);
}
测试结果
trim (where,set)
我们来看下面的例子:
<select id="dynamicSqlTest" resultType="User">
select * from user where
<if test="address != null">
address = #{address}
</if>
<if test="phone != null">
and phone like #{phone}
</if>
</select>
可以看到,如果两个条件都不满足的话,SQL
语句变成:
select * from user where ,
如果只满足后一个条件呢,
SQL 语句变成:
select * from user where and phone like #{phone}
这两种情况均会导致查询失败。
因此,动态 SQL 引入了 trim、where 和 set 元素。
trim
元素可以给自己包含的内容加上,前缀(prefix)或加上后缀(suffix),也可以把包含内容的首部(prefixOverrides)或尾部(suffixOverrides)某些内容移除。
<select id="queryTrim" resultType="UserResult">
select id,username,sex,age,phone,address from user
<trim prefix="where" prefixOverrides="and |or ">
<if test="address != null">
address = #{address}
</if>
<if test="phone != null">
and phone like #{phone}
</if>
</trim>
</select>
where
where 元素知道只有在一个以上的 if 条件满足的情况下才去插入 where 子句,而且能够智能地处理 and 和 or 条件。
<select id="queryWhere" resultType="UserResult">
select id,username,sex,age,phone,address from user
<where>
<if test="address != null">
address = #{address}
</if>
<if test="phone != null">
and phone like #{phone}
</if>
</where>
</select>
set
元素可以被用于动态包含需要更新的列,而舍去其他的。
<update id="querySet">
update User
<set>
<if test="phone != null">phone=#{phone},</if>
<if test="address != null">address=#{address}</if>
</set>
where id=#{id}
</update>
set 元素会动态前置 set 关键字,
同时也会消除无关的逗号。与其等价的 trim 语句如下:
<trim prefix="set" suffixOverrides=",">
...
</trim>