title: How to return a single result from Spring-Data-JPA?
body: <p>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.</p>
  
  <p>So far, based on <a href="https://stackoverflow.com/a/9315989/827480">this related SO post</a>, I've come to the conclusion that I need to use a <code>Specification</code> to define my query and <code>Page</code>d results, specifying the number of results I want to retrieve. Unfortunately, I'm getting a <code>HibernateJdbcException</code> data access exception.</p>
  
  <p>My <code>Specification</code>/<code>Predicate</code> is supposed to be fairly simple and reflect: <code>from User order by id</code>:</p>
  
  <pre><code>Page&lt;User&gt; result =userRepository.findAll(new Specification&lt;User&gt;() {
      @Override
      public Predicate toPredicate(Root&lt;User&gt; root, CriteriaQuery&lt;?&gt; 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);
  </code></pre>
  
  <p>Exception Thrown:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm a little lost by the Hibernate error - it's asking for a <code>group</code> 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.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>As suggested by @OliverGierke, I tried to remove the <code>root = query.from(User.class)</code> but the hibernate still throws the same error (I enabled full hibernate query tracing).  Strangely, however, this time, there is no <code>GROUP BY</code> in the generated SQL so I'm even more confused than before.</p>
  
  <pre><code>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]
  </code></pre>