Micronaut集成JPA/Hibernate时大写表名被转小写找不到如何配置

使用Micronaut框架集成JPA/Hibernate时，框架会自动将数据库表名转为小写，服务启动时抛出表不存在的错误；但对应表实际存在于数据库中，且表名为全大写格式。需要配置Micronaut/JPA/Hibernate，使其保留表名原有大小写、不自动转为小写。

需要在Micronaut服务中通过JPA/Hibernate访问MySQL8数据库中已有的WIDGET表，但服务启动阶段执行schema校验时抛出错误，框架实际查找的是小写表名widget，而非大写的WIDGET。

此前尝试过Spring、Postgres等其他环境下同类问题的解决方案，但都无法在Micronaut环境下生效。尝试过在实体类的@Entity注解中使用反引号、转义引号包裹表名（@Entity(name="WIDGET") & @Entity(name="\"WIDGET\"")），均未成功；也尝试过在application.yml中添加delimited-identifiers配置项，但不清楚该配置的正确编写位置与方式，同样没有效果。

相关代码与配置如下： #

application.yml配置： #

datasources:
  default:
    url: 'jdbc:mysql://localhost:3306/myschema'
    username: '${dbUser}'
    password: '${dbPass}'
jpa:
  default:
    properties:
      hibernate:
        hbm2ddl:
          auto: validate

Widget.java实体类： #

@Entity(name = "WIDGET")
//@Table(name = "`WIDGET`")  // this doesn't work either
public class Widget {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "year")
    private Integer year;

    @Column(name = "widget_title")
    private String title;

// appropriate constructors, getters, & setters omitted for brevity...
}

WidgetRepository.java数据访问接口： #

@Repository
public interface WidgetRepository extends CrudRepository<Widget, Integer>
{
    @Executable
    Optional<Widget> find(String title);
}

启动异常信息： #

14:40:33.759 [main] ERROR io.micronaut.runtime.Micronaut - Error starting Micronaut server: Bean definition [org.hibernate.SessionFactory] could not be loaded: Error instantiating bean of type  [org.hibernate.SessionFactory]

Message: Schema-validation: missing table [widget]
Path Taken: SessionFactory.hibernateSessionFactory(SessionFactoryBuilder sessionFactoryBuilder)
io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [org.hibernate.SessionFactory] could not be loaded: Error instantiating bean of type  [org.hibernate.SessionFactory]

Message: Schema-validation: missing table [widget]
Path Taken: SessionFactory.hibernateSessionFactory(SessionFactoryBuilder sessionFactoryBuilder)
    at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1898)
    at io.micronaut.context.DefaultApplicationContext.initializeContext(DefaultApplicationContext.java:245)
    at io.micronaut.context.DefaultBeanContext.readAllBeanDefinitionClasses(DefaultBeanContext.java:3298)
    at io.micronaut.context.DefaultBeanContext.finalizeConfiguration(DefaultBeanContext.java:3650)
    at io.micronaut.context.DefaultBeanContext.start(DefaultBeanContext.java:337)
    at io.micronaut.context.DefaultApplicationContext.start(DefaultApplicationContext.java:190)
    at io.micronaut.runtime.Micronaut.start(Micronaut.java:72)
    at io.micronaut.runtime.Micronaut.run(Micronaut.java:320)
    at io.micronaut.runtime.Micronaut.run(Micronaut.java:306)
    at com.example.Application.main(Application.java:11)
Caused by: io.micronaut.context.exceptions.BeanInstantiationException: Error instantiating bean of type  [org.hibernate.SessionFactory]

Message: Schema-validation: missing table [widget]
Path Taken: SessionFactory.hibernateSessionFactory(SessionFactoryBuilder sessionFactoryBuilder)
    at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2344)
    at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2282)
    at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2228)
    at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:2988)
    at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
    at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:2890)
    at io.micronaut.context.DefaultBeanContext.loadContextScopeBean(DefaultBeanContext.java:2709)
    at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1892)
    ... 9 common frames omitted
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [widget]
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:129)
    at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:42)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:97)
    at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:76)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:204)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:85)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:335)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471)
    at io.micronaut.configuration.hibernate.jpa.EntityManagerFactoryBean.hibernateSessionFactory(EntityManagerFactoryBean.java:222)
    at io.micronaut.configuration.hibernate.jpa.$EntityManagerFactoryBean$HibernateSessionFactory3$Definition.build(Unknown Source)
    at io.micronaut.context.BeanDefinitionDelegate.build(BeanDefinitionDelegate.java:161)
    at io.micronaut.context.DefaultBeanContext.resolveByBeanFactory(DefaultBeanContext.java:2331)
    ... 16 common frames omitted

问题核心由两个配置错误导致：

• 实体类注解使用错误：@Entity的name属性是用来指定JPQL中实体的名称，不是映射数据库表名，表名映射必须用@Table注解的name属性。
• 缺少Hibernate命名策略配置：Micronaut集成Hibernate时默认使用的命名策略会自动将名称转为小写，没有显式配置的情况下即使注解写了大写表名也会被转换。

按以下两步修改即可解决：

• 第一步：修正实体类注解
  去掉@Entity上的表名配置，单独使用@Table注解显式指定大写表名：

@Entity
@Table(name = "WIDGET")
public class Widget {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "year")
    private Integer year;

    @Column(name = "widget_title")
    private String title;

    // 构造方法、getter、setter省略
}

• 第二步：修正application.yml配置
  在JPA的Hibernate配置段添加命名策略和全局标识符引号配置，强制Hibernate保留你定义的表名/字段名大小写，不做自动小写转换：

datasources:
  default:
    url: 'jdbc:mysql://localhost:3306/myschema'
    username: '${dbUser}'
    password: '${dbPass}'
jpa:
  default:
    properties:
      hibernate:
        hbm2ddl:
          auto: validate
        # 关闭自动小写转换的命名策略，使用物理命名直接透传注解中定义的名称
        physical_naming_strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        # 全局开启标识符转义，保留名称大小写
        globally_quoted_identifiers: true

注意：如果你的字段名也存在大写的情况，globally_quoted_identifiers: true配置会自动给所有表名、字段名加上引号，强制数据库严格匹配大小写，不需要你在每个注解里单独加反引号或者转义符。

配置修改完成后重启服务，schema校验就会按照你指定的大写WIDGET表名去匹配，不会再自动转成小写抛出表不存在的错误。

内容的提问来源于stack exchange，提问作者Alpha Fighter