• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Bartender
Posts: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This works (in MySQL):


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

@K.Tsang thanks for help
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic