SQL 序列映射 - SqlServer2012SpatialDialect、Grails 4、Hibernate 5 兼容性

在根据以下文档保存数据条目后，我能够运行应用程序并最终从 SQL Server 序列中检索数据库表 ID：https://discourse.hibernate.org/t/java-lang-classcastexception-java-math-cannot-be-cast-to-java-lang-long-while-starting-server/4074/4

application.yml如下所示：

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

必须在应用中创建必要的 CustomDialect java 类和 SequenceInformationExtractorImpl，如下所示： 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();
    }
}

则应按如下方式配置实体类中的映射：

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

我在build.gradle中将jdbc dependency：替换为以下内容'com.microsoft.sqlserver', name: 'mssql-jdbc',version: '12.2.0.jre11''com.microsoft.sqlserver', name: 'mssql-jdbc',version: '7.2.2.jre11'

休眠版本设置如下： 5.4.25