How To Use Native SQL Functions in JPA Queries With Hibernate
Tags: programming, softwaredevelopment, softwareengineering, technology
Published: 2024-01-19 21:12:17

[JPA](https://en.wikipedia.org/wiki/Jakarta_Persistence) supports native SQL natively. This is useful when you want to take advantage of the unique features of the database in use. On the other hand, native SQL queries are not database agnostic, and this might turn into a problem.

For example, when switching from [MySQL](https://www.mysql.com/it/) to [PostgreSQL](https://www.postgresql.org/) or vice versa. If you employed native queries, the transition would not be straightforward because you had to actively translate them. This is why native queries should be used only when strictly required.

One of the main causes for opting for native SQL queries is native functions, such as MySQL's [`GROUP_CONCAT`](https://mariadb.com/kb/it/group_concat/). In fact, you might believe that using native queries is the only way you have to harness these functions. Actually, this is not true! In fact, [JPA 2.1](https://en.wikipedia.org/wiki/Jakarta_Persistence#JPA_2.1) introduced features allowing you to call any native function in your [JPQL](https://en.wikipedia.org/wiki/Jakarta_Persistence_Query_Language) or [Criteria queries](https://docs.jboss.org/hibernate/core/4.0/hem/en-US/html/querycriteria.html).

Let's see how to start using native SQL functions in your JPA queries with Hibernate in both Kotlin and Java.

## Registering Native Functions

The native SQL functions you want to use must be registered in your Hibernate [`Dialect`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/Dialect.html) implementation class, which depends on your database. You can find a list of all the dialects supported by Hibernate [here](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/package-summary.html). In this example, you are going to see [`MySQL8Dialect`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/MySQL8Dialect.html) and [`PostgreSQL9Dialect`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/PostgreSQL9Dialect.html).

First, you need to create a class extending your specific `Dialect` implementation class. In the example below, this class was placed in the `com.yourapp.configs` package. Then, use it to register native functions as follows:

**Java**

```java
package com.yourapp.configs;

import org.hibernate.dialect.MySQL8Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomSQLDialect extends MySQL8Dialect {
    public MySQLCustomDialect() {
        super();
        
        registerFunction(
            "STRING_CONCAT", 
            new StandardSQLFunction(
                "GROUP_CONCAT", 
                StandardBasicTypes.STRING
            )
       );
    }
}
```

**Kotlin**

```kotlin
package com.yourapp.configs

import org.hibernate.dialect.MySQL8Dialect
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.type.StandardBasicTypes

class CustomSQLDialect : MySQL8Dialect() {
    init {
        registerFunction(
            "STRING_CONCAT",
            StandardSQLFunction(
                "GROUP_CONCAT",
                StandardBasicTypes.STRING
            )
        )
    }
}
```

In this case, the MySQL `GROUP_CONCAT` function was added to `MySQL8Dialect` by passing a [`StandardSQLFunction`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/function/StandardSQLFunction.html) class to the [`registerFunction`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/dialect/Dialect.html#registerFunction-java.lang.String-org.hibernate.dialect.function.SQLFunction-) method. The first string passed to this method represents the name to use to call the native function in your queries. Note that many other native SQL functions could be registered similarly.

Now, you need to make this class the default Hibernate dialect. You can achieve this by adding the following property to your [`application.properties`](https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html) file:

```yaml
spring.jpa.properties.hibernate.dialect = com.myapp.util.CustomSQLDialect
```

Et voilà! You can start using the native SQL functions you just registered in your JPA queries.

## Native Functions in Action

Let's see an example of the just registered `STRING_CONCAT` function in action:

```sql
SELECT F, STRING_CONCAT(F.name) AS names
FROM Foo F 
GROUP BY F.birthDate
```

Let's assume that the [JPA Entity](https://writech.run/blog/defining-jpa-hibernate-entities-in-kotlin-1ff8ee470805) Foo maps a `foo` table having the columns `id`, `name`, `birthDate`.

Then, that JPQL query will be translated into the following MySQL query:

```sql
SELECT F.id, F.name, F.surname, GROUP_CONCAT(F.name) AS names
FROM foo F 
GROUP BY F.birthDate
```

As you can see, the MySQL `GROUP_CONCAT` function is now mapped by the `STRING_CONCAT` JPA function. This means that the MySQL native function can now be used without employing non-portable native SQL queries.

## Migrating to a Different Database

Let's say you wanted to migrate from MySQL to PostgreSQL. If every native MySQL function you registered has a counterpart in PostgreSQL, this will be easy. Plus, no JPA query will have to be changed.

The only thing you need to do is change your `CustomSQLDialect` class as follows:

**Java**

```java
package com.yourapp.configs;

import org.hibernate.dialect.PostgreSQL9Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomSQLDialect extends PostgreSQL9Dialect {
    public MySQLCustomDialect() {
        super();
        
        registerFunction(
            "STRING_CONCAT", 
            new StandardSQLFunction(
                "STRING_AGG", 
                StandardBasicTypes.STRING
            )
       );
    }
}
```

**Kotlin**

```kotlin
package com.yourapp.configs

import org.hibernate.dialect.PostgreSQL9Dialect
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.type.StandardBasicTypes

class CustomSQLDialect : PostgreSQL9Dialect() {
    init {
        registerFunction(
            "STRING_CONCAT",
            StandardSQLFunction(
                "STRING_AGG",
                StandardBasicTypes.STRING
            )
        )
    }
}
```

Since the MYSQL `GROUP_CONCAT` function corresponds to the PostgreSQL `STRING_AGG` function, no other changes are required. Every JPA function you wrote will still be working as expected. Note that the same procedure can be applied when migrating to other databases.

## Conclusion

Today, we looked at how to make Hibernate support native SQL functions in JPQL or Criteria queries. This is a useful workaround to avoid adopting native queries, which are not portable by definition. As you just saw, registering functions to your Hibernate `Dialect` class involves only a few lines of code. On the other hand, it reduces the need to use native queries, making the migration to a different database easier. This is an important goal, and showing how to achieve it was what this article was about.

Thanks for reading! I hope that you found this article helpful.

](https://writech.run/blog/defining-jpa-hibernate-entities-in-kotlin-1ff8ee470805)
  
***
_The post "[How To Use Native SQL Functions in JPA Queries With Hibernate](https://writech.run/blog/how-to-use-native-sql-functions-in-jpa-queries-with-hibernate-d1f17d07a588/)" appeared first on [Writech](https://writech.run)._