如何在 Spring Boot 应用程序中手动注册非标准化 SQL 函数？

Question

我在当前的 spring-boot 项目中使用 JPA 查询。如何添加GROUP_CONCAT等非标准化 SQL 函数？

我发现 GROUP_CONCAT 不是 JPA 查询中的注册函数，但可以通过手动注册来访问。我已经尝试过以下链接但对我不起作用：

如何在 Spring Boot 应用程序中添加非标准化的 sql 函数？

使用 JPA 和 Hibernate 注册 SQL 函数

https://thoughts-on-java.org/database-functions/

https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/

1.

public class SqlFunctionsMetadataBuilderContributor

        implements MetadataBuilderContributor {

    @Override

    public void contribute(MetadataBuilder metadataBuilder) {

        metadataBuilder.applySqlFunction(

                "group_concat",

                new StandardSQLFunction(

                        "group_concat",

                        StandardBasicTypes.STRING

                )

        );

    }

}

2.

 public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory)

            throws QueryException {

        if (arguments.size() < 1) {

            throw new QueryException(new IllegalArgumentException("group_concat should have at least one arg"));

        }

        StringBuilder builder = new StringBuilder();

        if (arguments.size() > 1 && arguments.get(0).equals("'distinct'")) {

            builder.append("distinct ");

            builder.append(arguments.get(1));

        } else {

            builder.append(arguments.get(0));

        }

        return "group_concat(" + builder.toString() + ")";

    }

3.

@Configuration

public class DataSource {

    @Bean

    public JpaVendorAdapter jpaVendorAdapter() {

        AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();

        adapter.setShowSql(true);

        adapter.setDatabase(Database.MYSQL);

        // package to CustomMysqlDialect

        adapter.setDatabasePlatform("com.myprojet.admin.configuration.RegisterSqlFunction");

        adapter.setGenerateDdl(false);

        return adapter;

    }

}

我除了将 group_concat 与 JPA 查询一起使用。

Answer 1

您可以在我的高性能 Java 持久性 GitHub 存储库中找到一个功能齐全的示例。在您的情况下，您不需要自定义JpaPlatform. 那应该设置为HibernateJpaPlatform.您可以MetadataBuilderContributer通过配置文件以编程方式注册application.properties：hibernate.metadata_builder_contributor=com.vladmihalcea.book.hpjp.SqlFunctionsMetadataBuilderContributor

Answer 2

创建一个类，在重写的方法中添加需要用到的 mySql 函数：public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor{&nbsp;@Override&nbsp; &nbsp; public void contribute(MetadataBuilder metadataBuilder) {&nbsp; &nbsp; &nbsp; &nbsp; metadataBuilder.applySqlFunction(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "group_concat",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new StandardSQLFunction(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "group_concat",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StandardBasicTypes.STRING&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; );&nbsp; &nbsp; }}之后，通过 application.properties 提供您的 metadata_builder_contributor：spring.jpa.properties.hibernate.metadata_builder_contributor = qualifiedClassName

Answer 3

如果有人在 SpringBoot 应用程序中注册时遇到问题，这是正确的方法：创建一个实现的类：MetadataBuilderContributor 接口。package com.application.config;public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {&nbsp; @Override&nbsp; public void contribute(MetadataBuilder metadataBuilder) {&nbsp; &nbsp; metadataBuilder.applySqlFunction(&nbsp; &nbsp; &nbsp; &nbsp; "STRING_AGG",&nbsp; &nbsp; &nbsp; &nbsp; new StandardSQLFunction(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "STRING_AGG",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StandardBasicTypes.STRING&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; );&nbsp; }}在您的应用程序 .yml（或 .properties）中，在以下属性路径中引用先前创建的类：spring.jpa.properties.hibernate.metadata_builder_contributor&nbsp; spring:&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; jpa:&nbsp; &nbsp; &nbsp; properties:&nbsp; &nbsp; &nbsp; &nbsp; hibernate:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; metadata_builder_contributor: com.application.config.SqlFunctionsMetadataBuilderContributor