Spring Boot 3中自定义PostgreSQL Dialect注册函数的替代实现方案

我们原本通过继承PostgreSQL Dialect并注册函数的方式实现了自定义PostgreSQL方言，但该方式在Spring Boot 3中不再受支持。以下是几种可行的替代实现方式：

原实现代码参考：

public class GlobalPostgresDialect extends PostgreSQL10Dialect {

    public static final String STRING_AGG = "string_agg";
    public static final String STRING_AGG_ORDER_BY = "string_agg_order_by";
    public static final String STRING_AGG_DISTINCT = "string_agg_distinct";
    public static final String STRING_AGG_DISTINCT_ORDER_BY = "string_agg_distinct_order_by";
    public static final String ARRAY_AGG = "array_agg";
    public static final String ARRAY_AGG_DISTINCT = "array_agg_distinct";
    public static final String ARRAY_AGG_ORDER_BY = "array_agg_order_by";
    public static final String ARRAY_AGG_DISTINCT_ORDER_BY = "array_agg_distinct_order_by";
    public static final String COUNT_DISTINCT_5_ARGS = "count_distinct_5_args";

    public GlobalPostgresDialect() {
        super();
        registerFunction(STRING_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2)"));
        registerFunction(STRING_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2 order by ?3)"));
        registerFunction(STRING_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2)"));
        registerFunction(STRING_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2 order by ?3)"));
        registerFunction(ARRAY_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1)"));
        registerFunction(ARRAY_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(distinct ?1)"));
        registerFunction(ARRAY_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1 order by ?2)"));
        registerFunction(ARRAY_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1, ?2 order by ?2)"));
        registerFunction(COUNT_DISTINCT_5_ARGS, new SQLFunctionTemplate(LongType.INSTANCE, "count(distinct(?1, ?2, ?3, ?4, ?5))"));
    }
}

替代方案1：使用Hibernate 6的DialectContributor接口 #

Hibernate 6推荐通过DialectContributor扩展方言功能，替代原有的继承Dialect方式：

1. 实现DialectContributor接口，在contribute方法中注册自定义函数：

import org.hibernate.dialect.PostgreSQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.LongType;
import org.hibernate.dialect.DialectContributor;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.service.ServiceRegistry;

public class GlobalPostgresDialectContributor implements DialectContributor {

    public static final String STRING_AGG = "string_agg";
    public static final String STRING_AGG_ORDER_BY = "string_agg_order_by";
    public static final String STRING_AGG_DISTINCT = "string_agg_distinct";
    public static final String STRING_AGG_DISTINCT_ORDER_BY = "string_agg_distinct_order_by";
    public static final String ARRAY_AGG = "array_agg";
    public static final String ARRAY_AGG_DISTINCT = "array_agg_distinct";
    public static final String ARRAY_AGG_ORDER_BY = "array_agg_order_by";
    public static final String ARRAY_AGG_DISTINCT_ORDER_BY = "array_agg_distinct_order_by";
    public static final String COUNT_DISTINCT_5_ARGS = "count_distinct_5_args";

    @Override
    public void contribute(Dialect dialect, SessionFactoryImplementor sessionFactory, ServiceRegistry serviceRegistry) {
        if (dialect instanceof PostgreSQLDialect) {
            PostgreSQLDialect postgresDialect = (PostgreSQLDialect) dialect;
            postgresDialect.registerFunction(STRING_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2)"));
            postgresDialect.registerFunction(STRING_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2 order by ?3)"));
            postgresDialect.registerFunction(STRING_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2)"));
            postgresDialect.registerFunction(STRING_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2 order by ?3)"));
            postgresDialect.registerFunction(ARRAY_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1)"));
            postgresDialect.registerFunction(ARRAY_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(distinct ?1)"));
            postgresDialect.registerFunction(ARRAY_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1 order by ?2)"));
            postgresDialect.registerFunction(ARRAY_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(distinct ?1 order by ?2)"));
            postgresDialect.registerFunction(COUNT_DISTINCT_5_ARGS, new SQLFunctionTemplate(LongType.INSTANCE, "count(distinct(?1, ?2, ?3, ?4, ?5))"));
        }
    }
}

2. 通过SPI注册该贡献者：在src/main/resources/META-INF/services下创建文件org.hibernate.dialect.DialectContributor，内容为你的贡献者全类名，例如：

com.yourpackage.GlobalPostgresDialectContributor

替代方案2：使用MetadataBuilderContributor注册函数 #

通过实现MetadataBuilderContributor，在Hibernate构建元数据时注册自定义函数：

1. 实现MetadataBuilderContributor接口：

import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.LongType;

public class CustomFunctionContributor implements MetadataBuilderContributor {

    public static final String STRING_AGG = "string_agg";
    public static final String STRING_AGG_ORDER_BY = "string_agg_order_by";
    public static final String STRING_AGG_DISTINCT = "string_agg_distinct";
    public static final String STRING_AGG_DISTINCT_ORDER_BY = "string_agg_distinct_order_by";
    public static final String ARRAY_AGG = "array_agg";
    public static final String ARRAY_AGG_DISTINCT = "array_agg_distinct";
    public static final String ARRAY_AGG_ORDER_BY = "array_agg_order_by";
    public static final String ARRAY_AGG_DISTINCT_ORDER_BY = "array_agg_distinct_order_by";
    public static final String COUNT_DISTINCT_5_ARGS = "count_distinct_5_args";

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(STRING_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2)"));
        metadataBuilder.applySqlFunction(STRING_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2 order by ?3)"));
        metadataBuilder.applySqlFunction(STRING_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2)"));
        metadataBuilder.applySqlFunction(STRING_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2 order by ?3)"));
        metadataBuilder.applySqlFunction(ARRAY_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1)"));
        metadataBuilder.applySqlFunction(ARRAY_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(distinct ?1)"));
        metadataBuilder.applySqlFunction(ARRAY_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(?1 order by ?2)"));
        metadataBuilder.applySqlFunction(ARRAY_AGG_DISTINCT_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "array_agg(distinct ?1 order by ?2)"));
        metadataBuilder.applySqlFunction(COUNT_DISTINCT_5_ARGS, new SQLFunctionTemplate(LongType.INSTANCE, "count(distinct(?1, ?2, ?3, ?4, ?5))"));
    }
}

2. 在application.properties或application.yml中配置该贡献者：

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

替代方案3：直接使用原生SQL查询 #

如果自定义函数仅用于特定查询场景，可以直接在@NamedNativeQuery或Repository方法中编写原生SQL，无需全局注册函数：

例如在实体类上定义原生查询：

import jakarta.persistence.Entity;
import jakarta.persistence.NamedNativeQuery;

@Entity
@NamedNativeQuery(
    name = "Entity.stringAgg",
    query = "select string_agg(distinct column1, ',') from your_table where id = ?1",
    resultClass = String.class
)
public class YourEntity {
    // 实体字段
}

然后在Repository中调用：

import org.springframework.data.jpa.repository.JpaRepository;

public interface YourEntityRepository extends JpaRepository<YourEntity, Long> {
    String stringAgg(Long id);
}

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