Hi all,
Hibernate version : 3.0.5
DBMS : Oraclce 9i

Iam having the problem in using the criteria Queries, In my project Iam having three table in which one is master table and other two table has a relation ship with this master table

parent --------------------- child1
one to one

parent --------------------- child2
one to many


consider parent = T_Quotation_mstr table
child1 = t_error_auth_dtls
child2 = t_quotation_acct_dtls


and I used the following

Criteria criteriaCheque = session.createCriteria(RezAffiliateDetailsBean.class)
.add(Restrictions.eq("accountAssistantCode",new Integer(objOPCorrectionViewBean.getAccountAssistantCode())));


Criteria subCriteriaErrorAuth = criteriaCheque.createCriteria("objErrorAuthBean")
.setFetchMode("objErrorAuthBean",FetchMode.JOIN)
.add(Restrictions.conjunction().add(Restrictions.eq("errorcode",new Long(objOPCorrectionViewBean.getErrorcode())))
.add(Restrictions.ne("authorisationFlag",new Short((short)1))));

Criteria subCriteriaQuotationAcctDetails = criteriaCheque.createCriteria("objQuotationAcctDetailsBean")
.setFetchMode("objQuotationAcctDetailsBean",FetchMode.JOIN)
.add(Restrictions.disjunction().add(Restrictions.eq("accountTypeId",new Short((short)1))));



Criteria subCriteriaQuotationAcctDetails1 = criteriaCheque.createCriteria("objQuotationAcctDetailsBean")
.add(Restrictions.disjunction().add(Restrictions.not(
Restrictions.in("accountTypeId",new Short[]{new Short((short)2),new Short((short)3),new Short((short)4),new Short((short)5)}))));


The sql generated by the Hibernate automatically appends "AND" in the where clause of the SQL I need OR condition instead AND in the where clause of the SQL,

I know that OR can be add between the two condition in single create criteria I need and condition between the two Sub criteria (In this case i need OR condition between the subCriteriaQuotationAcctDetails1 and subCriteriaErrorAuth )

please kindly do the needful...

for the information the SQL generated by the Hibernate is displayed below


select *
from (select this_.QTM_QTN_ID as QTM1_2_,
this_.QTM_AFF_RUT_ID as QTM2_3_2_,
this_.QTM_ROL_FOL_NN as QTM3_3_2_,
this_.QTM_IMG_FOL_NN as QTM4_3_2_,
this_.QTM_SEQ_NN as QTM5_3_2_,
this_.QTM_QTN_PRD_NN as QTM6_3_2_,
this_.QTM_PMT_FC as QTM7_3_2_,
this_.QTM_AFF_NM as QTM8_3_2_,
this_.QTM_PYR_RUT_ID as QTM9_3_2_,
this_.QTM_ACC_ASS_CD as QTM10_3_2_,
this_.EAD_ERR_AUT_NN as EAD11_3_2_,
quotationa2_.QAD_QTN_ACC_ID as QAD1_0_,
quotationa2_.QTM_QTN_ID as QTM2_5_0_,
quotationa2_.ATM_ACC_TYP_ID as ATM3_5_0_,
quotationa2_.QAD_DFF_ADQ_$$ as QAD4_5_0_,
quotationa2_.QAD_DFF_QOT_$$ as QAD5_5_0_,
quotationa2_.QAD_PMT_TYP_RE as QAD6_5_0_,
quotationa2_.QAD_ADL_QOT_$$ as QAD7_5_0_,
quotationa2_.QAD_ADL_PNP_$$ as QAD8_5_0_,
quotationa2_.QAD_PEN_QOT_$$ as QAD9_5_0_,
quotationa2_.QAD_QTN_QOT_$$ as QAD10_5_0_,
errorauthb1_.EAD_ERR_AUT_NN as EAD1_1_,
errorauthb1_.ERM_ERR_ID as ERM2_2_1_,
errorauthb1_.AXM_AUX_ID as AXM3_2_1_,
errorauthb1_.EAD_ATH_RE as EAD4_2_1_,
errorauthb1_.EAD_ATH_FC as EAD5_2_1_,
errorauthb1_.QTM_QTN_ID as QTM6_2_1_
from T_QUOTATION_MSTR this_ inner join T_QUOTATION_ACCT_DTLS quotationa2_ on this_.QTM_QTN_ID = quotationa2_.QTM_QTN_ID inner join T_ERROR_AUTH_DTLS errorauthb1_ on this_.QTM_QTN_ID = errorauthb1_.QTM_QTN_ID
where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) and
(quotationa2_.ATM_ACC_TYP_ID = ?))


I need the OR condition in the Where clause
where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) AND (quotationa2_.ATM_ACC_TYP_ID = ?))

instead of this and I need

where this_.QTM_ACC_ASS_CD = ? and
(errorauthb1_.ERM_ERR_ID = ? and errorauthb1_.EAD_ATH_RE <> ?) OR
(quotationa2_.ATM_ACC_TYP_ID = ?))







[/list]

You are misusing Restrictions.conjunction() and Restrictions.disjunction(). There is no point in writing code like this:You've done this twice in your code (once with a conjunction).

To put an OR between restrictions, the syntax isThat is, you add all the things that need to be ORed to the Restrictions.disjunction(), not to the base criteria.

Can you please use code tags when posting code, or other things that need to not be left aligned. Also, if you want to ask the same question twice, try posting twice in the same topic. Making new topics doesn't help.