How To Use Native SQL Functions in JPA Queries With Hibernate

JPA 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 to PostgreSQL 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. 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 introduced features allowing you to call any native function in your JPQL or Criteria queries.

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 implementation class, which depends on your database. You can find a list of all the dialects supported by Hibernate here. In this example, you are going to see MySQL8Dialect and PostgreSQL9Dialect.

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

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

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 class to the registerFunction 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 file:

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:

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

Let's assume that the JPA Entity Foo maps a foo table having the columns id, name, birthDate.

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

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

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

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" appeared first on Writech.