服务器之家

服务器之家 > 正文

Spring Data JPA实现动态条件与范围查询实例代码

时间：2020-11-24 15:10     来源/作者：汪云飞记录本

Spring Data JPA为我们提供了Query With Example来实现动态条件查询，当查询条件为空的时候，我们不用做大量的条件判断。但是Query With Example却不支持范围查询(包括日期范围，数值范围查询)，本文通过Specification实现了既支持动态条件查询又支持范围查询的方法。

1 实现方式

1.1 范围对象Range定义

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
import java.io.Serializable;
 
 
public class Range<E> implements Serializable {
  private static final long serialVersionUID = 1L;
 
  private String field;
  private Comparable from;
  private Comparable to;
  private Boolean includeNull;
 
 
  public Range(String field) {
    this.field = field;
  }
 
 
  public Range(String field, Comparable from, Comparable to) {
    this.field = field;
    this.from = from;
    this.to = to;
  }
 
  public Range(String field, Comparable from, Comparable to, Boolean includeNull) {
    this.field = field;
    this.from = from;
    this.to = to;
    this.includeNull = includeNull;
  }
 
 
  public Range(Range<E> other) {
    this.field = other.getField();
    this.from = other.getFrom();
    this.to = other.getTo();
    this.includeNull = other.getIncludeNull();
  }
 
  public String getField() {
    return field;
  }
 
  public Comparable getFrom() {
    return from;
  }
 
 
  public void setFrom(Comparable from) {
    this.from = from;
  }
 
  public boolean isFromSet() {
    return getFrom() != null;
  }
 
 
  public Comparable getTo() {
    return to;
  }
 
  public void setTo(Comparable to) {
    this.to = to;
  }
 
  public boolean isToSet() {
    return getTo() != null;
  }
 
  public void setIncludeNull(boolean includeNull) {
    this.includeNull = includeNull;
  }
 
  public Boolean getIncludeNull() {
    return includeNull;
  }
 
  public boolean isIncludeNullSet() {
    return includeNull != null;
  }
 
  public boolean isBetween() {
    return isFromSet() && isToSet();
  }
 
  public boolean isSet() {
    return isFromSet() || isToSet() || isIncludeNullSet();
  }
 
  public boolean isValid() {
    if (isBetween()) {
      return getFrom().compareTo(getTo()) <= 0;
    }
 
    return true;
  }
}

1.2 example的Specification

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import org.springframework.data.domain.Example;
import org.springframework.data.jpa.convert.QueryByExamplePredicateBuilder;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.Assert;
 
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
 
/**
 * Created by wangyunfei on 2017/6/6.
 */
public class ByExampleSpecification<T> implements Specification<T> {
  private final Example<T> example;
 
  public ByExampleSpecification(Example<T> example) {
 
    Assert.notNull(example, "Example must not be null!");
    this.example = example;
  }
 
 
  @Override
  public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
    return QueryByExamplePredicateBuilder.getPredicate(root, cb, example);
  }
}

1.3 Range的Specification

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
import org.springframework.data.jpa.domain.Specification;
 
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.List;
 
import static com.google.common.collect.Iterables.toArray;
import static com.google.common.collect.Lists.newArrayList;
import static java.lang.Boolean.FALSE;
import static java.lang.Boolean.TRUE;
 
/**
 * Created by wangyunfei on 2017/6/6.
 */
public class ByRangeSpecification<T> implements Specification<T> {
  private final List<Range<T>> ranges;
 
  public ByRangeSpecification(List<Range<T>> ranges) {
    this.ranges = ranges;
  }
 
  @Override
  public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    List<Predicate> predicates = newArrayList();
 
    for (Range<T> range : ranges) {
      if (range.isSet()) {
        Predicate rangePredicate = buildRangePredicate(range, root, builder);
 
        if (rangePredicate != null) {
          if (!range.isIncludeNullSet() || range.getIncludeNull() == FALSE) {
            predicates.add(rangePredicate);
          } else {
            predicates.add(builder.or(rangePredicate, builder.isNull(root.get(range.getField()))));
          }
        }
 
        if (TRUE == range.getIncludeNull()) {
          predicates.add(builder.isNull(root.get(range.getField())));
        } else if (FALSE == range.getIncludeNull()) {
          predicates.add(builder.isNotNull(root.get(range.getField())));
        }
      }
    }
    return predicates.isEmpty() ? builder.conjunction() : builder.and(toArray(predicates, Predicate.class));
  }
 
  private Predicate buildRangePredicate(Range<T> range, Root<T> root, CriteriaBuilder builder) {
    if (range.isBetween()) {
      return builder.between(root.get(range.getField()), range.getFrom(), range.getTo());
    } else if (range.isFromSet()) {
      return builder.greaterThanOrEqualTo(root.get(range.getField()), range.getFrom());
    } else if (range.isToSet()) {
      return builder.lessThanOrEqualTo(root.get(range.getField()), range.getTo());
    }
    return null;
  }
 
}

1.4 自定义Repository与实现

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;
 
import java.io.Serializable;
import java.util.List;
 
@NoRepositoryBean
public interface WiselyRepository<E, PK extends Serializable> extends JpaRepository<E, PK> {
 
 
 Page<E> queryByExampleWithRange(Example example,List<Range<E>> ranges, Pageable pageable);
 
}
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
 
import javax.persistence.EntityManager;
import java.io.Serializable;
import java.util.List;
 
import static org.springframework.data.jpa.domain.Specifications.where;
 
 
public class WiselyRepositoryImpl<E, PK extends Serializable> extends SimpleJpaRepository<E, PK> implements
    WiselyRepository<E, PK> {
  private final EntityManager entityManager;
 
  public WiselyRepositoryImpl(JpaEntityInformation entityInformation, EntityManager entityManager) {
    super(entityInformation, entityManager);
    this.entityManager = entityManager;
  }
 
 
 
  @Override
  public Page<E> queryByExampleWithRange(Example example, List<Range<E>> ranges, Pageable pageable) {
    Specification<E> byExample = new ByExampleSpecification<>(example);
    Specification<E> byRanges = new ByRangeSpecification<>(ranges);
    return findAll(where(byExample).and(byRanges),pageable);
  }
}

2 使用方式

2.1 开启支持

通过@EnableJpaRepositories(repositoryBaseClass = WiselyRepositoryImpl.class)开启对定义功能的支持。

2.2 示例

实体类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Person {
  @Id
  @GeneratedValue
  private Long id;
  private String name;
  private Integer height;
  @DateTimeFormat(pattern = "yyyy-MM-dd")
  private Date birthday;
}

PersonRepository

?
1
2
public interface PersonRepository extends WiselyRepository<Person,Long> {
}

测试控制器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@RestController
@RequestMapping("/people")
public class PersonController {
 
  @Autowired
  PersonRepository personRepository;
 
  @PostMapping
  public ResponseEntity<Person> save(@RequestBody Person person){
    Person p = personRepository.save(person);
    return new ResponseEntity<Person>(p, HttpStatus.CREATED);
  }
 
 
  @GetMapping
  public ResponseEntity<Page<Person>> query(Person person,
                       @DateTimeFormat(pattern = "yyyy-MM-dd")Date startDate,
                       @DateTimeFormat(pattern = "yyyy-MM-dd")Date endDate,
                       Integer startHeight,
                       Integer endHeight,
                       Pageable pageable){
    Example<Person> personExample = Example.of(person);
 
    List<Range<Person>> ranges = newArrayList();
    Range<Person> birthRange = new Range<Person>("birthday",startDate,endDate);
    Range<Person> heightRange = new Range<Person>("height",startHeight,endHeight);
    ranges.add(birthRange);
    ranges.add(heightRange);
 
    Page<Person> page = personRepository.queryByExampleWithRange(personExample,ranges,pageable);
 
    return new ResponseEntity<Page<Person>>(page,HttpStatus.OK);
 
  }
}

源码地址：https://github.com/wiselyman/query_with_example_and_range

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：http://www.wisely.top/2017/06/06/spring-data-jpa-dynamic-query-with-range/?utm_source=tuicool&utm_medium=referral

标签：
spring data jpa 动态条件 范围查询 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址 2020-08-12
最新idea2020注册码永久激活(激活到2100年)
最新idea2020注册码永久激活(激活到2100年) 2020-07-29
返回顶部