56

Is it possible to use both @Query annotation and specification in one repository method? For example I'd like to have a method like this:

@Query(value="SELECT e from EMPLOYEE where firstName <> ?1")
public Page<Employee> findEmployeeBySomethigFancy(String firstName, Pageable pageable, Specification<Employee> emp);

Is it possible or should I build the whole query as a Predicate and remove the @Query annotation?

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
woytech
  • 701
  • 1
  • 6
  • 15

2 Answers2

58

First thing, you should read this Spring blog post.

Second, according to the JpaSpecificationExecutor interface, that your repositories should implement, the following method take a Specification argument:

  • count(Specification<T> spec)
  • List<T> findAll(Specification<T> spec)
  • Page<T> findAll(Specification<T> spec, Pageable pageable)
  • List<T> findAll(Specification<T> spec, Sort sort)
  • T findOne(Specification<T> spec)

So, you can't mix a @Query definition with a Specification.

However, since you can express the firstName <> ?1 condition using a Specification and because you combine as many specifications as you want, you can rewrite the whole @Query using Specification(s) only.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 3
    Thanks for your response! I know that I can combine as many specification as I want, but I have couple of methods with *@Query* annotation and now there is a need to use Specification, so I have to rewrite existing queries to Specification's objects. I thought that maybe there is a way to somehow combine *@Query* and Specification. – woytech Oct 15 '14 at 21:27
  • For the API implementer point of view, it's much easier to separate those concerns and to offer specific querying facilities. Combining them might complicate the implementation even further. – Vlad Mihalcea Oct 16 '14 at 09:33
  • 9
    This subject is amazing, I have to do a many-to-many join on a JPA query method, and I pass a Specification and Pageable item. For sure the @Query can't be mixed with it (I tested it). And this brings no answers to my cup. don't know how to manage to get my query corresponding with a relationship table, and keeping my Specification and Pageable items :( – Alex Dec 07 '15 at 18:02
  • 2
    @Alex Did you find any solution for your concern? Because am having a similar situation, I can't combine both Query and Specification. Please help us if you have any solution. – Anshul Kabra Aug 26 '19 at 10:14
  • 2
    @AnshulKabra it was a very long time ago, but I can remember ended up using QueryBuilder and CriteriaAPI to extends my JpaRepository and implement the query by hand. Receiving Pageable and Specification, I create the whole query and Page item. – Alex Aug 26 '19 at 13:54
  • 1
    I have a similar issue. The data is selected by a complex query using several cta to add aggregated values. I think this can't be rewritten using specification. The only way I found is a database view. However, this comes with other problems (like dynamic conditions cant be added to the cta part of the query). How to handle such cases? – QStorm Mar 04 '22 at 11:04
0

I did this workaround in order to have a response other than the entity type using the specification I have, maybe it can help you to resolve your needs without @Query:

public ImovelFilterResponseDTO getImovelsFilterByCriteria(Specification<Imovel> specification) {
    ImovelFilterResponseDTO imovelFilterResponseDTO = new ImovelFilterResponseDTO();
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<BigDecimal> query = builder.createQuery(BigDecimal.class);
    Root<Imovel> root = query.from(Imovel.class);
    Predicate predicate = specification.toPredicate(root, query, builder);
    if (predicate != null) {
        query.where(predicate);
    }
    BigDecimal areaHectaresMax =  entityManager.createQuery(query.select(builder.max(root.<BigDecimal>get("areaHectares")))).getSingleResult();
    imovelFilterResponseDTO.setFilterMaxArea(areaHectaresMax);
    return imovelFilterResponseDTO;
}
Luiz Gustavo Aragão
  • 1