Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for manually specifying count query when the results of JPA criteria query are paginated [DATAJPA-263] #679

Closed
spring-projects-issues opened this issue Oct 9, 2012 · 4 comments

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Oct 9, 2012

Petri Kainulainen opened DATAJPA-263 and commented

If you want to paginate the query results of a JPA criteria query, you have to call the findAll(Specification s, Pageable p) method that is declared by the JpaSpecificationExecutor interface. The implementation of this method uses the provided Specification for two purposes: It gets the count of the matching entities and the actual entities.

This approach works fine in most of the cases. However, if you have to join a lazily fetched collection of the entity, a following exception is thrown (when Hibernate is used as JPA provider):

org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

In this case, the following query is created:

select count(*) from foo.bar.Person as generatedAlias0 
left join fetch generatedAlias0.pets as generatedAlias1 where
generatedAlias0.state=:param1

As the exception explained, the problem is that the Person is not present in the select list of the count query.

In this case it would useful if you could manually specify the specification that is used to create the count query. This way you create two specifications: the first one would be used to create the count query (no join needed) and the second one would fetch the actual data (joins can be used)


Affects: 1.1.2

7 votes, 10 watchers

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Apr 10, 2014

Thomas Cross commented

I'd like to add another situation where this feature would really help. At my company we use MS SQL Server. This RDBMS does not support a combination of DISTINCT and ORDER BY sufficiently to support all of our business logic. We use Specifications to programmatically generate queries using GROUP BY clauses to eliminate duplicate records where DISTINCT would typically be used. However, Spring Data JPA does not correctly calculate countQueries in this case. If I could provide a count query Specification, I could ensure that the correct corresponding count was calculated.

I'd be available to help work on this enhancement given a little guidance from the project team as to how they wanted the feature implemented. I don't believe the work is too complex once an API is decided upon

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Oct 13, 2014

Krzysztof Koziol commented

I'm having similar issue:

"java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched ass
ociation was not present in the select list"

Why Hibernate requires entities from left join clause to be present in select list?

@Oliver Gierke - any plans when this issue will be fixed?

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Jun 16, 2015

Aaron Wang commented

I create a class named XiaRepositoryImpl which extends SimpleJpaRepository and overrides method getCountQuery, code blew:

    protected TypedQuery<Long> getCountQuery(Specification<T> spec) {

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

        Root<T> root = applySpecificationToCriteria(spec, query);

        if (root.getFetches() != null && root.getFetches().size() != 0) {
            for (Fetch fetch : root.getFetches()) {
                root.getJoins().add((Join) fetch);
            }
            root.getFetches().clear();
        }
...
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants