[beginner] HQL : Like clause with a named parameter?

Amine Bousta · **Joined:** Thu Sep 30, 2004 6:00 am **Posts:** 13

Hibernate version: 2.1.6

Mapping documents:

[...]
<query name="FIND_BY_SITE_IN_FOLDER">
from Page as p
where p.SiteId = :site
and p.Path like :folder%
and p.Path not like :folder%/%
order by p.Name
</query>
[...]

Name and version of the database you are using: MySQL

Hello,

I'm trying to get a result from a HQL query having a "like" clause.
This like clause should contains a named parameter ; ie: "like :folder%"

But it doesn't work, I'm certainly using a wrong syntax.

Here's the Hibernate Exception:
java.lang.IllegalArgumentException: Parameter folder does not exist as a named parameter in [
from Page as p
where p.SiteId = :site
and p.Path like :folder%
and p.Path not like :folder%/%
order by p.Name
]

I didn't find the correct syntax in the Hibernate documentation.
It gives exemples that doesn't match my pb :
1. select cat.name from eg.DomesticCat cat where cat.name like 'fri%'
2. where order.paid = false and order.customer = :customer

It would be a mix of both of them for me.

Is it possible to do a "like" clause with a named parameter anyway?

Dencel · **Joined:** Tue Jan 27, 2004 12:22 pm **Posts:** 103

set the % in the named parameter.

Code:

...where p.Path like :folder ..

.setString("folder","/bla%")

You can also check the SQL generated with:
<property name="show_sql">false</property>
in you config file.

Amine Bousta · **Joined:** Thu Sep 30, 2004 6:00 am **Posts:** 13

Hello,

I think you wanted to write "true" instead of "false" in the last post.

I've tried your idea but it doesn't work.

Here's my HQL modification in the Page.hbm file:

Code:

<query name="FIND_BY_SITE_IN_FOLDER">
   from Page as p 
   where p.SiteId = :site 
   and p.Path like :folder1
   and p.Path not like :folder2
   order by p.Name
</query>

Here's my Java code to call the HQL:

Code:

namedQuery=QUERY_FIND_BY_SITE_IN_FOLDER;
Hashtable params=new Hashtable();
params.put("site", site.getId());
if (currentFolder!=null) {
   params.put("folder1", currentFolder+"%");
   params.put("folder2", currentFolder+"%/%");
}
List res=PageDAO.getInstance().getNamedQuery(namedQuery, params);

It doesn't throw me an Exception but the SQL output is:

Code:

Hibernate: select page0_.id as id, page0_.nav_html as nav_html, page0_.include as include, page0_.page_template_id as page_tem4_, page0_.path as path, page0_.name as name, page0_.site_id as site_id from page page0_ where (page0_.site_id=? )and(page0_.path like ? )and(page0_.path not like ? ) order by  page0_.name

I did a "step by step" debug and I saw that all parameters had the correct value.
Is it normal?
Should the SQL output mask the named parameters?

Anyway the returned List is empty...

Issahar · **Posted:** Wed Nov 24, 2004 5:46 pm

Hi.

I think you need to use Criteria:

Example from Hibernate reference:
List cats = sess.createCriteria(Cat.class)
.add( Expression.like("name", "Fritz%") )
.add( Expression.or(
Expression.eq( "age", new Integer(0) ),
Expression.isNull("age")
) )
.list();

hope it helps,
Jus.

jzhang_2004 · **Joined:** Tue Jun 22, 2004 3:16 pm **Posts:** 35

Using criteria is one of the options but it does not work in case of you are having implicit associate join. For example, you can do:
from User u where u.address.city = 'New York', assume that address is an association of user.

But you can not do it using Criteria:
session.createCriteria(User.class).add(Expression.eq("address.city", "New York"));

You can use like in named qurey:

from User user where user.address.city like :city

In your DAO or Java code, do:
query.setParameter("city", "New Yo%").list();

Hope that helps.

jw