Hibernate HQL like query using named parameters

Posted on July 3, 2008, 11:03, by stefan, under coding.

Using the LIKE condition with a “%” sign for pattern matching in a HQL query with named parameters required some tweaking.

This did not work:

String query = "from user u where u.name like %:name%"

getHibernateTemplate().findByNamedParam(query, "name", str);

It resulted in a org.hibernate.QueryException: unexpected char: ‘%’ error.

Next I tried using single quotes:

String query = "from user u where u.name like '%:name%'"

getHibernateTemplate().findByNamedParam(query, "name", str);

Didn’t work either, an org.hibernate.QueryParameterException: could not locate named parameter.

What actually worked was putting the “%” signs in the parameter:

String query = "from user u where u.name like :name"

getHibernateTemplate().findByNamedParam(query, "name", '%' + str + '%');
Tags: , ,
Comment (RSS)  |  Trackback

29 Comments

  1. miluch says:
    July 3, 2008 at 12:33

    Hi

    I am wondering why not getting rid of hibernate template and use hibernate session directly. Take a look at
    http://blog.springsource.com/main/2007/06/26/so-should-you-still-use-springs-hibernatetemplate-andor-jpatemplate/

  2. David Atkins says:
    July 16, 2008 at 15:07

    Thanks the blog, found it using google and it solved my weird ‘%’ related problem

  3. Tom says:
    July 25, 2008 at 19:04

    EXCELLENT BLOG!!!

    Thanks for sharing this. It solved my % problem too.

    Regards,
    Tom

  4. html says:
    August 5, 2008 at 10:55

    Thanks for sharing. But it cannot solve the problem with value searching is “%”.

    nameValue = “%”;
    String query = “from user u where u.name like ‘%:name%’”
    getHibernateTemplate().findByNamedParam(query, “name”, nameValue);

  5. Jot says:
    October 22, 2008 at 17:22

    An even simpler way:

    create a new string with the % around the string you want to insert into the query:..
    String temp = “%”+title+”%”;

    This will hold as %var%

    Then send that string TEMP to the HQL query…

    Query query = session.createQuery(“from Event e where e.title like ?” );

    query.setString(0, temp);

    therefore it will interpret as

    from Event e where e.title like %var%

  6. Jot says:
    October 22, 2008 at 17:23

    *String temp = “%”+var+”%”;

  7. auratus says:
    November 25, 2008 at 21:06

    Thanks for the blog entry, helped me out too – in my case, the comment about adding “%” to the var instead of the original String is what fixed it. :)

  8. Steve says:
    January 13, 2009 at 16:34

    Thanks, this helped me too I couldn’t find it anywhere!

  9. Michael Easter says:
    January 28, 2009 at 22:31

    +1 on a big thank you… this confirmed I haven’t completely lost my marbles

  10. Jacob Coens says:
    February 17, 2009 at 10:34

    Very helpful, thanks! I’m using Castle ActiveRecord and it’s sometimes hard to find out how to use certain features using the provided documentation only.

  11. Chetan says:
    February 18, 2009 at 5:49

    Thanks it solved my problem : String temp = “%”+var+”%”;

  12. kenneth Aqua says:
    February 26, 2009 at 19:22

    simple and concise, if unknown can hold you up quite a bit .. many thanks

  13. flagger says:
    April 3, 2009 at 21:44

    This works as well:

    String query = “from user u where u.name like ‘%’ || :name || ‘%’”

    getHibernateTemplate().findByNamedParam(query, “name”, str);

  14. denny says:
    April 21, 2009 at 2:19

    +1 thank you very much for the tip! I would have lost a lot of time on this!

  15. Sverre says:
    April 29, 2009 at 14:20

    Thx. You saved me a lot of time with this one.

  16. Vishal says:
    May 21, 2009 at 19:51

    Awesome brother helped me a lot

  17. Boris says:
    June 1, 2009 at 21:55

    Thank you!

  18. belano says:
    June 4, 2009 at 17:03

    thanks!

  19. calinutz says:
    July 4, 2009 at 18:41

    i should thank you 2. i used *String temp = “%”+var+”%”;
    works like a charm. very simple

  20. venkat says:
    July 15, 2009 at 6:31

    It worked. Thanks

  21. sugra says:
    July 27, 2009 at 18:15

    I’ve followed all the mental process described in this article (including comments)

    Thanks for make me feel as the rest of programmers :)

  22. rotaku says:
    July 29, 2009 at 20:19

    Thanks you save me!

  23. Steven says:
    October 7, 2009 at 0:22

    Thanks, exactly what I was looking for.

  24. mascagranza says:
    October 25, 2009 at 15:26

    quite usefull mf!!!!

  25. Diederick says:
    January 28, 2010 at 12:20

    Excellent. Works with TopLink Essentials as well.

  26. Aditya says:
    April 14, 2010 at 22:10

    Thanks Stefan….

  27. sam says:
    May 5, 2010 at 21:46

    session.createQuery(“from Entity where property like :prefix”).setString(“prefix”,myString.concat(“%”)).list();

    solved my problem, but actually i was searching for a better solution …
    looks quite dirty to me.

  28. Manish says:
    June 11, 2010 at 9:40

    Thanks a lot it works!!

  29. ActiveRecord HQL LIKE query with parameters « Dan Schwartz says:
    June 30, 2010 at 22:26

    [...] a little help from Stefan Pettersson’s blog I found my solution, add the percent signs inside the [...]

Leave a Reply

«
»