Spring data JPA retrieve data with between two dates not working

Question

Edited:

I am trying to get the data by filtering it by dates, But I am getting the following error. I am accepting date in LocalDateTimeFormat. However, the type of created_date is Instant in JPA entity. I cannot change the type in entity because it might break other APis

Parameter value [2021-12-23T13:00] did not match expected type [java.time.Instant (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [2021-12-23T13:00] did not match expected type [java.time.Instant (n/a)]

Curl request

curl --location --request GET 'http://localhost:9202/movement-history?amd=xs&fromCreatedDate=2021-12-23T13:00&toCreatedDate=2021-12-23T15:10'

Response

{
    "code": 0,
    "message": "Success",
    "data": {
        "movementHistory": [],
        "totalCount": 0,
        "page": 1,
        "limit": 20
    }
}

Controller

@GetMapping( value = "/movement-history")
    public ResponseDto<RetrieveMovementHistoryResponse> retrieveMovementHistory(
            @Valid RetrieveMovementHistoryRequest retrieveMovementHistoryRequest
    ) {
        return responseUtil.prepareSuccessResponse( retrieveHistoryService.doAction( retrieveMovementHistoryRequest ),
                null );
    }

retrieveMovementHistoryRequest DTO

package ai.growing.platform.inventory.dto;

import ai.growing.platform.library.dto.Request;
import lombok.*;
import lombok.experimental.FieldDefaults;
import org.hibernate.validator.constraints.Range;
import org.springframework.format.annotation.DateTimeFormat;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Positive;
import java.time.Instant;
import java.util.List;

@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@FieldDefaults( level = AccessLevel.PRIVATE )
public class RetrieveMovementHistoryRequest extends Request {
    List<String> amd;
    @DateTimeFormat( pattern = "yyyy-MM-dd'T'HH:mm" )
    LocalDateTime fromCreatedDate;
    @DateTimeFormat( pattern = "yyyy-MM-dd'T'HH:mm" )
    LocalDateTime toCreatedDate;
    @Positive( message = "limit can not be negative" )
    @Builder.Default
    @NotNull
    Integer limit = 20;

    @Builder.Default
    @Range( min = 1, message = "page can not be zero or negative" )
    @NotNull
    Integer page = 1;
}

Search specification for JPA

public class TransactionSearchSpecification {

    private static final String AMD = "amd";
    private static final String CREATED_DATE = "createdDate";

    public static Specification<Transaction> getTransactionBySpecification(
        TransactionSearchCriteria transactionSearchCriteria) {

        return (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            
            if( !CollectionUtils.isEmpty( transactionSearchCriteria.getAmdList() ) ) {
                predicates.add( root.get( SKU ).in( transactionSearchCriteria.getAmdList() ) );
            }

            if( !StringUtils.isEmpty( transactionSearchCriteria.getFromDate() ) && !StringUtils.isEmpty(
                transactionSearchCriteria.getToDate() ) ) {
                predicates.add( criteriaBuilder
                    .between( root.get( CREATED_DATE )
                        , transactionSearchCriteria.getFromDate(), transactionSearchCriteria.getToDate() ) );
            }

           
            return criteriaBuilder.and( predicates.toArray( new Predicate[ 0 ] ) );
        };
    }
}

entity

package ai.growing.platform.product.inventory.entity;

import java.io.Serializable;
import java.time.Instant;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@AllArgsConstructor
@Builder
@Entity
@Table( name = "transaction" )
@Data
@NoArgsConstructor
public class Transaction implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue( strategy = GenerationType.IDENTITY )
    @Column( name = "id", nullable = false )
    private Long id;

    @Column( name = "amd", nullable = false )
    private String amd;
    
    @Column( name = "request_type" )
    private String requestType;

    @CreationTimestamp
    @Column( name = "created_date", nullable = false )
    private Instant createdDate;
    
}

Data in DB

id  created_date    request_type    amd
850476  2021-12-23 13:01:19 COMPLETED   xs
850480  2021-12-23 14:58:17 COMPLETED   xs
850474  2021-12-23 13:00:41 INITIATED   xs
850478  2021-12-23 14:58:08 INITIATED   xs

Query issued by JPA

select
        transactio0_.id as id1_11_,
        transactio0_.created_date as created_2_11_,
        transactio0_.amd as amd3_11_,
    from
        transaction transactio0_ 
    where
        (
            transactio0_.amd in (
                ?
            )
        ) 
        and (
            transactio0_.created_date between ? and ?
        ) limit ?
2022-01-24 09:02:00.918 TRACE [...true] 29314 --- [tp1324897979-85] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [xs]
2022-01-24 09:02:00.918 TRACE [...,true] 29314 --- [tp1324897979-85] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [TIMESTAMP] - [2021-12-23T13:00:00Z]
2022-01-24 09:02:00.920 TRACE [...,true] 29314 --- [tp1324897979-85] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [TIMESTAMP] - [2021-12-23T15:10:00Z]

what was the [generated sql](https://www.baeldung.com/sql-logging-spring-boot)? — indybee, Jan 21 '22 at 13:46
Could you please specify the type of the `retrieveHistoryService` field and provide the implementation of this type? — Sergey Vyacheslavovich Brunov, Jan 24 '22 at 00:21
It looks like there is a typo: the controller method refers to the `RetrieveMovementHistoryRequest` class, but the request class is named as `RetrieveProductMovementHistoryRequest`. Which name is the right one? — Sergey Vyacheslavovich Brunov, Jan 24 '22 at 00:23
Could you please provide the implementation of the `TransactionSearchCriteria` type? — Sergey Vyacheslavovich Brunov, Jan 24 '22 at 00:23
Please, note that there is a typo in the `curl` command line: `&fromCreatedDat2021-12-23T13:00&`. It should be: `&fromCreatedDate=2021-12-23T13:00&`. Please, consider correcting the question. — Sergey Vyacheslavovich Brunov, Jan 24 '22 at 00:25
Since you tagged it with MySQL, please provide `SHOW CREATE TABLE` and the generated SQL so we can approach it from that direction. — Rick James, Jan 24 '22 at 02:04
@sergey: Sorry It was a typo error.TransactionSearchCriteria already there in question — Jitender, Jan 24 '22 at 02:56
That's just basic debugging issue... Add debug output here and there, step though with a debugger and you should be able to pinpoint the bug. — Alexey Veleshko, Jan 24 '22 at 13:00

jccampanero · Answer 1 · 2022-01-25T15:26:24.523

As you can see in your stacktrace, the error is related to the conversion between the value you provided 2021-12-23T13:00 to Instant, probably when Spring Data executes the JPA Criteria query you created in your Specification.

To solve the problem you could try manually converting from LocalDateTime to Instant in your Specification code - I assume that both transactionSearchCriteria.getFromDate() and transactionSearchCriteria.getToDate() are String. For example:

public class TransactionSearchSpecification {

    private static final String AMD = "amd";
    private static final String CREATED_DATE = "createdDate";

    public static Specification<Transaction> getTransactionBySpecification(
        TransactionSearchCriteria transactionSearchCriteria) {

        return (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            
            if( !CollectionUtils.isEmpty( transactionSearchCriteria.getAmdList() ) ) {
                predicates.add( root.get( SKU ).in( transactionSearchCriteria.getAmdList() ) );
            }

            String fromDate = transactionSearchCriteria.getFromDate();
            String toDate = transactionSearchCriteria.getToDate();
            if( !StringUtils.isEmpty( fromDate ) && !StringUtils.isEmpty(
                toDate ) {
                try {
                  Instant fromInstant = this.fromString(fromDate);
                  Instant toInstant = this.fromString(toDate);
                  predicates.add(
                    criteriaBuilder.between(
                      root.get( CREATED_DATE ), fromInstant, toInstant
                    )
                  );
                } catch (DateTimeParseException dtpe) {
                  // invalid format, consider log the error, etcetera
                  dtpe.printStackTrace();
                }
            }
           
            return criteriaBuilder.and( predicates.toArray( new Predicate[ 0 ] ) );
        };
    }

    // This method is suitable for being defined probably in a common, utility class
    // Following, for example, the advice provided in this SO question
    // https://stackoverflow.com/questions/50299639/converting-string-to-instant
    private Instant fromString(final String localDateTimeString) throws DateTimeParseException {
      if (StringUtils.isEmpty(localDateTimeString) {
        return null;
      }

      // Convert String to LocalDateTime
      LocalDateTime localDateTime = LocalDateTime.parse(
        localDateTimeString,
        DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm" , Locale.US )
      );

      // Provide the appropriate ZoneId
      Instant result = localDateTime
        .atZone(ZoneId.of("Asia/Kolkata"))
        .toInstant();

      // Return the obtained instant
      result;
    }
}

Which is unclear to me is the role of RetrieveProductMovementHistoryRequest. If, in any way, the Specification involves using, for example, LocalDateTime fromCreatedDate, then again my advice is that you need to manually adapt the code in the Specification to handle the Instant values. I mean:

public class TransactionSearchSpecification {

    private static final String AMD = "amd";
    private static final String CREATED_DATE = "createdDate";

    public static Specification<Transaction> getTransactionBySpecification(
        TransactionSearchCriteria transactionSearchCriteria) {

        return (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            
            if( !CollectionUtils.isEmpty( transactionSearchCriteria.getAmdList() ) ) {
                predicates.add( root.get( SKU ).in( transactionSearchCriteria.getAmdList() ) );
            }

            LocalDateTime fromDate = transactionSearchCriteria.getFromDate();
            LocalDateTime toDate = transactionSearchCriteria.getToDate();
            if( fromDate != null && toDate != null ) {
                Instant fromInstant = this.fromLocalDateTime(fromDate);
                Instant toInstant = this.fromLocalDateTime(toDate);
                predicates.add(
                  criteriaBuilder.between(
                    root.get( CREATED_DATE ), fromInstant, toInstant
                  )
                );
            }

           
            return criteriaBuilder.and( predicates.toArray( new Predicate[ 0 ] ) );
        };
    }

    // Again, this method is suitable for being defined probably in a common, utility class
    private Instant fromLocalDateTime(final LocalDateTime localDateTime) {
      if (localDateTime == null) {
        return null;
      }

      // Provide the appropriate ZoneId
      Instant result = localDateTime
        .atZone(ZoneId.of("Asia/Kolkata"))
        .toInstant();

      // Return the obtained instant
      result;
    }
}

For your comments it seems you aren't getting any results.

As a first step, try debugging and isolating the error: be sure to check whether your repository is actually returning or not any results.

If it is returning the appropriate records from the database, check your service code, the one that interact with the Specification.

If your repository is not returning any results, the problem could be related to different things.

Mainly, please, be aware that we are using time instants. As you can read in the MySql documentation, depending on different factors, especially on the type of the CREATED_DATE column, either TIMESTAMP or DATETIME, it is possible that you obtain unexpected results when performing your queries, which may explain why you aren't obtaining any results.

Thanks for answer, I will try this and`RetrieveProductMovementHistoryRequest` is just typo error. the method name is `RetrieveMovementHistoryRequest` — Jitender, Jan 24 '22 at 03:04
Hey, now the error is gone and I can see the query issued by JPA in the console. However, I am still getting an empty response. When I run query with those value directly in mySql. it is giving result. I have added my question to give you more details — Jitender, Jan 24 '22 at 05:05
Thank you very much for the feedback @Jitender. I am happy to hear that you were able to advance in the resolution of the problem. I updated the answer with some information summarizing my previous comments. I hope it helps. — jccampanero, Jan 25 '22 at 15:28

score 2 · Accepted Answer · answered Jan 27 '22 at 07:06

I found the issue, Well it might be a way of how JPA interacts with MySQL or MySQL itself. The issue was the fromCreatedDate and toCreatedDate was get converted into localDateTime at the of execution. For example I was sending date as instant 2021-12-20T13:00:41Z and 2021-12-29T15:10:08Z which was getting converted to UAE localtime 2021-12-23 17:00:41.0 and 2021-12-23 19:10:08.0. So the code was working fine but since there was not record on that day. I was giving me empty result.

I enable MySql general logs to see what query is actually being hit to MySql. I took reference from this video for enabling logs in MySql.

Spring data JPA retrieve data with between two dates not working

2 Answers2