Hibernate naming strategy for MariaDB (UPPER_SNAKE_CASE)

Question

In my application I'm using flyway for data migration, therefore I have defined all tables with all names in UPPER_SNAKE_CASE (ex. users -> USERS, candidateGroups -> CANDIDATE_GROUP). I have provided 2 configurations for different environments: local and docker. On local environment I'm running h2, on Docker I'm running with MariaDB.

Now while running on h2 everything works fine, on MariaDB hibernate is attempts to perform operations on all lower case tables, this results in error:

Caused by: java.sql.SQLException: Table 'application.users' doesn't exist

After some investigations i have tried adding to my application-docker.yml:

  spring:
    jpa:
     hibernate:
       naming:
         physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

This results in capitalization of table names in hibernate: Caused by: java.sql.SQLException: Table 'application.Users' doesn't exist

I know I can name all tables with annotation @Table(name = "USERS")and provide names for all columns as well, but this is not my desired solution.

Unfortunately I haven't found any strategy matching my case (I know this shows how to create custom naming strategy but I don't think my case is that uncommon), what naming strategy should I use for my MariaDB config to match my case?

The correct way to fix this problem is in fact to use the annotations which tell Hibernate what the table name is. You could also use the old school XML `hbm` configuration files for this. — Tim Biegeleisen, Oct 01 '19 at 15:33
@TimBiegeleisen but then why is it working with h2? It must be using some kind of other strategy, which means other solution than annotations is possible, do you know maybe what solution is h2 using to resolve names? — Akka Jaworek, Oct 01 '19 at 15:44
H2 isn't doing anything, it is Hibernate which is defaulting to something. The thing is, every database is different about case sensitivity. MySQL is case sensitive by default on Linux, but not on Windows. H2 is supposedly case sensitive everywhere. Regardless, you should not rely on default behavior, because it could break at some point in the future. — Tim Biegeleisen, Oct 01 '19 at 15:46
your explanation this seams reasonable, nevertheless if hibernate is using some default strategy in case of h2, then if i enforce this strategy as global for all types of database then it should work right? or am i missing something? — Akka Jaworek, Oct 01 '19 at 15:53

MartinBG · Accepted Answer · 2020-01-16T17:30:12.583

There's no UPPER_SNAKE_CASE naming strategy neither in Hibernate or Spring Data JPA, but one could define his/her own by extending PhysicalNamingStrategyStandardImpl or SpringPhysicalNamingStrategy.

It is easier in Spring Data JPA as Spring already converts names to snake_case:

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;

import java.io.Serializable;
import java.util.Locale;

public class UpperSnakeCaseSpringPhysicalNamingStrategyImpl extends SpringPhysicalNamingStrategy implements Serializable {

    @Override
    protected Identifier getIdentifier(String name, boolean quoted, JdbcEnvironment jdbcEnvironment) {
        name = name.toUpperCase(Locale.ROOT);
        return new Identifier(name, quoted);
    }
}

For Hibernate implementation of the conversion to snake_case and override of at least 2 of the 5 methods is required:

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

import java.io.Serializable;
import java.util.Locale;

public class UpperSnakeCasePhysicalNamingStrategyImpl extends PhysicalNamingStrategyStandardImpl implements Serializable {

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        return new Identifier(addUnderscores(name.getText()), name.isQuoted());
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        return new Identifier(addUnderscores(name.getText()), name.isQuoted());
    }

    private static String addUnderscores(String name) {
        final StringBuilder buf = new StringBuilder(name.replace('.', '_'));
        for (int i = 1; i < buf.length() - 1; i++) {
            if (
                    Character.isLowerCase(buf.charAt(i - 1)) &&
                            Character.isUpperCase(buf.charAt(i)) &&
                            Character.isLowerCase(buf.charAt(i + 1))
            ) {
                buf.insert(i++, '_');
            }
        }
        return buf.toString().toUpperCase(Locale.ROOT);
    }
}

Finally the customized naming strategy should be set in application.properties or application.yml:

spring.jpa.hibernate.naming.physical-strategy=my.package.UpperSnakeCaseSpringPhysicalNamingStrategyImpl

More information on SO:

Update (16.01.2020)

Naming Strategies in Hibernate 5

Hibernate naming strategy for MariaDB (UPPER_SNAKE_CASE)

1 Answers1

Linked