如何在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.

这是休眠核心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().

不幸的是，在通过休眠源代码进行搜索时，我发现初始化休眠状态时创建的配置对象没有以任何方式公开.

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的内部，这是一个hack，但我更喜欢它，而不是创建新的数据库方言并弄乱它.

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屋！