java 使用oracle调用findRevisions()甚至自定义审计查询- spring-data-jpa时出现数值溢出异常

62lalag4  于 2023-05-27  发布在  Java
关注(0)|答案(2)|浏览(158)

我挣扎与异常，因为过去4天，并完全沮丧，我问这个问题，我已经提到了各种答案已经在StackoverOverflow，但他们似乎没有作为我的问题，所以请不要标记为重复。
Hibernate could not fetch the SequenceInformation from the database
我有spring data jpa 2.4.4版本，我在oracle数据库上使用spring data envers和spring Boot ，我有ojdbc 7作为依赖。
我所有的表都使用envers审计，并通过确认自动生成，所以当我试图获取rev为Number（10）的记录时，我面临Numeric溢出异常，我的rev表有2列，其中rev为Number（10）位数精度。
REVINFO表是自动生成的Spring数据envers和我没有定制它在我的代码。

REVINFO table columns :
REV            NUMBER(10)
REVTIMESTAMP   TIMESTAMP

主表- Immigration_file

@Entity
@Audited
@NoArgsConstructor
@AllArgsConstructor
public class ImmigrationFile extends BaseEntity implements ImmigrationFileAware<ImmigrationFile>,Serializable, Cloneable {

    @Id
    @SequenceGenerator(name = "IMMIGRATION_FILE_ID_GENERATOR",
            sequenceName = "SEQ_IMMIGRATION_FILE_ID",
            allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "IMMIGRATION_FILE_ID_GENERATOR")
    @Column(name = "ID", unique = true, nullable = false)
    private Long immigrationFileId;

...other columns
}

审计表- Immigration_file_hist，具有主表的所有列沿着REV和REVTYPE，因此当REV的值为“4686710314”时会抛出错误。
我已经尝试了使用RevisionRepository和自定义Audit Query的方法，并且我使用这两种代码获取immigration_file_hist repository的修订版本时得到了相同的异常

@Repository
public interface ImmigrationFileDaoRepository extends RevisionRepository<ImmigrationFile, Long, BigInteger>, JpaRepository<ImmigrationFile, Long> {

        Page<Revision<BigInteger, ImmigrationFile>> revisions = immigrationFileDaoRepository.findRevisions(immigrationFile.getImmigrationFileId(), PageRequest.of(Integer.valueOf(startPageIndex), Integer.valueOf(endPageIndex), RevisionSort.desc()));

我之前使用的是Long，现在我把它改成了BigInteger，但我仍然面临同样的问题。
自定义审核查询

public List<ImmigrationFile> findRevisions(Long immigrationFileId) {
        AuditReader reader = AuditReaderFactory.get(entityManager);
        List<ImmigrationFile> results = reader
                .createQuery()
                .forRevisionsOfEntity(ImmigrationFile.class, true, false)
                .add(AuditEntity.id().eq(immigrationFileId))
                .addOrder(AuditEntity.property("version").desc())
                .setMaxResults(maxResult)
                .getResultList();
        return results;

我得到了下面的例外：

Caused by: org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:548)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy252.findRevisions(Unknown Source)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
    ... 82 common frames omitted
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.loader.Loader.doList(Loader.java:2853)
    at org.hibernate.loader.Loader.doList(Loader.java:2832)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
    at org.hibernate.loader.Loader.list(Loader.java:2659)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)/
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
    at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.buildAndExecuteQuery(AbstractAuditQuery.java:106)
    at org.hibernate.envers.query.internal.impl.RevisionsOfEntityQuery.getQueryResults(RevisionsOfEntityQuery.java:173)
    at org.hibernate.envers.query.internal.impl.RevisionsOfEntityQuery.list(RevisionsOfEntityQuery.java:136)
    at org.hibernate.envers.query.internal.impl.AbstractAuditQuery.getResultList(AbstractAuditQuery.java:112)
    at org.springframework.data.envers.repository.support.EnversRevisionRepositoryImpl.findRevisions(EnversRevisionRepositoryImpl.java:155)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:524)
    at org.springframework.data.repository.core.support.Reposito.ryComposition.invoke(RepositoryComposition.java:285)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:531)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:156)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:371)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:134)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 97 common frames omitted
Caused by: java.sql.SQLException: Numeric Overflow
    at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139)
    at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:125)
    at oracle.jdbc.driver.GeneratedStatement.getInt(GeneratedStatement.java:199)
    at oracle.jdbc.driver.GeneratedScrollableResultSet.getInt(GeneratedScrollableResultSet.java:246)
    at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:556)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getInt(WSJdbcResultSet.java:1089)
    at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329)
    at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:184)
    at org.hibernate.type.ComponentType.hydrate(ComponentType.java:670)
    at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:803)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:727)
    at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1039)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:990)
    at org.hibernate.loader.Loader.doQuery(Loader.java:959)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2850)
    ... 131 common frames omitted
Java

2条答案

按热度按时间
ars1skjm

ars1skjm1#

修订号（REV）值应始终递增且永不溢出。
Envers提供的默认实现使用int数据类型，其上限为Integer.MAX_VALUE。
由于您已经提到您的REV数字具有4686710314的值，并且它超过了这个限制，这就是为什么您会遇到Numeric Overflow异常。
要解决此问题，可以修改REVINFO表中REV列的数据类型，以容纳更大的值。但在您的情况下这是不可能的，因为REVINFO是自动生成的。
因此，您应该考虑使用long数据类型的自定义修订实体Map。
下面是实施

@Entity
@Table(name = "REV_INFO")
@RevisionEntity
public  class CustomRevisionEntity implements Serializable {

    private static final long serialVersionUID = 8530213963961662300L;

    @Id
    @GeneratedValue
    @RevisionNumber
    @Column(name = "REV")
    private long id;

    @RevisionTimestamp
    @Column(name = "REVTSTMP")
    private long timestamp;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Transient
    public Date getRevisionDate() {
        return new Date( timestamp );
    }

    public long getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(long timestamp) {
        this.timestamp = timestamp;
    }
}

然后是RevisionRepository<ImmigrationFile, Long, Long>
注意：验证Oracle中自动生成的REVINFO表REV列数据类型长度。

赞(0）分享  回复(0）举报  2023-05-27
jdg4fx2g

jdg4fx2g2#

不要使用int或long作为从数据库检索的标识符和数字。最有可能的是，您无论如何都不会对它们执行任何数学运算。
Oracle以十进制格式存储数字，Java中对应的类型是BigDecimal。
您的堆栈跟踪显示它：

at oracle.jdbc.driver.GeneratedResultSet.getInt(GeneratedResultSet.java:556)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getInt(WSJdbcResultSet.java:1089)

你在ResultSet上调用getInt()，你应该调用getBigDecimal()

赞(0）分享  回复(0）举报  2023-05-27
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com