Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3457
14
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The simplest would be adding a @NamedQueries at the top of the class and put that sql in.


 
Vladimir Razov
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Posts: 3457
14
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3457
14
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3457
14
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This works (in MySQL):


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

@K.Tsang thanks for help
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic