在实际应用中，经常会有需要使用到动态查询的地方，如上图所示。以下将使用一个例子来说明我是怎么使用Spring Data Jpa 实现动态查询的。
数据库中有student表：
现需要实现student表的动态查询功能，可能的查询条件有：id、name、age、sex、address、phone。
代码实现：
1、student表对应的实体类
package com.example.demo;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import javax.persistence.*;
/**
* @author 肖政宇
* @date 2019-09-05 20:23
* 说明：student表对应的实体类
*/
@Data
@Entity
@Table(name = "student")
public class StudentEntity {
@Id
private String id;
@Column
private String name;
@Column
private Integer age;
@Column
private String sex;
@Column
private String address;
@Column
private String phone;
/**
* 分页查询、排序查询
* pageNumber - 页码
* pageSize - 页数据条数
* direction - 升序|降序 （ASC、asc、DESC、desc）
* columnName - 用于排序的字段的名称
*/
@Transient
private int pageNumber;
@Transient
private int pageSize;
@Transient
private String direction;
@Transient
private String columnName;
public StudentEntity() {
}
public StudentEntity(String id, String name, Integer age, String sex, String address, String phone) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.address = address;
this.phone = phone;
}
}
因为studentt表中的字段很少，所以我打算直接使用student表对应的实体类，即StudentEntity来接收动态查询的数据。如果遇到的是这样的情况：需要使用5个查询条件对一个来自3张表，具有30个字段的视图进行查询。那么我会另外创建一个辅助类用于接收动态查询的条件。
一般来说，如果查询到的数据可能有很多条，最好使用分页查询。
2、Repostitory
package com.example.demo;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.PagingAndSortingRepository;
/**
* @author 肖政宇
* @date 2019-09-05 20:27
* 说明：
*/
public interface StudentRepository extends PagingAndSortingRepository<StudentEntity, String>, JpaSpecificationExecutor<StudentEntity> {
}
继承PagingAndSortingRepository接口是为了分页和排序查询。
继承JpaSpecificationExecutor接口是为了动态查询。
3、Service
package com.example.demo;
import com.example.util.Pageable.PageableFactory;
import com.example.util.Specification.SpecificationFactory;
import com.example.util.msg.Message;
import com.example.util.msg.MessageBox;
import com.example.util.string.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
/**
* @author 肖政宇
* @date 2019-09-05 22:10
* 说明：
*/
@Service
public class StudentService {
@Autowired
StudentRepository repository;
/**
* 动态查询
*
* @param entity - 存储动态查询条件
*/
public Message findConfitional(StudentEntity entity) {
List<Specification> specificationList = new ArrayList<>();
Specification specification = null;
/**
* 确定查询条件
*/
System.out.println("select * from student where ");
if (!StringUtil.isNullOrEmpty(entity.getId())) {
System.out.println("id = ");
specificationList.add(SpecificationFactory.like("id", entity.getId()));
}
if (!StringUtil.isNullOrEmpty(entity.getName())) {
System.out.println("name = ");
specificationList.add(SpecificationFactory.like("name", entity.getName()));
}
if (entity.getAge() != null) {
System.out.println("age = ");
specificationList.add(SpecificationFactory.like("age", entity.getAge()));
}
if (!StringUtil.isNullOrEmpty(entity.getSex())) {
System.out.println("sex = ");
specificationList.add(SpecificationFactory.like("address", entity.getAddress()));
}
if (!StringUtil.isNullOrEmpty(entity.getPhone())) {
System.out.println("phone = ");
specificationList.add(SpecificationFactory.like("phone", entity.getPhone()));
}
/**
* 综合查询条件
*/
for (Specification spe : specificationList) {
if (specification != null) {
specification = specification.and(spe);
} else {
specification = spe;
}
}
/**
* 执行查询操作
*/
Pageable pageable = PageableFactory.getPageable(entity.getPageNumber(), entity.getPageSize(), entity.getDirection(), entity.getColumnName());
if (specification != null) {
return new MessageBox<>(repository.findAll(specification, pageable));
} else {
/**
* 普通的分页、排序查询
*/
return new MessageBox<>(repository.findAll(pageable));
}
}
}
我的方法可能比较笨，应该可以进一步抽象。
代码中调用到StringUtil类以及SpecificationFactory工具类的代码放在文章后面。
4、Controller
package com.example.demo;
import com.example.util.msg.Message;
import com.example.util.msg.MessageBox;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
/**
* @author 肖政宇
* @date 2019-09-05 20:27
* 说明：
*/
@RestController
@RequestMapping("student/")
public class StudentController {
private StudentRepository repository;
private StudentService service;
@Autowired
private void init(StudentRepository studentRepository, StudentService studentService) {
this.repository = studentRepository;
this.service = studentService;
}
@GetMapping("findAll")
@ResponseBody
public Message findAll() {
return new MessageBox<>(repository.findAll());
}
@PostMapping("findConditional")
@ResponseBody
public Message findConditional(@RequestBody StudentEntity entity) {
return service.findConfitional(entity);
}
}
测试效果
1、不设置查询条件、不进行排序、进行分页查询
返回的数据：
{
"status": 1,
"message": "成功",
"data": {
"content": [
{
"id": "1",
"name": "李云龙",
"age": 21,
"sex": "男",
"address": "福建省厦门市集美区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "2",
"name": "楚云飞",
"age": 22,
"sex": "男",
"address": "福建省厦门市思明区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "3",
"name": "赵刚",
"age": 23,
"sex": "男",
"address": "福建省厦门市海沧区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
}
],
"pageable": {
"sort": {
"sorted": false,
"unsorted": true,
"empty": true
},
"offset": 0,
"pageNumber": 0,
"pageSize": 10,
"paged": true,
"unpaged": false
},
"totalElements": 3,
"totalPages": 1,
"last": true,
"number": 0,
"size": 10,
"sort": {
"sorted": false,
"unsorted": true,
"empty": true
},
"numberOfElements": 3,
"first": true,
"empty": false
},
"ok": true
}2、不设置查询条件、进行排序、进行分页查询
返回的数据：
{
"status": 1,
"message": "成功",
"data": {
"content": [
{
"id": "3",
"name": "赵刚",
"age": 23,
"sex": "男",
"address": "福建省厦门市海沧区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "2",
"name": "楚云飞",
"age": 22,
"sex": "男",
"address": "福建省厦门市思明区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "1",
"name": "李云龙",
"age": 21,
"sex": "男",
"address": "福建省厦门市集美区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
}
],
"pageable": {
"sort": {
"sorted": true,
"unsorted": false,
"empty": false
},
"offset": 0,
"pageNumber": 0,
"pageSize": 10,
"paged": true,
"unpaged": false
},
"totalElements": 3,
"totalPages": 1,
"last": true,
"number": 0,
"size": 10,
"sort": {
"sorted": true,
"unsorted": false,
"empty": false
},
"numberOfElements": 3,
"first": true,
"empty": false
},
"ok": true
}3、设置查询条件、排序、分页
为了测试效果，在数据库中对student表的数据进行添加：
返回的数据：
{
"status": 1,
"message": "成功",
"data": {
"content": [
{
"id": "9",
"name": "火娃",
"age": 25,
"sex": "男",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "8",
"name": "二娃",
"age": 32,
"sex": "女",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "7",
"name": "大娃",
"age": 21,
"sex": "男",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "6",
"name": "小马",
"age": 20,
"sex": "男",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "5",
"name": "小刘",
"age": 31,
"sex": "女",
"address": "福建省南平市",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "4",
"name": "小王",
"age": 21,
"sex": "女",
"address": "福建省厦门市集美区",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "3",
"name": "赵刚",
"age": 23,
"sex": "男",
"address": "福建省厦门市海沧区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "2",
"name": "楚云飞",
"age": 22,
"sex": "男",
"address": "福建省厦门市思明区",
"phone": "152********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "11",
"name": "千里眼",
"age": 23,
"sex": "女",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
},
{
"id": "10",
"name": "水娃",
"age": 32,
"sex": "男",
"address": "浙江省金华市",
"phone": "137********",
"pageNumber": 0,
"pageSize": 0,
"direction": null,
"columnName": null
}
],
"pageable": {
"sort": {
"sorted": true,
"unsorted": false,
"empty": false
},
"offset": 0,
"pageNumber": 0,
"pageSize": 10,
"paged": true,
"unpaged": false
},
"totalPages": 2,
"last": false,
"totalElements": 11,
"number": 0,
"size": 10,
"sort": {
"sorted": true,
"unsorted": false,
"empty": false
},
"numberOfElements": 10,
"first": true,
"empty": false
},
"ok": true
}工具类
1、Message.java
package com.example.util.msg;
import lombok.Data;
@Data
public class Message {
/**
* 状态码 1：成功， 0：失败
*/
public static final int STATUS_OK = 1;
public static final int STATUS_FAIL = 0;
public static final String MESSAGE_OK = "成功";
public static final String MESSAGE_FAIL = "失败";
private int status = STATUS_OK;
private String message = MESSAGE_OK;
public Message() {
super();
}
public Message(int status, String message) {
super();
this.status = status;
this.message = message;
}
public boolean isOk() {
return !this.isFail();
}
private boolean isFail() {
return this.status == 0;
}
public static Message ok(String message) {
return new Message(STATUS_OK, message);
}
public static Message fail(String message) {
return new Message(STATUS_FAIL, message);
}
public static Message ok() {
return ok(MESSAGE_OK);
}
}
2、MessageBox.java
package com.example.util.msg;
public class MessageBox<T> extends Message {
private T data;
public MessageBox() {
super();
}
public MessageBox(int status, String message) {
super(status, message);
}
public MessageBox(T data) {
this.data = data;
}
public T getData() {
return data;
}
public MessageBox setData(T data) {
this.data = data;
return this;
}
public boolean hasData() {
return this.data != null;
}
public static MessageBox send(Object data, Object... params) {
int status = STATUS_OK;
String message = MESSAGE_OK;
boolean getStatus = false;
boolean getMsg = false;
for (Object param: params) {
if (param instanceof Integer && !getStatus) {
status = (Integer)param;
getStatus = true;
} else if (param instanceof String && !getMsg) {
message = (String)param;
getMsg = true;
}
if (getStatus && getMsg) {
break;
}
}
MessageBox mb = new MessageBox(status, message);
return mb.setData(data);
}
}
3、PageFactory.java
package com.example.util.Pageable;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
/**
* @author 肖政宇
* @date 2019-09-05 22:18
* 说明：
*/
public class PageableFactory {
/**
* @param pageNumber - 页码
* @param pageSize - 页面大小
* @param direction - ASC|DESC
* @param columnName - 用于排序的字段
* @return
*/
public static Pageable getPageable(int pageNumber, int pageSize, String direction, String columnName) {
Sort sort = null;
if ("ASC".equals(direction) || "asc".equals(direction)) {
sort = new Sort(Sort.Direction.ASC, columnName);
} else if ("DESC".equals(direction) || "desc".equals(direction)) {
sort = new Sort(Sort.Direction.DESC, columnName);
}
return sort != null ? PageRequest.of(pageNumber, pageSize, sort) : PageRequest.of(pageNumber, pageSize);
}
}
4、SpecificationFactory.java
package com.example.util.Specification;
import org.springframework.data.jpa.domain.Specification;
import java.util.Collection;
import java.util.Date;
/**
* @author 肖政宇
* @date 2019-08-13 17:08
*/
public final class SpecificationFactory {
/**
* like查询
*
* @param attribute - 用于匹配的字段(实体类中对应的属性)
* @param value -该字段需要匹配的值
*/
public static Specification like(String attribute, String value) {
return (root, query, cb) -> cb.like(root.get(attribute), "%" + value + "%");
}
public static Specification like(String attribute, int value) {
return (root, query, cb) -> cb.like(root.get(attribute), "%" + value + "%");
}
public static Specification like(String attribute, Integer value) {
return (root, query, cb) -> cb.like(root.get(attribute), "%" + value + "%");
}
/**
* equal查询
*
* @param attribute - 用于匹配的字段
* @param value - 用于匹配的值
*/
public static Specification equal(String attribute, Object value) {
return (root, query, cb) -> cb.equal(root.get(attribute), value);
}
/**
* between查询
*
* @param attribute - 用于匹配的字段
* @param min - 区间下限
* @param max - 区间上限
*/
public static Specification between(String attribute, int min, int max) {
return (root, query, cb) -> cb.between(root.get(attribute), min, max);
}
public static Specification between(String attribute, double min, double max) {
return (root, query, cb) -> cb.between(root.get(attribute), min, max);
}
public static Specification between(String attribute, Date min, Date max) {
return (root, query, cb) -> cb.between(root.get(attribute), min, max);
}
/**
* in查询
*
* @param attribute - 用于匹配的属性
* @param c - 满足条件的属性值集合
*/
public static Specification in(String attribute, Collection c) {
return (root, query, cb) -> root.get(attribute).in(c);
}
/**
* 大于等于
*
* @param attribute - 用于匹配的属性
* @param date - 日期值
*/
public static Specification greaterOrEqual(String attribute, Date date) {
return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get(attribute), date);
}
/**
* 小于等于
*
* @param attribute - 用于匹配的属性
* @param date - 日期值
*/
public static Specification smallerOrEqual(String attribute, Date date) {
return (root, query, cb) -> cb.lessThanOrEqualTo(root.get(attribute), date);
}
/**
* 大于
*
* @param attribute - 用于匹配的属性
* @param date - 日期值
*/
public static Specification greaterThan(String attribute, Date date) {
return (root, query, cb) -> cb.greaterThan(root.get(attribute), date);
}
/**
* 小于
*
* @param attribute - 用于匹配的属性
* @param date - 日期值
*/
public static Specification smallerThan(String attribute, Date date) {
return (root, query, cb) -> cb.lessThan(root.get(attribute), date);
}
}
对SpecificationFactory类可根据需要进行扩充。
5、StringUtil.java
package com.example.util.string;
/**
* @author 肖政宇
* @date 2019-09-06 15:39
* 说明：String操作
*/
public class StringUtil {
public static boolean isNullOrEmpty(String string) {
return string == null || "".equals(string);
}
}
以上就是目前我使用Spring Data Jpa 进行动态查询的大概方式，动态查询中的范围查询也可以按照以上方式实现的，其实，如果不需要对某个字段的值进行范围查询，完全可以使用JPA的示例查询。
本人是刚入门的新人，有需要提高的地方希望各位能够支持，谢谢。
313
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
