如何在 Spring Boot 应用程序中添加非标准化的 sql 函数? [英] How to add non-standardized sql functions in Spring Boot application?

问题描述

我的应用程序需要在 Postgres、Mysql 和测试 Hsqldb 之间可移植.我已经设置 Flyway 以在所有三个中提供一些自定义函数，我现在想在我的 SQL/HQL 查询中使用这些函数.

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.

我目前的设置是使用单独的 Dialect ，我使用 application-{profile}.yml 在它们之间切换；哪个有效，但函数声明需要在各种方言之间重复，感觉不太理想.

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.

查看 15.29.Hibernate 文档中的非标准化函数，它说我应该使用 org.hibernate.cfg.Configuration#addSqlFunction()，它看起来更便携，并且不需要扩展所有三个方言.

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.

我的问题是:如何访问 Spring Boot (1.3) 应用程序中的 Hibernate Configuration 类?默认情况下没有要注入的 bean，也没有 LocalSessionFactoryBean bean.

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.

谁能指出我正确的方向，或以其他方式注册我的 sql 函数一次?

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

推荐答案

对于这个问题，我有一个小技巧.

I have rather a hack for the issue.

Hibernate 使用 org.hibernate.dialect.Dialect.SQLFunctionRegistry 来识别数据库函数.

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

这里是一个 hibernate core 4.3.10 的例子.在内部，它由两个私有字段组成:

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;

第一个字段代表数据库的方言.第二个包含用户定义的函数，可以由 org.hibernate.cfg.Configuration#addSqlFunction() 填充.

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

不幸的是，在我搜索 hibernate 源代码时，我发现在初始化 hibernate 时创建的配置对象没有以任何方式公开.

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

但是，我设法访问了 SQLFunctionRegistry.

However, I managed to get access to SQLFunctionRegistry.

需要创建一个 EntityManagerFactory 类型的本地自动装配字段

One needs to create a local autowired field of type 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 来获取它的值.它通常是空的，我只是将我的数据库函数添加到其中.

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.

由于我必须进入 SqlFunctionRegistry 的内部，这是一个黑客，但我更喜欢它而不是创建新的 DB 方言并弄乱它.

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.

这篇关于如何在 Spring Boot 应用程序中添加非标准化的 sql 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！