如何在JPQL中格式化LocalDateTime？不使用nativeQuery按日期聚合支付

你的代码无法运行，因为JPQL不支持直接调用Java的LocalDateTime.format()方法和DateTimeFormatter类——JPQL是面向JPA实体的查询语言，不能直接调用普通Java类的方法，必须使用JPA规范支持的函数、数据库原生函数（通过FUNCTION()包装）或者自定义函数。以下是几种无需使用nativeQuery的可行方案：

方案一：用FUNCTION()调用数据库原生日期格式化函数 #

通过JPQL的FUNCTION()函数，直接调用对应数据库的日期格式化方法，既保持JPQL的非原生特性，又能实现日期格式化。不同数据库的函数和格式符略有差异：

MySQL示例 #

@Query(value = "select new com.example.demoreconciliation.model.Test(" +
        "    FUNCTION('DATE_FORMAT', s.paymentDate, '%Y-%m-%d') as day, " +
        "    count(s.paymentAccountId), " +
        "    sum(s.amount) " +
        ") from AccountStatement s " +
        "GROUP BY day")
List<Test> test();

PostgreSQL/Oracle示例 #

@Query(value = "select new com.example.demoreconciliation.model.Test(" +
        "    FUNCTION('TO_CHAR', s.paymentDate, 'YYYY-MM-DD') as day, " +
        "    count(s.paymentAccountId), " +
        "    sum(s.amount) " +
        ") from AccountStatement s " +
        "GROUP BY day")
List<Test> test();

方案二：在实体类中用@Formula预定义格式化字段 #

在AccountStatement实体中添加一个用@Formula注解的派生字段，提前定义好日期格式化逻辑，后续JPQL查询直接使用该字段即可：

实体类代码（MySQL示例） #

@Entity
public class AccountStatement {
    // 其他字段...
    private LocalDateTime paymentDate;

    @Formula("DATE_FORMAT(payment_date, '%Y-%m-%d')")
    private String formattedPaymentDate;

    // getter/setter...
}

JPQL查询代码 #

@Query(value = "select new com.example.demoreconciliation.model.Test(" +
        "    s.formattedPaymentDate as day, " +
        "    count(s.paymentAccountId), " +
        "    sum(s.amount) " +
        ") from AccountStatement s " +
        "GROUP BY s.formattedPaymentDate")
List<Test> test();

方案三：自定义JPQL函数（跨数据库兼容） #

如果需要兼容多种数据库，可以自定义一个JPQL函数，将其映射到不同数据库的对应实现。以Hibernate为例：

1. 注册自定义函数（在application.yml中配置）：

spring:
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        query:
          substitutions: FORMAT_DATE=DATE_FORMAT(?1, ?2)

（其他数据库需要替换对应的函数映射，比如PostgreSQL替换为FORMAT_DATE=TO_CHAR(?1, ?2)）

2. 使用自定义函数的JPQL代码：

@Query(value = "select new com.example.demoreconciliation.model.Test(" +
        "    FORMAT_DATE(s.paymentDate, 'yyyy-MM-dd') as day, " +
        "    count(s.paymentAccountId), " +
        "    sum(s.amount) " +
        ") from AccountStatement s " +
        "GROUP BY day")
List<Test> test();

内容的提问来源于stack exchange，提问作者user9304291