Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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]

like image 991
JavaJd Avatar asked Jun 24 '17 15:06

JavaJd


People also ask

Should I use JpaRepository or CrudRepository?

Crud Repository doesn't provide methods for implementing pagination and sorting. JpaRepository ties your repositories to the JPA persistence technology so it should be avoided. We should use CrudRepository or PagingAndSortingRepository depending on whether you need sorting and paging or not.

What is @repository in spring boot?

@Repository Annotation is a specialization of @Component annotation which is used to indicate that the class provides the mechanism for storage, retrieval, update, delete and search operation on objects.

How can you configure JPQL query for a query method in a repository in Spring data JPA?

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.

What is @query used for spring?

The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward. The @Query annotation supports both native SQL and JPQL.


1 Answers

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);
like image 193
Darshan Mehta Avatar answered Sep 19 '22 07:09

Darshan Mehta