Wrong hibernate dialect for MSSQL 2014

Question

I have a problem with inserting entities, that use sequences, to a MSSQL 2014 database. I use hibernate that is shipped with Wildfly 10 CR4 (but in CR1 and CR2 I got the same issue).

Here is a general info on the webapp run environment:

1. Wildfly 10 (CR4)
2. Java 8 u 51
3. Windows 7 Proffesional 64bit
4. MSSQL Server 2014
5. MSSQL driver: sqljdbc42.jar is deployed on the application server

My persistence.xml file looks like this:

<persistence-unit name="mb_managed_pu" transaction-type="JTA">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <jta-data-source>java:/jdbc/datasource</jta-data-source>
    <properties>
        <property name="hibernate.archive.autodetection" value="class, hbm" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true" />
        <property name="hibernate.jdbc.batch_size" value="0" />
        <property name="hibernate.default_schema_" value="openmap"/>
        <property name="hibernate.connection.useUnicode" value="yes"/>
        <property name="hibernate.connection.characterEncoding" value="UTF-8"/>
    </properties>
</persistence-unit>

Now here is what happens when I run into an error.

First, when Wildfly is started, I can see this warning:

WARN [org.hibernate.engine.jdbc.dialect.internal.StandardDialectResolver] (ServerService Thread Pool -- 68) HHH000385: Unknown Microsoft SQL Server major version [12] using SQL Server 2000 dialect

I looked through the web and found that this problem is already known since January 2015, but unfortunately it is still an open issue.

The error itself is raised when I try to persist a new entity that has the ID configured to use sequences:

@Id
@Column(name = "MAP_BOOKMARK_ID")
@SequenceGenerator(name = "SEQ_MAP_BOOKMARKS", sequenceName = "SEQ_MAP_BOOKMARKS", allocationSize = 1)
@GeneratedValue(generator = "SEQ_MAP_BOOKMARKS", strategy = GenerationType.SEQUENCE)
private long                    id;

The exception raised is as follows:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name „SEQ_MAP_BOOKMARKS”.

This is not a surprise since hibernate is using the wrong dialect - the one that does not know anything about sequences.

When I modify persistence.xml and add this line:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>

everything works like a charm.

The problem is that the application will also work with Oracle database on another server and on Postgres on another. I'd like to avoid having to prepare multiple versions of the same application.

Does anybody know of a solution to this problem ? Or should I wait for another Wildfly and/or hibernate version to appear ?

Solution 1

Meanwhile the team does not solve this problem, you can create a custom dialect resolver:

public class ScopeStandardDialectResolver implements DialectResolver {


private static final long serialVersionUID = 1L;

    @Override
    public Dialect resolveDialect(DialectResolutionInfo info) {
        Dialect customDialectResolver = customDialectResolver(info);
        Log.getInstance().logInfo(Thread.currentThread().getStackTrace(), customDialectResolver.getClass().getName());
        return customDialectResolver;
    }

    private Dialect customDialectResolver(DialectResolutionInfo info) {
        final String databaseName = info.getDatabaseName();
        final int majorVersion = info.getDatabaseMajorVersion();
        if (isSqlServer2014(databaseName, majorVersion)) {
            return new SQLServer2012Dialect(); 
        } else {
            return StandardDialectResolver.INSTANCE.resolveDialect(info);
        }
    }

    private boolean isSqlServer2014(final String databaseName, final int majorVersion) {
        return databaseName.startsWith("Microsoft SQL Server") && majorVersion == 12;
    }

}

Then you configure in your persistence unit:

<property name="hibernate.dialect_resolvers" value="com.oki.scope.hibernate.ScopeStandardDialectResolver" />

Based in this example: http://blog.exxeta.com/2016/03/23/dynamically-resolve-hibernate-database-dialect/

Solution 2

For Springboot 1.4.7 and lower add below to properties file

spring.jpa.properties.hibernate.dialect =org.hibernate.dialect.SQLServer2012Dialect

Solution 3

I think that your problem could be related to this known issue HHH-9570. (The link actually contains my pull request to my proposed workaround)

Basically, as it happened before with SQL Server 2012, the StandardDialectResolver of Hibernate does not recognise SQL Server 2014 ([Major version 12]) and then the default dialect SQLServerDialect is returned, which is the one for SQL Server 2000