aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes JPQL query for searching if a word/string is consisted in one of entity's fields Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "JPQL query for searching if a word/string is consisted in one of entity Watch "JPQL query for searching if a word/string is consisted in one of entity New topic
Author

JPQL query for searching if a word/string is consisted in one of entity's fields

Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
Basicaly, its similar like looking if certain word exists in sentence. There is entity Post:


What I am trying to implement is simple search mechanism. User eneters a word and if that word is consisted in the post's title, then the post is being returned.
So, I nedd JPQL query that will search for Post entity instances that have certain word/string (called byTitle, passed as an argument) in their title field, and two more arguments of Date type, that represents date range - startDate and endDate.

I have something like this on my mind: SELECT p FROM Post p WHERE :byTitle IN (set of strings created from parsing p.title field) AND p.date>=:startDate AND p.date<=:endDate
May be, that I wrong approached implementing this. If you have any experience with this kind of searching concept, I appreciate any help.
How to implement this kind of JPQL query? Or maybe JPA Criteria API should be used?
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2419
    
    7

The simplest would be adding a @NamedQueries at the top of the class and put that sql in.



K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
@K. Tsang Yes, I am aware of adding named queries at the entity class. I already have some for this class.
My problem here is how should JPQL query look?
I need something similar to someString.contains(someSubstring) function in Java.

Actually, my goal is to get all posts where post's title field (which is string) contains given substring - searching posts by some word from their title.
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2419
    
    7

Well the concern is that part inside the IN. If run as sql, this is surely a another select statement. But in java such data is a List or Set or some collection. You need to convert it into a string and use this variable in your named query.

So you mostly will need some method to translate your list to " 'abc', 'def', 'prq', 'xyz' "
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
Actually I am trying to implement something like this (this is not valid JPQL): SELECT p FROM Post p WHERE p.title contains :substring
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2419
    
    7

Not sure if JPQL has contains. But a LIKE will work.

About the IN subquery, look into EXISTS:

Normal SQL = select * from table1 where a in (select b from table2 where c like '%blablabla%' );

JPQL = select * from table where exists (select b from table2 where c like '%blablabla%' );
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
It looks to me that its best to implement this using LIKE expression.
Something like this:

SELECT p FROM Post p where p.title LIKE :pattern and p.date>=:startDate AND p.date<=:endDate

Now, how should then pattern attribute look like. For example, if I want to check if there is word "hello" part of the title field. Is following expression appropriate: "%hello%"
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2419
    
    7

Vladimir Razov wrote:Now, how should then pattern attribute look like. For example, if I want to check if there is word "hello" part of the title field. Is following expression appropriate: "%hello%"


Isn't that what LIKE is for? Depending on what database you are using, content may be case sensitive (eg "%hello%" != "%Hello%"). So lower-casing or upper-casing such strings may be appropriate.
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
This works (in MySQL):


where pattern="%" + someString + "%";

@K.Tsang thanks for help
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPQL query for searching if a word/string is consisted in one of entity's fields