Registering a SQL function through Entity ManagerFactory

Question

In Hibernate, I can register custom SQL fuctions through the Configuration object:

public void registerExtensions(Configuration config) {
    config.addSqlFunction("s_sum", new StandardSQLFunction("sum"));
    ...
}

I wish to switch this configuration from "legacy" Hibernate to JPA.

I've read I can register SQL functions by specializing the dialect there : Registering a SQL function with JPA/Hibernate This is not what I would like to do.

Is there a way to register these functions when building the Entity Manager Factory ?

I'm running Hibernate 4.3 in a Spring 4.1 context.

Well you can't because JPA has no such mechanism. You will always be tying your code to one implementation in insisting on such things. — Neil Stockton, Mar 24 '16 at 18:49

score 1 · Answer 1 · answered Mar 24 '16 at 14:36

1

JPA registers SQL through the @NamedQueries and @NamedQuery annotations. These are placed in the same class as the Entity. See the Javadoc: Annotation Type NamedQuery.

@NamedQuery(
    name="findAllCustomersWithName",
    query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)

and

@PersistenceContext
public EntityManager em;
...
customers = em.createNamedQuery("findAllCustomersWithName")
    .setParameter("custName", "Smith")
    .getResultList();

answered Mar 24 '16 at 14:36

K.Nicholas

10,956
4
46
66

I'm afraid that's not what I need. The goal is to extend hql with new functions such as explained here http://stackoverflow.com/questions/4955580/hibernate-how-to-use-concat-and-group-concat – gregfqt Mar 24 '16 at 14:57
Well, the original post you linked to said to extend the dialect class. Did you try that? Id be interested to know the example and/or use case. – K.Nicholas Mar 24 '16 at 16:16
I would like to avoid extending the dialects because my app can run against several RDBMS. I would have to extend several dialects with mostly the same code. – gregfqt Mar 24 '16 at 16:25
I suppose you could get the Hibernate Session Factory and use that? – K.Nicholas Mar 24 '16 at 16:30

Vlad Mihalcea · Answer 2 · 2021-01-21T14:50:38.110

Since Hibernate ORM 5.2.18 and 5.3.1, the best way to register a SQL function when using JPA 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>

You might read articles telling you to register the SQL function by extending the Hibernate Dialect, but that's a naive solution. For more details about why you should avoid extending the Dialect, read this article.

Registering a SQL function through Entity ManagerFactory

2 Answers2