如何在 JPQL 中使用别名 [英] How to work with alias in JPQL
问题描述
我正在尝试从 H2 db 表中获取一些值.执行我需要的查询是这样的:
I'm trying to get some values from a H2 db table. The query which does what I need is this:
SELECT cast(creationDate as date) as DATE, SUM(paymentValue) as TOTAL,fxRate
FROM payment
group by DATE
其中creationDate"、paymentValue"、fxRate"是payment"表的列.CreationDate 是一个时间戳,所以我只需要从中获取日期.当我尝试用 Java 编写时
where "creationDate", "paymentValue", "fxRate" are columns of the table "payment". CreationDate is a timestamp so I have to get only the date from it. When I try to write it in Java
@Query("SELECT cast(creationDate as date) as daydate , SUM(paymentValue) as value1, fxRate as value2 FROM payment " +
"group by cast(creationDate as date)")
List<Payment> findPaymentValuePerDay ();
我收到错误 [Ljava.lang.Object;无法转换为 ...entity.Payment.
我还尝试使用一个名为 GraphDto 的不同对象,它具有属性 daydate、value1 和 value2
I also tried to use a different object called GraphDto which has as attributes daydate, value1 and value2
@Query("SELECT cast(creationDate as date) as daydate , SUM(paymentValue) as value1, fxRate as value2 FROM payment " +
"group by cast(creationDate as date)")
List<GraphDto> findPaymentValuePerDay ();
但我得到了同样的错误.
but I get the same error.
[Ljava.lang.Object; cannot be cast to ...entity.GraphDto.
那么,我如何在 JPQL 中使用别名??我只需要一个函数,它返回 3 个不同列的名称,其中的值使用正确的 H2 查询从现有实体中获取.谢谢大家
so, how can I work with alias in JPQL?? I just need a function that returns 3 different columns' name with values took from an existing entity using the right H2 query. Thank you all
推荐答案
您的查询返回一个 Object[]
数组而不是 GraphDto
对象,您有多种方法可以解决这个问题:
Your query return an array of Object[]
and not GraphDto
Object, you have multiple ways to solve this problems :
创建一个包含daydate
、value1
、value2
@Entity
public class GraphDto{
private Date daydate;
private Long value1;
private Long value2;
public GraphDto(Date daydate, Long value1, Long value2){
//...
}
//..getters and setters
}
那么您的查询应如下所示:
then your query should look like this :
SELECT NEW com.packagename.GraphDto(cast(creationDate AS date), SUM(paymentValue), fxRate)
FROM payment
GROUP BY cast(creationDate AS date)
解决方案 2
将返回类型更改为:
Solution 2
change the return type to :
List<Object[]> findPaymentValuePerDay ();
然后在您的服务中循环遍历此对象并提取值:
Then in your service loop over this object and extract the values :
List<Object[]> listObject = rep.findPaymentValuePerDay();
for(Object[] obj : listObject){
Date date = (Date) obj[0];
Long value1 = (Long) obj[1];
Long value2 = (Long) obj[2];
}
这篇关于如何在 JPQL 中使用别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!