CODEWITHSUNDEEP
hibernatejpaspring-data-jpaspring-dataspring-data-rest
James
James

Reputation: 3184

Spring Data - How to Convert @Param of type YearMonth to start and end LocalDate?

Background Domain

I have rates for a product that are stored monthly in a DB. The start date is always the last day of the previous month and the end date is the last date of the current month. For example, the March rate of 2020 has a start date of 2020-02-29 and end date of 2020-03-31.

Current Implementation

Here's the Rate class:

@Entity
@Data
public class Rate {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    @JoinColumn(name="productId")
    @JsonBackReference
    private Product product;
    
    private LocalDate startDate;
    private LocalDate endDate;
    private BigDecimal value;
    
}

I have a Spring Data repository that has the following method:

Rate findByProductIdAndStartDateAndEndDate(
        @Param("productId") Long productId,
        @Param("startDate") @DateTimeFormat(iso=DateTimeFormat.ISO.DATE) LocalDate startDate, 
        @Param("endDate") @DateTimeFormat(iso=DateTimeFormat.ISO.DATE) LocalDate endDate);

Goal

I would like to abstract this detail from clients by (for example) calling the Rate repository by rewriting findByProductIdAndStartDateAndEndDate as follows:

//OK with writing JPQL here via @Query
Rate findByProductIdAndYearMonth(
        @Param("productId") Long productId,
        @Param("yearMonth") @DateTimeFormat(iso=DateTimeFormat.ISO.DATE) YearMonth yearMonth);

What I've Tried / Considered

I've considered AttributeConverter after reading this post. But I'm trying to convert from one field, yearMonth to two database columns startDate and endDate.

I know that I could write a service class to take YearMonth and then call the repo after determining start and end dates. But I'm using Spring Data REST and want to allow clients to directly call this repo via:

/api/rates/search/findByProductIdAndYearMonth

Upvotes: 1

Views: 1085

Answers (2)

Alan Hay
Alan Hay

Reputation: 23236

If your database supports virtual columns then I would consider creating one named say, period, that is computed as month/year of the end date.

If that virtual column is mapped in your entity as any other property then querying becomes trivial. You can make the JPA property read only. There are probably advantages here for querying in general as any queries can simply be written as period = '0320' or whatever without having to worry about start and end dates.

If your DB does not support virtual columns then you can achieve similar by means of a 2 column database view (rate_id, period) which is then joined to Rate entity by means of @SecondaryTable.

In either solution, your Rate entity has a new property period which you can query and sort on as you would for any other column.

Upvotes: 2

msp
msp

Reputation: 3362

What you can always do is using SPeL that invokes a custom method in a @Query that does the converting for you. For example create a utility class like

package com.acme.util;

import java.time.LocalDate;
import java.time.YearMonth;

public class RateDateUtil {

   public static LocalDate toEndDate(YearMonth ym) {
     return ym.atEndOfMonth();
   }
    
   public static LocalDate toStartDate(YearMonth ym) {
     return ym.minusMonths(1).atEndOfMonth();
   }
}

And then use it in your @Query

@Query("from Rate r join r.product p where p.id = :#{#productId} and r.startDate = :#{T(com.acme.util.RateDateUtil).toStartDate(#yearMonth)} and r.endDate = :#{T(com.acme.util.RateDateUtil).toEndDate(#yearMonth)}")
Rate findByProductIdAndYearMonth(@Param("productId") Long productId, @Param("yearMonth") @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) YearMonth yearMonth);

Note that in older spring-data-jpa versions you can't mix SPeL and JPA ways, that's why the productId needs to be wrapped as well. This might have been fixed in newer versions (I tested this with 1.1.x version). See this answer to a different question for more info.

Upvotes: 1

Related Questions