无法将Microsoft SQL Server连接到Spring Boot应用程序。

huangapple go评论70阅读模式
英文:

Unable to Connect Microsoft SQL Server to Spring Boot Application

问题

我面临着这个问题:

我正在使用 SpringBoot 应用程序,有两个数据源,一个是 MySQL,另一个是 Microsoft SQL Server,都是使用 JPA 进行操作。这些数据库位于某个远程服务器/机器上。

以下是异常信息部分:

org.springframework.beans.factory.BeanCreationException: 在类路径资源 [org/my/in/config/SecondDBConfig.class] 中定义的名为 'secondEntityManagerFactory' 的 Bean 创建失败: 调用初始化方法失败; 嵌套异常是 javax.persistence.PersistenceException: [PersistenceUnit: default] 无法构建 Hibernate SessionFactory; 嵌套异常是 java.lang.ClassCastException: 无法将 java.lang.Integer 强制转换为 java.lang.Long
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1794) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	...

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] 无法构建 Hibernate SessionFactory; 嵌套异常是 java.lang.ClassCastException: 无法将 java.lang.Integer 强制转换为 java.lang.Long
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	...

Caused by: java.lang.ClassCastException: 无法将 java.lang.Integer 强制转换为 java.lang.Long
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2151) ~[mssql-jdbc-6.4.0.jre8.jar:na]
	...
英文:

I am facing this issue:

I am Using SpringBoot Application and have 2 Data Sources one is MySQl and second Microsoft SQL Server

Using JPA

This Database is present in some Remote Server/Machine.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'secondEntityManagerFactory' defined in class path resource [org/my/in/config/SecondDBConfig.class]: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1794) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1109) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:869) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551) ~[spring-context-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143) ~[spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.3.3.RELEASE.jar:2.3.3.RELEASE]
	at org.my.in.MyApplication.main(MyApplication.java:10) [classes/:na]
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:403) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1853) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1790) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	... 17 common frames omitted
Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2151) ~[mssql-jdbc-6.4.0.jre8.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getLong(HikariProxyResultSet.java) ~[HikariCP-3.4.5.jar:na]
	at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetStartValueSize(SequenceInformationExtractorLegacyImpl.java:129) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:59) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:65) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.<init>(DatabaseInformationImpl.java:59) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:155) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:96) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259) ~[hibernate-core-5.4.20.Final.jar:5.4.20.Final]
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) ~[spring-orm-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	... 21 common frames omitted


答案1

得分: 1

以下是翻译好的内容:

这是一个关于 Hibernate 中的已知问题(hubernate)。

他们找到的解决方法是创建一个自定义的方言(非常奇怪...)

package ....;

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorNoOpImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;

/**
 * 用于处理 SEQUENCE 模式查询结果处理问题的自定义 SQLServer 方言
 */
public class SQLServerCustomDialect extends SQLServer2012Dialect {

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

}

还需要一个稍微尴尬的适配器类来在 int 和 long 之间正确转换值。

/*
 * Hibernate,用于习惯性 Java 的关系持久性
 *
 * 许可证:GNU Lesser General Public License (LGPL),版本 2.1 或更高版本。
 * 请参阅根目录中的 lgpl.txt 文件或 <http://www.gnu.org/licenses/lgpl-2.1.html>。
 *
 * 改编自 Hibernate 发行版中同名的类。
 */

package ...;

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;

/**
 * 作者:Steve Ebersole
 */
public class SequenceInformationExtractorImpl implements SequenceInformationExtractor {
  /**
   * 单例访问
   */
  public static final SequenceInformationExtractorImpl INSTANCE = new SequenceInformationExtractorImpl();

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

    // *应该*永远不会发生,但为了安全起见,考虑到性能...
    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) {
      }
    }
  }

  // ...
}
英文:

This is a known bug in hubernate

The solution they found was to create a custom dialect (oddly enough...)

package ....;
import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorNoOpImpl;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;
/**
* Custom SQLServer dialect to deal with SEQUENCE schema query result processing issue
*/
public class SQLServerCustomDialect extends SQLServer2012Dialect {
@Override
public SequenceInformationExtractor getSequenceInformationExtractor() {
if ( getQuerySequencesString() == null ) {
return SequenceInformationExtractorNoOpImpl.INSTANCE;
}
else {
return SequenceInformationExtractorImpl.INSTANCE;
}
}
}

With a slightly awkward adapter class to properly convert the value between int and long

/*
* Hibernate, Relational Persistence for Idiomatic Java
*
* License: GNU Lesser General Public License (LGPL), version 2.1 or later.
* See the lgpl.txt file in the root directory or &lt;http://www.gnu.org/licenses/lgpl-2.1.html&gt;.
*
* Adapted from the class with the same name in the Hibernate distribution.
*/
package ...;
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&lt;SequenceInformation&gt; 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&lt;SequenceInformation&gt; sequenceInformationList = new ArrayList&lt;&gt;();
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 &quot;sequence_name&quot;;
}
protected String sequenceCatalogColumn() {
return &quot;sequence_catalog&quot;;
}
protected String sequenceSchemaColumn() {
return &quot;sequence_schema&quot;;
}
protected String sequenceStartValueColumn() {
return &quot;start_value&quot;;
}
protected String sequenceMinValueColumn() {
return &quot;minimum_value&quot;;
}
protected String sequenceMaxValueColumn() {
return &quot;maximum_value&quot;;
}
protected String sequenceIncrementColumn() {
return &quot;increment&quot;;
}
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();
}
}

huangapple
  • 本文由 发表于 2020年9月7日 18:21:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/63775678.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定