How to use Regex keyword in Spring Data Repository Method

Question

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]

Given that Regex it is already listed in the docs for 1.3.0 (docs.spring.io/spring-data/jpa/docs/1.3.0.RELEASE/reference/…), the version of spring-data-jpa is likely not the issue here — crizzis
– crizzis, Commented Jun 24, 2017 at 16:01
I think the problem is that your conditions are ambiguous (do you group right or left?), and it's unclear whether you're wanting the regex to apply to just description or also summary. This is complicated enough it really seems best to me to write the JPQL explicitly. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented Jun 24, 2017 at 16:08
@Chrylis Thank you for your response! For the time being I am going to go that route and research this issue on the side. It bothers me the documentation indicates one thing and not the other. — JavaJd
– JavaJd, Commented Jun 24, 2017 at 16:14

Darshan Mehta · Accepted Answer · 2017-06-24 16:13:43Z

8

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);

edited Jun 24, 2017 at 16:13

answered Jun 24, 2017 at 15:51

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

crizzis Over a year ago

I believe SummaryRegexOrDescriptionRegex implies two string parameters

JavaJd Over a year ago

I updated, compiled and ran the code for each example you suggested and still received the IllegalArgumentException for Unsupported keyword REGEX. When I look at the keywords, [link]docs.spring.io/spring-data/jpa/docs/1.9.4.RELEASE/reference/… it indicates that Regex should be allowed.

JavaJd Over a year ago

@crizzis - even using the single example that Darshan suggested resulted in the same error being thrown, 'findByProjectAndDescriptionRegex(long project, String regex). So I am not certain that the arg is the issue at this point.

crizzis Over a year ago

The original example, findByProjectAndSummaryOrDescriptionRegex, implies two string parameters as well (AND summary= ? OR description REGEXP ?)

JavaJd Over a year ago

Darshan Mehta & @crizzis, Thank you both for your time and assistance today. I am going to go ahead and use the native query as a placeholder since I have not found a well documented demarcation as to which version actually supports the keyword even though the linked documentation indicates it is. I hope you both have a grand Saturday!

|

JavaJd · Accepted Answer · 2017-06-28 14:33:24Z

6

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

answered Jun 28, 2017 at 14:33

JavaJd

4771 gold badge8 silver badges19 bronze badges

Comments

Tomasz · Accepted Answer · 2021-02-08 07:39:29Z

0

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

answered Feb 8, 2021 at 7:39

Tomasz

1,45413 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to use Regex keyword in Spring Data Repository Method

3 Answers 3

12 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related