Hi,
My situation is like I want to have my queries externalized, and at the same time pass bind variable values to them.--> Answer is: SQL Named Queries. That's working fine.

Now the issue is that for three/four/five different criteria and I end up writing those many named SQL Queries in a seperate hbm xml file.

Is there any way as below:
Current API code takes:

Query q = session.getNamedQuery("SQL_DBREPORTS_SUMMARY_AUDIT_REPORT");
q.setString("p_startDate", startDate);
q.setString("p_endDate", endDate);
q.setString("p_srcSystem", srcSystem);
return q.list();

Could we not pass some Properties(i.e. Map)
( having Properties.put(k1,v1); Properties.put(k2,v2); Properties.put(k3,v3) etc )
to

Query q = session.getNamedQuery("SQL_DBREPORTS_SUMMARY_AUDIT_REPORT",map);
and then I could do
Query q = session.getNamedQuery("SQL_DBREPORTS_SUMMARY_AUDIT_REPORT");
q.setString("p_startDate1", startDate1);
q.setString("p_endDate1", endDate1);
q.setString("p_srcSystem1", "VALUE1");
q.setString("p_startDate2", startDate2);
q.setString("p_endDate2", endDate2);
q.setString("p_srcSystem2", "VALUE2");
return q.list();


and then later in the

<sql-query name="SQL_DBREPORTS_SUMMARY_AUDIT_REPORT">

<return class="com.xxx.idm.toolkit.object.DBAuditReportRecord">
<return-property name="operation" column="idm_aud_operation"/>
<return-property name="count" column="operation_count"/>
</return>

<![CDATA[


select idm_aud_operation,
count(idm_aud_operation) as operation_count
from xxdv_idm.xxdv_idm_aud

<jython/jruby scripting to prepare dynamic query>
if(map.get(k1) == v1){
where (date_created >= trunc(to_date(:p_startDate1,'MM/DD/YYYY')) and
date_created <= trunc(nvl2(:p_endDate1, to_date(:p_endDate1,'MM/DD/YYYY'), sysdate))
)
}
else if(map.get(k2) == v2) {

where (date_created >= trunc(to_date(:p_startDate2,'MM/DD/YYYY')) and
date_created <= trunc(nvl2(:p_endDate2, to_date(:p_endDate2,'MM/DD/YYYY'), sysdate))
)
}
</jython/jruby>

<jython/jruby scripting to prepare dynamic query>
if(map.get(k1) == v1){
and IDM_AUD_SOURCE_SYSTEM = nvl(:p_srcSystem1,'UDC1')
}
else if(map.get(k2) == v2) {
and IDM_AUD_SOURCE_SYSTEM = nvl(:p_srcSystem2,'UDC2')
<and some more conditions etc>
}


group by idm_aud_operation
]]>
</sql-query>


This way I would have:
1. only ONE SQL Query to manage
2. Still Externalized
3. Much more dynamic
4. Still Native SQL

I'm not sure if this just my imagination and doesn't make any sense.