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]

Could you please specify the type of the retrieveHistoryService field and provide the implementation of this type? — Sergey Vyacheslavovich Brunov
– Sergey Vyacheslavovich Brunov, Commented Jan 24, 2022 at 0: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
– Sergey Vyacheslavovich Brunov, Commented Jan 24, 2022 at 0:23
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
– Rick James, Commented Jan 24, 2022 at 2:04

Jitender · Accepted Answer · 2022-01-27 07:06:50Z

4

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.

answered Jan 27, 2022 at 7:06

Jitender

7,99932 gold badges117 silver badges218 bronze badges

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

Comments

jccampanero · Accepted Answer · 2022-01-25 15:26:24Z

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 andRetrieveProductMovementHistoryRequest is just typo error. the method name is RetrieveMovementHistoryRequest
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
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.

Collectives™ on Stack Overflow

Spring data JPA retrieve data with between two dates not working

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related