Hibernate 6 自定义 Predicate 与 SQL 函数迁移指南

// HSQLDialect 注册自定义函数
public class HSQLIntBooleanDialect extends HSQLDialect {
    public HSQLIntBooleanDialect() {
        super();
        // 注册 'rlike' 函数，映射到 HSQL 的 regexp_substring
        registerFunction("rlike", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "regexp_substring (?1, ?2) "));
    }
}

// MySQLDialect 注册自定义函数
public class MySQL5IntBooleanDialect extends MySQL5Dialect {
    public MySQL5IntBooleanDialect() {
        super();
        // 注册 'rlike' 函数，映射到 MySQL 的 RLIKE
        registerFunction("rlike", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "(?1 RLIKE ?2)"));
    }
}

// Hibernate 5 RLikePredicate 的 render 方法片段
public class RLikePredicate extends AbstractSimplePredicate implements Serializable {
    // ... 构造器和成员变量 ...

    @Override
    public String render(boolean isNegated, RenderingContext renderingContext) {
        StringBuilder buffer = new StringBuilder();
        // 根据方言手动拼接 SQL
        if (renderingContext.getDialect() instanceof HSQLDialect) {
            final String operator = isNegated ? " IS NULL" : " IS NOT NULL";
            buffer.append("rlike (").append(((Renderable) getMatchExpression()).render(renderingContext))
                .append(",")
                .append(escapeSqlRegex(renderingContext, String::valueOf)).append(")").append(operator);
        } else if (renderingContext.getDialect() instanceof MySQLDialect) {
            final String operator = isNegated ? " != 1" : " = 1";
            buffer.append("rlike (").append(((Renderable) getMatchExpression()).render(renderingContext))
                .append(",")
                .append(escapeSqlRegex(renderingContext, String::valueOf)).append(")").append(operator);
        } else {
            throw new UnsupportedOperationException("Unsupported dialect.");
        }
        return buffer.toString();
    }
    // ... 其他方法 ...
}

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.boot.model.FunctionContributor;
import org.hibernate.dialect.HSQLDialect;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.query.sqm.function.SqmFunctionRegistry;
import org.hibernate.query.sqm.function.PatternBasedSqmFunctionDescriptor;
import org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.query.sqm.produce.function.StandardFunctionArgumentTypeResolvers;
import org.hibernate.query.sqm.tree.expression.SqmExpression;
import org.hibernate.query.sqm.produce.function.FunctionReturnTypeResolver;
import org.hibernate.type.Type;

import java.util.List;

public class MyCustomFunctionContributor implements FunctionContributor {

    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        // 获取类型配置
        var typeConfiguration = functionContributions.getTypeConfiguration();
        var basicTypeRegistry = typeConfiguration.getBasicTypeRegistry();

        // 注册一个名为 "rlike" 的函数，它返回一个布尔值
        // 对于简单的SQL模式，可以使用 registerPattern
        // functionContributions.getFunctionRegistry().registerPattern(
        //     "rlike",
        //     "?1 rlike ?2", // 适用于MySQL等直接支持 RLIKE 的数据库
        //     basicTypeRegistry.resolve(StandardBasicTypes.BOOLEAN)
        // );

        // 对于需要根据方言生成不同SQL的复杂情况，需要注册一个自定义的 SQLFunction
        functionContributions.getFunctionRegistry().register(
            "rlike",
            new AbstractSqmFunctionDescriptor("rlike", StandardFunctionArgumentTypeResolvers.ANY_TYPE, new FunctionReturnTypeResolver() {
                @Override
                public Type resolveFunctionReturnType(Type firstArgumentType, List<SqmExpression> arguments, SessionFactoryImplementor sessionFactory) {
                    return basicTypeRegistry.resolve(StandardBasicTypes.BOOLEAN); // 始终返回布尔类型
                }
            }) {
                @Override
                public String render(List<? extends SqmExpression> arguments, SqmFunctionRegistry functionRegistry, SessionFactoryImplementor sessionFactory) {
                    // 获取当前方言
                    var dialect = sessionFactory.getJdbcServices().getDialect();
                    String firstArg = functionRegistry.render(arguments.get(0), sessionFactory);
                    String secondArg = functionRegistry.render(arguments.get(1), sessionFactory);

                    if (dialect instanceof HSQLDialect) {
                        // HSQL 使用 regexp_substring 并检查是否为 NULL
                        return "regexp_substring (" + firstArg + ", " + secondArg + ") IS NOT NULL";
                    } else {
                        // MySQL 或其他数据库使用 RLIKE 并检查结果是否为 1 (或直接作为布尔)
                        return "(" + firstArg + " RLIKE " + secondArg + ") = 1";
                    }
                }
            }
        );
    }
}