代码之家 › 专栏 › 技术社区 › thmmy95

主查询的构造函数中的多个子查询中断了Hibernate转换

hibernate java

thmmy95 · 技术社区 · 5 年前

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

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")

0 回复 | 直到 5 年前

SternK 5 年前

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

@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")

Christian Beikov 5 年前

@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-Persistence Entity Views .

我创建了这个库,以便在JPA模型和自定义接口或抽象类定义的模型之间进行简单的映射,类似于类固醇上的Spring数据投影。其思想是以您喜欢的方式定义目标结构(域模型),并通过JPQL表达式将属性(getter)映射到实体模型。

@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();
}

Statistics a = entityViewManager.find(entityManager, Statistics.class, id);

Spring数据集成允许您像使用Spring数据投影一样使用它: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

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