Hi,

I'm running Hibernate3 on Oracle.

I want to use parameter binding to construct an SQL query with the "like" operator. I want to let my users search records that contain a string that they input, and so I want to build a query with a where clause that would ultimately look like this (suppose that "strSearch" contains the search string):


String sql = "select [fields...] from table where lower(field) like '%" + strSearch.toLowerCase() + "%'";

To avoid security problems as well as crashes when users input quotes in the search string, I want to use parameter binding. So, my code changes to this :

String sql = "select [fields...] from table where lower(field) like :strSearch";
Query q = session.createQuery(sql);
q.setString("strSearch", strSearch.toLowerCase());
...

The code compiles fine. The problem is that it doesn't return any records. (By the way, I've tested the original query without parameter binding and it worked fine).

Looking at the hibernate log:

...
DEBUG select [fields] from table where (lower(field)like ?))
DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
DEBUG org.hibernate.loader.hql.QueryLoader - bindNamedParameters() jou -> strSearch [1]
DEBUG org.hibernate.type.StringType - binding 'jou' to parameter: 1
DEBUG org.hibernate.jdbc.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
DEBUG org.hibernate.loader.Loader - processing result set
DEBUG org.hibernate.loader.Loader - done processing result set (0 rows)
...

I'm not sure exactly what SQL query is ultimately executed... Is the question mark replaced by 'jou', or '%jou%' ?

I am guessing that it's replaced by 'jou', since executing that exact query in fact returns 0 records. If that's the case, my question is : how do I code my query so that the ? is replaced by '%jou%' instead of 'jou' ?

Thanks for your input.

Duh !

Works like a charm, thanks...

On another note, am I the only one having problems with this forum ?

Problem 1: When searching in the forums, if I click a search result, and then click "Back" to get back to the results, I get a page saying that the page has expired ?!?!?

Problem 2: When posting something new or replying to an existing post, I sometimes get redirected to the Forums list instead of the posting form ?!?!