Spring Data JPA: Parameterize @Query Annotation String, Refactor Two Similar @Query Methods

Question

In Spring Data I have 2 very large queries which are essentially identical, but with small differences. I don't want to duplicate the methods for both queries. Suppose I have

Method 1

@Query(value = ".. " +
      " .. " + 
      //... big query
      "...")
public List<Bean> getResult(@Param("studyId") long studyId);

Method 2

@Query(value = ".. " +
      " .. " + 
      //... big query, after WHERE: 
      " and (:startDate is null or :startDate = '' or r.recall_date >= to_date(cast(:startDate as TEXT) " + 
      "...")
public List<Bean> getResult(@Param("studyId") long studyId, @Param("startDate" String startDate);

My goal is:

1) Parameterize the @Query string so that it can either take or omit the optional additional WHERE as a sub-string.

2) Somehow refactor the methods so they don't call separate SQL. The only difference is the additional parameter in Method 2.

Is this possible?

Could you just put the main SQL in a string, have two method signatures, and concatenate the extra SQL to the query on the second method? — darkmnemic
– darkmnemic, Commented Feb 6, 2020 at 19:44
Spring Data won't allow variables inside @Query. It only allows constants. — gene b.
– gene b., Commented Feb 6, 2020 at 19:48

Dirk Deyne · Accepted Answer · 2020-02-06 21:06:37Z

2

Something like this should work

interface ReportTypeRepository extends PagingAndSortingRepository<ReportType,String> {

    final String report = " select r from ReportType r ";
    final String where = " where r.active=:active ";
    final String sort = " order by r.id asc ";

    @Query(report + sort) // <-- all with sort
    List<ReportType> findByQuery();

    @Query(report + where + sort)  // <-- all with where and sort
    List<ReportType> findByActiveQuery(@Param("active") boolean active);

}

Another (probably better) solution is to use Spring Data JPA with Querydsl or the JPA 2 Criteria API where you can define some Predicates and combine then to use multiple constraints.

edited Feb 6, 2020 at 21:06

answered Feb 6, 2020 at 19:57

Dirk Deyne

6,9961 gold badge22 silver badges36 bronze badges

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

2 Comments

gene b. Over a year ago

Are you familiar with Spring Data? Are you aware that Query has a value= attribute?

Dirk Deyne Over a year ago

@geneb.Yes, value is optional, you only need to use it if you do something like @Query( value = "SELECT * FROM .....", nativeQuery = true)

Eduardo Meneses · Accepted Answer · 2020-02-07 15:38:48Z

0

You could also take a look on Specification if you plan to do dynamic queries.

answered Feb 7, 2020 at 15:38

Eduardo Meneses

5344 silver badges18 bronze badges

Collectives™ on Stack Overflow

Spring Data JPA: Parameterize @Query Annotation String, Refactor Two Similar @Query Methods

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related