Mysql Question posted in
The official MySQL documentation can be found here.

SpringBoot JPA and MySQL 8 issue with case sensitivity

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.

Tags:

2

Answers


  1. 0

    You can extend org.hibernate.boot.model.naming.PhysicalNamingStrategy and perform the lowercase conversions on both the table and column names. You may also need to override the conversions of catalog, schema, sequence, and type names. You then need to set the hibernate.physical_naming_strategy property to your custom class. See this and this for more information.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search