-->
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
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
lavalars
 Post subject: Help with Subquery
PostPosted: Wed Mar 11, 2009 6:54 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
Hello. I'm working with Hibernate 3 and Java 1.4. I have a working subquery in a junit test. The problem comes when I try to run the exact same code in my spring app. I have other hibernate queries and criteria working just fine, and its using the same jar files and the same hibernate.cfg.xml. I've manually checked to make sure the correct files are getting deployed. Any help would be great. Thanks!

Hibernate Mapping

Code:
<hibernate-mapping>
  <class name="com.goldsystems.shs.dataBeans.Client" table="SHS_CLIENTS">
    <id name="clientId" type="int" column="CLI_SEQ">
      <generator class="assigned"/>
    </id>
    <property name="firstName" column="CLI_FNAME"/>
    <property name="lastName" column="CLI_LNAME"/>
    <property name="otherName" column="CLI_OTH_NAME"/>
    <property name="dateOfBirth" column="CLI_DOB" type="date"/>
    <property name="gender" column="CLI_SEX"/>
    <property name="dateModified" column="CLI_DT_CHNG"/>
    <many-to-one name="statusCode" column="CLI_STS_SEQ" class="com.goldsystems.shs.dataBeans.ClientStatusCode"
                 lazy="false" not-found="ignore"/>
    <set name="addresses" table="SHS_CLIENT_ADDRESS">
      <key column="ADD_CLI_SEQ"/>
      <one-to-many class="com.goldsystems.shs.dataBeans.Address" not-found="ignore"/>
    </set>
    <property name="middleName" column="CLI_MNAME"/>
    <many-to-one name="race" column="CLI_RACE_SEQ" class="com.goldsystems.shs.dataBeans.Race" />
    <many-to-one name="ethnicity" column="CLI_ETH_SEQ" class="com.goldsystems.shs.dataBeans.Ethnicity" />
    <property name="dateOpened" column="CLI_DT_OPEN" type="date"/>
    <property name="dateClosed" column="CLI_DT_CLOSED" type="date"/>
    <one-to-one name="programActivity" foreign-key="clientId" class="com.goldsystems.shs.dataBeans.ProgramActivity" lazy="false"/>
  </class>
</hibernate-mapping>

<hibernate-mapping>
  <class name="com.goldsystems.shs.dataBeans.ProgramActivity" table="SHS_PROGRAM_ACTIVITY">
    <id name="programActivityId" type="int" column="PA_SEQ">
      <generator class="assigned"/>
    </id>
    <many-to-one name="referredCode" column="PA_REFX_SEQ" class="com.goldsystems.shs.dataBeans.ReferredCode" lazy="false" />
    <property name="clientId" type="int" column="PA_CLI_SEQ"/>
    <property name="startDate" type="date" column="PA_START_DT" />
    <property name="lastActivityDate" type="date" column="PA_LAST_ACTIVITY_DT" />
    <many-to-one name="clientStatusCode" column="PA_STATUS_CSX_SEQ" class="com.goldsystems.shs.dataBeans.ClientStatusCode"
                 lazy="false"/>
    <property name="closeDate" type="date" column="PA_CLOSE_DT" />
    <property name="staffId" type="int" column="PA_STAFF_SEQ" />
  </class>
</hibernate-mapping>

<hibernate-mapping>
  <class name="com.goldsystems.shs.dataBeans.ReferredCode" table="SHS_REFERRED_CODES">
    <id name="referredCodeId" type="int" column="REFX_SEQ">
      <generator class="assigned"/>
    </id>
    <property name="referredCode" column="REFX_CODE"/>
    <property name="referredCodeDescription" column="REFX_DESC"/>
    <property name="activeFlag" type="boolean" column="REFX_ACT_FLG"/>
    <property name="dateOpened" type="date" column="REFX_DT_OPENED"/>
    <property name="dateClosed" type="date" column="REFX_DT_CLOSED"/>
    <property name="internalProgram" type="int" column="REFX_INTERNAL_PROGRAM"/>
  </class>
</hibernate-mapping>



JUnit

Code:
  private static void buildFancyCritieria() {
    try {
      DetachedCriteria subquery = DetachedCriteria.forClass(ProgramActivity.class, "pa");
      subquery.add(Restrictions.eqProperty("pa.clientId","client.clientId"));
      subquery.createAlias("pa.referredCode","refCode").add(Restrictions.eq("refCode.referredCode","MSD"));
      subquery.setProjection(Projections.id());
      Criteria criteria = HibernateUtils.currentSession().createCriteria(Client.class, "client");
      criteria.add(Subqueries.exists(subquery));
      List result = criteria.list();
...


Code that breaks

Code:
    Paginate result = new Paginate(request, tableName);
    DetachedCriteria subquery = DetachedCriteria.forClass(ProgramActivity.class, "pa");
    subquery.add(Restrictions.eqProperty("pa.clientId", "client.clientId"));
    subquery.createAlias("pa.referredCode", "refCode").add(Restrictions.eq("refCode.referredCode", "MSD"));
    subquery.setProjection(Projections.id());
    Criteria criteria = HibernateUtils.currentSession().createCriteria(Client.class, "client");
    criteria.add(Subqueries.exists(subquery));


I'm totally stumped. :(
Top
 Profile  
 
lavalars
 Post subject: Exception
PostPosted: Wed Mar 11, 2009 6:56 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
I forgot to mention the exception that is thrown is:

Code:
org.hibernate.exception.SQLGrammarException: could not execute query


And here are the last lines in the log....

Code:
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - start
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - end
Hibernate: select county0_.CNTYX_SEQ as CNTYX1_5_, county0_.CNTYX_CODE as CNTYX2_5_, county0_.CNTYX_NAME as CNTYX3_5_ from SHS_COUNTY_CODES county0_ order by county0_.CNTYX_NAME
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - start
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - end
Hibernate: select clientstat0_.CSX_SEQ as CSX1_4_, clientstat0_.CSX_CODE as CSX2_4_, clientstat0_.CSX_DESC as CSX3_4_, clientstat0_.CSX_MASTER_FLG as CSX4_4_ from SHS_CLIENT_STATUS_CODES clientstat0_ where clientstat0_.CSX_MASTER_FLG=1 order by clientstat0_.CSX_DESC
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - start
[http-8080-Processor25] DEBUG com.goldsystems.utils.HibernateUtils - currentSession() - end
Hibernate: select * from ( select this_.CLI_SEQ as CLI1_3_1_, this_.CLI_FNAME as CLI2_3_1_, this_.CLI_LNAME as CLI3_3_1_, this_.CLI_OTH_NAME as CLI4_3_1_, this_.CLI_DOB as CLI5_3_1_, this_.CLI_SEX as CLI6_3_1_, this_.CLI_DT_CHNG as CLI7_3_1_, this_.CLI_STS_SEQ as CLI8_3_1_, this_.CLI_MNAME as CLI9_3_1_, this_.CLI_RACE_SEQ as CLI10_3_1_, this_.CLI_ETH_SEQ as CLI11_3_1_, this_.CLI_DT_OPEN as CLI12_3_1_, this_.CLI_DT_CLOSED as CLI13_3_1_, programact2_.PA_SEQ as PA1_8_0_, programact2_.PA_REFX_SEQ as PA2_8_0_, programact2_.PA_CLI_SEQ as PA3_8_0_, programact2_.PA_START_DT as PA4_8_0_, programact2_.PA_LAST_ACTIVITY_DT as PA5_8_0_, programact2_.PA_STATUS_CSX_SEQ as PA6_8_0_, programact2_.PA_CLOSE_DT as PA7_8_0_, programact2_.PA_STAFF_SEQ as PA8_8_0_ from SHS_CLIENTS this_ left outer join SHS_PROGRAM_ACTIVITY programact2_ on this_.CLI_SEQ=programact2_.PA_SEQ where exists (select this0__.PA_SEQ as y0_ from SHS_PROGRAM_ACTIVITY this0__ where this0__.PA_CLI_SEQ=this_.CLI_SEQ and refcode1_.REFX_CODE=?) order by this_.CLI_SEQ asc ) where rownum <= ?
[http-8080-Processor25] WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 904, SQLState: 42000
[http-8080-Processor25] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00904: "REFCODE1_"."REFX_CODE": invalid identifier
Top
 Profile  
 
lavalars
 Post subject: HQL Differences
PostPosted: Thu Mar 12, 2009 12:27 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
When I compare the HQL there are differences between them, but I still haven't found out why that is....

Working HQL
Code:
                           select this_.CLI_SEQ as CLI1_3_1_, this_.CLI_FNAME as CLI2_3_1_, this_.CLI_LNAME as CLI3_3_1_, this_.CLI_OTH_NAME as CLI4_3_1_, this_.CLI_DOB as CLI5_3_1_, this_.CLI_SEX as CLI6_3_1_, this_.CLI_DT_CHNG as CLI7_3_1_, this_.CLI_STS_SEQ as CLI8_3_1_, this_.CLI_MNAME as CLI9_3_1_, this_.CLI_RACE_SEQ as CLI10_3_1_, this_.CLI_ETH_SEQ as CLI11_3_1_, this_.CLI_DT_OPEN as CLI12_3_1_, this_.CLI_DT_CLOSED as CLI13_3_1_, programact2_.PA_SEQ as PA1_8_0_, programact2_.PA_REFX_SEQ as PA2_8_0_, programact2_.PA_CLI_SEQ as PA3_8_0_, programact2_.PA_START_DT as PA4_8_0_, programact2_.PA_LAST_ACTIVITY_DT as PA5_8_0_, programact2_.PA_STATUS_CSX_SEQ as PA6_8_0_, programact2_.PA_CLOSE_DT as PA7_8_0_, programact2_.PA_STAFF_SEQ as PA8_8_0_ from SHS_CLIENTS this_ left outer join SHS_PROGRAM_ACTIVITY programact2_ on this_.CLI_SEQ=programact2_.PA_SEQ where exists (select pa_.PA_SEQ as y0_ from SHS_PROGRAM_ACTIVITY pa_ inner join SHS_REFERRED_CODES refcode1_ on pa_.PA_REFX_SEQ=refcode1_.REFX_SEQ where pa_.PA_CLI_SEQ=this_.CLI_SEQ and refcode1_.REFX_CODE=?)



Non-Working HQL

Code:
select * from ( select this_.CLI_SEQ as CLI1_3_1_, this_.CLI_FNAME as CLI2_3_1_, this_.CLI_LNAME as CLI3_3_1_, this_.CLI_OTH_NAME as CLI4_3_1_, this_.CLI_DOB as CLI5_3_1_, this_.CLI_SEX as CLI6_3_1_, this_.CLI_DT_CHNG as CLI7_3_1_, this_.CLI_STS_SEQ as CLI8_3_1_, this_.CLI_MNAME as CLI9_3_1_, this_.CLI_RACE_SEQ as CLI10_3_1_, this_.CLI_ETH_SEQ as CLI11_3_1_, this_.CLI_DT_OPEN as CLI12_3_1_, this_.CLI_DT_CLOSED as CLI13_3_1_, programact2_.PA_SEQ as PA1_8_0_, programact2_.PA_REFX_SEQ as PA2_8_0_, programact2_.PA_CLI_SEQ as PA3_8_0_, programact2_.PA_START_DT as PA4_8_0_, programact2_.PA_LAST_ACTIVITY_DT as PA5_8_0_, programact2_.PA_STATUS_CSX_SEQ as PA6_8_0_, programact2_.PA_CLOSE_DT as PA7_8_0_, programact2_.PA_STAFF_SEQ as PA8_8_0_ from SHS_CLIENTS this_ left outer join SHS_PROGRAM_ACTIVITY programact2_ on this_.CLI_SEQ=programact2_.PA_SEQ where exists (select this0__.PA_CLI_SEQ as y0_ from SHS_PROGRAM_ACTIVITY this0__ where this0__.PA_CLI_SEQ=this_.CLI_SEQ and refcode1_.REFX_CODE=?) order by this_.CLI_SEQ asc ) where rownum <= ?
Top
 Profile  
 
latha1119
 Post subject:
PostPosted: Thu Mar 12, 2009 12:42 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
I think there is an issue on this out there already.
http://opensource.atlassian.com/project ... e/HHH-2847

if you read the entire thread, it has a solution also.
hope this helps,
latha.
Top
 Profile  
 
lavalars
 Post subject: Hibernate bug?
PostPosted: Thu Mar 12, 2009 1:18 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
So I read through that thread and the line code that is the "solution" is the same as the line with the problem. I'm wondering if maybe it was lost in translation. I also found:

http://opensource.atlassian.com/project ... se/HHH-952

Which describes the bug, but is marked as resolved.

And then:

http://opensource.atlassian.com/project ... e/HHH-3534

Which explains the odd subquery aliases and seems to explain why it doesn't work. The biggest mystery to me now is why does it work in my JUnit but not in my web app?

:(
Top
 Profile  
 
latha1119
 Post subject:
PostPosted: Thu Mar 12, 2009 1:29 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
I cannot answer why it works in your JUnit test and not in the App. May be someone could.
Based on the Thread
http://opensource.atlassian.com/project ... e/HHH-2847

I would attempt to take out the alias name from the subquery and see if it works fine in both JUnit test as well as the App.
//get rid of the alias in this line. I did not run a JUnit test to even know if taking out the alias would work or not.

subquery.createAlias("pa.referredCode", "refCode").add(Restrictions.eq("refCode.referredCode", "MSD"));

Hope this helps,
Latha.
Top
 Profile  
 
lavalars
 Post subject:
PostPosted: Thu Mar 12, 2009 1:41 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
It didn't change anything except for the field name that was in the exception:

New Code
Code:

    DetachedCriteria subquery = DetachedCriteria.forClass(ProgramActivity.class);
    subquery.add(Restrictions.eqProperty("clientId","client.clientId"));
    subquery.createCriteria("referredCode").add(Restrictions.eq("referredCode","MSD"));
    subquery.setProjection(Projections.projectionList()
      .add(Projections.property("clientId")));
    Criteria criteria = HibernateUtils.currentSession().createCriteria(Client.class, "client");
    criteria.add(Subqueries.propertyEq("client.clientId", subquery));


New Exception
Code:
[http-8080-Processor20] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00904: "REFERREDCO1_"."REFX_CODE": invalid identifier


HQL
Code:
select * from ( select this_.CLI_SEQ as CLI1_3_1_, this_.CLI_FNAME as CLI2_3_1_, this_.CLI_LNAME as CLI3_3_1_, this_.CLI_OTH_NAME as CLI4_3_1_, this_.CLI_DOB as CLI5_3_1_, this_.CLI_SEX as CLI6_3_1_, this_.CLI_DT_CHNG as CLI7_3_1_, this_.CLI_STS_SEQ as CLI8_3_1_, this_.CLI_MNAME as CLI9_3_1_, this_.CLI_RACE_SEQ as CLI10_3_1_, this_.CLI_ETH_SEQ as CLI11_3_1_, this_.CLI_DT_OPEN as CLI12_3_1_, this_.CLI_DT_CLOSED as CLI13_3_1_, programact2_.PA_SEQ as PA1_8_0_, programact2_.PA_REFX_SEQ as PA2_8_0_, programact2_.PA_CLI_SEQ as PA3_8_0_, programact2_.PA_START_DT as PA4_8_0_, programact2_.PA_LAST_ACTIVITY_DT as PA5_8_0_, programact2_.PA_STATUS_CSX_SEQ as PA6_8_0_, programact2_.PA_CLOSE_DT as PA7_8_0_, programact2_.PA_STAFF_SEQ as PA8_8_0_ from SHS_CLIENTS this_ left outer join SHS_PROGRAM_ACTIVITY programact2_ on this_.CLI_SEQ=programact2_.PA_SEQ where this_.CLI_SEQ = (select this0__.PA_CLI_SEQ as y0_ from SHS_PROGRAM_ACTIVITY this0__ where this0__.PA_CLI_SEQ=this_.CLI_SEQ and referredco1_.REFX_CODE=?) order by this_.CLI_SEQ asc ) where rownum <= ?


I'm going to keep on this until I figure out the inconsistent results. I'm also going to install an older hibernate 3 (3.2.5?) and see if the problem is happening there as well.

Thanks...
Top
 Profile  
 
lavalars
 Post subject: The solution:
PostPosted: Thu Mar 12, 2009 2:18 pm 
Newbie

Joined: Wed Mar 11, 2009 6:40 pm
Posts: 6
So, I found the solution. The problem was that someone had checked a hibernate.jar into the project (prior to my time) and there was also a hibernate3.jar which was the latest release. The JUnit and Web App used different jars apparently, and the issue really is fixed in 3.3.1. Whew. Thanks for the help... :)

Just as a side note, the subquery does work equally well with or without the aliases on the subquery.
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
  [ 8 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.