Hibernate version:2.1.3

Mapping documents:

<hibernate-mapping>
<class table="ORGANIZATION" name="enet_read_full.generated.model.ORGANIZATION" where="(end_datetime is null or end_datetime > sysdate) and (expiry_date is null or expiry_date > sysdate)">





Name and version of the database you are using:Oracle 8i


where condition doesn't filter out the data, there is no explanation in document, any solution as our retreival is totally based on these hbm.xml

show generated SQL

Hibernate: select organizati0_.ID as ID0_, organizati0_.END_REASON_DESC as END_R
EAS2_0_, organizati0_.CREATE_USER as CREATE_U3_0_, organizati0_.DESCRIPTION as D
ESCRIPT4_0_, organizati0_.UPDATE_DATETIME as UPDATE_D5_0_, organizati0_.ORGN_OWN
ERSHIP_TYPE_ID as ORGN_OWN6_0_, organizati0_.DATA_SOURCE_ID as DATA_SOU7_0_, org
anizati0_.CREATE_DATETIME as CREATE_D8_0_, organizati0_.EFFECTIVE_DATE as EFFECT
IV9_0_, organizati0_.NAME as NAME0_, organizati0_.END_DATETIME as END_DAT11_0_,
organizati0_.SHORT_NAME as SHORT_NAME0_, organizati0_.EXPIRY_DATE as EXPIRY_13_0
_, organizati0_.UPDATE_USER as UPDATE_14_0_ from ORGANIZATION organizati0_ where
organizati0_.ID=?


Hibernate: select organizati0_.ID as ID__, organizati0_.ORGANIZATION_ID as ORGAN
IZ10___, organizati0_.ID as ID0_, organizati0_.END_REASON_DESC as END_REAS2_0_,
organizati0_.EXPIRY_DATE as EXPIRY_D3_0_, organizati0_.UPDATE_USER as UPDATE_U4_
0_, organizati0_.UPDATE_DATETIME as UPDATE_D5_0_, organizati0_.CREATE_USER as CR
EATE_U6_0_, organizati0_.EFFECTIVE_DATE as EFFECTIV7_0_, organizati0_.CREATE_DAT
ETIME as CREATE_D8_0_, organizati0_.END_DATETIME as END_DATE9_0_, organizati0_.O
RGANIZATION_ID as ORGANIZ10_0_, organizati0_.ROLE_TYPE_ID as ROLE_TY11_0_, organ
izati0_.LOCATION_ID as LOCATIO12_0_ from ORGANIZATION_LOCATION organizati0_ wher
e organizati0_.ORGANIZATION_ID=?

I am using this hbm.xml file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping SYSTEM "hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class table="ORGANIZATION" name="enet_read_full.generated.model.ORGANIZATION" where="(end_datetime is null or end_datetime > sysdate) and (expiry_date is null or expiry_date > sysdate)">
<id unsaved-value="0" name="ID">
<generator class="native"/>
</id>

<bag name="ORGANIZATION_TYPE" cascade="none" lazy="true" inverse="false" outer-join="false" batch-size="1">
<key column="ORGANIZATION_ID"/>
<one-to-many class="enet_read_full.generated.model.ORGANIZATION_TYPE"/>
</bag>
<property name="END_REASON_DESC" not-null="false" unique="false" update="true" insert="true"/>
<property name="CREATE_USER" not-null="false" unique="false" update="true" insert="true"/>
<property name="DESCRIPTION" not-null="false" unique="false" update="true" insert="true"/>
<property name="UPDATE_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<property name="CREATE_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<property name="EFFECTIVE_DATE" not-null="false" unique="false" update="true" insert="true"/>
<property name="NAME" not-null="false" unique="false" update="true" insert="true"/>
<property name="END_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<property name="EXPIRY_DATE" not-null="false" unique="false" update="true" insert="true"/>
<property name="UPDATE_USER" not-null="false" unique="false" update="true" insert="true"/>
</class>
</hibernate-mapping>

how are you querying ORGANIZATION ? "from ORGANIZATION " ?

We have around 70 tables with relationships, and data from all the tables is retreived based on a ID, we have hbm.xml for each table and there are relationships defined in corresponding Java beans.

eg: Organization table >> Organization hbm.xml >> Organization.java

to get type of Organization:
Organization >> Organization_Type >> Type >> ID, Name

Problem is, if Organization has 5 Organization Type , it produces 5 SQL queries, even if based on the retreival ID only one Organization type is there, I have used Outer-join=false, it doesn't work, and the Where condition to check for expired records at class level is also not working.
Should we go back to native SQL queries, because performance wise it's nightmare to use hibernate for retreival, for Insertion it's the best in the market.

I went through the chapters, and the documentation, Icouldn't find anything on this topic.

Your help is highly appreciated.

we are loading the root Object called Model using this method:
public static Model load( Serializable id ) throws Exception {

Session session = factory.openSession();
Model model = null;
Transaction tx = null;
try {
tx = session.beginTransaction();
model = (Model)session.load( Model.class, id );
tx.commit();
}
catch (Exception e) {
if (tx!=null) tx.rollback();
throw e;
}
finally {
session.close();
}

return model;
}

we are passing the ID to this method, all the Inheritance relationships have model as the root.


eg: Model >> Abc >> Organization >> Organization_Type >> Type

The "where" condition does not affect lookups by pk.

What's the best way to make the Where condition working, do we have to loop through each object and check if it meets the criteria, that will be huge task, can't we filter at hbm.xml

Your primary key is unique, so applying a where condition to lookup by primary key is basically useless.

You can do "from Object where id=?" however.

Hi Team,

I would like to expalin the scenario in detail,

Let's say I have this relationship:


Organization 1 -------------M Organization_Type

if a Organization has 5 Organization_Type and let's say one Organization_Type expires, as a rule we should retrieve only 4 Organization_Type (i.e filtered), and I want it to be filtered at hbm.xml level by putting this condition :

<hibernate-mapping>
<class table="ORGANIZATION_TYPE" name="enet_read_full.generated.model.ORGANIZATION_TYPE" where="(end_datetime is null or end_datetime > sysdate) and (expiry_date is null or expiry_date > sysdate)">

The where condition doesn't work.

again the question is : What's the best way to make the Where condition working, do we have to loop through each object and check if it meets the criteria, that will be huge task, can't we filter at hbm.xml.

in your example: do it in the one-to-many declaration

this is the mapping for Organization_Type

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping SYSTEM "hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class table="ORGANIZATION_TYPE" name="enet_read_full.generated.model.ORGANIZATION_TYPE" where="(end_datetime is null or end_datetime > sysdate) and (expiry_date is null or expiry_date > sysdate)">
<id unsaved-value="0" name="ID">
<generator class="native"/>
</id>
<property name="CREATE_USER" not-null="false" unique="false" update="true" insert="true"/>
<property name="CREATE_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<!--property name="ORGANIZATION_ID" not-null="false" unique="false" update="true" insert="true"-->
<property name="END_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<property name="END_REASON_DESC" not-null="false" unique="false" update="true" insert="true"/>
<property name="UPDATE_USER" not-null="false" unique="false" update="true" insert="true"/>
<property name="UPDATE_DATETIME" not-null="false" unique="false" update="true" insert="true"/>
<property name="TYPE_ID" not-null="false" unique="false" update="true" insert="true"/>
<many-to-one name="ORGANIZATION" column="ORGANIZATION_ID" cascade="none" outer-join="false" not-null="false" unique="false" update="true" insert="true"/>
</class>
</hibernate-mapping>

delcare the one to many (with where attribute) relationship in the organization mapping

Thanks a lot for your support, we tested it and it worked.