ResultSet extraction failed due to InvalidDataAccessResourceUsageException

Solution 1:
To customize exception translation, if Spring exception translation algorithm maps SQL exception code to the Spring’s exception incorrectly, you can determine the SQL code yourself, as described in 12.2.4 SQLExceptionTranslator.
Solution 2:
It is worth noting that even though you haven’t mentioned it further, you have tagged this question with ‘spring’. As a result, all exceptions thrown will be translated into generic Spring exceptions, eliminating the need to worry about specific DBMS Exceptions.

Question:

Intro

As the new project owner, I am implementing modifications to the web application. However, I am experiencing issues when I include a new field and methods to the Entity.

@Lob
@Column(name = "FIELDS")
private String partnerFields;
public String getPartnerFields() {
    return partnerFields;
}
public void setPartnerFields(String partnerFields) {
    this.partnerFields = partnerFields;
}

Error

Upon inclusion of these, I encounter the subsequent errors.

2016-05-30 15:36:40,550 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] SQL Error: 932, SQLState: 42000
2016-05-30 15:36:40,550 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] ORA-00932: inconsistent datatypes: expected - got CLOB
raised org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:172) ~[spring-orm-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:155) ~[spring-orm-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) ~[spring-orm-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111) ~[spring-data-jpa-1.7.0.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) ~[spring-aop-4.0.7.RELEASE.jar:4.0.7.RELEASE]
    at com.sun.proxy.$Proxy73.findAll(Unknown Source) ~[na:na]

Additional Information

Due to its proprietary nature, the entire code cannot be shared. However, it can be mentioned that the error occurs when a method,

findAll

, is invoked with a parameter of

Specification

.

The call to findAll.

Line where exception is thrown:

return partnerRepository.findAll(PartnerSpecifications.isForSelection(selectionFilter), pageable);

What is the name of this particular JPA method?

The Hibernate framework shows an SQL statement.

Here is a modified SQL statement that has been stripped of the majority of its columns.

SELECT * 
FROM   (SELECT DISTINCT partnerent0_.partner_id                 AS 
                        PARTNER_ID1_16_, 
                        ...
        FROM   partner partnerent0_, 
               partner_id partneride1_ 
        WHERE  partnerent0_.id = partneride1_.id 
               AND ( partnerent0_.status IS NOT NULL ) 
               AND partnerent0_.status <>? 
               AND ( partneride1_.identifier LIKE ? ) 
               AND ( partnerent0_.cat2016 <>?  OR partnerent0_.category_2016 IS NULL )
               )

Can you suggest the possible reason for the error, which is being triggered by the

LOB

?

My Current Workaround

My temporary solution was to create a separate entity to store the LOB and establish a one-to-one correlation with the original entity.

Within my initial object:

 @OneToOne(cascade = CascadeType.ALL, mappedBy = "partner")
    private PartnerFieldsEntity partnerFieldsEntity;

My latest creation, specifically designed for singular interactions:

@Entity
@Table(name="PARTNER_FIELDS")
public class PartnerFieldsEntity{
    @Id
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "PARTNER_ID", nullable = false)
    private PartnerEntity partner;
    @Lob
    @Column(name = "FIELDS")
    private String partnerFields;
    public PartnerEntity getPartner() {
        return partner;
    }
    public void setPartner(PartnerEntity partner) {
        this.partner = partner;
    }
    public String getPartnerFields() {
        return partnerFields;
    }
    public void setPartnerFields(String partnerFields) {
        this.partnerFields = partnerFields;
    }
    public void setPartnerFields(PartnerFieldsWrapper projectFields) {
        setPartnerFields(projectFields.toJson());
    }
}


Solution:

To use

SELECT DISTINCT

in Oracle 10 for

CLOB

, you cannot rely on

UNION

as it is not supported. Instead, consider using other options for

DISTINCT

and avoid including the

CLOB

column in any

WHERE

statements.

Frequently Asked Questions