Select ex.* From tbl_exrate ex
where (ex.mandate_fund in (select UM.mandate from vw_user2mandate UM
where UM.user_id = 'XXXXXX')
or
ex.mandate_fund in (select MF.fund
from tbl_mandate2fund MF join vw_user2mandate UM on UM.Mandate=MF.mandate
where UM.user_id = 'XXXXXX'))
--and ex.end_rpt_date = :date
--and ex.mandate_fund = :fund
--and curr_code = :currency
Order by end_rpt_date, mandate_fund, curr_code
应用criteria,DetachedCriteria实现上面的查询语句
,其中“--”后面的条件是动态的,根据date,fund,currency是否有输入来确定是否添加条件请熟悉hibernate的大侠赐教!
where (ex.mandate_fund in (select UM.mandate from vw_user2mandate UM
where UM.user_id = 'XXXXXX')
or
ex.mandate_fund in (select MF.fund
from tbl_mandate2fund MF join vw_user2mandate UM on UM.Mandate=MF.mandate
where UM.user_id = 'XXXXXX'))
--and ex.end_rpt_date = :date
--and ex.mandate_fund = :fund
--and curr_code = :currency
Order by end_rpt_date, mandate_fund, curr_code
应用criteria,DetachedCriteria实现上面的查询语句
,其中“--”后面的条件是动态的,根据date,fund,currency是否有输入来确定是否添加条件请熟悉hibernate的大侠赐教!
貌似hib支持原生sql的对象映射吧?我没用过。
Criteria exrateCriteria = session.createCriteria(tbl_exrate.class,"ex");
Conjunction conjunction = Restrictions.conjunction();
Disjunction disjunction = Restrictions.disjunction();
subquery.add(Restrictions.eq("UM.user_id", "XXXXXX")).setProjection(Property.forName("UM.mandate"));
disjunction.add(Subqueries.propertyIn("ex.mandate_fund", subquery));
String sqlFragment =
"{alias}.mandate_fund in (select MF.fund from tbl_mandate2fund MF join vw_user2mandate UM on UM.Mandate=MF.mandate where UM.user_id = 'XXXXXX')";
disjunction.add(Restrictions.sqlRestriction(sqlFragment));
conjunction.add(disjunction);
if(date != null){
Criterion c1 = Restrictions.eq("ex.end_rpt_date", date);
conjunction.add(c1);
exrateCriteria.addOrder(Order.asc("ex.end_rpt_date"));
}
if(fund != null){
Criterion c2 = Restrictions.eq("ex.mandate_fund", fund);
conjunction.add(c2);
exrateCriteria.addOrder(Order.asc("ex.mandate_fund"));
}
if(currency != null){
Criterion c3 = Restrictions.eq("ex.curr_code", currency);
conjunction.add(c3);
exrateCriteria.addOrder(Order.asc("ex.curr_code"));
}
exrateCriteria.add(conjunction);