Hibernate 4 -> 5 migration: NamingStrategy changes, Tables not found

Question

What i want to do

I'm trying to migrate from WildFly 8.2.0 to WildFly 10.0.0 which means that i have (and want) to migrate from Hibernate 4.3 to Hibernate 5.0.

Setup

Java 8u40 
Spring 4.1.9 
SQL Server 2012
Wildfly 8.2.0 -> Wildfly 10.0.0 
Hibernate 4.3.6 -> Hibernate 5.0.7

I have read the migration guide and i'm hit by the Naming Strategy changes. I have read many questions about this on SO, but mine seems a bit different. Hibernate complains that tables are not found:

INFO [o.h.Version] HHH000412: Hibernate Core {5.0.7.Final}
INFO [o.h.cfg.Environment] HHH000206: hibernate.properties not found
INFO [o.h.cfg.Environment] HHH000021: Bytecode provider name : javassist
INFO [o.h.annotations.common.Version] HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
INFO [o.h.dialect.Dialect] HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect
INFO [o.h.envers.boot.internal.EnversServiceImpl] Envers integration enabled? : true
INFO [o.h.validator.internal.util.Version] HV000001: Hibernate Validator 5.2.3.Final
INFO [o.h.tool.hbm2ddl.SchemaValidator] HHH000229: Running schema validator
INFO [o.h.t.s.e.i.InformationExtractorJdbcDatabaseMetaDataImpl] HHH000262: Table not found: SEC_AUTHORIZATION_RULES
INFO [o.h.t.s.e.i.InformationExtractorJdbcDatabaseMetaDataImpl] HHH000262: Table not found: SEC_USER
More tables not found ...
INFO [o.h.hql.internal.QueryTranslatorFactoryInitiator] (ServerService Thread Pool -- 62) HHH000397: Using ASTQueryTranslatorFactory

When i switched to DEBUG logging i saw for example that he is binding the entity to the correct DB table:

DEBUG [o.h.c.a.EntityBinder] Bind entity com.company.user.User on table SEC_USER
DEBUG [o.h.c.Ejb3Column] Binding column: Ejb3Column{table=org.hibernate.mapping.Table(SEC_USER), mappingColumn=ID, insertable=true, updatable=true, unique=false}

What is odd to me is that the app works. After this Table not founds it does not complain that schema is not right. The app works. Selecting, inserting, updating data works.

I have hibernate configured through it's spring-orm abstraction:

@Bean(name = "myEmf")
@DependsOn({"dataSource", "flyway"})
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {
    LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
    em.setDataSource(dataSource());
    em.setPackagesToScan(new String[]{"com.company.**.*"});
    em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
    em.setJpaProperties(additionalProperties());
    return em;
}

private Properties additionalProperties() {
  Properties propFile = propertiesFile();
  properties.setProperty("hibernate.hbm2ddl.auto", "validate");
  properties.setProperty("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect");
  properties.setProperty("hibernate.show_sql", "false");
  properties.setProperty("hibernate.format_sql", "true");
  properties.setProperty("hibernate.id.new_generator_mappings", "false");
  properties.setProperty("hibernate.use_sql_comments", "false");
  properties.setProperty("hibernate.implicit_naming_strategy", "legacy-jpa");
  return properties;
}

In this correspoding entity i have table names and column names named explicitly:

@Entity
@Table(name = "SEC_USER")
public class User extends BaseEntity {

   @Column(name = "LOGIN", nullable = false, unique = true)
   private String login;

Questions

• How to make this table not found log messages disappear?
• Why they are appearing if i have table names explicitly named?
• Why is he not complainging about column names?
• Why is he seemingly working correct?

What i have tried

• Upgrading Spring 4.1.9 to 4.2.5 which says he has support for Hibernate 5
• Set hibernate.implicit_naming_strategy to legacy-jpa according to this

• Set manually the default schema and assigned the role db_owner. Note i never had to do this before with hibernate 4.

• I have debugged hibernate a bit and what i found in the InformationExtractorJdbcDatabaseMetaDataImpl.java that hibernate does not see the catalog (whatever this is) and schema. At leat i think he should see the schema. See screenshot below: catalog and schema are null.

Skink

Answer 1

Solved this by using the newest SQL Server JDBC Driver. A had an old one from 2012. Now i downloaded and used the newest JDBC 4.2 from https://www.microsoft.com/en-us/download/details.aspx?id=11774 (sqljdbc_4.2.6420.100_enu.exe -> sqljdbc42.jar) and it started working. I could even revert the default schema changes for the SQL user.

Skink

Hmm. That really makes no sense. Are you sure you didn't try multiple things at the same time? The SQL-driver version should have nothing to do with this. – Suzettesuzi

Yeah, really. I Reverted all my changes regarding the schema and left only the hibernate.implicit_naming_strategy set and then switched the JDBC driver back and forth and that was it. Maybe stackoverflow.com/users/1025118/vlad-mihalcea will see this thread and comment on it :) – Skink

Answer 2

I was figuring out the same issue, but with jtds driver. After some research, I founded, that hibernate with SqlServerDialect uses sp_tables stored procedure to find tables declaration. In that SP second parameter is schema name, so, if it is null table search works correctly and if it empty string - not.

Hibernate sets this parameter to null in two cases:

• if dialect method getNameQualifierSupport() returns NameQualifierSupport.CATALOG, but all SqlServerDialects return null
• if driver returs false in 'supportsSchemasInTableDefinitions()' method, but jtds returns true.

To solve this, I decided to extend SqlServer2012Dialect end override getNameQualifierSupport() method.

public class SqlServer2012DialectWithCatalogOnly extends SQLServer2012Dialect {
    @Override
    public NameQualifierSupport getNameQualifierSupport() {
        return NameQualifierSupport.CATALOG;
    }
}

and set property hibernate.dialect to new class org.company.SqlServer2012DialectWithCatalogOnly

Hope this helps...

Disparage

Thank you. Fixed my issue with spring data jpa not using the schema in the table name. – Donation

Answer 3

As of Hibernate 5, the logic of NamingStrategy is splitted into two concepts: Implicit and Physical. You've set the implicit naming strategy correctly, but you haven't set the Physical one. Hibernate didn't provide a compatible one, so you have to create one yourself:

public class LegacyPhysicalNamingStrategy implements PhysicalNamingStrategy {
    private final Pattern pattern = Pattern.compile("(\\p{Lower}+)(\\p{Upper}+)");
    public Identifier toPhysicalCatalogName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return convert(name);
    }

    public Identifier toPhysicalSequenceName(Identifier name, JdbcEnvironment jdbcEnvironmen) {
        return convert(name);
    }

    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment jdbcEnvironmen) {
        return convert(name);
    }

    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment jdbcEnvironmen) {
        return convert(name);
    }

    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment jdbcEnvironmen) {
        return convert(name);
    }

    private Identifier convert(Identifier identifier) {
        if (identifier == null || identifier.getText().trim().isEmpty()) {
            return identifier;
        }

        String text = identifier.getText();
        text =  pattern.matcher(text).replaceAll("$1_$2").toLowerCase();
        return Identifier.toIdentifier(text, identifier.isQuoted());
    }

}

Change to using this by setting the property hibernate.physical_naming_strategy to the fully qualified class name of the above class.

Pugliese

I have verified this but that's not it. In my case it seems it was the JDBC Driver. – Skink

Answer 4

I think you'll all be interested to know, I've logged the following ticket:

https://hibernate.atlassian.net/browse/HHH-11424

Updating off JTDS to the microsoft JDBC driver will give you a driver that implements Connection.getSchema() which, obviously, will resolve the issue, as reported.

However, if you do update to that driver, and you rely upon default_schema to effectively change the location tables are referenced from (so that it doesn't look at the current users default 'dbo' schema, for instance) you'll still be out of luck, without providing a customized SchemaNameResolver.

The real bothersome piece of all this is that the implicitSchemaName is never being applied during validation.

Cuddle

Answer 5

I had similar issue and I solved in this way:

jpaProperties.put("hibernate.hbm2ddl.jdbc_metadata_extraction_strategy", "individually")

Criner