动态查询是各大应用常见的功能,相信大家的第一印象就是采用mybatis的动态标签来实现。那究竟是如何实现的呢?相信大部分人,包括之前的我也是一知半解的,本文将尽力解答你的所有疑惑!
1 需求分析
要实现上图这个功能,前端传入查询条件列表,必须明确数据请求和响应的格式是怎样的。很显然,请求的数据是个列表,列表中是一个个的查询条件对象,每个查询条件对象都有三个属性(查询字段、查询规则、查询值)。
在此基础上,我们进行后续的工作。
2 实现
为了方便演示,本文使用Springboot + Mybatis来快速演示。
1. 数据库准备
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`sid` int(11) PRIMARY,
`sname` varchar(20),
`sage` int(20),
`score` int(20),
`city` varchar(20),
`gender` varchar(20)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `t_student` VALUES (1, '张三', 18, 88, '赣州', '男');
INSERT INTO `t_student` VALUES (2, '张四', 19, 90, '深圳', '女');
INSERT INTO `t_student` VALUES (3, '张五', 20, 88, '青岛', '男');
INSERT INTO `t_student` VALUES (4, '张六', 21, 90, '深圳', '男');
INSERT INTO `t_student` VALUES (5, '张七', 18, 88, '深圳', '男');
INSERT INTO `t_student` VALUES (6, '李四', 20, 88, '广州', '男');
INSERT INTO `t_student` VALUES (7, '李五', 22, 98, '深圳', '女');
INSERT INTO `t_student` VALUES (8, '李六', 27, 88, '深圳', '男');
- SpringBoot整合Mybatis准备
2.1 建立Springboot项目
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</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>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
</dependencies>
2.2 建立如下目录结构并完成简单编写
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200726162633318.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xlbzY2Nmxlbw==,size_16,color_FFFFFF,t_70)
2.2.1 Domain
@Getter
@Setter
public class Student implements Serializable {
private Integer sid;
private String sname;
private Integer sage;
private Integer score;
private String city;
private String gender;
}
@Getter
@Setter
public class InputDTO implements Serializable {
private String searchProperty;
private String searchRule;
private Object searchValue;
@Override
public String toString() {
return "InputDTO{" +
"searchProperty='" + searchProperty + '\'' +
", searchRule='" + searchRule + '\'' +
", searchValue='" + searchValue + '\'' +
'}';
}
}
2.2.2 Dao
public interface StudentDao {
//查找学生列表
List<Student> findStudentList();
//动态条件查找学生列表
List<Student> dynamicSelect(List<InputDTO> inputDTOList);
}
2.2.3 Service
public interface StudentService {
//查找学生列表
List<Student> findStudentList();
List<Student> dynamicSelect(List<InputDTO> inputDTOList);
}
2.2.4 ServiceImpl
@Service
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentDao studentDao;
@Override
public List<Student> findStudentList() {
return studentDao.findStudentList();
}
@Override
public List<Student> dynamicSelect(List<InputDTO> inputDTOList) {
//为了测试连表和前后端字段名不一致,可省略
for (InputDTO inputDTO : inputDTOList) {
if (StringUtils.equalsIgnoreCase("id", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.sid");
}else if (StringUtils.equalsIgnoreCase("name", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.sname");
}else if (StringUtils.equalsIgnoreCase("age", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.sage");
}else if (StringUtils.equalsIgnoreCase("score", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.score");
}else if (StringUtils.equalsIgnoreCase("city", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.city");
}else if (StringUtils.equalsIgnoreCase("gender", inputDTO.getSearchProperty())){
inputDTO.setSearchProperty("a.gender");
}
}
return studentDao.dynamicSelect(inputDTOList);
}
}
2.2.4 Controller
@RestController
@RequestMapping("/student")
public class StudentController {
@Autowired
private StudentService studentService;
//查找学生列表
@GetMapping
public List<Student> findStudentList(){
return studentService.findStudentList();
}
//查找学生列表
@PostMapping("/dyna")
public List<Student> dynamicSelect(@RequestBody List<InputDTO> inputDTOList){
System.out.println("***************搜索条件是:************************");
System.out.println(inputDTOList);
return studentService.dynamicSelect(inputDTOList);
}
}
2.2.5 Application
@SpringBootApplication
@MapperScan("com.leo.dyna.dao")
public class DynaApplication {
public static void main(String[] args) {
SpringApplication.run(DynaApplication.class, args);
}
}
2.2.6 Mapper.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.leo.dyna.dao.StudentDao">
<!--查找学生列表-->
<select id="findStudentList" resultType="Student">
select * from t_student
</select>
<!--动态查询-->
<select id="dynamicSelect" parameterType="list" resultType="Student">
SELECT * FROM t_student a
<where>
<foreach collection="list" index="index" item="inputDTO" open=" " separator=" " close=" ">
<!--注意test这里写法和字段名写法-->
<if test="inputDTO.searchRule == 'equal'">
AND ${inputDTO.searchProperty} = #{inputDTO.searchValue}
</if>
<if test="inputDTO.searchRule == 'large'">
AND ${inputDTO.searchProperty} > #{inputDTO.searchValue}
</if>
<if test="inputDTO.searchRule == 'small'">
AND ${inputDTO.searchProperty} < #{inputDTO.searchValue}
</if>
<if test="inputDTO.searchRule == 'like'">
AND ${inputDTO.searchProperty} like concat("%", #{inputDTO.searchValue}, "%")
</if>
</foreach>
</where>
</select>
</mapper>
2.3 配置文件application.yml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/day17?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false
driverClassName: com.mysql.cj.jdbc.Driver
username: root
password: 1234546
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.leo.dyna.domain
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2.4 调试-成功
2.4.1 POST请求参数 :127.0.0.1:8080/student/dyna
[
{
"searchProperty": "name",
"searchRule": "like",
"searchValue": "张"
},
{
"searchProperty": "id",
"searchRule": "large",
"searchValue": "4"
},
{
"searchProperty": "gender",
"searchRule": "equal",
"searchValue": "男"
}
]
2.4.2 响应结果
[
{
"sid": 5,
"sname": "张七",
"sage": 18,
"score": 88,
"city": "深圳",
"gender": "男"
}
]
3 总结
- 核心还是动态标签的使用,我也是经过反复调试才成功的
- 其它细节注意
- 项目地址:https://gitee.com/leoyue-max/dyna.git
希望这篇文章能帮助到你,我是非常力偶!