This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
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: 41
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: 1963
    
    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
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 41
@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: 1963
    
    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: 41
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: 1963
    
    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: 41
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: 1963
    
    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: 41
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
 
Similar Threads
Assigning jsonobject to java class having one to many relationship
Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"
How should JPQL query look like for described scenario?
Implementing pagination in MVC - Servlets + JSP
Edit primary key, if it's a foreign key