4

I'm using JPA query in my current spring-boot project. How can I add non-standardized SQL functions like GROUP_CONCAT?

Prior, to my previous problem : How to show a column result in a one line comma separated list in JPA query

I found that GROUP_CONCAT is not a registered function in JPA query but could be accessed by registering it manually. I already tried following links but didn't work for me :

How to add non-standardized sql functions in Spring Boot application?

Registering a SQL function with JPA and Hibernate

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;
    }
}

    public RegisterSqlFunction() {
        super();

         registerFunction("group_concat, new StandardSQLFunction("group_concat", 
       StandardBasicTypes.STRING));
    }

I except using group_concat with JPA query.

Smriti mool
  • 139
  • 4
  • 11

3 Answers3

11

You can find a fully functional example in my High-Performance Java Persistence GitHub repository.

In your case, you don't need to customize the JpaPlatform. That should be set to the HibernateJpaPlatform.

You can register the MetadataBuilderContributer either programaticallly via the application.properties configuration file:

hibernate.metadata_builder_contributor=com.vladmihalcea.book.hpjp.SqlFunctionsMetadataBuilderContributor
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • hey thanks. I did look through your github project previously. You've used "group_concat" in one of the test class "GroupConcatFunctionMetadataBuilderContributorRegisterTest" but the class seems to extend lots of other classes and they have their own several implementations, which to me, seems very confusing. Is there any other option? Is there any way to configure directly through "application.properties". Also, to remind you, my application is based on spring boot. – Smriti mool Aug 13 '19 at 09:35
  • I thoroughly went through your article again and it solved my issue. Thanks!! Also, previously I used **Dbeaver** to execute my native queries. Are there any tools as such to execute JPA queries? – Smriti mool Aug 13 '19 at 14:07
  • I use the Database tool from IntelliJIDEA. – Vlad Mihalcea Aug 13 '19 at 14:18
  • The above configuration worked fine for me. But, currently I'm following microservice based architecture and I tried to read all the hibernate properies through **Spring cloud config server ** . So, when I set above properties in my config-server microservice, my other client microservices didn't start with exception "org.hibernate.boot.registry.classloading.spi.ClassLoadingException" – Smriti mool Aug 14 '19 at 01:58
  • Hi @VladMihalcea , can you please tell us how to add more than one function ? thanks – Francisco Quiñones Feb 06 '23 at 19:06
  • @FranciscoQuiñones If you follow the solution in this article, you'll see that you can call `applySqlFunction` multiple times withing the `MetadataBuilderContributer`. – Vlad Mihalcea Feb 06 '23 at 21:09
  • Hi @VladMihalcea thanks ! This is working as you mentioned. – Francisco Quiñones Feb 07 '23 at 20:29
5

Create a class and add mySql Function you need to use in the overridden method:

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor{

 @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "group_concat",
            new StandardSQLFunction(
                "group_concat",
                StandardBasicTypes.STRING
            )
        );
    }
}

After that, provide your metadata_builder_contributor via application.properties:

spring.jpa.properties.hibernate.metadata_builder_contributor = qualifiedClassName
Foram Shah
  • 107
  • 1
  • 4
  • this is not working for me , this is the error I see in the console : unexpected AST node: near group_concat ... . Is there a place to create the class. SqlFunctionsMetadataBuilderContributor ? I'm using Spring boot and hibernate 5.6.9.Final library version – Francisco Quiñones Feb 03 '23 at 19:14
2

In case someone is having issues when registering this in a SpringBoot app this is the right way:

Create a class that implements: MetadataBuilderContributor interface.

package com.application.config;

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {

  @Override
  public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction(
        "STRING_AGG",
        new StandardSQLFunction(
            "STRING_AGG",
            StandardBasicTypes.STRING
        )
    );
  }
}

In your application .yml (or .properties) refer to the previously created class in the following properties path: spring.jpa.properties.hibernate.metadata_builder_contributor

  spring:      
    jpa:
      properties:
        hibernate:
          metadata_builder_contributor: com.application.config.SqlFunctionsMetadataBuilderContributor
Luis Miguel
  • 495
  • 8
  • 22
  • this is not working for me , this is the error I see in the console : unexpected AST node: near STRING_AGG ... . Is there a place to create the class. SqlFunctionsMetadataBuilderContributor ? I'm using Spring boot and hibernate 5.6.9.Final library version – Francisco Quiñones Feb 03 '23 at 18:58