-->
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
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
mcburton3
 Post subject: Problems with HQL left join
PostPosted: Sun Apr 27, 2008 6:41 pm 
Beginner
Beginner

Joined: Sun Oct 14, 2007 7:29 pm
Posts: 23
I am having issues with HQL left joins not returning what I expect. I am expecting to get a single record back but I am getting several back in the List, but they all appear to have the same values.

The scenario is this. I have a Usrprofiles table that holds users and their account info. That table is related to a many-to-many table that references which "Compdata" a given "Usrprofile" can access (one user can be allowed to access one or multiple Companies). This many-to-many then has a many-to-one relationsip with the "Compdata" table. The "Compdata" table has a one-to-many relationship with the "Compmsgs" table where messages for a specific Company re stored.

The "Usrprofiles" table also has a one to many relationship to the "Usrmsgs" table where messages for the specific "Usrprofile" are stored.

My HQL query is:
"select usrprof from Usrprofiles as usrprof, Compmsgs as compmsgs, Usrmsgs usrmsgs left join fetch usrprof.compdatas as comps left join fetch usrprof.usrmsgses left join fetch comps.compmsgses where (usrprof.username='johndoe' and compmsgs.viewed=false and usrmsgs.viewed=false) order by comps.companyname asc, compmsgs.logdate desc, usrmsgs.logDate desc"

I am trying to query for an individual "Usrprofile" record and retrieve all the "Compmsgs" to which they should have access (via the "Compdata" table) and all "Usrmsgs" to which they are related. So I am expecting one "Usrprofile" record to be returned with all the Usrmsgs, Compdatas, and related Compmsgs in the object tree. However, I am getting multiple records back and I cannot figure out why.

Version: Hibernate 3.2
Database: DB2 V8.2
Log set to Debug
Mapping docs:
Usrprofiles.hbm.xml:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 25, 2007 11:40:03 PM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.faweb.entities.Usrprofiles" table="USRPROFILES">
        <id name="uid" type="int">
            <column name="UID" />
            <generator class="identity" />
        </id>
        <many-to-one name="contactinfo" class="com.faweb.entities.Contactinfo" fetch="select" >
            <column name="CONTACTID" />
        </many-to-one>
        <many-to-one name="usrroles" class="com.faweb.entities.Usrroles" fetch="select" >
            <column name="ROLE" length="30" not-null="true" />
        </many-to-one>
        <property name="firstname" type="string">
            <column name="FIRSTNAME" length="30" not-null="true" />
        </property>
        <property name="lastname" type="string">
            <column name="LASTNAME" length="30" not-null="true" />
        </property>
        <property name="datecreated" type="timestamp">
            <column name="DATECREATED" length="26" not-null="true" />
        </property>
        <property name="username" type="string">
            <column name="USERNAME" length="60" not-null="true" />
        </property>
        <property name="password" type="string">
            <column name="PASSWORD" length="50" not-null="true" />
        </property>
        <property name="desc" type="string">
            <column name="DESC" length="100" />
        </property>
        <property name="email" type="string">
            <column name="EMAIL" length="60" />
        </property>
        <property name="firstChQ" type="string">
            <column name="FIRST_CH_Q" length="100" />
        </property>
        <property name="firstChA" type="string">
            <column name="FIRST_CH_A" length="40" />
        </property>
        <property name="secChQ" type="string">
            <column name="SEC_CH_Q" length="100" />
        </property>
        <property name="secChA" type="string">
            <column name="SEC_CH_A" length="40" />
        </property>
        <property name="thirdChQ" type="string">
            <column name="THIRD_CH_Q" length="100" />
        </property>
        <property name="thirdChA" type="string">
            <column name="THIRD_CH_A" length="40" />
        </property>
        <property name="active" type="true_false">
            <column name="ACTIVE" length="1" not-null="true" />
        </property>
        <property name="emailSignature" type="string">
            <column name="EMAIL_SIGNATURE" length="150" />
        </property>
        <property name="emailAlertsOk" type="true_false">
            <column name="EMAIL_ALERTS_OK" length="1" />
        </property>
        <property name="lastUpdate" type="timestamp">
            <column name="LAST_UPDATE" length="26" />
        </property>
        <set name="actlogs" inverse="true">
            <key>
                <column name="UID" />
            </key>
            <one-to-many class="com.faweb.entities.Actlog" />
        </set>
        <set name="sesserrses" inverse="true">
            <key>
                <column name="UID" not-null="true" />
            </key>
            <one-to-many class="com.faweb.entities.Sesserrs" />
        </set>
        <set name="emailOutLogs" inverse="true">
            <key>
                <column name="UID" />
            </key>
            <one-to-many class="com.faweb.entities.EmailOutLog" />
        </set>
        <set name="compdatas" lazy="true" inverse="true" table="USRPROFILES_X_COMPDATA">
            <key>
                <column name="UID" not-null="true" />
            </key>
            <many-to-many entity-name="com.faweb.entities.Compdata">
                <column name="COMPANYID" not-null="true" />
            </many-to-many>
        </set>
        <set name="compdeps" inverse="true">
            <key>
                <column name="UID" />
            </key>
            <one-to-many class="com.faweb.entities.Compdep" />
        </set>
        <set name="employeeses" inverse="true">
            <key>
                <column name="UID" />
            </key>
            <one-to-many class="com.faweb.entities.Employees" />
        </set>
        <set name="usrmsgses" lazy="true" inverse="true">
            <key>
                <column name="UID" not-null="true" />
            </key>
            <one-to-many class="com.faweb.entities.Usrmsgs" />
        </set>
    </class>
</hibernate-mapping>


Usrmsgs.hbm.xml:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 25, 2007 11:40:03 PM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.faweb.entities.Usrmsgs" table="USRMSGS">
        <id name="usrmsgsid" type="long">
            <column name="USRMSGSID" />
            <generator class="identity" />
        </id>
        <many-to-one name="msgType" class="com.faweb.entities.MsgType" fetch="select">
            <column name="MSGTYPENUM" />
        </many-to-one>
        <many-to-one name="usrprofiles" class="com.faweb.entities.Usrprofiles" fetch="join">
            <column name="UID" not-null="true" />
        </many-to-one>
        <property name="msg" type="string">
            <column name="MSG" length="32700" />
        </property>
        <property name="logDate" type="date">
            <column name="LOG_DATE" length="10" not-null="true" />
        </property>
        <property name="viewed" type="true_false">
            <column name="VIEWED" length="1" not-null="true" />
        </property>
        <property name="timeViewed" type="timestamp">
            <column name="TIME_VIEWED" length="26" />
        </property>
        <property name="sentBy" type="string">
            <column name="SENT_BY" length="40" />
        </property>
        <set name="emailOutLogs" inverse="true">
            <key>
                <column name="USRMSGSID" />
            </key>
            <one-to-many class="com.faweb.entities.EmailOutLog" />
        </set>
    </class>
</hibernate-mapping>


Compdata.hbm.xml:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Oct 21, 2007 3:32:03 PM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.faweb.entities.Compdata" table="COMPDATA" schema="FAWEB">
        <id name="companyid" type="int">
            <column name="COMPANYID" />
            <generator class="identity" />
        </id>
        <many-to-one name="contactinfo" class="com.faweb.entities.Contactinfo" fetch="select">
            <column name="CONTACTID" />
        </many-to-one>
        <property name="companyname" type="string">
            <column name="COMPANYNAME" length="50" not-null="true" />
        </property>
        <property name="remBal" type="big_decimal">
            <column name="REM_BAL" precision="11" />
        </property>
        <property name="accesscode" type="string">
            <column name="ACCESSCODE" length="50" />
        </property>
        <set name="compmsgses" inverse="true">
            <key>
                <column name="COMPANYID" not-null="true" />
            </key>
            <one-to-many class="com.faweb.entities.Compmsgs"/>
        </set>
        <set name="compdeps" inverse="true">
            <key>
                <column name="COMPANYID" not-null="true" />
            </key>
            <one-to-many class="com.faweb.entities.Compdep" />
        </set>
        <set name="usrprofileses" inverse="true" table="USRPROFILES_X_COMPDATA">
            <key>
                <column name="COMPANYID" not-null="true" />
            </key>
            <many-to-many entity-name="com.faweb.entities.Usrprofiles">
                <column name="UID" not-null="true" />
            </many-to-many>
        </set>
        <set name="fratesconfigs" inverse="true">
            <key>
                <column name="COMPANYID" />
            </key>
            <one-to-many class="com.faweb.entities.Fratesconfig" />
        </set>
        <set name="actlogs" inverse="true">
            <key>
                <column name="COMPANYID" />
            </key>
            <one-to-many class="com.faweb.entities.Actlog" />
        </set>
    </class>
</hibernate-mapping>


Compmsgs.hbm.xml:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 25, 2007 11:40:03 PM by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
    <class name="com.faweb.entities.Compmsgs" table="COMPMSGS">
        <id name="msgnum" type="long">
            <column name="MSGNUM" />
            <generator class="identity" />
        </id>
        <many-to-one name="msgType" class="com.faweb.entities.MsgType" fetch="select">
            <column name="MSGTYPENUM" />
        </many-to-one>
        <many-to-one name="compdata" class="com.faweb.entities.Compdata" fetch="select">
            <column name="COMPANYID" not-null="true" />
        </many-to-one>
        <property name="msg" type="string">
            <column name="MSG" length="32700" />
        </property>
        <property name="logdate" type="date">
            <column name="LOGDATE" length="10" not-null="true" />
        </property>
        <property name="viewed" type="true_false">
            <column name="VIEWED" length="1" not-null="true" />
        </property>
        <property name="timeRead" type="timestamp">
            <column name="TIME_READ" length="26" />
        </property>
        <property name="timeViewed" type="timestamp">
            <column name="TIME_VIEWED" length="26" />
        </property>
        <property name="sentBy" type="string">
            <column name="SENT_BY" length="40" />
        </property>
        <set name="emailOutLogs" inverse="true">
            <key>
                <column name="MSGNUM" />
            </key>
            <one-to-many class="com.faweb.entities.EmailOutLog" />
        </set>
        <set name="noticeses" inverse="true">
            <key>
                <column name="MSGNUM" not-null="true" />
            </key>
            <one-to-many class="com.faweb.entities.Notices" />
        </set>
    </class>
</hibernate-mapping>



Code:
Here is the code I am calling, and I am passing it an HQL string of "select usrprof from Usrprofiles as usrprof, Compmsgs as compmsgs, Usrmsgs usrmsgs left join fetch usrprof.compdatas as comps left join fetch usrprof.usrmsgses left join fetch comps.compmsgses where (usrprof.username='johndoe' and compmsgs.viewed=false and usrmsgs.viewed=false) order by comps.companyname asc, compmsgs.logdate desc, usrmsgs.logDate desc"

Code:
   public com.faweb.entities.Usrprofiles testHQL2(String strQuery)  {
       Logger myLogger = Logger.getLogger("com.faweb.ejbs.ManageUserProfiles");
        myLogger.debug("***FAWEB*** Getting Hibernate session object");
       
        Usrprofiles usrProf = null;  //new Usrprofiles();
       
        myLogger.debug("***FAWEB*** Getting current Hibernate session object");
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();

      
      try {
         
         myLogger.debug("***FAWEB*** Creating Usrprofile object");
         
         myLogger.debug("***FAWEB*** In ManageUserProfiles.getUserProfile(), creating query");
               Query q = session.createQuery(strQuery);
         myLogger.debug("***FAWEB*** In ManageUserProfiles.getUserProfile(), query created");
         myLogger.debug("***FAWEB*** In ManageUserProfiles.getUserProfile(), set string");
            List usrs = q.list();
            myLogger.debug("***FAWEB*** In ManageUserProfiles.getUserProfile(), got users list.");
         myLogger.debug("***FAWEB*** usrs_list is of size = " + usrs.size());
         for (int i = 0; i < usrs.size(); i++) {
               Object obj =  usrs.get(i);
               usrProf = (com.faweb.entities.Usrprofiles) obj;
               System.out.println("**FAWEB** Testing HQL:  obj = " + obj.toString());
            }
           
         return usrProf;
      }

        catch (Exception e) {
         myLogger.error("**FAWEB** Thew Exception e = " + e);
         System.out.println(e);
          return usrProf;
       }
   }





Generated SQL:
Code:
[4/27/08 17:10:01:234 CDT] 000000a4 SystemOut     O Hibernate:
    select
        usrprofile0_.UID as UID296_0_,
        compdata4_.COMPANYID as COMPANYID266_1_,
        compmsgses6_.MSGNUM as MSGNUM269_2_,
        usrmsgses5_.USRMSGSID as USRMSGSID295_3_,
        usrprofile0_.CONTACTID as CONTACTID296_0_,
        usrprofile0_.ROLE as ROLE296_0_,
        usrprofile0_.FIRSTNAME as FIRSTNAME296_0_,
        usrprofile0_.LASTNAME as LASTNAME296_0_,
        usrprofile0_.DATECREATED as DATECREA6_296_0_,
        usrprofile0_.USERNAME as USERNAME296_0_,
        usrprofile0_.PASSWORD as PASSWORD296_0_,
        usrprofile0_.DESC as DESC296_0_,
        usrprofile0_.EMAIL as EMAIL296_0_,
        usrprofile0_.FIRST_CH_Q as FIRST11_296_0_,
        usrprofile0_.FIRST_CH_A as FIRST12_296_0_,
        usrprofile0_.SEC_CH_Q as SEC13_296_0_,
        usrprofile0_.SEC_CH_A as SEC14_296_0_,
        usrprofile0_.THIRD_CH_Q as THIRD15_296_0_,
        usrprofile0_.THIRD_CH_A as THIRD16_296_0_,
        usrprofile0_.ACTIVE as ACTIVE296_0_,
        usrprofile0_.EMAIL_SIGNATURE as EMAIL18_296_0_,
        usrprofile0_.EMAIL_ALERTS_OK as EMAIL19_296_0_,
        usrprofile0_.LAST_UPDATE as LAST20_296_0_,
        compdata4_.CONTACTID as CONTACTID266_1_,
        compdata4_.COMPANYNAME as COMPANYN3_266_1_,
        compdata4_.REM_BAL as REM4_266_1_,
        compdata4_.ACCESSCODE as ACCESSCODE266_1_,
        compdatas3_.UID as UID0__,
        compdatas3_.COMPANYID as COMPANYID0__,
        compmsgses6_.MSGTYPENUM as MSGTYPENUM269_2_,
        compmsgses6_.COMPANYID as COMPANYID269_2_,
        compmsgses6_.MSG as MSG269_2_,
        compmsgses6_.LOGDATE as LOGDATE269_2_,
        compmsgses6_.VIEWED as VIEWED269_2_,
        compmsgses6_.TIME_READ as TIME7_269_2_,
        compmsgses6_.TIME_VIEWED as TIME8_269_2_,
        compmsgses6_.SENT_BY as SENT9_269_2_,
        compmsgses6_.COMPANYID as COMPANYID1__,
        compmsgses6_.MSGNUM as MSGNUM1__,
        usrmsgses5_.MSGTYPENUM as MSGTYPENUM295_3_,
        usrmsgses5_.UID as UID295_3_,
        usrmsgses5_.MSG as MSG295_3_,
        usrmsgses5_.LOG_DATE as LOG5_295_3_,
        usrmsgses5_.VIEWED as VIEWED295_3_,
        usrmsgses5_.TIME_VIEWED as TIME7_295_3_,
        usrmsgses5_.SENT_BY as SENT8_295_3_,
        usrmsgses5_.UID as UID2__,
        usrmsgses5_.USRMSGSID as USRMSGSID2__
    from
        faweb.USRPROFILES usrprofile0_
    left outer join
        faweb.USRPROFILES_X_COMPDATA compdatas3_
            on usrprofile0_.UID=compdatas3_.UID
    left outer join
        FAWEB.COMPDATA compdata4_
            on compdatas3_.COMPANYID=compdata4_.COMPANYID
    left outer join
        faweb.COMPMSGS compmsgses6_
            on compdata4_.COMPANYID=compmsgses6_.COMPANYID
    left outer join
        faweb.USRMSGS usrmsgses5_
            on usrprofile0_.UID=usrmsgses5_.UID,
        faweb.COMPMSGS compmsgs1_,
        faweb.USRMSGS usrmsgs2_
    where
        usrprofile0_.USERNAME='johndoe'
        and compmsgs1_.VIEWED='F'
        and usrmsgs2_.VIEWED='F'
    order by
        compdata4_.COMPANYNAME asc,
        compmsgs1_.LOGDATE desc,
        usrmsgs2_.LOG_DATE desc
[4/27/08 17:10:01:578 CDT] 000000a4 NamingHelper  I org.hibernate.util.NamingHelper getInitialContext JNDI InitialContext properties:{java.naming.provider.url=iiop://localhost:2809/, java.naming.factory.initial=com.ibm.websphere.naming.WsnInitialContextFactory}
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O 17:10:01,609 DEBUG ManageUserProfiles:401 - ***FAWEB*** In ManageUserProfiles.getUserProfile(), got users list.
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O 17:10:01,609 DEBUG ManageUserProfiles:402 - ***FAWEB*** usrs_list is of size = 9
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741
[4/27/08 17:10:01:609 CDT] 000000a4 SystemOut     O **FAWEB** Testing HQL:  obj = com.faweb.entities.Usrprofiles@e3ee3741


Any ideas?

Thank you!

-Matt Burton
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
  [ 1 post ] 

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.