如何在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 ();

但我得到同样的错误。

 [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

Create a constructor which hold 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屋！