Hibernate Query Language Question (simple)

[Michael Jammy]

Hi there,

I have a question, which should be easy to answer for any HQL pro. I execute the following code:

hibernateTemplate.find("from Product p where lower(p.title) like lower(?)", query);

It queries the title column of the Product database, for my search query by the name of, "query". It works as it should. However, I want to use the same query to also check in p.productDesc.

I've tried many ways I can think of, including:

hibernateTemplate.find("from Product p where lower(p.title) or lower(p.productDesc) like lower(?)", query);

My other question is, how do I get it to also find part of the word. E.g. if my query is "hel", it also finds a row/column with "help me", or "where the hell did you go?". I tried "%", but it gives me errors everytime I add it.

[Hebert Coelho]

q01) try this:

 hibernateTemplate.find("from Product p where (lower(p.title) like lower(?)) or (lower(p.productDesc) like lower(?))", query);  

q02) "%hel" try something like this.

[Michael Jammy]

Thank you for your response Hebert, however it did not work.

Here I querried for the word, "catisfactions" and the error was:

org.hibernate.QueryException: Expected positional parameter count: 2, actual parameters: [catisfactions] [from Product p where (lower(p.title) like lower(?)) or (lower(p.productDesc) like lower(?))]

Also, adding % to my method as such:

hibernateTemplate.find("from Product p where lower(p.title) like %?", query);

causes the following error:

15132 [http-bio-8080-exec-5] ERROR org.hibernate.hql.PARSER - line 1:63: unexpected token: %
Nov 3, 2011 11:01:23 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [product] in context with path [/petshop] threw exception [Handler processing failed; nested exception is java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V] with root cause
java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V

[Hebert Coelho]

At the way I putted to you, you will need to set up your parameter two times. [=

[Michael Jammy]

Hebert Coelho wrote:At the way I putted to you, you will need to set up your parameter two times. [=

Thank you for your aid, I solved it as follows:

hibernateTemplate.find("from Product p where (lower(p.title) like ?) or (lower(p.productDesc) like ?)", "%" + query + "%", "%" + query + "%");

Also removed the lower() from ? because like ignores case.