When JPA meets four styles of MySQL table names: full capitalization + full lowercase + hump + Hungary

Keywords: MySQL Hibernate Java Spring SQL

I have always known that MySQL has the case problem of field names, table names, etc. on Linux, so in order to avoid this problem, I chose underscores and all lowercase, saying that although we use JPA, as long as we use annotations to write clearly that table names and field names are uppercase, that's all right. For example

It turns out that imagination is much richer than moving bricks. Dear relatives, when I used JPA to implement table lookup operation, Exception jumped out and said that the table did not exist, because the table name is all lowercase, how can I (). Next, the toss is just beginning... You might as well look at the log, the heart is unusually heavy

2019-09-24 17:31:16.407 ERROR 25864 --- [  XNIO-2 task-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : (conn=348) Table '4a.t_assets_mgr' doesn't exist
2019-09-24 17:31:16.420 ERROR 25864 --- [  XNIO-2 task-1] c.s.xxxx.aop.logging.LoggingAspect       : Exception in com.xxx.yyyy.service.StaticQuadraa.initLevel() with cause = 'org.hibernate.exception.SQLGrammarException: could not extract ResultSet' and exception = 'could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet'

org.springframework.dao.InvalidDataAcce***esourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy195.getAllByBusinessName(Unknown Source)

Usually to solve this problem, if your database is built on windows and you write and play by yourself, the first step must be to change MySQL's cnf to case-insensitive, so it's always possible? But now all in the docker environment, this operation, ah, and then install Oracle is troublesome, how to do? Now that MySQL has solved the problem, let's find a way out in Java. Simple analysis of the process, browser button, initiate the request to the background, Controller receives the request through service to find the corresponding DTO, DTO through Spring Data to find Hibernate to achieve the JPA specification, and then translated into SQL statements sent to the database for execution. So as long as you can change the name of the table to uppercase before sending it, you can solve the problem.
So where does this change the table name to capitalization or lowercase? Remember that JPA has a conversion for fields, such as a_b to aB. This is certainly not implemented by the Java kernel, but by Hibernate's JPA implementation. Specifically to the code, this thing in spring is org. spring framework. boot. orm. jpa. hibernate. Spring Physical Naming Strategy, which is used by default. Hibernate implements this by converting hump rules. The table name in my comment is capitalized, but the sql statement output is capitalized. There is no doubt that there is code for converting to capitalization. At present, spring data jpa is based on Hibernate 5, and Hibernate 5 has a slightly different configuration about database naming strategy from previous versions. It uses implicit-strategy and physical-strategy to control naming strategy respectively.


There is still a slight difference between the two strategies:

  • implicit-strategy is responsible for the processing of the object level of the model, and the object model is treated as a logical name.
  • physical-strategy is responsible for the processing of mapping to real data names, and the logical names mentioned above are treated as physical names.
  • When the @Table and @Column annotations are not used, implicit-strategy configuration items are used, and implicit-strategy does not work when specified in the object model.
  • physical-strategy is bound to be applied regardless of whether column names are explicitly specified in the object model or whether they have been implicitly determined.

Here are two solutions:

  1. Configuration lines can be added to the configuration file in the springboot project and named as unmodified naming policies:

  2. The method of changing the name to lowercase in the rewrite naming policy:

    public class MySQLUpperCaseStrategy extends PhysicalNamingStrategyStandardImpl {
    private static final long serialVersionUID = 1383021413247872469L;
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        // Convert all table names to uppercase
        String tableName = name.getText().toUpperCase();
        return name.toIdentifier(name.getText());

    Then we need to use our own implementation strategy in the relevant.yml files.

    spring.jpa.hibernate.naming:physical-strategy: xx.xx.xx.config.Strategy.MySQLUpperCaseStrategy

    Here I used method 2, because in the follow-up development, our predecessors poisoned the database, sharing four naming styles of hump, Hungary, underline and all lowercase. There is hardly anyone before and after. I hope that the blue buff will be snatched as soon as possible, the beginning will be overturned, the landlord 3456 fewer than 7, and the high-speed railway will eat less instant noodles. Fork, stocks will fall whenever they buy.
    Here I use method 2 to map and modify the table names freely, not depending on their naming style, so as to salute our ancestors!!

Posted by mananx on Wed, 09 Oct 2019 20:34:27 -0700