SpringBoot项目中使用Spring-data-jpa启动时报错Could not fetch the SequenceInformation from the database

怼烎@ 2023-01-13 05:49 13阅读 0赞

在写一个简单的SpringBatch项目时出现了如下报错：

2021-04-16 09:03:17.085 ERROR 26864 --- \[           main\] o.h.e.j.e.internal.JdbcEnvironmentImpl   : Could not fetch the SequenceInformation from the database

java.sql.SQLException: 数字溢出  
    at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139) ~\[ojdbc8-19.8.0.0.jar:19.8.0.0.0\]  
    at oracle.jdbc.driver.NumberCommonAccessor.getLong(NumberCommonAccessor.java:571) ~\[ojdbc8-19.8.0.0.jar:19.8.0.0.0\]  
    at oracle.jdbc.driver.GeneratedStatement.getLong(GeneratedStatement.java:208) ~\[ojdbc8-19.8.0.0.jar:19.8.0.0.0\]  
    at oracle.jdbc.driver.GeneratedScrollableResultSet.getLong(GeneratedScrollableResultSet.java:261) ~\[ojdbc8-19.8.0.0.jar:19.8.0.0.0\]  
    at oracle.jdbc.driver.GeneratedResultSet.getLong(GeneratedResultSet.java:560) ~\[ojdbc8-19.8.0.0.jar:19.8.0.0.0\]  
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.resultSetMinValue(SequenceInformationExtractorLegacyImpl.java:134) ~\[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:60) ~\[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.sequenceInformationList(JdbcEnvironmentImpl.java:403) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.<init>(JdbcEnvironmentImpl.java:268) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:114) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:176) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1224) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1255) \[hibernate-core-5.4.28.Final.jar:5.4.28.Final\]  
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) \[spring-orm-5.3.4.jar:5.3.4\]  
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) \[spring-orm-5.3.4.jar:5.3.4\]  
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) \[spring-orm-5.3.4.jar:5.3.4\]  
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) \[spring-orm-5.3.4.jar:5.3.4\]  
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) \[spring-orm-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~\[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) \[spring-beans-5.3.4.jar:5.3.4\]  
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1153) ~\[spring-context-5.3.4.jar:5.3.4\]  
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:907) ~\[spring-context-5.3.4.jar:5.3.4\]  
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:582) ~\[spring-context-5.3.4.jar:5.3.4\]  
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) ~\[spring-boot-2.4.3.jar:2.4.3\]  
    at com.example.batchprocessing.BatchProcessingApplication.main(BatchProcessingApplication.java:14) ~\[classes/:na\]

经过debug发现是hibernate核心包中的org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl类中的134行报错，该段代码为：

132 protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {
    133		String column = sequenceMinValueColumn();
    134		return column != null ? resultSet.getLong( column ) : null;
    135	}

发现该行代码为一行很简单的获取数据库字段值的代码，进一步向上溯源，发现在

@Override
    public Iterable<SequenceInformation> extractMetadata(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 (SQLException ignore) {
    				}
    			}
    		}
    		finally {
    			try {
    				statement.close();
    			}
    			catch (SQLException ignore) {
    			}
    		}
    	}

发现resultSet获取方式为

final ResultSet resultSet = statement.executeQuery( lookupSql );

而lookupSql获取方式为

final String lookupSql = extractionContext.getJdbcEnvironment().getDialect().getQuerySequencesString();

经分析该sql获取方式与Dialect有关，Dialect可以在springboot项目的application.properties文件中使用spring.jpa.properties.hibernate.dialect属性进行设置。

Debug发现使用的Dialect是Oracle12cDialect，其实看一下这个类的源码会很有意思Oracle12cDialect继承自Oracle10gDialect，而Oracle10gDialect继承自Oracle9iDialect，Oracle9iDialect又继承自Oracle8iDialect，我们要找的getQuerySequencesString()实际返回的值为“select \* from all\_sequences”，这样我们就可以分析出来这段代码的作用是获取数据库中所有的sequence的属性，之所以我们之前项目启动时会报错是因为数据库中存在某一个或者多个的sequence的最大或最小值超过了java中Long类型的范围，导致转换报错。

解决方案（参考[https://stackoverflow.com/questions/58570032/hibernate-could-not-fetch-the-sequenceinformation-from-the-database][https_stackoverflow.com_questions_58570032_hibernate-could-not-fetch-the-sequenceinformation-from-the-database]）

1.新建一个自定义CustomSequenceInformationExtractor

package com.example.batchprocessing.hibernate;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorOracleDatabaseImpl;
    
    public class CustomSequenceInformationExtractor extends SequenceInformationExtractorOracleDatabaseImpl {
    	/**
    	 * Singleton access
    	 */
    	public static final CustomSequenceInformationExtractor INSTANCE = new CustomSequenceInformationExtractor();
    
    	@Override
    	protected Long resultSetMinValue(ResultSet resultSet) throws SQLException {
    		return resultSet.getBigDecimal("min_value").longValue();
    	}
    }

2.新建一个自定义Dialect类CustomOracleDialect

package com.example.batchprocessing.hibernate;
    
    import org.hibernate.dialect.Oracle12cDialect;
    import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;
    
    public class CustomOracleDialect extends Oracle12cDialect {
    	@Override
    	public SequenceInformationExtractor getSequenceInformationExtractor() {
    		return CustomSequenceInformationExtractor.INSTANCE;
    	}
    
    	@Override
    	public String getQuerySequencesString() {
    		return "select * from user_sequences";
    	}
    }

在application.properties文件中指定Dialect类

spring.jpa.properties.hibernate.dialect=com.example.batchprocessing.hibernate.CustomOracleDialect

该种解决方案的原理是在去获取数据库sequnce的最小值时，使用resultSet.getBigDecimal("min\_value").longValue()的方式去获取，这样就可以避免出现数值超出Long范围导致报错的问题。**当然，另一种不更改代码的解决方式就是修改数据库中的sequence定义，将sequence的值设置在Long的范围内**。

[https_stackoverflow.com_questions_58570032_hibernate-could-not-fetch-the-sequenceinformation-from-the-database]: https://stackoverflow.com/questions/58570032/hibernate-could-not-fetch-the-sequenceinformation-from-the-database