-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
ThomasBecker
 Post subject: Native SQL issue with subselect mapping
PostPosted: Mon Aug 11, 2008 10:14 am 
Newbie

Joined: Mon Aug 11, 2008 10:05 am
Posts: 2
Location: Germany
Hi everyone,

I've an issue with a native sql named query and custom mapping I need due to missing object relations in the model.
I'm stuck in debugging, searching the board, google, etc.

The problem is that the subselects I've got in my select clause don't get injected into the created objects.

Here's the mapping first:

Code:
   <sql-query name="SQLSelectItemToContentGroupPlusDownlodsAndReleaseDateOrderByArtist">
       <return alias="itemToContentGroupExtended" class="net.netm.me.core.model.ItemToContentGroupExtended">
          <return-property name="itemToContentGroupId" column="icg_sid"/>
           <return-property name="position" column="icg_position"/>
           <return-property name="title" column="itm_name1"/>
           <return-property name="artist" column="itm_name2"/>
           <return-property name="itemId" column="itm_sid"/>
           <return-property name="itemOrderId" column="itm_orderid"/>
           <return-property name="itemMasterOrderId" column="itm_masterorderid"/>
           <return-property name="itemUpdateDate" column="itm_updatedate"/>
           <return-property name="contentGroupId" column="ctg_sid"/>
           <return-property name="contentTypeKey" column="cty_key"/>
           <return-property name="contentTypeName" column="cty_name"/>
           <return-property name="contentImportDate" column="cti_importdate"/>
           <return-property name="downloads7" column="cln_downloads7"/>
           <return-property name="downloads30" column="cln_downloads30"/>
           <return-property name="downloads90" column="cln_downloads90"/>
       </return>
       SELECT DISTINCT itemtocontentgroup.icg_sid, itemtocontentgroup.icg_position, item.itm_sid, item.itm_orderid,
          item.itm_masterorderid, item.itm_name2, item.itm_name1, item.itm_updatedate, contentgroup.ctg_sid, contenttype.cty_key,
          contenttype.cty_name, contentimport.cti_importdate,
          (SELECT SUM(imc_counter) FROM itemcounter
             WHERE itemcounter.itm_sid2 = item.itm_sid
              AND imc_date > current_date - 7
          ) AS cln_downloads7,
          (SELECT SUM(imc_counter) FROM itemcounter
              WHERE itemcounter.itm_sid2 = item.itm_sid
              AND imc_date > current_date - 30
          ) AS cln_downloads30,
          (SELECT SUM(imc_counter) FROM itemcounter
              WHERE itemcounter.itm_sid2 = item.itm_sid
              AND imc_date > current_date - 90
          ) AS cln_downloads90
      FROM bla
      JOIN somejoins
      WHERE whereclause
   </sql-query>


A snipplet of the pojo:

Code:
public class ItemToContentGroupExtended {

   private static final long serialVersionUID = -2465392632324750424L;
   
   private Long itemToContentGroupId;
   private String title;
   private String artist;
   private int position;
   private Long itemId;
   private Long itemOrderId;
   private Long itemMasterOrderId;
   private Long contentGroupId;
   private String contentTypeKey;
   private String contentTypeName;
   private Date contentImportDate;
   private int downloads7;
   private int downloads30;
   private int downloads90;

   /**
    * @return the downloads7
    */
   public int getDownloads7() {
      return downloads7;
   }

   /**
    * @param downloads7 the downloads7 to set
    */
   public void setDownloads7(int downloads7) {
      this.downloads7 = downloads7;
   }

   /**
    * @return the downloads30
    */
   public int getDownloads30() {
      return downloads30;
   }

   /**
    * @param downloads30 the downloads30 to set
    */
   public void setDownloads30(int downloads30) {
      this.downloads30 = downloads30;
   }

   /**
    * @return the downloads90
    */
   public int getDownloads90() {
      return downloads90;
   }

   /**
    * @param downloads90 the downloads90 to set
    */
   public void setDownloads90(int downloads90) {
      this.downloads90 = downloads90;
   }


The query is send as it should to the database (jpda debugger and p6spy are showing that the exact right query is sent to the db). And the desired objects are created with the correct values. Except for the download* fields which all stay unpopulated. The setter for download* are never called by hibernate.
If I execute the query manually I get all results with the aggregated sums of the download* columns.

Is this a known limitation? I checked the mappings and everything again and again. Tried renaming the columns, object properties, etc. But I'm not getting it running.

Would be great if anyone's got a hint for me. I will go on searching for a solution in the meanwhile.

Thanks a lot!
Thomas
Top
 Profile  
 
ThomasBecker
 Post subject:
PostPosted: Tue Aug 12, 2008 5:26 am 
Newbie

Joined: Mon Aug 11, 2008 10:05 am
Posts: 2
Location: Germany
Tried to rewrite the query inline in the JavaCode instead of in the mapping file:

Code:
            String queryString = "SELECT DISTINCT itemtocontentgroup.icg_sid as {itcg.itemToContentGroupId}, itemtocontentgroup.icg_position as {itcg.position}, " +
                  "item.itm_sid as {itcg.itemId}, item.itm_orderid as {itcg.itemOrderId}, item.itm_masterorderid as {itcg.itemMasterOrderId}, item.itm_name2 as {itcg.artist}, " +
                  "item.itm_name1 as {itcg.title}, item.itm_updatedate, contentgroup.ctg_sid as {itcg.contentGroupId}, contenttype.cty_key as {itcg.contentTypeKey}, " +
                  "contenttype.cty_name as {itcg.contentTypeName}, contentimport.cti_importdate as {itcg.contentImportDate}, " +
                  "(SELECT SUM(imc_counter) " +
                     "FROM itemcounter " +
                     "WHERE itemcounter.itm_sid2 = item.itm_sid " +
                     "AND imc_date > current_date - 7) AS {itcg.downloads7}, " +
                  "(SELECT SUM(imc_counter) " +
                     "FROM itemcounter " +
                     "WHERE itemcounter.itm_sid2 = item.itm_sid " +
                     "AND imc_date > current_date - 30) AS {itcg.downloads30}, " +
                  "(SELECT SUM(imc_counter) " +
                     "FROM itemcounter " +
                     "WHERE itemcounter.itm_sid2 = item.itm_sid " +
                     "AND imc_date > current_date - 90) AS {itcg.downloads90} " +
                  "FROM itemtocontentgroup " +
                  "JOIN item ON item.itm_sid = itemtocontentgroup.itm_sid " +
                  "JOIN contentimport ON item.cti_sid = contentimport.cti_sid " +
                  "JOIN contentgroup ON contentgroup.ctg_sid = itemtocontentgroup.ctg_sid " +
                  "JOIN contenttype ON contentgroup.cty_sid = contenttype.cty_sid " +
                  "WHERE itemtocontentgroup.ctg_sid=:contentGroupId " +
                  "ORDER BY " + orderByQueryString;
            Query q = session.createSQLQuery(queryString).addEntity("itcg", ItemToContentGroupExtended.class);
            q.setLong("contentGroupId", contentGroupId);


Now I'm getting the following exception message:

Code:
org.springframework.orm.hibernate3.HibernateQueryException: No column name found for property [itemMasterOrderId] for alias [itcg] [SELECT DISTINCT itemtocontentgroup.icg_sid as {itcg.itemToContentGroupId}, itemtocontentgroup.icg_position as {itcg.position}, item.itm_sid as {itcg.itemId}, item.itm_orderid as {itcg.itemOrderId}, item.itm_masterorderid as {itcg.itemMasterOrderId}, item.itm_name2 as {itcg.artist}, item.itm_name1 as {itcg.title}, item.itm_updatedate, contentgroup.ctg_sid as {itcg.contentGroupId}, contenttype.cty_key as {itcg.contentTypeKey}, contenttype.cty_name as {itcg.contentTypeName}, contentimport.cti_importdate as {itcg.contentImportDate}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 7) AS {itcg.downloads7}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 30) AS {itcg.downloads30}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 90) AS {itcg.downloads90} FROM itemtocontentgroup JOIN item ON item.itm_sid = itemtocontentgroup.itm_sid JOIN contentimport ON item.cti_sid = contentimport.cti_sid JOIN contentgroup ON contentgroup.ctg_sid = itemtocontentgroup.ctg_sid JOIN contenttype ON contentgroup.cty_sid = contenttype.cty_sid WHERE itemtocontentgroup.ctg_sid=:contentGroupId ORDER BY downloads90]; nested exception is org.hibernate.QueryException: No column name found for property [itemMasterOrderId] for alias [itcg] [SELECT DISTINCT itemtocontentgroup.icg_sid as {itcg.itemToContentGroupId}, itemtocontentgroup.icg_position as {itcg.position}, item.itm_sid as {itcg.itemId}, item.itm_orderid as {itcg.itemOrderId}, item.itm_masterorderid as {itcg.itemMasterOrderId}, item.itm_name2 as {itcg.artist}, item.itm_name1 as {itcg.title}, item.itm_updatedate, contentgroup.ctg_sid as {itcg.contentGroupId}, contenttype.cty_key as {itcg.contentTypeKey}, contenttype.cty_name as {itcg.contentTypeName}, contentimport.cti_importdate as {itcg.contentImportDate}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 7) AS {itcg.downloads7}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 30) AS {itcg.downloads30}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 90) AS {itcg.downloads90} FROM itemtocontentgroup JOIN item ON item.itm_sid = itemtocontentgroup.itm_sid JOIN contentimport ON item.cti_sid = contentimport.cti_sid JOIN contentgroup ON contentgroup.ctg_sid = itemtocontentgroup.ctg_sid JOIN contenttype ON contentgroup.cty_sid = contenttype.cty_sid WHERE itemtocontentgroup.ctg_sid=:contentGroupId ORDER BY downloads90]
   at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
   at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
   at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
   at net.netm.me.core.dao.hibernate.ItemToContentGroupDaoHibernate.findItemToContentGroupAndDownloadsAndReleaseDateByContentGroupId(ItemToContentGroupDaoHibernate.java:117)
   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:310)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at org.springframework.aop.interceptor.AbstractTraceInterceptor.invoke(AbstractTraceInterceptor.java:113)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy53.findItemToContentGroupAndDownloadsAndReleaseDateByContentGroupId(Unknown Source)
   at net.netm.me.core.dao.ItemToContentGroupDaoTest.testFindItemToContentGroupAndDownloadsAndReleaseDateByContentGroupId(ItemToContentGroupDaoTest.java:51)
   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 junit.framework.TestCase.runTest(TestCase.java:168)
   at junit.framework.TestCase.runBare(TestCase.java:134)
   at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
   at junit.framework.TestResult$1.protect(TestResult.java:110)
   at junit.framework.TestResult.runProtected(TestResult.java:128)
   at junit.framework.TestResult.run(TestResult.java:113)
   at junit.framework.TestCase.run(TestCase.java:124)
   at junit.framework.TestSuite.runTest(TestSuite.java:232)
   at junit.framework.TestSuite.run(TestSuite.java:227)
   at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: org.hibernate.QueryException: No column name found for property [itemMasterOrderId] for alias [itcg] [SELECT DISTINCT itemtocontentgroup.icg_sid as {itcg.itemToContentGroupId}, itemtocontentgroup.icg_position as {itcg.position}, item.itm_sid as {itcg.itemId}, item.itm_orderid as {itcg.itemOrderId}, item.itm_masterorderid as {itcg.itemMasterOrderId}, item.itm_name2 as {itcg.artist}, item.itm_name1 as {itcg.title}, item.itm_updatedate, contentgroup.ctg_sid as {itcg.contentGroupId}, contenttype.cty_key as {itcg.contentTypeKey}, contenttype.cty_name as {itcg.contentTypeName}, contentimport.cti_importdate as {itcg.contentImportDate}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 7) AS {itcg.downloads7}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 30) AS {itcg.downloads30}, (SELECT SUM(imc_counter) FROM itemcounter WHERE itemcounter.itm_sid2 = item.itm_sid AND imc_date > current_date - 90) AS {itcg.downloads90} FROM itemtocontentgroup JOIN item ON item.itm_sid = itemtocontentgroup.itm_sid JOIN contentimport ON item.cti_sid = contentimport.cti_sid JOIN contentgroup ON contentgroup.ctg_sid = itemtocontentgroup.ctg_sid JOIN contenttype ON contentgroup.cty_sid = contenttype.cty_sid WHERE itemtocontentgroup.ctg_sid=:contentGroupId ORDER BY downloads90]
   at org.hibernate.loader.custom.SQLQueryParser.resolveProperties(SQLQueryParser.java:262)
   at org.hibernate.loader.custom.SQLQueryParser.substituteBrackets(SQLQueryParser.java:149)
   at org.hibernate.loader.custom.SQLQueryParser.process(SQLQueryParser.java:85)
   at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:157)
   at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:20)
   at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:113)
   at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:137)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
   at net.netm.me.core.dao.hibernate.ItemToContentGroupDaoHibernate$2.doInHibernate(ItemToContentGroupDaoHibernate.java:163)
   at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
   ... 34 more


Here's the full ItemToContentGroupExtended.class:
Code:
public class ItemToContentGroupExtended {

   private static final long serialVersionUID = -2465392632324750424L;
   
   private Long itemToContentGroupId;
   private String title;
   private String artist;
   private int position;
   private Long itemId;
   private Long itemOrderId;
   private Long itemMasterOrderId;
   private Long contentGroupId;
   private String contentTypeKey;
   private String contentTypeName;
   private Date contentImportDate;
   private int downloads7;
   private int downloads30;
   private int downloads90;
...getters/setters few logic ...
}


itemMasterOrderId property is there. Still I'm getting this exception. Cleaned my compiled classes and rebuild them again, still having the issue and the more I try, the more clueless I get. :(
If I remove item.masterorderid from the select I get the same exception for the download fields. So maybe I'm getting something wrong with the mappings?!
itemMasterOrderId is an int4 column in the db btw.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.