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

java jpa spring-data spring-data-jpa

2022-09-03 13:03:55

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

到目前为止，基于这篇相关的SO帖子，我得出的结论是，我需要使用a来定义我的查询和d结果，指定要检索的结果数量。不幸的是，我遇到了数据访问异常。SpecificationPageHibernateJdbcException

我的 / 应该相当简单，并反映：：SpecificationPredicatefrom 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);

抛出异常：

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

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

编辑

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

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]

答案 1

我不确定你为什么要获取一个集合来获得一个结果。如果我错了，请纠正我，但是正如我所解释的那样，您的问题的解决方案很容易使用.将以下内容添加到存储库界面。@Query

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

答案 2

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

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

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

鉴于下面的评论，似乎您实际上试图实现的是找到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);

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