Java 如何从 Spring-Data-JPA 返回单个结果？

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

提问by Eric B.

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.

我正在尝试从 Spring Data 查询中获取单个结果。我希望从用户表中返回最大的 ID。我希望它会很简单，但我有点迷茫。

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

到目前为止，基于这个相关的 SO post，我得出的结论是我需要使用 aSpecification来定义我的查询和Paged 结果，指定我想要检索的结果数。不幸的是，我遇到了HibernateJdbcException数据访问异常。

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

我的Specification/Predicate应该相当简单并反映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.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 groupclause. 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.

我对 Hibernate 错误有点迷茫 - 它要求一个group子句。我认为这与我创建 Predicate 的方式有关，但我不确定如何创建这样的简单谓词。

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 BYin the generated SQL so I'm even more confused than before.

正如@OliverGierke 所建议的那样，我尝试删除 ，root = query.from(User.class)但休眠仍然引发相同的错误（我启用了完整的休眠查询跟踪）。然而奇怪的是，这一次，GROUP BY生成的SQL中没有，所以我比以前更困惑。

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]

采纳答案by Oliver Drotbohm

You're not using the rootgetting handed into the Specificationinstance to invoke the .get(…)method on. That fails the instance to register idbeing used and thus transparently adding it to the result set.

您没有使用root传递到Specification实例来调用.get(…)方法。这使实例无法注册id正在使用，从而透明地将其添加到结果集。

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

简单地删除root = query.from(User.class);应该可以解决问题。

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 CrudRepositoryyou can use.

不过让我感到困惑的是，您提到您打算构建一个“按 id 查找”查询。您实际构建的是“查找所有按 id 排序的内容”。如果确实是您想要获得的前者，则可以使用预定义的findOne(…)方法CrudRepository。

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 PagingAndSortingRepositoryand then use client code like this:

鉴于下面的评论，您实际上想要实现的是找到具有最小 id 的单个用户。这也可以通过简单地扩展PagingAndSortingRepository然后使用这样的客户端代码来实现：

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.

这会将结果限制为第一页，页面大小为 1，按 id 升序排列。

回答by Bart

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. 将以下内容添加到您的存储库界面。

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