Is there any way to do this query in HQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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.
java sql hibernate hql sql-like
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
add a comment |
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.
java sql hibernate hql sql-like
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
add a comment |
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.
java sql hibernate hql sql-like
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
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.
java sql hibernate hql sql-like
java sql hibernate hql sql-like
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
edited Mar 17 '18 at 12:56
Cœur
19.4k10116155
19.4k10116155
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
asked Mar 23 '16 at 15:28
Elias GarciaElias Garcia
1,61321734
1,61321734
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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)
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard?parameters instead ofnamedparameters.
– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
add a comment |
Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like :sf");
qry.setString("sf",'%'+searchField+'%');
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
add a comment |
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()
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f36182290%2fis-there-any-way-to-do-this-query-in-hql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard?parameters instead ofnamedparameters.
– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
add a comment |
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)
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard?parameters instead ofnamedparameters.
– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
add a comment |
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)
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
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)
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
answered Mar 23 '16 at 15:39
ThiharaThihara
5,97622150
5,97622150
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard?parameters instead ofnamedparameters.
– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
add a comment |
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard?parameters instead ofnamedparameters.
– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Hi, thanks for your answer! I need to use the SQL wildcard "%", so the only way to go is to iterate the keywords list and build the query string on the go right? Also I need to iterate the list a second time to set the parameters right? Thanks!
– Elias Garcia
Mar 23 '16 at 15:45
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
Yes. But you can create the parameters in the same iteration as well.
– Thihara
Mar 24 '16 at 7:15
In that case, you might find it easier to use the standard
? parameters instead of named parameters.– Thihara
Mar 24 '16 at 7:16
In that case, you might find it easier to use the standard
? parameters instead of named parameters.– Thihara
Mar 24 '16 at 7:16
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
Ok, thanks a lot!
– Elias Garcia
Mar 24 '16 at 15:22
add a comment |
Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like :sf");
qry.setString("sf",'%'+searchField+'%');
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
add a comment |
Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like :sf");
qry.setString("sf",'%'+searchField+'%');
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
add a comment |
Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like :sf");
qry.setString("sf",'%'+searchField+'%');
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
Query qry = session.createQuery("From RegistrationBean as rb where rb."+searchCriteria+" like :sf");
qry.setString("sf",'%'+searchField+'%');
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
answered Mar 23 '16 at 15:41
zaafranizaafrani
4027
4027
add a comment |
add a comment |
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()
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
add a comment |
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()
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
add a comment |
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()
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
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()
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
answered Nov 17 '18 at 2:28
MiguelMunozMiguelMunoz
1,8891619
1,8891619
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f36182290%2fis-there-any-way-to-do-this-query-in-hql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
