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;@NoRepositoryBeanpublic 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@NoArgsConstructorpublic 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
