JpaSpecificationExecutor : complex queries with specifications

Question

I'm actually creating research with different filters.

As I'm using the JpaRepository to make simple queries, I found the JpaSpecificationExecutor to make dynamic queries with the Criterias.

My problem is I need to create a complex query with a group by and a count(). It's ok for the group by but I don't find how I can overide the "select" part to put a "count" instruction.

Does anybody can help me please ?

I'm using spring 3.1.2 and spring-jpa-data 1.0.3 Here is my code :

return new Specification< Article >() {

    @Override
    public Predicate toPredicate(final Root<Article> root,
        final CriteriaQuery<?> query, final CriteriaBuilder builder) {
        //count ???
        query.groupBy(root.get(Article_.id));
        Predicate p = builder.and(builder.like(root.<String> get(Article_.title), "%" + title + "%"));

        return p;
    }
}

Thanks !

score 5 · Accepted Answer · edited May 23 '17 at 10:29

5

Unfortunately you cannot do this with spring-data Specification.

You can see why and how to do it while still using spring-data here(there is a simple query returning just one field): spring-data specifications - return list of ids instead of objects

To get a list of fields you can use JPA Tuple. You can find an example here: JPA & Criteria API - Select only specific columns

Short version: You need to create a custom spring-data repository which will use CriteriaQuery<Tuple>.

edited May 23 '17 at 10:29

Community

1
1

answered Jan 09 '13 at 09:49

rchukh

2,877
2
21
25

2

Thanks for your help. I choosed to make two request. One to get all my id's with the Specification. The second (the count) with a simple query which match my ids. – user1838850 Jan 09 '13 at 20:11

score 1 · Answer 2 · answered Jan 08 '13 at 02:16

if you look at the Spring Data's org.springframework.data.jpa.repository.support.SimpleJpaRepository there is a method called getCountQuery(..). this query looks like the following;

/**
 * Creates a new count query for the given {@link Specification}.
 * 
 * @param spec can be {@literal null}.
 * @return
 */
private TypedQuery<Long> getCountQuery(Specification<T> spec) {

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Long> query = builder.createQuery(Long.class);

    Root<T> root = applySpecificationToCriteria(spec, query);
    query.select(builder.count(root));

    return em.createQuery(query);
}

this is used the in the Pageable component of Spring Data JPA and may provide a solution

Hello,thanks for your answer, but this method only make a simple count. How can I make a querylike "select a.value,count(a.id) from A group by a.value" ? — user1838850, Jan 08 '13 at 22:31
Spring Data JPA, as rchukh mentions, can do complete custom queries. if you've got the SQL statement already, you can have the entity manager execute it as a NativeQuery and then map the results back to an object yourself — incomplete-co.de, Jan 09 '13 at 12:40

JpaSpecificationExecutor : complex queries with specifications

2 Answers2

Linked