CODEWITHSUNDEEP
javamysqlspringspring-data-jpajpql
JavaJd
JavaJd

Reputation: 487

How to use Regex keyword in Spring Data Repository Method

I am currently using spring-data-jpa version 1.9.4.

I have a MySql table with columns project(integer), summary(varchar), and description(varchar).

I have a regex that I would like to use to search the summary and/or description field meaning that if it finds it in summary does not need to apply regex to description.

The repository method I am attempting to use is:

List<Issue> findByProjectAndSummaryOrDescriptionRegex(long project, String regex)

The error I am receiving is:

java.lang.IllegalArgumentException: Unsupported keyword REGEX (1): [MatchesRegex, Matches, Regex]

It is difficult in my company environment to update/upgrade versions, so if the issue is NOT my syntax but rather the then if someone knows which version now supports 'Regex' for query derivation or where I could find that specific information I would be grateful. I have looked at the Changelog and it appears that 1.9.4 should support but it appears not.

Thanks for your help!

JD

EDIT 1: I am aware of the @Query annotation but have been asked by my lead to only use that as a last resort if I cannot find the correct version which supports keyword REGEX [MatchesRegex, Matches, Regex]

Upvotes: 9

Views: 20401

Answers (3)

Tomasz
Tomasz

Reputation: 1396

try tu use @Query with param nativeQuery = true inside You can use database regexp_like function :

@Query(value = "select t.* from TABLE_NAME t where regexp_like(t.column, ?1)", nativeQuery = true)

Documentation : https://www.techonthenet.com/oracle/regexp_like.php

Upvotes: 0

JavaJd
JavaJd

Reputation: 487

In a followup, I discovered by doing some digging that the authoratative list will reside in the org.springframework.data.jpa.repository.query.JpaQueryCreator class. So for future folks that want to know which keywords from the 'Documented' list are ACTUALLY implemented, look inside JpaQueryCreator and you will the keywords supported as case arguments inside a switch!

Hope this helps!

PS - as suspected, REGEX was not supported in my version

Upvotes: 6

Darshan Mehta
Darshan Mehta

Reputation: 30819

I would recommend using native query (with @Query annotation) if the Spring data syntax does not work, e.g.:

@Query(nativeQuery=true, value="SELECT * FROM table WHERE project = ?1 AND (summary regexp ?2 OR description regexp ?2)")
List<Issue> findByProjectAndSummaryOrDescription(long project, String regex);

Update

If native query is not an option then (a) could you try it with single column and see if that works and (b) could you try by appending regex to both the columns, e.g.:

List<Issue> findByProjectAndDescriptionRegex(long project, String regex);

List<Issue> findByProjectAndSummaryRegexOrDescriptionRegex(long project, String regex, String regex);

Upvotes: 8

Related Questions