Hi,

Does anyone know if it's possible to extract REGEXP values using named Parameters in Hibernate Native SQL Query?

I have a SQLQuery created using the sessionFactory - the query below runs good when the parameters are embedded into the sql as below:
.... sql ...
sql += "(r.title REGEXP '[[:<:]]" + crit.getKeyword() + "[[:>:]]' ) or (r.annotation REGEXP '[[:<:]]" + crit.getKeyword() + "[[:>:]]') or (r.author REGEXP '[[:<:]]" + crit.getKeyword() + "[[:>:]]'))";

//I changed the above sql to use named parameters (:reg)
sql += "(r.title REGEXP :reg) or (r.annotation REGEXP :reg) or (r.author REGEXP :reg))";
...
SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(sql);
....

String s1 += "'[[:<:]]" + crit.getKeyword() + "[[:>:]]'";
//set the string for the named parameter:
sqlQuery.setString("reg", s1); //am unable to extract the values for the REGEXP out to be used as named Parameters

Would appreciate if anyone has any insight. Thanks in advance! :-)

Lin

Andre,

My apology ... I probably wrote too much in my earlier post and you might have missed the part where I mentioned that I was able to run the query successfully (created with Hibernate sessionFactory) with the embedded parameters.

What I need is to be able to pass in the parameters as named parameters using REGEXP so as to prevent SQL injection. Below was what I did and it was not working:

//I changed the above sql to use named parameters (:reg)
sql += "(r.title REGEXP :reg) or (r.annotation REGEXP :reg) or (r.author REGEXP :reg))";

Thanks.

Lin