Issue about assemble where clause dynamicly.

sfykemble · **Joined:** Tue Jun 15, 2004 1:10 am **Posts:** 5

Code:

    String hql="select * from Tog t where t.f1=:pf1 and t.f2=:pf2 and t.f3=:pf3"
    Query q = session.createQuery(hql);
    q.setString("pf1","vf1");
    q.setString("pf2",null);
    q.setString("pf3","vf1");

Is there an approach to get the target sqls as follows by setting some FLAG in Hibernate respectively?

Code:

1: target output SQL: "select * from Tbl_Tog t where t.f1=? and t.f2 IS NULL and t.f3=?"
corresponding param: q.setString("pf2",null); //the input "null" param is substituted by "IS NULL"

2: target output SQL: "select * from Tbl_Tog t where t.f1=? and t.f3=?"
corresponding param: q.setString("pf2",null); //the sub where clause is omitted

IMHO,the following methods should be added into Query object to achieve such function:

Code:

public Query setIsNull(String name);// to change "t.f2=:pf2" to "t.f2 IS NULL"
public Query setIsNull(int position);

public Query setOmitted(String name);// to omit the sub where clause "t.f2=:pf2"
public Query setOmitted(int position);

Thanks a lot!

emmanuel · **Posted:** Mon Jun 21, 2004 3:00 am

Check the Query by Example for the Omited stuff

sfykemble · **Joined:** Tue Jun 15, 2004 1:10 am **Posts:** 5

Hi,emmanuel :
Thanks your reply,Could you please explain it more detailedly,I did not quite catch you.

Thanks a lot!

sdknott · **Joined:** Tue Oct 07, 2003 10:20 am **Posts:** 77

I think he's saying to have a look at http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html#querycriteria-examples.

emmanuel · **Posted:** Mon Jun 28, 2004 12:19 pm

sdknott wrote:

I think he's saying to have a look at http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html#querycriteria-examples.

Hehe yes