我试图从H2 db表中获取一些值.
执行我需要的查询是：

SELECT cast(creationDate as date) as DATE, SUM(paymentValue) as TOTAL,fxRate 
FROM payment 
group by DATE

其中“creationDate”,“paymentValue”,“fxRate”是表“付款”的列.
CreationDate是一个时间戳,所以我只能从中获取日期.
当我尝试用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属性

@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查询从现有实体获取值.
谢谢你们

解决方法:

您的查询返回一个Object []数组而不是GraphDto Object数组,您有多种方法可以解决这个问题：

解决方案1

创建一个包含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
}

那么你的查询应该是这样的：

SELECT NEW com.packagename.GraphDto(cast(creationDate AS date), SUM(paymentValue), fxRate)
FROM payment
GROUP BY cast(creationDate AS date)

解决方案2

将返回类型更改为：

List<Object[]> findPaymentValuePerDay ();

然后在您的服务循环中对此对象进行提取并提取值：

List<Object[]> listObject = rep.findPaymentValuePerDay();
for(Object[] obj : listObject){
   Date date = (Date) obj[0];
   Long value1 = (Long) obj[1];
   Long value2 = (Long) obj[2];
}