Spring Boot 3.2.0升级后自定义to_timestamp SQL函数无法识别求助

问题背景 #

将Spring Boot应用从2.3.8（Java 11）升级至3.2.0（Java 17）后，基于PostgreSQL的JPA动态时间范围查询功能失效，报错：

ERROR: function to_timestamp(text) does not exist

原功能通过自定义MetadataBuilderContributor注册to_timestamp函数实现，升级后尝试用NamedSqmFunctionDescriptor重构未解决问题，需适配Spring Boot 3.2.0对应Hibernate版本的方案。

相关代码与错误信息 #

1. JPA查询语句 #

select c from Customer c where to_timestamp(c.created_date) BETWEEN to_timestamp(cast(:fromtime as text)) AND to_timestamp(cast(:toTime as text))

2. 旧版本（Spring Boot 2.3.8）配置代码 #

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;

@Component 
public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override 
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("to_timestamp", 
            new SQLFunctionTemplate(StandardBasicTypes.TIMESTAMP, 
                "to_timestamp(to_char(?1 :: timestamptz AT TIME ZONE 'UTC' ,'HH24:MI'),'HH24:MI')::time"));
    }
}

3. 升级后尝试的代码 #

@Component 
public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override 
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("to_timestamp", ToTimestampFunction.INSTANCE);
    }

    public static class ToTimestampFunction extends NamedSqmFunctionDescriptor {
        public static final ToTimestampFunction INSTANCE = new ToTimestampFunction();

        public ToTimestampFunction() {
            super("to_timestamp", false, StandardArgumentsValidators.exactly(1), null);
        }

        public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, SqlAstTranslator<?> walker) {
            Expression time = (Expression) arguments.get(0);
            sqlAppender.appendSql("to_timestamp(");
            walker.render(time, SqlAstNodeRenderingMode.DEFAULT);
            sqlAppender.appendSql(" ::text,'HH24:MI')::time");
        }
    }
}

适配方案 #

方案1：使用SqmFunctionTemplate快速迁移 #

Hibernate 6.x中SQLFunctionTemplate已过时，替换为SqmFunctionTemplate即可兼容原有逻辑：

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.query.sqm.function.SqmFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;

@Component
public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        // 复用旧版本的SQL模板逻辑，注册自定义to_timestamp函数
        metadataBuilder.applySqlFunction(
            "to_timestamp",
            SqmFunctionTemplate.from(
                "to_timestamp(to_char(?1 :: timestamptz AT TIME ZONE 'UTC' ,'HH24:MI'),'HH24:MI')::time",
                StandardBasicTypes.TIME
            )
        );
    }
}

方案2：自定义SqmFunctionDescriptor实现更灵活逻辑 #

如果需要更精细的SQL渲染控制，可继承AbstractSqmFunctionDescriptor实现：

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor;
import org.hibernate.query.sqm.function.FunctionRenderingContext;
import org.hibernate.query.sqm.produce.function.StandardArgumentsValidators;
import org.hibernate.sql.ast.SqlAstNode;
import org.hibernate.sql.ast.spi.SqlAppender;
import org.hibernate.sql.ast.tree.expression.Expression;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("to_timestamp", new CustomToTimestampFunction());
    }

    public static class CustomToTimestampFunction extends AbstractSqmFunctionDescriptor {
        public CustomToTimestampFunction() {
            super(
                "to_timestamp",
                StandardArgumentsValidators.exactly(1),
                null,
                StandardBasicTypes.TIME,
                null
            );
        }

        @Override
        public void render(
            SqlAppender sqlAppender,
            List<? extends SqlAstNode> arguments,
            FunctionRenderingContext context
        ) {
            Expression timeExpr = (Expression) arguments.get(0);
            
            sqlAppender.appendSql("to_timestamp(to_char(");
            context.getTranslator().render(timeExpr);
            sqlAppender.appendSql(" :: timestamptz AT TIME ZONE 'UTC' ,'HH24:MI'),'HH24:MI')::time");
        }
    }
}

关键说明 #

• 报错根源是升级后自定义函数未正确注册，Hibernate直接调用PostgreSQL原生to_timestamp函数，但原生函数需要格式参数，因此触发函数不存在的错误。
• 两种方案均确保生成的SQL与旧版本一致，完美兼容原有业务逻辑，同时适配Hibernate 6.x的API规范。

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