CODEWITHSUNDEEP
javasqlhibernatehqlsql-like
Elias Garcia
Elias Garcia

Reputation: 7282

Is there any way to do this query in HQL?

I'm new to Hibernate and I'm creating a simple movie finder app in Java. I need to make a query in my dao to search movies by a list of keywords. I know how to do it in SQL, but I was wondering if there is any way to make an HQL query to do this. The Java Method in the dao receives a List which is a list of keyowrds and the SQL statement is like this:

SELECT *
FROM movies
WHERE name LIKE '%keyword1%' OR name LIKE 'keyword2' ...

So to achieve this is necessary to iterate trough all the list and concatenate the Query for each keyword.

It's possible to make this query more simple in HQL, so you can pass a list of keywords to que query so it can use it with the LIKE statement?

Thanks.

Upvotes: 0

Views: 337

Answers (3)

MiguelMunoz
MiguelMunoz

Reputation: 4952

Not only did I need to do this, but I needed to do it in a @Query annotation in Spring Data. This is the only way I got it to work. (Keep in mind that this is in an interface.) 

@Query(value="from Movie h where lower(h.name) like concat('%',lower(:term),'%'")
List<Movie> findLike(@Param("term") String term);

I put the calls to lower() to make it case-insensitive, and I used the concat() function to add the % characters. It didn't work when I wrote it like this:

@Query(value="from Movie h where lower(h.name) like lower(%:term%)")
List<Movie> findLike(@Param("term") String term);   // Query doesn't work! Use concat()

It didn't even work like this, without case sensitivity:

@Query(value="from Movie h where h.name like %:term%")
List<Movie> findLike(@Param("term") String term);   // Query doesn't work! Use concat()

Upvotes: 0

zaafrani
zaafrani

Reputation: 85

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

Upvotes: 0

Thihara
Thihara

Reputation: 6969

SELECT *
FROM movies
WHERE name LIKE '%keyword1%' OR name LIKE 'keyword2'

Would translate to HQL as

from movies m where m.name like :keyword1 or m.name like :keyword2

You will need to pass the named parameters keyword1 and keyword2 when querying.

And if you insist of using the like matchers, you will need to loop over the list and dynamically generate the query.

The other option is to actually use the IN clause with the HQL, that would however make wildcard matches impossible.

from movies m where m.name in (:keywords)

Upvotes: 1

Related Questions