冲突?
在本文中,我们将研究如何在Spring + JPA / Hibernate中使用具有变化的条件列表的表进行查询,而无需附加其他库。
只有两个主要问题:
- 如何动态组装SQL查询
- 如何通过形成此请求的条件
为了组装JPA请求,从2.0开始(
规范-总查询约束,包含WHERE,HAVING条件等谓词对象。谓词是可以为true或false的最终表达式。
一个条件由一个字段,一个比较运算符和一个要比较的值组成。条件也可以嵌套。让我们使用SearchCriteria类充分描述条件:
public class SearchCriteria{
//
String key;
// (, .)
SearchOperator operator;
//
String value;
//
private JoinType joinType;
//
private List<SearchCriteria> criteria;
}
现在让我们描述构建器本身。他将能够基于提交的条件列表来建立规范,并以某种方式组合多个规范:
/**
*
*/
public class JpaSpecificationsBuilder<T> {
// join-
private Map<String,Join<Object, Object>> joinMap = new HashMap<>();
//
private Map<SearchOperation, PredicateBuilder> predicateBuilders = Stream.of(
new AbstractMap.SimpleEntry<SearchOperation,PredicateBuilder>(SearchOperation.EQ,new EqPredicateBuilder()),
new AbstractMap.SimpleEntry<SearchOperation,PredicateBuilder>(SearchOperation.MORE,new MorePredicateBuilder()),
new AbstractMap.SimpleEntry<SearchOperation,PredicateBuilder>(SearchOperation.MOREQ,new MoreqPredicateBuilder()),
new AbstractMap.SimpleEntry<SearchOperation,PredicateBuilder>(SearchOperation.LESS,new LessPredicateBuilder()),
new AbstractMap.SimpleEntry<SearchOperation,PredicateBuilder>(SearchOperation.LESSEQ,new LesseqPredicateBuilder())
).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
/**
*
*/
public Specification<T> buildSpecification(SearchCriteria criterion){
this.joinMap.clear();
return (root, query, cb) -> buildPredicate(root,cb,criterion);
}
/**
*
*/
public Specification<T> mergeSpecifications(List<Specification> specifications, JoinType joinType) {
return (root, query, cb) -> {
List<Predicate> predicates = new ArrayList<>();
specifications.forEach(specification -> predicates.add(specification.toPredicate(root, query, cb)));
if(joinType.equals(JoinType.AND)){
return cb.and(predicates.toArray(new Predicate[0]));
}
else{
return cb.or(predicates.toArray(new Predicate[0]));
}
};
}
}
为了避免在比较操作中产生巨大的麻烦,我们实现了<Operation,Operator>形式的Map运算符。运算符必须能够构造一个谓词。我将举一个操作“>”的示例,其余类似地编写:
public class EqPredicateBuilder implements PredicateBuilder {
@Override
public SearchOperation getManagedOperation() {
return SearchOperation.EQ;
}
@Override
public Predicate getPredicate(CriteriaBuilder cb, Path path, SearchCriteria criteria) {
if(criteria.getValue() == null){
return cb.isNull(path);
}
if(LocalDateTime.class.equals(path.getJavaType())){
return cb.equal(path,LocalDateTime.parse(criteria.getValue()));
}
else {
return cb.equal(path, criteria.getValue());
}
}
}
现在,我们需要对SearchCriteria结构进行递归解析。请注意,通过Root-对象T的作用域的buildPath方法将找到SearchCriteria.key引用的字段的路径:
private Predicate buildPredicate(Root<T> root, CriteriaBuilder cb, SearchCriteria criterion) {
if(criterion.isComplex()){
List<Predicate> predicates = new ArrayList<>();
for (SearchCriteria subCriterion : criterion.getCriteria()) {
// ,
predicates.add(buildPredicate(root,cb,subCriterion));
}
if(JoinType.AND.equals(criterion.getJoinType())){
return cb.and(predicates.toArray(new Predicate[0]));
}
else{
return cb.or(predicates.toArray(new Predicate[0]));
}
}
return predicateBuilders.get(criterion.getOperation()).getPredicate(cb,buildPath(root, criterion.getKey()),criterion);
}
private Path buildPath(Root<T> root, String key) {
if (!key.contains(".")) {
return root.get(key);
} else {
String[] path = key.split("\\.");
String subPath = path[0];
if(joinMap.get(subPath) == null){
joinMap.put(subPath,root.join(subPath));
}
for (int i = 1; i < path.length-1; i++) {
subPath = Stream.of(path).limit(i+1).collect(Collectors.joining("."));
if(joinMap.get(subPath) == null){
String prevPath = Stream.of(path).limit(i).collect(Collectors.joining("."));
joinMap.put(subPath,joinMap.get(prevPath).join(path[i]));
}
}
return joinMap.get(subpath).get(path[path.length - 1]);
}
}
让我们为构建器编写一个测试用例:
// Entity
@Entity
public class ExampleEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
public int value;
public ExampleEntity(int value){
this.value = value;
}
}
...
//
@Repository
public interface ExampleEntityRepository extends JpaRepository<ExampleEntity,Long>, JpaSpecificationExecutor<ExampleEntity> {
}
...
//
/*
*/
public class JpaSpecificationsTest {
@Autowired
private ExampleEntityRepository exampleEntityRepository;
@Test
public void getWhereMoreAndLess(){
exampleEntityRepository.save(new ExampleEntity(3));
exampleEntityRepository.save(new ExampleEntity(5));
exampleEntityRepository.save(new ExampleEntity(0));
SearchCriteria criterion = new SearchCriteria(
null,null,null,
Arrays.asList(
new SearchCriteria("value",SearchOperation.MORE,"0",null,null),
new SearchCriteria("value",SearchOperation.LESS,"5",null,null)
),
JoinType.AND
);
assertEquals(1,exampleEntityRepository.findAll(specificationsBuilder.buildSpecification(criterion)).size());
}
}
总的来说,我们讲授了应用程序使用Criteria.API解析布尔表达式。当前实现中的操作集是有限的,但是读者可以独立实现他需要的操作。实际上,已经应用了该解决方案,但是用户不感兴趣(
免责声明处理程序并不声称是完全通用的;如果您需要添加复杂的JOIN,则必须进入实现。
您可以在我在Github上的存储库中找到带有扩展测试的实现版本,
还可以在此处阅读有关Criteria.Api的更多信息。