How to add non-standardized sql functions in Spring Boot application?

Question

My app needs to be portable between Postgres, Mysql and for testing Hsqldb. I've setup Flyway to make some custom functions available on all three, that I now want to use in my SQL/HQL queries.

My current setup is using separate Dialects that I switch between using application-{profile}.yml; which works, but the function declarations need to be duplicated among the various dialects, and it feels suboptimal.

Looking at 15.29. Non-standardized functions in the Hibernate documentation, it says I should be using org.hibernate.cfg.Configuration#addSqlFunction(), which seems more portable and removes the need to extend all three dialects.

My issue is: How do I get access to the Hibernate Configuration class in my Spring Boot (1.3) application? There's no bean to inject by default, and no LocalSessionFactoryBean bean either.

Can anyone point me in the right direction, or at some other way to register my sql functions once?

Answer 1

I have rather a hack for the issue.

Hibernate uses org.hibernate.dialect.Dialect.SQLFunctionRegistry to recognize a DB function.

Here is an example of hibernate core 4.3.10. Internally it consists of two private fields:

/**
 * Defines a registry for SQLFunction instances
 *
 * @author Steve Ebersole
 */
public class SQLFunctionRegistry {
    private final Dialect dialect;
    private final Map<String, SQLFunction> userFunctions;

The first field represents dialect of the database. Second contains user defined functions which can be filled by org.hibernate.cfg.Configuration#addSqlFunction().

Unfortunately, in my search through hibernate source code I found that configuration object created while initializing hibernate is not exposed in any way.

However, I managed to get access to SQLFunctionRegistry.

One needs to create a local autowired field of type EntityManagerFactory

@Autowired
private EntityManagerFactory emFactory;

and later call the following code:

private void registerMyDbFunctions()
{
    SQLFunctionRegistry registry = this.emFactory.unwrap(org.hibernate.internal.SessionFactoryImpl.class).getSqlFunctionRegistry();
    Field field = ReflectionUtils.findField(SQLFunctionRegistry.class, "userFunctions");
    ReflectionUtils.makeAccessible(field);
    Map<String, SQLFunction> userFunctions = (Map<String, SQLFunction>)ReflectionUtils.getField(field, registry);

    userFunctions.put("my_func", new SQLFunctionTemplate(TextType.INSTANCE, "my_func(?1, ?2)"));
}

Since userFunctions field is private and not exposed in the class, I used ReflectionUtils to get its value. It's usually empty and I just add my DB functions to it.

Since I had to get into SqlFunctionRegistry's internals it's a hack, but I prefer it over creating new DB dialect and messing up with it.