Hi,

I am using criteria,i am running the following query in HQL editior it is running fine,How can i create same thing using Criteria,currently i am using hibernate teamplate .please suggest us.

select stmt.fbpCrbTxnCd.txnCd from com.fgb.fbp.core.model.FbpAcctStmt stmt,com.fgb.fbp.core.model.FbpCrbTxnCd Product where stmt.id.acctId in('1031001006774010','1031001006774010') and stmt.txnDt between '01-MAy-12' and '02-May-12' and stmt.fbpCrbTxnCd.txnCd=Product.txnCd and Product.prodId in('TPT')

and my java code is
DetachedCriteria accNoCriteria = DetachedCriteria.forClass(FbpAcct.class).setProjection(Property.forName("id.acctId"));
accNoCriteria.add(Restrictions.in("fbpCust.custId", transactionInfo.getCustomeridList()));
DetachedCriteria transactionEnquiry = DetachedCriteria.forClass(FbpAcctStmt.class,"acct");
transactionEnquiry.add(Property.forName("acct.id.acctId").in(accNoCriteria));
Criterion dateCriteria = Restrictions.between("acct.txnDt",
transactionInfo.getFromDate(), transactionInfo.getToDate());
//transactionEnquiry.add(acctCriteria);
transactionEnquiry.add(dateCriteria);
transactionEnquiry.createAlias("fbpCrbTxnCd", "txn");
transactionEnquiry.add(Property.forName("txn.prodId").in(transactionInfo.getSubProductList()));
but it is returning null,for me actuall result of list size shouid be one.

and my model class is




package com.fgb.fbp.core.model;
// Generated Apr 27, 2012 3:18:21 PM by Hibernate Tools 3.4.0.CR1


import java.math.BigDecimal;
import java.util.Date;

/**
* FbpAcctStmt generated by hbm2java
*/
public class FbpAcctStmt implements java.io.Serializable {


private FbpAcctStmtId id;
private FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmA6bf;
private FbpCcy fbpCcy;
private FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmF8a8;
private FbpCrbTxnCd fbpCrbTxnCd;
private String clientRefNbr;
private String cheqNbrTxt;
private String creditDebitCd;
private String txnDesc;
private String narrTxt;
private BigDecimal txnAmt;
private Date postDt;
private Date valDt;
private String benfNm;
private String remitNm;
private BigDecimal exchngRate;
private String txnRmrksTxt;
private String stmtId;
private String swiftTxnCd;
private BigDecimal openBalAmt;
private String routCd;
private boolean actvInd;
private Date txnDt;

public FbpAcctStmt() {
}


public FbpAcctStmt(FbpAcctStmtId id, FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmA6bf, boolean actvInd) {
this.id = id;
this.fbpOwnAcctByFkOwnAcctAcctStmA6bf = fbpOwnAcctByFkOwnAcctAcctStmA6bf;
this.actvInd = actvInd;
}
public FbpAcctStmt(FbpAcctStmtId id, FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmA6bf, FbpCcy fbpCcy, FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmF8a8, FbpCrbTxnCd fbpCrbTxnCd, String clientRefNbr, String cheqNbrTxt, String creditDebitCd, String txnDesc, String narrTxt, BigDecimal txnAmt, Date postDt, Date valDt, String benfNm, String remitNm, BigDecimal exchngRate, String txnRmrksTxt, String stmtId, String swiftTxnCd, BigDecimal openBalAmt, String routCd, boolean actvInd, Date txnDt) {
this.id = id;
this.fbpOwnAcctByFkOwnAcctAcctStmA6bf = fbpOwnAcctByFkOwnAcctAcctStmA6bf;
this.fbpCcy = fbpCcy;
this.fbpOwnAcctByFkOwnAcctAcctStmF8a8 = fbpOwnAcctByFkOwnAcctAcctStmF8a8;
this.fbpCrbTxnCd = fbpCrbTxnCd;
this.clientRefNbr = clientRefNbr;
this.cheqNbrTxt = cheqNbrTxt;
this.creditDebitCd = creditDebitCd;
this.txnDesc = txnDesc;
this.narrTxt = narrTxt;
this.txnAmt = txnAmt;
this.postDt = postDt;
this.valDt = valDt;
this.benfNm = benfNm;
this.remitNm = remitNm;
this.exchngRate = exchngRate;
this.txnRmrksTxt = txnRmrksTxt;
this.stmtId = stmtId;
this.swiftTxnCd = swiftTxnCd;
this.openBalAmt = openBalAmt;
this.routCd = routCd;
this.actvInd = actvInd;
this.txnDt = txnDt;
}

public FbpAcctStmtId getId() {
return this.id;
}

public void setId(FbpAcctStmtId id) {
this.id = id;
}
public FbpOwnAcct getFbpOwnAcctByFkOwnAcctAcctStmA6bf() {
return this.fbpOwnAcctByFkOwnAcctAcctStmA6bf;
}

public void setFbpOwnAcctByFkOwnAcctAcctStmA6bf(FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmA6bf) {
this.fbpOwnAcctByFkOwnAcctAcctStmA6bf = fbpOwnAcctByFkOwnAcctAcctStmA6bf;
}
public FbpCcy getFbpCcy() {
return this.fbpCcy;
}

public void setFbpCcy(FbpCcy fbpCcy) {
this.fbpCcy = fbpCcy;
}
public FbpOwnAcct getFbpOwnAcctByFkOwnAcctAcctStmF8a8() {
return this.fbpOwnAcctByFkOwnAcctAcctStmF8a8;
}

public void setFbpOwnAcctByFkOwnAcctAcctStmF8a8(FbpOwnAcct fbpOwnAcctByFkOwnAcctAcctStmF8a8) {
this.fbpOwnAcctByFkOwnAcctAcctStmF8a8 = fbpOwnAcctByFkOwnAcctAcctStmF8a8;
}
public FbpCrbTxnCd getFbpCrbTxnCd() {
return this.fbpCrbTxnCd;
}

public void setFbpCrbTxnCd(FbpCrbTxnCd fbpCrbTxnCd) {
this.fbpCrbTxnCd = fbpCrbTxnCd;
}
public String getClientRefNbr() {
return this.clientRefNbr;
}

public void setClientRefNbr(String clientRefNbr) {
this.clientRefNbr = clientRefNbr;
}
public String getCheqNbrTxt() {
return this.cheqNbrTxt;
}

public void setCheqNbrTxt(String cheqNbrTxt) {
this.cheqNbrTxt = cheqNbrTxt;
}
public String getCreditDebitCd() {
return this.creditDebitCd;
}

public void setCreditDebitCd(String creditDebitCd) {
this.creditDebitCd = creditDebitCd;
}
public String getTxnDesc() {
return this.txnDesc;
}

public void setTxnDesc(String txnDesc) {
this.txnDesc = txnDesc;
}
public String getNarrTxt() {
return this.narrTxt;
}

public void setNarrTxt(String narrTxt) {
this.narrTxt = narrTxt;
}
public BigDecimal getTxnAmt() {
return this.txnAmt;
}

public void setTxnAmt(BigDecimal txnAmt) {
this.txnAmt = txnAmt;
}
public Date getPostDt() {
return this.postDt;
}

public void setPostDt(Date postDt) {
this.postDt = postDt;
}
public Date getValDt() {
return this.valDt;
}

public void setValDt(Date valDt) {
this.valDt = valDt;
}
public String getBenfNm() {
return this.benfNm;
}

public void setBenfNm(String benfNm) {
this.benfNm = benfNm;
}
public String getRemitNm() {
return this.remitNm;
}

public void setRemitNm(String remitNm) {
this.remitNm = remitNm;
}
public BigDecimal getExchngRate() {
return this.exchngRate;
}

public void setExchngRate(BigDecimal exchngRate) {
this.exchngRate = exchngRate;
}
public String getTxnRmrksTxt() {
return this.txnRmrksTxt;
}

public void setTxnRmrksTxt(String txnRmrksTxt) {
this.txnRmrksTxt = txnRmrksTxt;
}
public String getStmtId() {
return this.stmtId;
}

public void setStmtId(String stmtId) {
this.stmtId = stmtId;
}
public String getSwiftTxnCd() {
return this.swiftTxnCd;
}

public void setSwiftTxnCd(String swiftTxnCd) {
this.swiftTxnCd = swiftTxnCd;
}
public BigDecimal getOpenBalAmt() {
return this.openBalAmt;
}

public void setOpenBalAmt(BigDecimal openBalAmt) {
this.openBalAmt = openBalAmt;
}
public String getRoutCd() {
return this.routCd;
}

public void setRoutCd(String routCd) {
this.routCd = routCd;
}
public boolean isActvInd() {
return this.actvInd;
}

public void setActvInd(boolean actvInd) {
this.actvInd = actvInd;
}
public Date getTxnDt() {
return this.txnDt;
}

public void setTxnDt(Date txnDt) {
this.txnDt = txnDt;
}




}


and the generated sql after running above code is
select this_.CRB_TXN_REF_NBR as CRB1_91_1_, this_.ACCT_ID as ACCT2_91_1_, this_.BANK_ID as BANK3_91_1_, this_.CCY_CD as CCY4_91_1_, this_.MICR_ACCT_ID as MICR5_91_1_, this_.MICR_ACCT_BANK_ID as MICR6_91_1_, this_.TXN_CD as TXN7_91_1_, this_.CLIENT_REF_NBR as CLIENT8_91_1_, this_.CHEQ_NBR_TXT as CHEQ9_91_1_, this_.CREDIT_DEBIT_CD as CREDIT10_91_1_, this_.TXN_DESC as TXN11_91_1_, this_.NARR_TXT as NARR12_91_1_, this_.TXN_AMT as TXN13_91_1_, this_.POST_DT as POST14_91_1_, this_.VAL_DT as VAL15_91_1_, this_.BENF_NM as BENF16_91_1_, this_.REMIT_NM as REMIT17_91_1_, this_.EXCHNG_RATE as EXCHNG18_91_1_, this_.TXN_RMRKS_TXT as TXN19_91_1_, this_.STMT_ID as STMT20_91_1_, this_.SWIFT_TXN_CD as SWIFT21_91_1_, this_.OPEN_BAL_AMT as OPEN22_91_1_, this_.ROUT_CD as ROUT23_91_1_, this_.ACTV_IND as ACTV24_91_1_, this_.TXN_DT as TXN25_91_1_, txn1_.TXN_CD as TXN1_93_0_, txn1_.CMPNY_CD as CMPNY2_93_0_, txn1_.TRANASCTION_CD_TYPE as TRANASCT3_93_0_, txn1_.PROD_ID as PROD4_93_0_, txn1_.TXN_DESC as TXN5_93_0_, txn1_.CHEQ_IND as CHEQ6_93_0_, txn1_.DATA_CAPTURE_IND as DATA7_93_0_, txn1_.DEBIT_CREDIT_CD as DEBIT8_93_0_, txn1_.SWIFT_NARR_CD as SWIFT9_93_0_ from FBPDEV.FBP_ACCT_STMT this_, FBPDEV.FBP_CRB_TXN_CD txn1_ where this_.TXN_CD=txn1_.TXN_CD and this_.ACCT_ID in (select this_.ACCT_ID as y0_ from FBPDEV.FBP_ACCT this_ where this_.CUST_ID in (?, ?)) and this_.TXN_DT between ? and ? and txn1_.PROD_ID in (?)
please inform what my mistake is