JPA/Hibernate & Micronaut, Uppercased Table Name Not Found

Question

Summary:

Micronaut/JPA/Hibernate is lowercasing the name of my database table and throwing an error on startup saying the table can't be found. The table is there, but it's named in uppercase. How do I configure micronaut/jpa/hibernate to respect the case of my table name & not lowercase it?

Details:

I have an existing table WIDGET in a MySQL8 database that I need to access from a micronaut service using jpa/hibernate. Unfotunately an error is thrown during schema validation on startup, it's looking for the table widget instead of WIDGET.

I have looked and found solutions for similar issues in different environments (spring, postgres), but have been unable to get them to work in micronaut. I have tried using backticks & escaped quotes for my entity name (@Entity(name="`WIDGET`") & @Entity(name="\"WIDGET\"")), but had no success. I have tried adding a delimited-identifiers element to the application.yml, but had no success (not sure where/how to specify it).

Here are the relevant bits:

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

Exception:

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

Answer 1

A coworker with better search-fu than me found this. While I did not need to @Replace the DefaultPhysicalNamingStrategy.class with my own, I was able to solve my problem by changing my application.yml as follows, specifically adding the physical_naming_strategy: 'org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl' under the jpa.default.properties.hibernate node:

jpa:
  default:
    properties:
      hibernate:
        physical_naming_strategy: 'org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl'
        hbm2ddl:
          auto: validate
        show_sql: true