How to write a like query in HQL

Question

I want to perform search for a particular string that is starting with a particular alphabet.

So, for example if the starting alphabet is 'A' then it should produce a result which will contain all the strings with alphabet 'A'.

How do I achieve this ?

My query is as shown below:

Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+"  like %?%");
qry.setString(0,searchField);

no there is some problem with my query i think. Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like %?%") qry.setString(0,searchField); — Ni3, Nov 26 '12 at 09:55
[What have you tried?](https://whathaveyoutried.com) Please post what you have so far in the question. — Keppil, Nov 26 '12 at 09:55

score 31 · Answer 1 · answered Nov 26 '12 at 10:01

31

Change your query to this:

Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+"  like ?");
qry.setString(0, "%"+searchField+"%");

answered Nov 26 '12 at 10:01

Aleksandr M

24,264
12
69
143

3

is this not vulnerable to SQL injection? – Optimus Prime Feb 24 '21 at 13:07
@OptimusPrime No, it is. – Roman C Sep 17 '21 at 08:15

score 18 · Answer 2 · answered Nov 26 '12 at 09:57

You can use criteria for using like

session = sessionFactory.openSession();
Criteria query = session.createCriteria(Pojo.class);
query.add(Restrictions.like("column", "a", MatchMode.START));

It will give you the list of string which start by alpha-bate 'a'.

score 12 · Answer 3 · edited Oct 26 '16 at 17:34

12

Change to

Query qry = session.createQuery("From RegistrationBean as rb where rb ";
if (searchField != null)
{
    qry = qry + " where rb.searchCriteria like :searchField ";
}
if ( searchField!= null)
{
    query.setString("searchField","%"+searchField+"%");
}

edited Oct 26 '16 at 17:34

Lukasz Stelmach

5,281
4
25
29

answered Apr 14 '14 at 12:28

rinuthomaz

1,393
2
23
38

2

I would do that with two separate queries. – MiguelMunoz Nov 17 '18 at 02:13

Roman C · Answer 4 · 2017-02-07T19:38:32.907

10

This way

createQuery("from RegistrationBean as rb where rb.:searchCriteria like '%:searchField%'");

edited Feb 07 '17 at 19:38

answered Nov 26 '12 at 10:33

Roman C

49,761
33
66
176

2

Have you heard anything about SQL injection? http://en.wikipedia.org/wiki/SQL_injection One have to use parameter binding to avoid it. – Aleksandr M Nov 26 '12 at 22:07
This link have nothing with hibernate and hql. – Roman C Nov 27 '12 at 11:34
https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java – Aleksandr M Nov 27 '12 at 11:41
Well, there's nothing vulnerable found, to make more sense the query above does not contain user input. – Roman C Nov 27 '12 at 12:09
How do you know that? The `searchField` parameter could be entered by user. – Aleksandr M Nov 27 '12 at 12:16
if searchField = "' OR '1'='1" then it is subject to sql injection – Oleksii Kyslytsyn Feb 07 '17 at 13:17
@Oleksii However this is to old post for downvote, I changed it because it's not about sql injection but it doesn't matter, everyone who answered with `"+searchCriteria+"` should be downvoted? – Roman C Feb 07 '17 at 19:41
3

@RomanC there are three types of responses: upvote, downvote, ignore. Welcome. – Oleksii Kyslytsyn Nov 17 '17 at 22:02

How to write a like query in HQL

4 Answers4

Linked

Related