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

[Vladimir Razov]

Basicaly, its similar like looking if certain word exists in sentence. There is entity Post:

public class Post implements Serializable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "post_id", unique = true, nullable = false)
    private Integer id; 

    @Column(name = "post_title", length=300, unique = false, nullable = false)
    private String title;

    @Column(name = "post_date", unique = false, nullable = false)
    private Date date;
        ...}

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]

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

@NamedQueries({
  @NamedQuery(name = "Post.findXXX", query= "select p from post .....")
})
public class Post implements Serializable { ... }

[Vladimir Razov]

@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]

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]

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]

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]

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]

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]

This works (in MySQL):

@NamedQuery(name =  "searchPosts", query = "SELECT p FROM Post p WHERE p.title LIKE :pattern AND p.date>=:startDate AND p.date<=:endDate")

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

@K.Tsang thanks for help