CODEWITHSUNDEEP
javasqlspringspring-data-jpajpql
Martin
Martin

Reputation: 1350

How to select a variable number of like strings at same field with a JPA query?

I am triying to do a like query in spring using a JPA query in order to find a variable set of texts (like OR query) on same field.

In SQL language:

SELECT * FROM A WHERE text LIKE '%A%' OR text LIKE '%B%' OR text  LIKE '%C%'.... (Variable number)

In JPA it could be similar to this (this not a real code, its the main idea of expected working):

@Query("SELECT t FROM table t WHERE t.text LIKE :setOfTerms")
    Page<Example> findTweetsNotParsed(@Param("setOfTerms") Set<String> likeQuery, Pageable pageable);

Is there any technique or methodology for do this with JPA queries?

EDIT: I had been edited the question due to a mistake in the like query. I write AND but i wanted to put OR.

Upvotes: 1

Views: 249

Answers (1)

Joe W
Joe W

Reputation: 2891

, nativeQuery=trueIn MySQL you probably will need to process the set of terms before it gets passed to the query so that you could generate something like

... text LIKE '%A%B%C%

So your sample code could become:

Page<Example> findTweetsNotParsed(Set<String> likeTerms, Pageable pageable) {
   return executeTweetsNotParsed(String.join("%", likeTerms),pageable)
}

@Query("SELECT t FROM table t WHERE t.text LIKE :setOfTerms")
    Page<Example> executeTweetsNotParsedQuery(@Param("setOfTerms")String likeQuery, Pageable pageable);

For the OR case you need to use REGEXP. Something like:

Page<Example> findTweetsNotParsed(Set<String> likeTerms, Pageable pageable) {
       String regex = ".*"+String.join(".*|.*", likeTerms)+".*";
       return executeTweetsNotParsed(regex, pageable)
    }

@Query(value = "SELECT t FROM table t WHERE t.text REGEXP (:setOfTerms)", nativeQuery=true)
    Page<Example> executeTweetsNotParsedQuery(@Param("setOfTerms")String likeQuery, Pageable pageable);

I didn't test the regex but if it is a pure contains it should be something like that so that you get a query like

... text REGEXP '.*A.*|.*B.*|.*C.*"

You may need to craft the regex gen code to your specific needs.

As an aside you can achieve the same sort of thing in Postgres using the SIMILAR TO operator.

Upvotes: 1

Related Questions