Numeric Overflow exception when calling findRevisions() or even custom audit query – spring-data-jpa with oracle

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

Numeric Overflow exception when calling findRevisions() or even custom audit query - spring-data-jpa with oracle

问题

I understand that you want a translation of the provided code and text. Here is the translated code portion:

我已经努力解决异常问题已经有4天，对此感到非常沮丧，所以我提出这个问题，我已经在StackOverflow上参考了各种答案，但似乎没有人遇到与我相同的问题，请不要将其标记为重复。

我使用的是spring data jpa 2.4.4版本，并且在oracle数据库上使用spring data envers和spring boot，我有ojdbc 7作为依赖项。

我的所有表格都使用envers进行审计并自动由配置生成，所以当我尝试获取rev为Number(10)的记录时，我会遇到数值溢出异常，我的rev表格有2列，其中rev是Number(10)数字精度。

REVINFO表格是由spring data envers自动生成的，我没有在我的代码中进行自定义。

REVINFO表格列：
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;

...其他列
}

审计表格 - 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;

Please note that this translation is only for the provided code and does not include the surrounding text.

英文:

I'm struggling with exception since the past 4 days and being completely frustrated with it that I'm asking this question, I have referred various answers already on StackoverOverflow but they dont seem to have the issue as mine, so please don't mark it as duplicate.

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

I have spring data jpa 2.4.4 version and I'm using spring data envers with spring boot on oracle database, I have ojdbc 7 as a dependency.

All my tables are audited using envers and generated automatically by confirguration, so Im facing Numeric overflow exception when Im trying to fetch records whose rev is Number(10), my rev table has 2 columns where rev is a Number(10) digits precision.

REVINFO table is auto generated by spring data envers and i have not customised it in my code.

REVINFO table columns :
REV            NUMBER(10)
REVTIMESTAMP   TIMESTAMP

Main Table - Immigration_file

@Entity
@Audited
@NoArgsConstructor
@AllArgsConstructor
public class ImmigrationFile extends BaseEntity implements ImmigrationFileAware&lt;ImmigrationFile&gt;,Serializable, Cloneable {
@Id
@SequenceGenerator(name = &quot;IMMIGRATION_FILE_ID_GENERATOR&quot;,
sequenceName = &quot;SEQ_IMMIGRATION_FILE_ID&quot;,
allocationSize = 1)
@GeneratedValue(strategy = GenerationType.AUTO, generator = &quot;IMMIGRATION_FILE_ID_GENERATOR&quot;)
@Column(name = &quot;ID&quot;, unique = true, nullable = false)
private Long immigrationFileId;
...other columns
}

Audit table - Immigration_file_hist, having all columns of main table along with REV and REVTYPE, so the error is being thrown when the value of REV is "4686710314".

I have tried both the approached of using RevisionRepository and custom Audit Query and I get the same exception with both of them
code to fetch the revisions of immigration_file_hist repository

@Repository
public interface ImmigrationFileDaoRepository extends RevisionRepository&lt;ImmigrationFile, Long, BigInteger&gt;, JpaRepository&lt;ImmigrationFile, Long&gt; {
Page&lt;Revision&lt;BigInteger, ImmigrationFile&gt;&gt; revisions = immigrationFileDaoRepository.findRevisions(immigrationFile.getImmigrationFileId(), PageRequest.of(Integer.valueOf(startPageIndex), Integer.valueOf(endPageIndex), RevisionSort.desc()));

I was using Long earlier and i have changed it to BigInteger and i still face the same issue.

Custom Audit Query

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

Im getting the below exception :

 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

答案1

得分: 1

A revision number (REV) value should always be increasing and never overflows.

The default implementations provided by Envers use an int data type that has an upper bound of Integer.MAX_VALUE.

Since you have already mentioned that your REV number has the value of 4686710314 and it exceeds this limit, that's why you are getting a Numeric Overflow exception.

To resolve this issue, you can modify the data type of the REV column in the REVINFO table to accommodate larger values. But this is not possible in your case as the REVINFO is auto-generated.

So you should consider using a custom revision entity mapping using a long data type.

Below is the implementation

@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;
    }
}

And then RevisionRepository<ImmigrationFile, Long, Long>

Note: Verify the autogenerated REVINFO table REV column datatype length in Oracle.

英文:

A revision number(REV) value should always be increasing and never overflows.

The default implementations provided by Envers use an int data type that has an upper bound of Integer.MAX_VALUE.

Since you have already mentioned that your REV number has the value of 4686710314 and it exceeds this limit, that's why you are getting a Numeric Overflow exception.

To resolve this issue, you can modify the data type of the REV column in the REVINFO table to accommodate larger values. But this is not possible in your case as the REVINFO is auto-generated.

So you should consider using a custom revision entity mapping using a long data type.

Below is the implementation

@Entity
@Table(name = &quot;REV_INFO&quot;)
@RevisionEntity
public  class CustomRevisionEntity implements Serializable {
private static final long serialVersionUID = 8530213963961662300L;
@Id
@GeneratedValue
@RevisionNumber
@Column(name = &quot;REV&quot;)
private long id;
@RevisionTimestamp
@Column(name = &quot;REVTSTMP&quot;)
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;
}
}

And then RevisionRepository&lt;ImmigrationFile, Long, Long&gt;

Note: Verify the autogenerated REVINFO table REV column datatype length in Oracle.

答案2

得分: 0

不要使用 int 或 long 作为标识符和从数据库检索的数字。很可能您无论如何都不执行任何数学操作。

Oracle以十进制格式存储数字，在Java中对应的类型是 BigDecimal。

您的堆栈跟踪显示：

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

您正在调用 ResultSet 的 getInt()，您应该调用 getBigDecimal()。

英文:

Do not use int or long for identifiers and numbers retrieved from database. Most likely you do not perform any mathematical operations with them anyway.

Oracle stores numbers in decimal format, the corresponding type in Java is BigDecimal.

Your stack trace shows it:

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

You're calling getInt() on ResultSet, you should call getBigDecimal()

huangapple
  • 本文由 发表于 2023年5月22日 02:31:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/76301369.html
  • java
  • oracle11g
  • spring-boot
  • spring-data-envers
  • spring-data-jpa
匿名

发表评论

匿名网友

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

确定