SpringBoot JPA and MySQL 8 issue with case sensitivity

Question

We have applications written in Spring Boot and using jpa to access existing databases. We are in need to upgrade our Aurora (MySQL) database to the latest version which is compatible with MySQL 8. The problem we are having is that it does not allow for the setting of the lower_case_table_names attribute to 1 which allows for not case-sensitive searches/queries. This is causing problems because a lot of the Entity definitions have the @Table(name = "TABLE_NAME") or @Column(name = "COLUMN_NAME") annotation with the name of the table or column in upper case, so the queries are generating an error because the Table or column is not found.

Is there a way in configuration in SpringBoot jpa to submit the queries in lowercase?

Just wondering, otherwise we are going to have to find all instances in all projects where table and column names are uppercase and change them.

Thanks in advance.

Answer 1

You should follow this step shown below

1) Define a class named like LowercaseNamingStrategy extending from PhysicalNamingStrategy

Here is the example codes of LowercaseNamingStrategy

public class LowercaseNamingStrategy implements PhysicalNamingStrategy {

    @Override
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment context) {
        return convertToLowercase(name);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment context) {
        return convertToLowercase(name);
    }

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        return convertToLowercase(name);
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment context) {
        return convertToLowercase(name);
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        return convertToLowercase(name);
    }

    private Identifier convertToLowercase(Identifier name) {
        if (name == null) return null;
        return Identifier.toIdentifier(name.getText().toLowerCase());
    }
}

2) Define this strategy in application.properties file of application.yl file

If you want to use it in application.properties file, you must define it

spring.jpa.hibernate.naming.physical-strategy=com.example.config.LowercaseNamingStrategy

If you want to use it in application.yml file, you must define it

spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: com.example.config.LowercaseNamingStrategy