Home/Criteria Query

Criteria Query

Published On: 30 December 2022.By .
  • General

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 :

@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.

@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.

@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.

@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.

@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.

@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.

@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

We Love Conversations

Say Hello
Go to Top