How to add non-standard sql functions to spring boot application?

Question

My application must be portable between Postgres, Mysql and for testing Hsqldb. I installed Flyway to make some custom functions available to all three that I now want to use in my SQL / HQL queries.

My current setup uses a separate one Dialect, which I switch between uses application-{profile}.yml; which works, but function declarations need to be duplicated between different dialects, and it feels suboptimal.

Looking at 15.29. The non-standard features in the Hibernate documentation, he says I should use org.hibernate.cfg.Configuration#addSqlFunction(), which seems more portable and eliminates the need to expand all three dialects.

My problem: how do I access the Hibernate class Configurationin my Spring Boot (1.3) application? There is no bean for input by default, but no LocalSessionFactoryBeanbean.

Can someone point me in the right direction or in some other way register my sql functions once?

Answer 1

I have a problem with this.

Hibernate uses a org.hibernate.dialect.Dialect.SQLFunctionRegistrydatabase function for recognition.

Here is an example of a sleeping kernel 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 the dialect of the database. The second contains custom functions that can be populated org.hibernate.cfg.Configuration#addSqlFunction().

, , , hibernate, .

SQLFunctionRegistry.

EntityManagerFactory

@Autowired
private EntityManagerFactory emFactory;

:

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)"));
}

userFunctions , ReflectionUtils, . , DB.

SqlFunctionRegistry , , .