How to return a single result from Spring-Data-JPA?

Question

I'm trying to get a single result from a Spring Data query. I'm looking to return the greatest ID from a user table. I was hoping it would be straightforward, but I'm a little lost.

So far, based on this related SO post, I've come to the conclusion that I need to use a Specification to define my query and Paged results, specifying the number of results I want to retrieve. Unfortunately, I'm getting a HibernateJdbcException data access exception.

My Specification/Predicate is supposed to be fairly simple and reflect: from User order by id:

Page<User> result =userRepository.findAll(new Specification<User>() {
    @Override
    public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        query.orderBy(cb.desc(root.get("id")));
        return query.getRestriction();
    }
}, new PageRequest(0, 10));

MatcherAssert.assertThat(result.isFirstPage(), is(true));
User u = result.getContent().get(0);

Exception Thrown:

org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [90016]; error code [90016]; could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:651)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:92)
    ...
    ...
Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:909)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
    at org.springframework.data.jpa.repository.query.QueryUtils.executeCountQuery(QueryUtils.java:406)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:433)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:332)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:358)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    ... 46 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "USER1_.ID" must be in the GROUP BY list; SQL statement:
/* select count(generatedAlias0) from User as generatedAlias0, User as generatedAlias1 where 1=1 order by generatedAlias1.id desc */ select count(user0_.id) as col_0_0_ from user user0_ cross join user user1_ where 1=1 order by user1_.id desc [90016-173]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:331)
    at org.h2.message.DbException.get(DbException.java:171)
    at org.h2.message.DbException.get(DbException.java:148)
    at org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:166)
    at org.h2.command.dml.Select.queryGroup(Select.java:344)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:620)
    at org.h2.command.dml.Query.query(Query.java:314)
    at org.h2.command.dml.Query.query(Query.java:284)
    at org.h2.command.dml.Query.query(Query.java:36)
    at org.h2.command.CommandContainer.query(CommandContainer.java:91)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:106)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
    ... 78 more

I'm a little lost by the Hibernate error - it's asking for a group clause. I presume it has something to do with the way I've created the Predicate, but I am not sure how to create a simple predicate like this.

EDIT

As suggested by @OliverGierke, I tried to remove the root = query.from(User.class) but the hibernate still throws the same error (I enabled full hibernate query tracing). Strangely, however, this time, there is no GROUP BY in the generated SQL so I'm even more confused than before.

2014-03-18 11:59:44,475 [main] DEBUG org.hibernate.SQL - 
    /* select
        count(generatedAlias0) 
    from
        User as generatedAlias0 
    order by
        generatedAlias0.id desc */ select
            count(user0_.id) as col_0_0_ 
        from
            user user0_ 
        order by
            user0_.id desc
Hibernate: 
    /* select
        count(generatedAlias0) 
    from
        User as generatedAlias0 
    order by
        generatedAlias0.id desc */ select
            count(user0_.id) as col_0_0_ 
        from
            user user0_ 
        order by
            user0_.id desc
2014-03-18 11:59:44,513 [main] WARN  hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 90016, SQLState: 90016
2014-03-18 11:59:44,513 [main] ERROR hibernate.engine.jdbc.spi.SqlExceptionHelper - Column "USER0_.ID" must be in the GROUP BY list; SQL statement:
/* select count(generatedAlias0) from User as generatedAlias0 order by generatedAlias0.id desc */ select count(user0_.id) as col_0_0_ from user user0_ order by user0_.id desc [90016-173]

This will solve your problem without the need for a specification http://stackoverflow.com/questions/9314078 — azerafati, Oct 20 '14 at 15:58

score 13 · Answer 1 · answered Mar 19 '14 at 13:11

13

I'm not sure why you are fetching a collection to get a single result. Correct me if I'm wrong but the solution to your problem, as I interpreted it, is very easy to solve using @Query. Add the following to your repository interface.

@Query("SELECT max(t.id) FROM #{#entityName} t")
Integer getMaxId();

answered Mar 19 '14 at 13:11

Bart

17,070
5
61
80

I'm trying to do this without `@Query` and modifying my repository interface. It's actually a method I only need in a unit test, and consequently, I'm trying to use use the Specification/Predicate to retrieve the same information. Do you know of a way I can do this without modifying the repo interface? – Eric B. Mar 19 '14 at 16:16
I don't have any experience using them. Never needed them. I cannot recommend you a different solution. Sorry. – Bart Mar 19 '14 at 17:00

Oliver Drotbohm · Accepted Answer · 2014-03-19T09:10:40.807

12

You're not using the root getting handed into the Specification instance to invoke the .get(…) method on. That fails the instance to register id being used and thus transparently adding it to the result set.

Simply removing root = query.from(User.class); should do the trick.

What puzzles me though is that you mention you're intending to build a "find by id" query. What you're actually building is a "find all ordered by id". If it's really the former you want to get, there's a predefined findOne(…) method on CrudRepository you can use.

Given the comments below it seems what you're actually trying to achieve is finding a single user with the smallest id. This can also be achieved by simply extending PagingAndSortingRepository and then use client code like this:

interface UserRepository extends PagingAndSortingRepository<User, Long> { … }

Page<User> users = repository.findAll(new PageRequest(0, 1, Direction.ASC, "id"));
User user = users.getContent.get(0);

This will limit the result to the first page by a page size of 1 with ascending ordering by id.

edited Mar 19 '14 at 09:10

answered Mar 18 '14 at 07:31

Oliver Drotbohm

80,157
18
225
211

Removing `root = ...` from the predicate did not help. I've edited my question to reflect the changes when I remove `root = ...` from the predicate. I'm actually trying to build a "find greatest id" query. I'm only interested in the greatest `id`. The `findOne(...)` in the `CrudRepository` failed (understandably) when there was more than 1 result filtered by the predicate. To be honest, I'm not entirely sure what the best way to do this is; I'm new to SpringData and still trying to learn how to use it properly. – Eric B. Mar 18 '14 at 16:10
1

Your code sample above still contains the invalid line. Is that by intention? I'll update my answer with a suggested workaround. – Oliver Drotbohm Mar 19 '14 at 09:07
Thanks; I'll try your suggestion. I had not edited the original code in my post; I'll fix that now. Is there a reason why the other method does not work? Is that a bug in Spring Data? – Eric B. Mar 19 '14 at 12:44
You just build an invalid SQL statement / Criteria query… :) – Oliver Drotbohm Mar 19 '14 at 12:47
:) Any idea what makes it an invalid criteria? – Eric B. Mar 19 '14 at 12:49

How to return a single result from Spring-Data-JPA?

2 Answers2