如何在JPA Criteria Builder中将varchar类型日期转为date格式排序

报错原因 #

你当前运行环境使用的是H2数据库，H2没有内置MySQL专属的STR_TO_DATE函数，因此抛出函数不存在的异常。以下是不同场景的解决方案：

方案1（最推荐）：修改字段存储类型 #

将column_value字段从varchar类型改为datetime/timestamp类型，Java实体类对应的属性同步改为LocalDateTime类型，排序时无需做任何函数转换，直接编写如下代码即可：

criteriaQuery.orderBy(criteriaBuilder.asc(root.get("columnValue")));

该方案兼容所有支持标准SQL时间类型的数据库，从根源解决varchar存日期带来的各种转换、排序问题。

方案2：自定义方言适配多数据库 #

如果必须保留varchar存日期的现状，可以通过自定义JPA方言做函数映射，实现一套代码同时适配H2测试环境和MySQL生产环境：

1. 自定义H2方言类，映射STR_TO_DATE到H2自带的PARSEDATETIME函数（二者功能完全一致）：

public class CustomH2Dialect extends H2Dialect {
    public CustomH2Dialect() {
        super();
        registerFunction("STR_TO_DATE", 
            new SQLFunctionTemplate(StandardBasicTypes.LOCAL_DATE_TIME, 
                "PARSEDATETIME(?1, ?2)"));
    }
}

2. 调整项目配置，测试环境指定使用自定义的H2方言，生产环境使用原生MySQL方言即可，原有业务代码无需修改。

方案3：动态切换函数名快速适配 #

如果只是临时在H2环境跑测试，不想修改方言配置，可以动态根据当前使用的数据库类型选择对应的函数：

// 从配置中读取当前数据库类型，以Spring Boot为例
String dbType = environment.getProperty("spring.datasource.platform");
String dateParseFunc = "mysql".equals(dbType) ? "STR_TO_DATE" : "PARSEDATETIME";
// 注意：格式串不需要手动加单引号，literal会自动处理参数转义，原代码多写了单引号会导致格式匹配失败
Path<String> columnValue = root.get("columnValue");
Expression<LocalDateTime> timeExpr = criteriaBuilder.function(dateParseFunc, LocalDateTime.class, 
    columnValue, criteriaBuilder.literal("%m/%d/%Y %r"));
criteriaQuery.orderBy(criteriaBuilder.asc(timeExpr));

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