构造函数中的多个子查询从主查询中断hibernate转换

kgsdhlau 于 2021-06-30 发布在 Java

关注(0)|答案(2)|浏览(282)

我有以下简单的类来Map从查询中提取的数据。

public class Statistics {
    private double maxPrice;
    private double minPrice;
    private double actualPrice;
    private double startPrice;
}

它还有一个构造函数。

public Statistics(double maxPrice, double minPrice, double actualPrice, double startPrice) {
        this.maxPrice = maxPrice;
        this.minPrice = minPrice;
        this.actualPrice = actualPrice;
        this.startPrice = startPrice;
    }

这个查询看起来很难看，但应该可以工作。

@Query(value = "select new Statistics(max(price.value), min(price.value), " +
    "                 (select price.value as startPrice " +
    "                  from Price price " +
    "                  where price.date = (select min(date) from Price where price.item.id = :item_id )" +
    "                        and price.item.id = :item_id" +
    "                 ) as min_price," +
    "                 (select price.value as endPrice " +
    "                  from Price price " +
    "                  where price.date = (select max(date) from Price where price.item.id = :item_id )" +
    "                        and price.item.id = :item_id" +
    "                 ) as max_price " +
    "               ) " +
    " from Price price" +
    " where price.item.id = :item_id")

hibernate生成以下异常跟踪：

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not instantiate class [Statistics] from tuple
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1514)
    at org.hibernate.query.Query.getResultList(Query.java:132)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:402)
    at com.sun.proxy.$Proxy222.getResultList(Unknown Source)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    ... 157 common frames omitted
Caused by: org.hibernate.QueryException: could not instantiate class [Statistics] from tuple
    at org.hibernate.transform.AliasToBeanConstructorResultTransformer.transformTuple(AliasToBeanConstructorResultTransformer.java:41)
    at org.hibernate.hql.internal.HolderInstantiator.instantiate(HolderInstantiator.java:85)
    at org.hibernate.loader.hql.QueryLoader.getResultList(QueryLoader.java:472)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
    at org.hibernate.loader.Loader.list(Loader.java:2501)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
    ... 178 common frames omitted

除了它不起作用之外，我还说了一个有趣的事实。如果我更改查询以返回与first date和latest date相同的值（我在两个子查询中都更改为调用min函数），它就可以工作了。

@Query(value = "select new Statistics(max(price.value), min(price.value), " +
        "                 (select price.value as startPrice " +
        "                  from Price price " +
        "                  where price.date = (select min(date) from Price where price.item.id = :item_id )" +
        "                        and price.item.id = :item_id" +
        "                 ) as min_price," +
        "                 (select price.value as endPrice " +
        "                  from Price price " +
        "                  where price.date = (select min(date) from Price where price.item.id = :item_id )" +
        "                        and price.item.id = :item_id" +
        "                 ) as max_price " +
        "               ) " +
        " from Price price" +
        " where price.item.id = :item_id")

需要提到的是，对于db中的测试，我有一个条目，它将为两个子查询返回相同的结果。

Java Hibernate

来源：https://stackoverflow.com/questions/65380775/multiple-subqueries-in-constructor-from-main-query-breaks-hibernate-conversion

2条答案

按热度按时间

pinkon5k1#

我认为不可能在构造函数参数中使用别名。请尝试以下操作：

@Query(value = "select new Statistics(max(price.value), min(price.value), " +
"     (select distinct price.value as startPrice " +
"      from Price price " +
"      where price.date = (select min(date) from Price where price.item.id = :item_id )" +
"      and price.item.id = :item_id" +
"      )," +
"      (select distinct price.value as endPrice " +
"       from Price price " +
"       where price.date = (select max(date) from Price where price.item.id = :item_id )" +
"       and price.item.id = :item_id" +
"      ) " +
" ) " +
" from Price price" +
" where price.item.id = :item_id")

除此之外，我认为这是blaze持久性实体视图的完美用例。
我创建了这个库，以便在jpa模型和自定义接口或抽象类定义的模型之间进行简单的Map，比如spring数据在steroids上的投影。其思想是以您喜欢的方式定义目标结构（域模型），并通过jpql表达式将属性（getter）Map到实体模型。
对于blaze持久性实体视图，用例的dto模型可以如下所示：

@EntityView(Price.class)
public interface Statistics {
    @Mapping("MAX(value)")
    double getMaxPrice();
    @Mapping("MIN(value)")
    double getMinPrice();
    @Limit(limit = "1", order = "date asc")
    @MappingCorrelatedSimple(
      correlated = Price.class,
      correlationBasis = "this",
      correlationExpression = "item.id = EMBEDDING_VIEW(item.id)",
      correlationResult = "value"
    )
    double getStartPrice();
    @Limit(limit = "1", order = "date desc")
    @MappingCorrelatedSimple(
      correlated = Price.class,
      correlationBasis = "this",
      correlationExpression = "item.id = EMBEDDING_VIEW(item.id)",
      correlationResult = "value"
    )
    double getLastPrice();
}

查询是将实体视图应用于查询的问题，最简单的就是按id进行查询。 Statistics a = entityViewManager.find(entityManager, Statistics.class, id); spring数据集成允许您像spring数据投影一样使用它：https://persistence.blazebit.com/documentation/entity-view/manual/en_us/index.html#spring-数据特征

Statistics findByItemId(int itemId);

如果dbms允许，这将使用横向连接，这通常比执行两个嵌套子查询更有效。生成的sql查询大致如下所示：

select max(p.value), min(p.value), p1.value, p2.value
from price p
left join lateral (select * from price p0 where p0.item_id = p.item_id order by p0.date asc limit 1) p1 on 1=1
left join lateral (select * from price p0 where p0.item_id = p.item_id order by p0.date desc limit 1) p2 on 1=1
where p.item_id = ?
group by p1.value, p2.value

赞(0）回复(0）举报 2021-06-30

czq61nw12#

请尝试按以下方式更正您的查询：

@Query(value = "select new Statistics(max(price.value), min(price.value), " +
    "     (select distinct price.value as startPrice " +
    "      from Price price " +
    "      where price.date = (select min(date) from Price where price.item.id = :item_id )" +
    "      and price.item.id = :item_id" +
    "      ) as min_price," +
    "      (select distinct price.value as endPrice " +
    "       from Price price " +
    "       where price.date = (select max(date) from Price where price.item.id = :item_id )" +
    "       and price.item.id = :item_id" +
    "      ) as max_price " +
    " ) " +
    " from Price price" +
    " where price.item.id = :item_id")

由于子查询返回了重复的行，您似乎遇到了此错误。

赞(0）回复(0）举报 2021-06-30

我来回答

构造函数中的多个子查询从主查询中断hibernate转换

2条答案

相关问题

热门标签

最新问答