Migration from SQLFunctionTemplate in FunctionContributor

Hibernate ORM
1

Hello!

We recently migrated to Hibernate 6 from Hibernate 5 and that made us to change the code.

May I ask the community to check if I did the migration correctly? We used to use these lines of code:

public class DB2FunctionRegister extends DB2Dialect {

  public DB2FunctionRegister() {
    super();
    registerFunction("listagg", new StandardSQLFunction("listagg"));
    registerFunction("listaggDistinct",
        new SQLFunctionTemplate(StandardBasicTypes.STRING, "LISTAGG(DISTINCT ?1,', ') "));
    registerFunction("listaggDistinctLtrimZero",
        new SQLFunctionTemplate(StandardBasicTypes.STRING, "LISTAGG(DISTINCT LTRIM(?1, '0'), ', ') "));
    registerFunction("length", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "LENGTH(?1)"));
    registerFunction("varcharFormat",
        new StandardSQLFunction("varchar_format", StandardBasicTypes.STRING));
  }
}

but as SQLFunctionTemplate was removed in new Hibernate version I used this doc to upgrade the code to:

package com.company.config;

public class DB2FunctionRegister extends DB2Dialect implements FunctionContributor {

  @Override
  public void contributeFunctions(FunctionContributions functionContributions) {
    functionContributions.getFunctionRegistry().register("string_agg",
            new StandardSQLFunction("string_agg", StandardBasicTypes.STRING));
    functionContributions.getFunctionRegistry().register("listagg",
            new StandardSQLFunction("listagg", StandardBasicTypes.STRING));

    functionContributions.getFunctionRegistry().registerPattern(
            "listaggDistinct", "LISTAGG(DISTINCT ?1,', ') ",
                    functionContributions.getTypeConfiguration()
                            .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING));
    functionContributions.getFunctionRegistry().registerPattern(
            "listaggDistinctLtrimZero", "LISTAGG(DISTINCT LTRIM(?1, '0'), ', ') ",
            functionContributions.getTypeConfiguration()
                    .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING));
    functionContributions.getFunctionRegistry().registerPattern(
            "length", "LENGTH(?1)",
            functionContributions.getTypeConfiguration()
                    .getBasicTypeRegistry().resolve(StandardBasicTypes.INTEGER));
    functionContributions.getFunctionRegistry().registerPattern(
            "varcharFormat", "varchar_format",
            functionContributions.getTypeConfiguration()
                    .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING));
  }

}

and also created META-INF/services/FunctionContributor file and put com.company.config.DB2FunctionRegister into it.

Have I made everything correct?

Thanks in advance,
Nick.

2

Your DB2FunctionRegister should not extend DB2Dialect, it only needs to implement the FunctionContributor interface and be available for service loading, other than that this looks fine to me.

3

Hello @mbladel !

Thanks for checking.

But I have a question regarding your suggestion not to extend DB2Dialect in DB2FunctionRegister.

In my entityManagerFactory I use DB2FunctionRegister to set dbDialect. In my case it is DB2Dialect. My snippet below:

jpaProperties.setProperty("hibernate.dialect",
        dbDialect == null || dbDialect.trim().isEmpty()
            ? "com.company.config.DB2FunctionRegister"
            : dbDialect);

If I not extend DB2Dialect in DB2FunctionRegister then I get an error: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Could not instantiate named strategy class.

Shall I keep to extend DB2Dialect?

Thanks,
Nick.

4

No, you should simply use Hibernate’s standard org.hibernate.dialect.DB2Dialect, your function contributor will be loaded separately and you don’t seem to have any other customization to the dialect class itself.

5

Thanks @mbladel !

But I still cannot understand what do you mean that my function contributor will be loaded separately.

Before my understanding was that when I extend DB2Dialect in my DB2FunctionRegister it means that when I specify hibernate.dialect by setting it to DB2FunctionRegister the DB2Dialect will be loaded together with my override SQL functions.

But if I set hibernate.dialect to be just DB2Dialect so how my code will know about DB2FunctionRegister? Could you help me to understand this please?

6

When you create the file META-INF/services/org.hibernate.boot.model.FunctionContributor, which contains the FQDN of your DB2FunctionRegister, you are effectively making that class java service loadable.

Hibernate, during the boot phase of your application, will load the available FunctionContributor services and invoke their contributeFunctions method, thus allowing them to register your custom functions and making them available, regardless of the Dialect you choose.

7

Thank you so much for the clarification!

8

Hello @mbladel !
Sorry for get back to you, but after our migration we also faced an issue on these lines execution:

  public static StringTemplate getListAgg(StringPath path) {
    return Expressions.stringTemplate("listaggDistinct({0}, ', ')", path);
  }

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Function listaggDistinct() has 1 parameters, but 2 arguments given

The same lines of code used to work with Hibernate 5. Please note that StringTemplate and Expressions are part of Querydsl v4 library.

I think I need to update these lines

functionContributions.getFunctionRegistry().registerPattern(
            "listaggDistinct", "LISTAGG(DISTINCT ?1,', ') ",
                    functionContributions.getTypeConfiguration()
                            .getBasicTypeRegistry().resolve(StandardBasicTypes.STRING));

but not sure how.

Also here is the old code which we use before migration to Hibernate 6:

    registerFunction("listaggDistinct",
            new SQLFunctionTemplate(StandardBasicTypes.STRING, "LISTAGG(DISTINCT ?1,', ') "));

Best regards,
Nick.

9

Apparently Expressions.stringTemplate("listaggDistinct({0}, ', ')", path); is creating an expression with 2 input parameters, while your pattern only has one ?1. I would need to see the full HQL query to understand what’s going on there.

You could get around it by registering the function to have 2 parameters:

functionContributions.getFunctionRegistry().patternDescriptorBuilder( "listaggDistinct", "LISTAGG(DISTINCT ?1,', ')" )
				.setInvariantType( functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve( StandardBasicTypes.STRING) )
				.setExactArgumentCount( 2 )
				.register()
10

Thanks so much @mbladel !
Seems it work!