Issue
I am struggling with such a simple task and I have no idea what I am doing wrong.
Based on personal research, it seems I am coding what I am supposed to in order to search all records between two dates using Spring Data. As far as I understood, "@Temporal(TemporalType.DATE)" does the "magic".
Repository
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.persistence.TemporalType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.Temporal;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import com.mybank.accountmanagement.model.Transaction;
@Repository
public interface TransactionRepository extends JpaRepository<Transaction, Long> {
@Query(value = "SELECT * FROM transactions WHERE account_id=:idAccount AND CAST(created_at AS date) BETWEEN :fromDate AND :toDate ", nativeQuery = true)
List<Transaction> findByAccountIdWithCreationDateBetween(@Param("idAccount") Long idAccount,
@Param("fromDate") @Temporal(TemporalType.DATE) Date fromDate,
@Param("toDate") @Temporal(TemporalType.DATE) Date toDate);
}
Models:
Transaction
package com.mybank.accountmanagement.model;
import java.math.BigDecimal;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;
import org.springframework.format.annotation.NumberFormat;
import com.fasterxml.jackson.annotation.JsonIdentityInfo;
import com.fasterxml.jackson.annotation.JsonIdentityReference;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.ObjectIdGenerators;
@Entity
@Table(name = "transactions")
public class Transaction extends AuditModel {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "account_id", nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
@JsonIdentityReference(alwaysAsId = true)
@JsonProperty("accoout_id")
private Account account;
@NotNull
@NumberFormat(pattern = "#,###,###,###.##")
private BigDecimal amount;
@NotNull
private int transactionType;
public Transaction(Account account, @NotNull BigDecimal amount, @NotNull int transactionType) {
super();
this.account = account;
this.amount = amount;
this.transactionType = transactionType;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Account getAccount() {
return account;
}
public void setAccount(Account account) {
this.account = account;
}
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
public int getTransactionType() {
return transactionType;
}
public void setTransactionType(int transactionType) {
this.transactionType = transactionType;
}
}
AuditModel
package com.mybank.accountmanagement.model;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = { "createdAt", "updatedAt" }, allowGetters = true)
public abstract class AuditModel implements Serializable {
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "created_at", nullable = false, updatable = false)
@CreatedDate
private Date createdAt;
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "updated_at", nullable = false)
@LastModifiedDate
private Date updatedAt;
public Date getCreatedAt() {
return createdAt;
}
public void setCreatedAt(Date createdAt) {
this.createdAt = createdAt;
}
public Date getUpdatedAt() {
return updatedAt;
}
public void setUpdatedAt(Date updatedAt) {
this.updatedAt = updatedAt;
}
}
What I was expcting to return when searching with "fromDate":"2020-08-02", "toDate":"2020-08-02"
But it is returning no record at all
*** Edited
For some unknown reason for me, my records with exact date matching fromDate and toDate are token off from result
Searching with longer fromDate and toDate
curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{ "fromDate":"2020-08-01", "toDate":"2020-08-03" }'
brings two records:
[
{
"createdAt": "2020-08-02T16:29:08.085+00:00",
"updatedAt": "2020-08-02T16:29:08.085+00:00",
"id": 1,
"amount": 1.00,
"transactionType": 2,
"accoout_id": 1
},
{
"createdAt": "2020-08-02T16:29:11.185+00:00",
"updatedAt": "2020-08-02T16:29:11.185+00:00",
"id": 2,
"amount": 2.00,
"transactionType": 1,
"accoout_id": 1
}
]
so far so good
now I get a problem while searching with exact date. I would expect same result from above query
curl --location --request GET 'localhost:2000/transaction/1/bankstatement' --header 'Content-Type: application/json' --data-raw '{ "fromDate":"2020-08-02", "toDate":"2020-08-02" }'
brings no result at all
*** edited. Thanks to sample provided by Kavithakaran Kanapathippillai, I noticed that it works with new Date or 2020-08-02T00:00:00.000+00:00 but it fails with 2020-08-02. I am still lost why it fails with 2020-08-02 since I don't care about time and also it works if try one day less: 2020-08-01
Controller
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.mybank.accountmanagement.BankStatementFilter;
import com.mybank.accountmanagement.model.Transaction;
import com.mybank.accountmanagement.repository.TransactionRepository;
import com.mybank.accountmanagement.service.TransactionService;
@RestController
public class TransactionController {
@Autowired
TransactionService transactionService;
@Autowired
TransactionRepository transactionRepository;
@GetMapping("/transaction/{accountId}/bankstatement")
public List<Transaction> bankStatement(@PathVariable(value = "accountId") Long accountId,
@Valid @RequestBody BankStatementFilter bankStatementFilter) {
return transactionRepository.findByAccountIdWithCreationDateBetween(accountId,
bankStatementFilter.getFromDate(), bankStatementFilter.getToDate());
}
}
A simple Pojo used only to get new Date from the string sent by client (ex. Postman or curl)
import java.util.Date;
public class BankStatementFilter {
private Date fromDate;
private Date toDate;
public Date getFromDate() {
return fromDate;
}
public void setFromDate(Date fromDate) {
this.fromDate = fromDate;
}
public Date getToDate() {
return toDate;
}
public void setToDate(Date toDate) {
this.toDate = toDate;
}
}
It smells some stupid thing done from my part with this POJO BankStatementFilter
Here is what I noted while comparing new Date() with 2020-08-02. Since I don't care about time, isn't that correctly?
*** Final comment
It is working now. I changed my POJO to bellow. If I am doing some silly thing I will be thanks for advice. BTW, my original question was 100% answered.
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class BankStatementFilter {
private Date fromDate;
private Date toDate;
public Date getFromDate() {
return fromDate;
}
public void setFromDate(String fromDate) {
String pattern = "yyyy-MM-dd";
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
try {
this.fromDate = simpleDateFormat.parse(fromDate);
} catch (ParseException e) {
e.printStackTrace();
}
}
public Date getToDate() {
return toDate;
}
public void setToDate(String toDate) {
String pattern = "yyyy-MM-dd";
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
try {
this.toDate = simpleDateFormat.parse(toDate);
} catch (ParseException e) {
e.printStackTrace();
}
}
}
Solution
@Temporal.Datedoes not have any impact on your spring data repository method. It has impact only when it is annotated in the entity.Instead do the following to
castyour parameters too:
@Query(value = "SELECT * FROM transactions WHERE account_id=:idAccount " +
"AND CAST(created_at AS date) " +
"BETWEEN CAST(:fromDate AS date) AND CAST(:toDate AS date)"
, nativeQuery = true)
List<Transaction> findBy(@Param("idAccount") Long idAccount,
@Param("fromDate") Date fromDate,
@Param("toDate") Date toDate);
Here is sample project which I set up with your entity except
accountas it is not in the question if you like to compare. You can import the project as maven into IntelliJ and run the main application, it will insert data and bring it back
Answered By - Kavithakaran Kanapathippillai
