-->
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
SonicWave
 Post subject: Filtering Associations With WHERE clause in <SET>
PostPosted: Wed Sep 14, 2005 6:34 am 
Newbie

Joined: Tue Aug 30, 2005 10:01 pm
Posts: 2
Good day,

The following shows the associations between Orgrel (which store the relationship between OrgUnit) and OrgUnit (Organization Unit). When a OrgUnit is deleted, the status is marked with 'D' as logical delete. Orgrel contains the defination of the relationship between OrgUnit with parentid and childid stored. Records with parentid as NULL, it's a root OrgUnit.

As example, I tried attribute where="COMMON.ORG_UNIT.STATUS != 'A'" to filter record with the status not equal to A, it return an error when it's executed.

    How can I retrieve a list of Orgrel, with the OrgUnit.Status <> 'D' ?
    WHERE and SUBSELECT attributes only accept SQL ? What about HQL ?

I tried the following workaround in my java code:

Code:
criteriaDetails = criteria.createCriteria("orgUnit")
               .add(Expression.not(Expression.eq(OrgUnit.PROP_STATUS, OrgUnit.STS_DELETE)))
               .addOrder(Order.asc(OrgUnit.PROP_ORG_NAME));
         
for (int iX = 1; iX < 7; iX++)
{
   criteria = criteria.createCriteria("details");
   
   criteria.createCriteria("orgUnit")
            .add(Expression.not(Expression.eq(OrgUnit.PROP_STATUS, OrgUnit.STS_DELETE)))
            .addOrder(Order.asc(OrgUnit.PROP_ORG_NAME));
   

}


As shown in the code, it will do a 7-level filtering on the Orgrel with OrgUnit status. But, if the level of the hierachy is less than 7, the record would be filtered off.

    Is there any way to modify the code, so that the all records from level-1 to level-7 will be included ?

Thanks.

Boo Ching Hau

Hibernate version:3

Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="my.com.common.orgunit.db" schema="COMMON">
   <class name="Orgrel" table="ORGREL">
      <id
         column="ORGCNBR"
         name="Orgcnbr"
         type="integer"
      >
      </id>
      <property
         column="ORGPNBR"
         length="10"
         name="Orgpnbr"
         not-null="false"
         type="integer"
       />
      
      <one-to-one name="orgUnit" class="OrgUnit">
      </one-to-one>
      
      <set name="details" inverse="true" where="COMMON.ORG_UNIT.STATUS != 'A'">
         <key>
            <column name="ORGPNBR" />
         </key>
         <one-to-many class="Orgrel"/>
      </set>   
   </class>
</hibernate-mapping>


Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="my.com.common.orgunit.db" schema="COMMON">
   <class name="OrgUnit" table="ORG_UNIT">
      <id
         column="ORG_ID"
         name="Id"
         type="integer"
      >
         <generator class="increment" />
      </id>
      <property
         column="EPF_NO"
         length="15"
         name="EpfNo"
         not-null="false"
         type="string"
       />
      <property
         column="CITY"
         length="50"
         name="City"
         not-null="false"
         type="string"
       />
      <property
         column="DATE_CREATED"
         length="26"
         name="DateCreated"
         not-null="false"
         type="timestamp"
       />
      <property
         column="CREATED_BY"
         length="12"
         name="CreatedBy"
         not-null="false"
         type="string"
       />
      <property
         column="MODIFY_DATE"
         length="26"
         name="ModifyDate"
         not-null="false"
         type="timestamp"
       />
      <property
         column="STATUS"
         length="1"
         name="Status"
         not-null="false"
         type="string"
       />
      <property
         column="CONTACT_PERSON"
         length="100"
         name="ContactPerson"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_PATH"
         length="100"
         name="OrgPath"
         not-null="false"
         type="string"
       />
      <property
         column="COMPANY_NO"
         length="15"
         name="CompanyNo"
         not-null="false"
         type="string"
       />
      <property
         column="APPROVED_DATE"
         length="26"
         name="ApprovedDate"
         not-null="false"
         type="timestamp"
       />
      <property
         column="REGION_CODE"
         length="15"
         name="RegionCode"
         not-null="false"
         type="string"
       />
      <property
         column="POSTCODE"
         length="6"
         name="Postcode"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_NAME"
         length="254"
         name="OrgName"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_HEAD3"
         length="12"
         name="OrgHead3"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_CODE"
         length="12"
         name="OrgCode"
         not-null="false"
         type="string"
       />
      <property
         column="APPROVED_BY"
         length="12"
         name="ApprovedBy"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_HEAD2"
         length="12"
         name="OrgHead2"
         not-null="false"
         type="string"
       />
      <property
         column="ADDR2"
         length="50"
         name="Addr2"
         not-null="false"
         type="string"
       />
      <property
         column="CTRY_CODE"
         length="5"
         name="CtryCode"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_HEAD1"
         length="12"
         name="OrgHead1"
         not-null="false"
         type="string"
       />
      <property
         column="ADDR1"
         length="50"
         name="Addr1"
         not-null="false"
         type="string"
       />
      <property
         column="STATE"
         length="20"
         name="State"
         not-null="false"
         type="string"
       />
      <property
         column="SOCSO_NO"
         length="15"
         name="SocsoNo"
         not-null="false"
         type="string"
       />
      <property
         column="TAX_NO"
         length="15"
         name="TaxNo"
         not-null="false"
         type="string"
       />
      <property
         column="ORG_TYPE"
         length="10"
         name="OrgType"
         not-null="false"
         type="integer"
       />
      <property
         column="PHONE"
         length="15"
         name="Phone"
         not-null="false"
         type="string"
       />
      <property
         column="MODIFY_BY"
         length="12"
         name="ModifyBy"
         not-null="false"
         type="string"
       />
      <property
         column="FAX"
         length="15"
         name="Fax"
         not-null="false"
         type="string"
       />
      
      <one-to-one name="type"
            class="OrgType"
            foreign-key="OrgType" />
                  
   </class>
</hibernate-mapping>


Name and version of the database you are using:
IBM DB2 v8.1

The generated SQL (show_sql=true):
Code:
select details0_.ORGPNBR as ORGPNBR3_, details0_.ORGCNBR as ORGCNBR3_, details0_.ORGCNBR as ORGCNBR2_, details0_.ORGPNBR as ORGPNBR38_2_, orgunit1_.ORG_ID as ORG1_0_, orgunit1_.EPF_NO as EPF2_37_0_, orgunit1_.CITY as CITY37_0_, orgunit1_.DATE_CREATED as DATE4_37_0_, orgunit1_.CREATED_BY as CREATED5_37_0_, orgunit1_.MODIFY_DATE as MODIFY6_37_0_, orgunit1_.STATUS as STATUS37_0_, orgunit1_.CONTACT_PERSON as CONTACT8_37_0_, orgunit1_.ORG_PATH as ORG9_37_0_, orgunit1_.COMPANY_NO as COMPANY10_37_0_, orgunit1_.APPROVED_DATE as APPROVED11_37_0_, orgunit1_.REGION_CODE as REGION12_37_0_, orgunit1_.POSTCODE as POSTCODE37_0_, orgunit1_.ORG_NAME as ORG14_37_0_, orgunit1_.ORG_HEAD3 as ORG15_37_0_, orgunit1_.ORG_CODE as ORG16_37_0_, orgunit1_.APPROVED_BY as APPROVED17_37_0_, orgunit1_.ORG_HEAD2 as ORG18_37_0_, orgunit1_.ADDR2 as ADDR19_37_0_, orgunit1_.CTRY_CODE as CTRY20_37_0_, orgunit1_.ORG_HEAD1 as ORG21_37_0_, orgunit1_.ADDR1 as ADDR22_37_0_, orgunit1_.STATE as STATE37_0_, orgunit1_.SOCSO_NO as SOCSO24_37_0_, orgunit1_.TAX_NO as TAX25_37_0_, orgunit1_.ORG_TYPE as ORG26_37_0_, orgunit1_.PHONE as PHONE37_0_, orgunit1_.MODIFY_BY as MODIFY28_37_0_, orgunit1_.FAX as FAX37_0_, orgtype2_.ORG_TYPE_ID as ORG1_1_, orgtype2_.ORG_TYPE_DESC as ORG2_39_1_ from COMMON.ORGREL details0_ left outer join COMMON.ORG_UNIT orgunit1_ on details0_.ORGCNBR=orgunit1_.ORG_ID left outer join COMMON.ORG_TYPE orgtype2_ on orgunit1_.ORG_ID=orgtype2_.ORG_TYPE_ID where  COMMON.ORG_UNIT.STATUS = 'A'


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.