如何通过MetadataBuilderContributor为Hibernate添加MATCH AGAINST支持

我的项目需要使用MySQL/MariaDB的全文搜索功能，但MATCH (col1,col2,...) AGAINST (expr [search_modifier])并非标准SQL函数。

我之前基于旧版Hibernate的实现方式如下（通过自定义Dialect实现）：

package com.ictcg.mariadbwithmatch;

import java.util.List;

import org.hibernate.QueryException;
import org.hibernate.dialect.MariaDB53Dialect;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.TemplateRenderer;
import org.hibernate.engine.spi.Mapping;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

public class MariaDBWithMatchDialect extends MariaDB53Dialect {

    public MariaDBWithMatchDialect() {
        super();
        registerFunction("match",
                //new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against  (?2 in boolean mode)")
                new MatchFunctionTemplate()
                );
    }

    public static class MatchFunctionTemplate implements SQLFunction {
        public MatchFunctionTemplate() {
        }

        @Override
        public String render(Type argumentType, @SuppressWarnings("rawtypes") List args, SessionFactoryImplementor factory) {
            String template="match(?1";
            int i=2;
            for(;i<args.size();i++)
                template+=",?"+i;
            template+=") against  (?"+i+" in boolean mode)";
            return new TemplateRenderer( template ).render(args, factory);
        }

        @Override
        public Type getReturnType(Type argumentType, Mapping mapping) throws QueryException {
            return StandardBasicTypes.DOUBLE;
        }

        @Override
        public boolean hasArguments() {
            return true;//renderer.getAnticipatedNumberOfArguments() > 0;
        }

        @Override
        public boolean hasParenthesesIfNoArguments() {
            return true;
        }

        @Override
        public String toString() {
            return "MATCH(col,col,... ) AGAINST ( ? in boolean mode)";
        }
    }
}

这个实现能正常工作，但依赖MariaDB驱动，不够理想。

Spring Boot 3.0.0升级了Hibernate，现在需要通过MetadataBuilderContributor来实现。我写了部分代码，求补全：

metadataBuilder.applySqlFunction("match", new SqmFunctionDescriptor() {
    
    @Override
    public ArgumentsValidator getArgumentsValidator() {
        // TODO Auto-generated method stub
        return null;
    }
    
    @Override
    public <T> SelfRenderingSqmFunction<T> generateSqmExpression(List<? extends SqmTypedNode<?>> arguments,
            ReturnableType<T> impliedResultType, QueryEngine queryEngine, TypeConfiguration typeConfiguration) {
        // TODO Auto-generated method stub
        new SelfRenderingSqmFunction()
        return null;
    }
});

以下是基于MetadataBuilderContributor的完整实现，替代原有的自定义Dialect方式，无需依赖特定数据库驱动：

1. 自定义MetadataBuilderContributor类 #

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.query.sqm.function.AbstractSqmFunctionDescriptor;
import org.hibernate.query.sqm.function.ArgumentsValidator;
import org.hibernate.query.sqm.function.FunctionKind;
import org.hibernate.query.sqm.function.SelfRenderingSqmFunction;
import org.hibernate.query.sqm.function.SqmFunctionDescriptor;
import org.hibernate.query.sqm.produce.function.StandardArgumentsValidators;
import org.hibernate.query.sqm.tree.SqmTypedNode;
import org.hibernate.query.sqm.tree.expression.SqmExpression;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.TypeConfiguration;

import java.util.List;

public class MatchFunctionContributor implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("match", new MatchSqmFunctionDescriptor());
    }

    private static class MatchSqmFunctionDescriptor extends AbstractSqmFunctionDescriptor {

        public MatchSqmFunctionDescriptor() {
            super(FunctionKind.NORMAL, StandardBasicTypes.DOUBLE);
        }

        @Override
        public ArgumentsValidator getArgumentsValidator() {
            // 至少需要2个参数：多个列 + 搜索表达式
            return StandardArgumentsValidators.min(2);
        }

        @Override
        public <T> SelfRenderingSqmFunction<T> generateSqmExpression(
                List<? extends SqmTypedNode<?>> arguments,
                ReturnableType<T> impliedResultType,
                QueryEngine queryEngine,
                TypeConfiguration typeConfiguration) {

            // 提取列参数和搜索表达式参数
            List<SqmExpression<?>> columnExpressions = arguments.stream()
                    .limit(arguments.size() - 1)
                    .map(SqmTypedNode::asExpression)
                    .toList();
            SqmExpression<?> searchExpression = arguments.get(arguments.size() - 1).asExpression();

            return new SelfRenderingSqmFunction<>(
                    "match",
                    impliedResultType,
                    arguments,
                    (sqlAppender, context, argumentsList) -> {
                        sqlAppender.append("MATCH(");
                        // 拼接所有列
                        for (int i = 0; i < columnExpressions.size(); i++) {
                            if (i > 0) {
                                sqlAppender.append(", ");
                            }
                            columnExpressions.get(i).appendSql(sqlAppender, context);
                        }
                        sqlAppender.append(") AGAINST (");
                        searchExpression.appendSql(sqlAppender, context);
                        sqlAppender.append(" IN BOOLEAN MODE)");
                    },
                    queryEngine.getTypeConfiguration(),
                    getArgumentsValidator()
            );
        }
    }
}

2. 配置Spring Boot加载该Contributor #

在application.properties中添加配置：

spring.jpa.properties.hibernate.metadata_builder_contributor=com.yourpackage.MatchFunctionContributor

或者通过Java配置类注册：

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class HibernateConfig {

    @Bean
    public MetadataBuilderContributor matchFunctionContributor() {
        return new MatchFunctionContributor();
    }
}

3. 使用方式 #

在JPQL中可以直接调用自定义的match函数，例如：

SELECT e FROM Entity e WHERE match(e.col1, e.col2, :searchTerm) > 0 
ORDER BY match(e.col1, e.col2, :searchTerm) DESC

说明 #

• 支持传入任意数量的列参数，最后一个参数固定为搜索表达式
• 自动生成MATCH(col1, col2) AGAINST (:searchTerm IN BOOLEAN MODE)格式的SQL
• 返回类型为DOUBLE，对应全文搜索的匹配得分
• 仅依赖Hibernate的SQM API，不绑定特定数据库驱动，适配Spring Boot 3.x的Hibernate版本

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