# Migration from SQLFunctionTemplate in FunctionContributor

**URL:** <https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419>\
**Category:** Hibernate ORM\
**Created:** [April 24, 2024, 11:43am UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419 "2024-04-24T11:43:57Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [April 24, 2024, 11:43am UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/1 "2024-04-24T11:43:57Z")

</div>

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:

```auto
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](https://discourse.hibernate.org/t/migration-of-dialect-to-hibernate-6/6956) to upgrade the code to:

```auto
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.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [April 24, 2024, 2:48pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/2 "2024-04-24T14:48:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [April 26, 2024, 1:55pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/3 "2024-04-26T13:55:37Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [April 29, 2024, 7:48am UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/4 "2024-04-29T07:48:47Z")

</div>

> [@mhais](#):
>
> Shall I keep to extend `DB2Dialect`?

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.

---

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [April 30, 2024, 12:53pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/5 "2024-04-30T12:53:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [April 30, 2024, 1:01pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/6 "2024-04-30T13:01:09Z")

</div>

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**](https://docs.oracle.com/javase/8/docs/api/java/util/ServiceLoader.html).

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.

---

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [April 30, 2024, 10:01pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/7 "2024-04-30T22:01:40Z")

</div>

Thank you so much for the clarification!

---

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [May 9, 2024, 10:37pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/8 "2024-05-09T22:37:30Z")

</div>

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

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

```

```auto
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

```auto
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:

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

```

Best regards,  
Nick.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 10, 2024, 7:09am UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/9 "2024-05-10T07:09:05Z")

</div>

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:

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

```

---

<div class="post-metadata">

**Author:** ![mhais](https://avatars.discourse-cdn.com/v4/letter/m/58956e/32.png) [@mhais](https://discourse.hibernate.org/u/mhais)\
**Post date:** [May 14, 2024, 2:15pm UTC](https://discourse.hibernate.org/t/migration-from-sqlfunctiontemplate-in-functioncontributor/9419/10 "2024-05-14T14:15:46Z")

</div>

Thanks so much @mbladel !  
Seems it work!