Registering a SQL function with JPA and Hibernate

Question

I would like to know what's the best way to register a custom SQL function with JPA/Hibernate.

Do I have to go through extending the MysqlInnodb dialect or is there a better way?

Can anyone please provide code samples and pointers to relevant documentation?

This method is not working in SQL server custom dialect. Rasied a question https://stackoverflow.com/questions/64905785/how-to-use-sql-server-freetext-in-spring-data-jpa-query-using-jpql — pj2494, Nov 23 '20 at 05:13

Vlad Mihalcea · Answer 1 · 2021-01-10T10:39:21.793

You might read articles telling you to register the SQL function by extending the Hibernate Dialect, but that's a naive solution.

Since Hibernate ORM 5.2.18 and 5.3.1, the best way to register a SQL function is to supply a MetadataBuilderContributor like this:

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

Which you can pass to Hibernate via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor" 
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Or, if you bootstrap Hibernate natively, you can just apply the SQL function to the MetadataBuilder during bootstrap.

How I can manage multiple MetadataBuilderContributor if I have to handle multiple dialects using the dialect resolver (h2, mysql, oracle) ? — antoine.lange, Dec 15 '22 at 22:01

score 7 · Accepted Answer · edited Jun 18 '21 at 09:22

7

Yes extending the dialect is a good way of registering custom SQL function.

Add something like this in your Dialect classes constructor.

registerFunction("current_timestamp", new NoArgSQLFunction(Hibernate.TIMESTAMP) );
registerFunction("date", new StandardSQLFunction(Hibernate.DATE) );

Look at the source code of one of the existing dialect classes. http://www.koders.com/java/fid0E7F787E2EC52F1DA8DFD264EDFBD2DE904A0927.aspx

edited Jun 18 '21 at 09:22

jakubde

31
1
2
4

answered Sep 10 '12 at 07:47

gkamal

20,777
4
60
57

Do I have to create a new class and extends an existing dialect or is there another simpler way? – balteo Sep 10 '12 at 07:54
Yes create a new class that extends an existing dialect class. Use your class as the dialect class in the hibernate configuration. – gkamal Sep 10 '12 at 08:12
2

Thanks! Also: [Interesting and relevant link](http://stackoverflow.com/questions/6113167/hibernate-3-6-registerfunction-in-sql-dialect-not-working) – balteo Sep 10 '12 at 08:18

score 2 · Answer 3 · answered Sep 29 '18 at 09:55

Register SQL Method every version

        //Add Hibernate Properties
        properties.put("hibernate.dialect",
                        "com.sparkslink.web.config.sql.RegisterSqlFunction");

        //Create A Class 
        public class RegisterSqlFunction extends MySQLDialect {

            public RegisterSqlFunction() {
                super();
                registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
            }
        }

        //Dao Method

        public List<Client> getTest() {
                Query query = getSession()
                        .createQuery("SELECT sl.name as name ,group_concat(sl.domain) as domain FROM SlClient sl GROUP BY sl.name");
                query.setResultTransformer(Transformers.aliasToBean(Client.class));
                return query.list();
            }
//DTO Class
    public class Client {
        private String name;
        private String domain;
    //Getter 
    //Setter
    }

Registering a SQL function with JPA and Hibernate

3 Answers3

Linked