Announcement Announcement Module
Collapse
No announcement yet.
wrong count query for Entities with EmbeddedID in Spring Data and Hibernate 4.0.1 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • wrong count query for Entities with EmbeddedID in Spring Data and Hibernate 4.0.1

    Hello,

    It seems that Spring Data (spring-data-jpa-1.0.2.RELEASE) and Hibernate 4.0.1 do not work right when Spring Data generates count query for custom method and Entity with EmbeddedId.
    Programdescription class is an Entity with EmbeddedId.

    Here is the example:

    Code:
    CriteriaBuilder cb = em.getCriteriaBuilder();
    		CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    		Root<Programdescription> r = cq.from(Programdescription.class);
    		
    		cq.where(r.get("programdescription").isNotNull());
    		
    		cq.select(cb.count(r));
    generates this query:
    Code:
    select count(*) as col_0_0_ from programDescription programdes0_ where programdes0_.programDescription is not null limit ?
    which is right according to https://hibernate.onjira.com/browse/HHH-5419

    But when Spring data is used with following custom method:

    Code:
    programdescriptionRepository.findProgramNull(new PageRequest(1, 10) );
    Spring Data with Hibernate generate this SQL which causes exception (please, check count statement in select clause - all fields from EmbeddedId):
    Code:
    select count((programdes0_.descriptionCulture, programdes0_.programDescriptionType, programdes0_.programId, programdes0_.sourceId)) as col_0_0_ from programDescription programdes0_ where programdes0_.programDescription is not null
    However this call
    Code:
    programdescriptionRepository.count();
    generates query with count(*) (which is desirable)

    Here is the Query definition for findProgramNull method:
    Code:
    @NamedQuery(name="Programdescription.findProgramNull", query="SELECT pd.programdescriptionid.programid FROM Programdescription pd WHERE pd.programdescription is not null ")

    I do not know exactly where the problem is, but it looks like Hibernate generates right query when used alone (see QueryBuilder example at the top of the post)

    Here is the stack trace after calling the method from the Spring Data repository

    Code:
    xception in thread "main" org.springframework.dao.DataIntegrityViolationException: Operand should contain 1 column(s); SQL [n/a]; nested exception is org.hibernate.exception.DataException: Operand should contain 1 column(s)
    	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
    	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:102)
    	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:368)
    	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
    	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    	at $Proxy39.findProgramNull(Unknown Source)
    	at com.cyandia.dms.dataprovider.rovi.service.impl.ProgramServiceImpl.getDetails(ProgramServiceImpl.java:131)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:597)
    	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    	at $Proxy71.getDetails(Unknown Source)
    	at XXXXXXX.DAOTest.main(DAOTest.java:88)
    Caused by: org.hibernate.exception.DataException: Operand should contain 1 column(s)
    	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:134)
    	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    	at $Proxy78.executeQuery(Unknown Source)
    	at org.hibernate.loader.Loader.getResultSet(Loader.java:1978)
    	at org.hibernate.loader.Loader.doQuery(Loader.java:829)
    	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    	at org.hibernate.loader.Loader.doList(Loader.java:2463)
    	at org.hibernate.loader.Loader.doList(Loader.java:2449)
    	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279)
    	at org.hibernate.loader.Loader.list(Loader.java:2274)
    	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115)
    	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:252)
    	at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:99)
    	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:54)
    	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:93)
    	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:83)
    	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:343)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    	... 16 more
    Caused by: java.sql.SQLException: Operand should contain 1 column(s)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    	at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:597)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    	... 40 more
    UPDATE:

    after performing some checks I found that hibernate still translates queries like (which is sent from Spring Data)
    Code:
    SELECT count(pd) FROM Programdescription pd WHERE pd.programdescription is not null
    into SQL with all fields from EmbeddedID in the count statement

    Cheers
    A.
    Last edited by sejmajnejm; Jan 26th, 2012, 07:48 AM.
Working...
X