Like clause and "%" characters in query

bdusauso · **Joined:** Fri Oct 21, 2005 9:27 am **Posts:** 13

Hibernate version:
2.1.7c

Name and version of the database you are using:
PostgreSQL

The problem:
I'd like to return a Collection of objects (type = Newsletter). I do it with a like clause in the HQL, but I need some "%" characters.
It seems Hibernate doesn't like this kind of characters.
The query is :

Code:

this.getHibernateTemplate().createQuery(this.getSession(),"select n from Newsletter n where n.recipients like %:role% and n.sent = true").setInteger("role", new Integer(role)).list();

and I get this exception message :

java.lang.IllegalArgumentException: Parameter role does not exist as a named parameter in [select n from Newsletter n where n.recipients like %:role% and n.sent = true]

It works if I write :

Code:

this.getHibernateTemplate().createQuery(this.getSession(),"select n from Newsletter n where n.recipients like %" +role + "% and n.sent = true").list();

The generated SQL (show_sql=true) is then :
select newsletter0_.id as id, newsletter0_.authorId as authorId, newsletter0_.content as content, newsletter0_.creationDate as creation4_, newsletter0_.recipients as recipients, newsletter0_.sendingDate as sendingD6_, newsletter0_.sent as sent, newsletter0_.title as title from Newsletters newsletter0_ where (newsletter0_.recipients like ? )and(newsletter0_.sent=true )

Is it a bug or am I missing something ?

pksiv · **Posted:** Fri Oct 21, 2005 10:02 am

[select n from Newsletter n where n.recipients like %:role% and n.sent = true]

should be [select n from Newsletter n where n.recipients like :role and n.sent = true]

And when you set the value of :role, the String should include the % characters.

bdusauso · **Joined:** Fri Oct 21, 2005 9:27 am **Posts:** 13

I'm sorry, I totally forgot to mention that, but I've also tried your query, and Hibernate generates exactly the same SQL mentioned earlier.

nathanmoon · **Posted:** Fri Oct 21, 2005 10:20 am

This should also work (and doesn't make you stick the '%' characters on in your code):

Code:

where n.recipients like '%' || :role || '%'