InvalidDataAccessResourceUsageException: could not extract ResultSet

Question

Intro

I have inherited a project and am trying to make changes. The web application works fine until I add the following 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

After adding these, I get the following 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

The code is proprietary so I can't post it all but I can also add that the error takes place in a findAll method invocation that takes a Specification as a param.

1. findAll Invocation

Line where exception is thrown:

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

Which is called this specific JPA method:

2. SQL Statement Displayed by Hibernate

Below is an edited version of the SQL statement with most of the columns removed:

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 )
               )

Any ideas why LOB would be causing this error?

My Current Workaround

My current workaround was to make another entity to hold the LOB, and make a 1 to 1 mapping to the original entity:

In my original entity:

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

My new entity (1 to 1):

@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

SELECT DISTINCT in Oracle 10 requires a UNION and this is not supported for CLOB. You will have to use alternatives to DISTINCT. Additionally do not include the CLOB column in WHERE statements.

Comments

[Menelaos]

Yes... I checked and I did have query.distinct(true); . I will try without distinct. Your source: forum.spring.io/forum/spring-projects/data/… :)

[Suraj Shingade]

Not found working solution for me. Might scenario is different