SQL Sequence mapping - SqlServer2012SpatialDialect, Grails 4, Hibernate 5 compatibility

Question

So far in the configuration file (application.yml) hibernate-dialect has been set as follows:

dataSource:
   pooled: true
   jmxExport: true
   driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
   dialect: org.hibernate.spatial.dialect.sqlserver.SqlServer2008SpatialDialect

Everything works perfectly fine, however due to SQL Sequences mapping for some Classes (DB Tables) we have to upgrade dialect to SqlServer2012SpatialDialect (e.g Sequence in Sql Server 2014 and Hibernate) and there is a number of docus how can I map SQL Sequence in the Class (e.g https://www.linkedin.com/pulse/hibernate-sql-sequence-avital-arvivo).

The thing is Grails4 is compatible starting from hibernate-spatial 5.4.0 and I am not able to make app running as I am getting following error each time when I test with another hibernate-spatial 5.* version and SqlServer2012SpatialDialect:

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean
with name 'hibernateDatastore': Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap')
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:304)
at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:285)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1340)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1186)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:277)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.addCandidateEntry(DefaultListableBeanFactory.java:1471)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1435)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveMultipleBeans(DefaultListableBeanFactory.java:1303)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1213)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1175)
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredMethodElement.inject(AutowiredAnnotationBeanPostProcessor.java:670)
... 109 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap')
at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:184)
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:117)
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:300)
... 125 common frames omitted
Caused by: java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader 'bootstrap')
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2328)
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetStartValueSize(SequenceInformationExtractorLegacyImpl.java:129)
at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:59)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59)
at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:155)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:314)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
at org.grails.orm.hibernate.cfg.HibernateMappingContextConfiguration.buildSessionFactory(HibernateMappingContextConfiguration.java:287)
at org.grails.orm.hibernate.connections.HibernateConnectionSourceFactory.create(HibernateConnectionSourceFactory.java:86)
at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:39)
at org.grails.orm.hibernate.connections.AbstractHibernateConnectionSourceFactory.create(AbstractHibernateConnectionSourceFactory.java:23)
at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:64)
at org.grails.datastore.mapping.core.connections.AbstractConnectionSourceFactory.create(AbstractConnectionSourceFactory.java:52)
at org.grails.datastore.mapping.core.connections.ConnectionSourcesInitializer.create(ConnectionSourcesInitializer.groovy:24)
at org.grails.orm.hibernate.HibernateDatastore.<init>(HibernateDatastore.java:212)
at jdk.internal.reflect.GeneratedConstructorAccessor95.newInstance(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:172)
... 127 common frames omitted
> Task :bootRun FAILED

Is there any step that I am missing in order to properly map SQL Sequence or just SqlServer2012SpatialDialect should I extend or assign in an another way?

sql server: Microsoft SQL Server 2019
gradle: 'com.microsoft.sqlserver', name: 'mssql-jdbc',version: '12.2.0.jre11'

Answer 1

I was able to run app and eventually retrieve the database table ID from SQL Server sequence after saving the data entry based on following documentation: https://discourse.hibernate.org/t/java-lang-classcastexception-java-math-cannot-be-cast-to-java-lang-long-while-starting-server/4074/4

application.yml looks as follows:

dataSource:
   pooled: true
   jmxExport: true
   driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
   dialect:  dialect.CustomSqlServer2012SpatialDialect
   dbCreate: update

The neccessary CustomDialect java Class and SequenceInformationExtractorImpl have to be created in your app as follows: CustomSqlServer2012SpatialDialect

import org.hibernate.spatial.dialect.sqlserver.SqlServer2012SpatialDialect;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorNoOpImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;

public class CustomSqlServer2012SpatialDialect extends SqlServer2012SpatialDialect {

    @Override
    public SequenceInformationExtractor getSequenceInformationExtractor() {
        if ( getQuerySequencesString() == null ) {
            return SequenceInformationExtractorNoOpImpl.INSTANCE;
        }
        else {
            return SequenceInformationExtractorImpl.INSTANCE;
        }
    }

}

SequenceInformationExtractorImpl

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.boot.model.relational.QualifiedSequenceName;
import org.hibernate.engine.jdbc.env.spi.IdentifierHelper;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorNoOpImpl;
import org.hibernate.tool.schema.extract.internal.SequenceInformationImpl;
import org.hibernate.tool.schema.extract.spi.ExtractionContext;
import org.hibernate.tool.schema.extract.spi.SequenceInformation;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;

/**
 * @author Steve Ebersole
 */
public class SequenceInformationExtractorImpl implements SequenceInformationExtractor {
    /**
     * Singleton access
     */
    public static final SequenceInformationExtractorImpl INSTANCE = new SequenceInformationExtractorImpl();

    @Override
    public Iterable<SequenceInformation> extractMetadata(final ExtractionContext extractionContext) throws SQLException {
        final String lookupSql = extractionContext.getJdbcEnvironment().getDialect().getQuerySequencesString();

        // *should* never happen, but to be safe in the interest of performance...
        if ( lookupSql == null ) {
            return SequenceInformationExtractorNoOpImpl.INSTANCE.extractMetadata( extractionContext );
        }

        final IdentifierHelper identifierHelper = extractionContext.getJdbcEnvironment().getIdentifierHelper();
        final Statement statement = extractionContext.getJdbcConnection().createStatement();
        try {
            final ResultSet resultSet = statement.executeQuery( lookupSql );
            try {
                final List<SequenceInformation> sequenceInformationList = new ArrayList<>();
                while ( resultSet.next() ) {
                    sequenceInformationList.add(
                            new SequenceInformationImpl(
                                    new QualifiedSequenceName(
                                            identifierHelper.toIdentifier(
                                                    resultSetCatalogName( resultSet )
                                            ),
                                            identifierHelper.toIdentifier(
                                                    resultSetSchemaName( resultSet )
                                            ),
                                            identifierHelper.toIdentifier(
                                                    resultSetSequenceName( resultSet )
                                            )
                                    ),
                                    resultSetStartValueSize( resultSet ),
                                    resultSetMinValue( resultSet ),
                                    resultSetMaxValue( resultSet ),
                                    resultSetIncrementValue( resultSet )
                            )
                    );
                }
                return sequenceInformationList;
            }
            finally {
                try {
                    resultSet.close();
                }
                catch (final SQLException ignore) {
                }
            }
        }
        finally {
            try {
                statement.close();
            }
            catch (final SQLException ignore) {
            }
        }
    }

    protected String sequenceNameColumn() {
        return "sequence_name";
    }

    protected String sequenceCatalogColumn() {
        return "sequence_catalog";
    }

    protected String sequenceSchemaColumn() {
        return "sequence_schema";
    }

    protected String sequenceStartValueColumn() {
        return "start_value";
    }

    protected String sequenceMinValueColumn() {
        return "minimum_value";
    }

    protected String sequenceMaxValueColumn() {
        return "maximum_value";
    }

    protected String sequenceIncrementColumn() {
        return "increment";
    }

    protected String resultSetSequenceName(final ResultSet resultSet) throws SQLException {
        return resultSet.getString( sequenceNameColumn() );
    }

    protected String resultSetCatalogName(final ResultSet resultSet) throws SQLException {
        final String column = sequenceCatalogColumn();
        return column != null ? resultSet.getString( column ) : null;
    }

    protected String resultSetSchemaName(final ResultSet resultSet) throws SQLException {
        final String column = sequenceSchemaColumn();
        return column != null ? resultSet.getString( column ) : null;
    }

    protected Long resultSetStartValueSize(final ResultSet resultSet) throws SQLException {
        final String column = sequenceStartValueColumn();
        return column != null ? resultSetGetAsLong( resultSet, column ) : null;
    }

    protected Long resultSetMinValue(final ResultSet resultSet) throws SQLException {
        final String column = sequenceMinValueColumn();
        return column != null ? resultSetGetAsLong( resultSet, column ) : null;
    }

    protected Long resultSetMaxValue(final ResultSet resultSet) throws SQLException {
        final String column = sequenceMaxValueColumn();
        return column != null ? resultSetGetAsLong( resultSet, column ) : null;
    }

    protected Long resultSetIncrementValue(final ResultSet resultSet) throws SQLException {
        final String column = sequenceIncrementColumn();
        return column != null ? resultSetGetAsLong( resultSet, column ) : null;
    }

    private Long resultSetGetAsLong(final ResultSet resultSet, final String column) throws SQLException {
        final Object value = resultSet.getObject(column);
        if (value == null || !(value instanceof Number)) {
            return null;
        }
        return ((Number)value).longValue();
    }
}

then the mapping in the Entity Class should be configured as follows:

static mapping = {
    id column: "ObjectId" // the column name in the database table
    id generator: "sequence",params: [sequence:"dbo.seq_NAME",sequenceName:"dbo.seq_NAME"]
}

I replaced jdbc dependency: 'com.microsoft.sqlserver', name: 'mssql-jdbc',version: '12.2.0.jre11' with the following 'com.microsoft.sqlserver', name: 'mssql-jdbc',version: '7.2.2.jre11' in build.gradle

hibernate version is set as follows: 5.4.25