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]
CodePudding user response：
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.
CodePudding user response：
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.