Collided?
In this article, we will look at how you can make queries on a table with a changing list of criteria in the Spring + JPA / Hibernate framework without attaching additional libraries.
There are only two main questions:
- How to dynamically assemble a SQL query
- How to pass conditions for the formation of this request
For assembling JPA requests, starting from 2.0 (
Specification - total query constraints, contains Predicate objects as WHERE, HAVING conditions. Predicates are final expressions that can be true or false.
A single condition consists of a field, a comparison operator, and a value to compare. Conditions can also be nested. Let's fully describe the condition with the SearchCriteria class:
public class SearchCriteria{
//
String key;
// (, .)
SearchOperator operator;
//
String value;
//
private JoinType joinType;
//
private List<SearchCriteria> criteria;
}Now let's describe the builder itself. He will be able to build a specification based on the submitted list of conditions, as well as combine several specifications in a certain way:
/**
*
*/
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]));
}
};
}
}In order not to fence a huge if for comparison operations, we implement Map operators of the form <Operation, Operator>. The operator must be able to construct a single predicate. I will give an example of the ">" operation, the rest are written by analogy:
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());
}
}
}Now it remains to implement recursive parsing of our SearchCriteria structure. Note that the buildPath method, which by Root - the scope of the object T will find the path to the field referenced by 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]);
}
}Let's write a test case for our builder:
// 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());
}
}In total, we taught our application to parse a boolean expression using the Criteria.API. The set of operations in the current implementation is limited, but the reader can independently implement the ones he needs. In practice, the solution has been applied, but users are not interested (
DISCLAIMER handler does not claim to be completely universal; if you need to add complicated JOINs, you will have to go into the implementation.
You can find the implemented version with extended tests in my repository on Github . You
can read more about Criteria.Api here .