How to apply @Table annotation with different names based on some Spring condition?

Question

Problem: Reserved keywords

I have an entity class named User for historical reasons which Hibernate calculates a table name of user by default. That name is a reserved SQL keyword and breaks without quoting at least on Postgres, while it does work on other RDBMS like DB2i. Additionally, DB2i not only uses that unquoted name, but because it's unquoted it applies upper-case name-folding as well. This means in production some tables named USER already exists. While Postgres doesn't support this concrete table name without quoting, it was able to create other tables, but applies lower-case name-folding instead, so naming scheme of tables differs between RDBMS currently.

Differently cased names vs. quoting

And this is where things become difficult: In theory the best approach si to simply quote all identifiers always, but that wasn't the case right from the start, so I now have lower- and upper-case table names, depending on RDBMS used. This means quoting the value to user fails on DB2i, but works on Postgres. Quoting the value to USER works on DB2i, but at least looks strange on Postgres because the other lower-case names.

Conditionally use different names?

I'm aware of many other possible solutions to the problem like quoting and renaming everything, renaming the one table only to user_account, implement different custom naming strategies etc. But what I would like to know instead is if it's possible to apply two different @Table annotations to the same class, depending on some Spring property. Something like the following, only with one and the same class:

@ConditionalOnProperty(
  value="spring.jpa.properties.hibernate.dialect",
  havingValue = "com.ibm.as400.access.AS400JDBCDriver"
  matchIfMissing = true)
@Table(name = "\"user\"")
public class User extends BusinessEntity {

vs.

@ConditionalOnProperty(
  value="spring.jpa.properties.hibernate.dialect",
  havingValue = "com.ibm.as400.access.AS400JDBCDriver"
  matchIfMissing = false)
@Table(name = "\"USER\"")
public class User extends BusinessEntity {

In the example above, the @ConditionalOnProperty targets the class, not the @Table. I would like to have the @Table depend on the property instead.

So, is something like that possible somehow?

Thanks!

Answer 1

There are a couple of options which might resolve your case (all of them are actually poor cause the best option is to rename tables):

1. set hibernate.auto_quote_keyword to true as it was suggested by Mark Rotteveel

2. take advantage of physical naming strategies, for example:

public class ReservedWordAwareCamelCaseToUnderscoresNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        Identifier suggested = super.toPhysicalTableName(name, jdbcEnvironment);
        Dialect dialect = jdbcEnvironment.getDialect();
        if (...) {
            return ...
        }
        return suggested;
    }

}

3. take advantage of Integrator SPI:

public class LegacyUserTableNameIntegrator implements Integrator {

    @Override
    public void integrate(Metadata metadata, SessionFactoryImplementor sessionFactory, SessionFactoryServiceRegistry serviceRegistry) {
        Dialect dialect = serviceRegistry.getService(JdbcEnvironment.class).getDialect();
        for (PersistentClass persistentClass : metadata.getEntityBindings()) {
            if (persistentClass.getMappedClass() == User.class) {
                persistentClass.getRootClass().getTable().setName("...");
            }


        }
    }

    ...

}