Criteria Query
- General
Criteria Query
Queries are the methods that finds the information from the database and these methods are written inside the interface.
Query Creation from method names
We have User Model with the attributes :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@Data @AllArgsConstructor @NoArgsConstructor @Entity public class User { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "id", nullable = false) private Long id; private String firstname; private String lastname; private Long price; private Integer phoneNo; @OneToOne @JoinColumn(name = "address_id") private Address address; } |
Now if we have to find the user by firstname or we have to find the list of user with given price, for that we have to write the query but we have some JPA methods to find these. So no need to write query, just simply declare the methods.
You can see this article of spring.io for more details.
|
1 2 3 4 5 |
@Repository public interface UserRepository extends JpaRepository<User, Long>{ User findByFirstname(String firstname); List<User> findByPrice(Long price); } |
Why we need Specification ?
In the Query creation from method names we have to write method query or query for every new demand. So,
- it is not reusable
- keeping track of all methods is difficult
That’s why we need specification because it is reusable.
|
1 2 3 |
@Repository public interface UserRepository extends JpaSpecificationExecutor<User> { } |
Spring JPA Specifications is a great tool whether we want to create reusable predicates or want to generate typesafe queries programmatically.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
@Data @AllArgsConstructor @NoArgsConstructor public class SpecifiactionDto { private String column; private String value; private Operation operation; private String joinTable; public enum Operation{ EQUAL, LIKE, IN, BETWEEN, GREATER_THAN, LESS_THAN, JOIN; } } |
In the service class we create method whose return type is specification and it overrides a toPredicate method which accepts the 3 parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@Service public class UserService<T> { public Specification<T> getBySpecifiaction(SpecifiactionDto specifiactionDto){ return new Specification<T>() { @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { // return criteriaBuilder.equal(root.get("attribute name"), "value"); return criteriaBuilder.equal(root.get(specifiactionDto.getColumn()), specifiactionDto.getValue()); } }; } } |
In the controller, we call the findAll method of userRepository and pass the specification in that.
Here the Request method is post because we have to pass the values for searching and sometimes the values can be large. So we have to use the POST method.
|
1 2 3 4 5 |
@RequestMapping(value = "/specification", method = RequestMethod.POST) public List<User> getBySpecification(@RequestBody SpecifiactionDto specifiactionDto){ Specification<User> specifiaction = userService.getBySpecifiaction(specifiactionDto); return userRepository.findAll(specifiaction); } |
Now If we want to pass multiple list of Criteria Query then we can return lambda function.
We can also write like, equal, in, between, join, and many more operators as we use in the SQL Query.
|
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 |
@Service public class UserService<T> { public Specification<T> getBySpecifiactions(List<SpecifiactionDto> request, SpecificationRequest.Operator operator){ return (root, query, criteriaBuilder) -> { List<Predicate> predicates = new ArrayList<>(); for (SpecifiactionDto dto : request){ switch (dto.getOperation()){ case LIKE: // eg : a Predicate like = criteriaBuilder.like(root.get(dto.getColumn()), "%"+dto.getValue()+"%"); predicates.add(like); break; case EQUAL: // eg : Riya Predicate equal = criteriaBuilder.equal(root.get(dto.getColumn()), dto.getValue()); predicates.add(equal); break; case IN: // eg : Riya, Siya, Prachi String[] split = dto.getValue().split(","); Predicate in = root.get(dto.getColumn()).in(Arrays.asList(split)); predicates.add(in); break; case BETWEEN: // eg : 10, 20 String[] split1 = dto.getValue().split(","); Predicate between = criteriaBuilder.between(root.get(dto.getColumn()), split1[0], split1[1]); predicates.add(between); break; case GREATER_THAN: // eg : Riya Predicate greaterThan = criteriaBuilder.greaterThan(root.get(dto.getColumn()), dto.getValue()); predicates.add(greaterThan); break; case LESS_THAN: // eg : Riya Predicate lessThan = criteriaBuilder.lessThan(root.get(dto.getColumn()), dto.getValue()); predicates.add(lessThan); break; case JOIN: // eg : Riya //criteriaBuilder.equal(root.join("Join Table name - address").get("attribute from join table - city"), dto.getValue()); Predicate join = criteriaBuilder.equal(root.join(dto.getJoinTable()).get(dto.getColumn()), dto.getValue()); predicates.add(join); break; default: throw new IllegalStateException("Unexpected value: "); } } if(operator.equals(SpecificationRequest.Operator.AND)) return criteriaBuilder.and(predicates.toArray(new Predicate[0])); // if(operator.equals(SpecificationRequest.Operator.OR)) return criteriaBuilder.or(predicates.toArray(new Predicate[0])); }; } } |
Output :
In this way we can use Criteria API. It offers a programmatic way to create typed queries, which helps us avoid syntax errors.
Related content
Auriga: Leveling Up for Enterprise Growth!
Auriga’s journey began in 2010 crafting products for India’s