8

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]

harshavmb
  • 3,404
  • 3
  • 21
  • 55
JavaJd
  • 445
  • 1
  • 7
  • 17
  • Given that `Regex` it is already listed in the docs for 1.3.0 (http://docs.spring.io/spring-data/jpa/docs/1.3.0.RELEASE/reference/html/repository-query-keywords.html), the version of spring-data-jpa is likely not the issue here – crizzis Jun 24 '17 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- Jun 24 '17 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 Jun 24 '17 at 16:14

3 Answers3

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);
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • I believe `SummaryRegexOrDescriptionRegex` implies two string parameters – crizzis Jun 24 '17 at 16:02
  • 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]http://docs.spring.io/spring-data/jpa/docs/1.9.4.RELEASE/reference/html/#repository-query-keywords it indicates that Regex should be allowed. – JavaJd Jun 24 '17 at 16:03
  • @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. – JavaJd Jun 24 '17 at 16:08
  • The original example, `findByProjectAndSummaryOrDescriptionRegex`, implies two string parameters as well (`AND summary= ? OR description REGEXP ?`) – crizzis Jun 24 '17 at 16:10
  • 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 Jun 24 '17 at 16:11
  • @JavaJd yes, as per the Spring data documentation, it should work. I would probably try changing the logging level and see where it fails and why it should work. If not, native query is anyway there :) – Darshan Mehta Jun 24 '17 at 16:15
  • I just tried your suggestion @crizzis and it resulted in the same error being thrown. I am definitely thinking that 1.9.4.RELEASE of spring-data-jpa does not support keyword REGEX. Thanks again for your time. – JavaJd Jun 24 '17 at 16:17
  • @DarshanMehta I attempted to upvote your answer but unfortunately I am too low on the totem pole. Thank you again! – JavaJd Jun 24 '17 at 16:18
  • Wow, thanks to you guys and your activity today I have earned enough rep to upvote, ty both! --> @DarshanMehta increased logging level, great idea, i will do that now. – JavaJd Jun 24 '17 at 16:20
  • @JavaJD The error message `Unsupported keyword REGEX (1): [MatchesRegex, Matches, Regex]` suggests Spring Data knows what you're trying to do, I'd rather suspect that `Regex` is not supported for (your version of?) MySQL – crizzis Jun 24 '17 at 16:20
  • @crizzis [link]https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html I think you may be correct in that the MySql documenation shows Regexp is allowed but not Regex, somehow I thought the JPA converter for MySql would have changed this in the query derivation logic. – JavaJd Jun 24 '17 at 16:28
  • @DarshanMehta, I upped the logging to DEBUG but nothing in addition to the stack trace from before was helpful. Again, I should have thought to have done this already but am happy you reminded me :) – JavaJd Jun 24 '17 at 16:29
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

JavaJd
  • 445
  • 1
  • 7
  • 17
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

Tomasz
  • 884
  • 8
  • 12