MariaDB 的休眠命名策略 (UPPER_SNAKE_CASE)

2022-01-15 00:00:00 mariadb spring java Hibernate jpa

在我的应用程序中，我使用 flyway 进行数据迁移，因此我在 UPPER_SNAKE_CASE 中定义了所有名称的所有表(例如 users -> USERS、candidateGroups -> CANDIDATE_GROUP).我为不同的环境提供了 2 种配置:本地和 docker.在本地环境中我运行 h2，在 Docker 上我使用 MariaDB 运行.

In my application I'm using flyway for data migration, therefore I have defined all tables with all names in UPPER_SNAKE_CASE (ex. users -> USERS, candidateGroups -> CANDIDATE_GROUP). I have provided 2 configurations for different environments: local and docker. On local environment I'm running h2, on Docker I'm running with MariaDB.

现在在 h2 上运行时一切正常，在 MariaDB 上休眠是尝试对所有小写表执行操作，这会导致错误:

Now while running on h2 everything works fine, on MariaDB hibernate is attempts to perform operations on all lower case tables, this results in error:

原因:java.sql.SQLException:表 'application.users' 不存在

Caused by: java.sql.SQLException: Table 'application.users' doesn't exist

经过一些调查，我尝试添加到我的 application-docker.yml:

After some investigations i have tried adding to my application-docker.yml:

  spring:
    jpa:
     hibernate:
       naming:
         physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

这会导致休眠中的表名大写:原因:java.sql.SQLException:表'application.Users'不存在

This results in capitalization of table names in hibernate: Caused by: java.sql.SQLException: Table 'application.Users' doesn't exist

我知道我可以使用注释 @Table(name = "USERS")为所有表命名并为所有列提供名称，但这不是我想要的解决方案.

I know I can name all tables with annotation @Table(name = "USERS")and provide names for all columns as well, but this is not my desired solution.

不幸的是，我没有找到任何符合我情况的策略(我知道 this 展示了如何创建自定义命名策略，但我认为我的情况并不少见)，我的 MariaDB 配置应该使用什么命名策略来匹配我的情况?

Unfortunately I haven't found any strategy matching my case (I know this shows how to create custom naming strategy but I don't think my case is that uncommon), what naming strategy should I use for my MariaDB config to match my case?

推荐答案

UPPER_SNAKE_CASE命名策略树/master/hibernate-core/src/main/java/org/hibernate/boot/model/naming" rel="nofollow noreferrer">Hibernate 或 Spring Data JPA，但是可以通过扩展 PhysicalNamingStrategyStandardImpl 或 SpringPhysicalNamingStrat埃及.

There's no UPPER_SNAKE_CASE naming strategy neither in Hibernate or Spring Data JPA, but one could define his/her own by extending PhysicalNamingStrategyStandardImpl or SpringPhysicalNamingStrategy.

在 Spring Data JPA 中更容易，因为 Spring 已经将名称转换为蛇形大小写:

It is easier in Spring Data JPA as Spring already converts names to snake_case:

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;

import java.io.Serializable;
import java.util.Locale;

public class UpperSnakeCaseSpringPhysicalNamingStrategyImpl extends SpringPhysicalNamingStrategy implements Serializable {

    @Override
    protected Identifier getIdentifier(String name, boolean quoted, JdbcEnvironment jdbcEnvironment) {
        name = name.toUpperCase(Locale.ROOT);
        return new Identifier(name, quoted);
    }
}

对于转换为snake_case 的Hibernate 实现，需要至少覆盖​​5 个方法中的2 个:

For Hibernate implementation of the conversion to snake_case and override of at least 2 of the 5 methods is required:

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

import java.io.Serializable;
import java.util.Locale;

public class UpperSnakeCasePhysicalNamingStrategyImpl extends PhysicalNamingStrategyStandardImpl implements Serializable {

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        return new Identifier(addUnderscores(name.getText()), name.isQuoted());
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        return new Identifier(addUnderscores(name.getText()), name.isQuoted());
    }

    private static String addUnderscores(String name) {
        final StringBuilder buf = new StringBuilder(name.replace('.', '_'));
        for (int i = 1; i < buf.length() - 1; i++) {
            if (
                    Character.isLowerCase(buf.charAt(i - 1)) &&
                            Character.isUpperCase(buf.charAt(i)) &&
                            Character.isLowerCase(buf.charAt(i + 1))
            ) {
                buf.insert(i++, '_');
            }
        }
        return buf.toString().toUpperCase(Locale.ROOT);
    }
}

最后应该在application.properties或者application.yml中设置自定义的命名策略:

Finally the customized naming strategy should be set in application.properties or application.yml:

spring.jpa.hibernate.naming.physical-strategy=my.package.UpperSnakeCaseSpringPhysicalNamingStrategyImpl

关于 SO 的更多信息:

More information on SO:

  • 更改Hibernate 5命名策略+spring boot+注解
  • https://stackoverflow.com/questions/32437202

更新 (16.01.2020)

Hibernate 5 中的命名策略

相关文章