在HQL/JPQL中比较LocalDate与LocalDateTime [英] Compare LocalDate with LocalDateTime in HQL/JPQL

问题描述

是否可以在HQL/JPQL中将 LocalDate 与 LocalDateTime 进行比较?这是我在春季数据中查询存储库方法的信息，该数据应按要求的日期返回订单量.订单"实体中的字段 deliveryDateTime 的类型为 LocalDateTime .我想忽略时间部分，只比较日期部分.

Is it possible to compare LocalDate with LocalDateTime in HQL/JPQL? This is my query of repository method in spring data which should return amount of orders per required dates. The field deliveryDateTime in Order entity has type LocalDateTime. I want to ignore time part and compare only date part.

@Query("SELECT new OrderCountPerDate(DATE(o.deliveryDateTime), COUNT(o.id)) FROM Order o WHERE DATE(o.deliveryDateTime) IN :dates GROUP BY DATE(o.deliveryDateTime) ")
List<LocalDate> findValidOrderDates(List<LocalDate> dates);

我当前的代码给我这个错误:

My current code gives me this error:

    java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
     \-[METHOD_CALL] MethodNode: '('
        +-[METHOD_NAME] IdentNode: 'DATE' {originalText=DATE}

似乎DATE方法不存在.那么，还有其他方法吗?

It looks like DATE method doesn't exist. So, is there other way how to do it?

推荐答案

因此，您应该通过以下方式创建自定义方言:

So, you should create a custom dialect in the following way:

import org.hibernate.dialect.PostgreSQL10Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.LocalDateType;
import org.hibernate.type.StandardBasicTypes;

public class MyDialect extends PostgreSQL10Dialect
{

   public MyDialect()
   {
      registerFunction( "my_date", new StandardSQLFunction( "date", LocalDateType.INSTANCE ) );
   }
}

在您的 application.properties 中设置此方言:

spring.jpa.properties.hibernate.dialect=com.me.MyDialect

然后您可以在HQL/JPQL查询中使用 my_date 函数:

And then you can use the my_date function in your HQL/JPQL queries:

@Query("select new com.me.OrderCountPerDate(my_date(o.deliveryDateTime), count(o.id)) from Order o where my_date(o.deliveryDateTime) in :dates group by my_date(o.deliveryDateTime) ")
List<OrderCountPerDate> findValidOrderDates(List<LocalDate> dates);

这篇关于在HQL/JPQL中比较LocalDate与LocalDateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！