如何在Hibernate 6中创建自定义PostgreSQL cast interval函数

迁移至Spring Boot 3.0后，原自定义SQL方言中基于SQLFunction实现的castInterval函数因该类被移除失效，以下是用SqmFunctionDescriptor替代的实现方案：

完整实现代码 #

import org.hibernate.metamodel.mapping.JdbcMapping
import org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor
import org.hibernate.query.sqm.function.FunctionKind
import org.hibernate.query.sqm.function.SqmFunctionDescriptor
import org.hibernate.query.sqm.produce.function.StandardArgumentsValidator
import org.hibernate.query.sqm.produce.function.StandardFunctionReturnTypeResolver
import org.hibernate.query.sqm.produce.function.StandardFunctionReturnTypeResolvers
import org.hibernate.query.sqm.tree.SqmTypedNode
import org.hibernate.query.sqm.tree.expression.SqmExpression
import org.hibernate.type.BasicType
import org.hibernate.type.StandardBasicTypes
import org.hibernate.type.spi.TypeConfiguration
import org.hibernate.boot.MetadataBuilder
import org.hibernate.boot.spi.MetadataBuilderContributor
import org.hibernate.sql.ast.SqlAstTranslator
import org.hibernate.sql.ast.spi.SqlAppender
import org.hibernate.sql.ast.spi.SqmToSqlAstConverter
import org.hibernate.sql.ast.tree.expression.SelfRenderingFunctionSqlAstExpression

class CustomMetadataBuilderContributor : MetadataBuilderContributor {

    override fun contribute(metadataBuilder: MetadataBuilder) {
        // 注册time_bucket函数
        metadataBuilder.applySqlFunction(
            "time_bucket", StandardSQLFunction("time_bucket", StandardBasicTypes.OFFSET_DATE_TIME)
        )
        // 注册castInterval函数
        metadataBuilder.applySqlFunction(
            "castInterval",
            CastIntervalFunctionDescriptor()
        )
    }

    class CastIntervalFunctionDescriptor : AbstractSqmFunctionDescriptor(
        FunctionKind.NORMAL,
        StandardArgumentsValidator(1), // 要求必须传入1个参数
        StandardFunctionReturnTypeResolvers.useArgType(0) // 返回类型与第一个参数类型一致
    ) {
        override fun generateSqmExpression(
            arguments: List<SqmTypedNode<*>>,
            context: SqmFunctionDescriptor.ArgumentsValidatorContext,
            typeConfiguration: TypeConfiguration
        ): SqmExpression<*> {
            return object : AbstractSqmSelfRenderingFunctionExpression<Any>(
                "castInterval",
                arguments,
                resolveReturnType(null, arguments.map { it.nodeType }, typeConfiguration),
                typeConfiguration
            ) {
                override fun render(
                    sqlAppender: SqlAppender,
                    context: SqmToSqlAstConverter,
                    topLevelPredicate: Boolean
                ) {
                    // 生成目标SQL：cast(参数 as interval)
                    sqlAppender.append("cast(")
                    (arguments[0] as SqmExpression<*>).render(sqlAppender, context, topLevelPredicate)
                    sqlAppender.append(" as interval)")
                }
            }
        }
    }
}

关键逻辑对应说明 #

• 参数校验：StandardArgumentsValidator(1)确保函数仅接收1个参数，对应原SQLFunction的hasArguments()逻辑。
• 返回类型：StandardFunctionReturnTypeResolvers.useArgType(0)让返回类型与传入参数类型保持一致，对应原getReturnType()的逻辑。
• SQL生成：在render方法中直接拼接出cast(参数 as interval)的SQL语句，和原SQLFunction的render()逻辑完全匹配。

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