File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/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


Win a copy of Android Security Essentials Live Lessons this week in the Android 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: 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: 2247
    
    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: 2247
    
    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: 2247
    
    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: 2247
    
    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
 
Similar Threads
Assigning jsonobject to java class having one to many relationship
How should JPQL query look like for described scenario?
Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"
Implementing pagination in MVC - Servlets + JSP
Edit primary key, if it's a foreign key