How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

huangapple go评论53阅读模式
英文:

How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

问题

您正在遇到一个问题,当使用Spring Data搜索两个日期之间的所有记录时,您期望返回包含“fromDate”和“toDate”的记录,但实际上没有返回任何记录。在您的问题描述中,您已提供了相关的代码和一些测试情况。您还提到了在使用新日期或“2020-08-02T00:00:00.000+00:00”时可以正常工作,但使用“2020-08-02”时无法正常工作。

您还分享了关于“BankStatementFilter”类的修改,它使用SimpleDateFormat将日期字符串转换为Date对象,这似乎解决了问题。

请问有什么特定的问题或需求您希望我帮助解决吗?

英文:

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"

How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

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

  1. 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

  1. 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

How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

*** 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?

How to use springframework.data.jpa.repository.Query to find all results between two dates ignoring time

*** 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();
		}
	}

}

答案1

得分: 1

你尝试在SQL查询中使用 >= 和 <= 吗?在我的情况下,这能正常工作。

此外,我的建议是不要使用本机查询,而是尝试使用模型类进行快速和正确的反序列化,因为你已经定义了模型类。

英文:

Have you tried using >= and <= in the SQL query, in my case that works as expected.

Also my advice would be to not use native query and try using model class for quick and correct Deserialization in your case since you have the model class defined.

答案2

得分: 1

  • @Temporal.Date对你的Spring数据存储库方法没有任何影响。它只有在实体上进行注解时才会产生影响。

  • 相反,要对参数进行cast,请执行以下操作:

@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);
  • 这是一个示例项目,我使用了你的实体设置,除了account,因为问题中没有提到。如果你想进行比较,可以将该项目导入到IntelliJ中,并运行主应用程序,它将插入数据并将其返回。

    https://github.com/kavi-kanap/stackoverflow-63212441

英文:
  • @Temporal.Date does 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 cast your parameters too:

    @Query(value = &quot;SELECT * FROM transactions WHERE account_id=:idAccount &quot; +
            &quot;AND CAST(created_at AS date) &quot; +
            &quot;BETWEEN CAST(:fromDate AS date) AND CAST(:toDate AS date)&quot;
            , nativeQuery = true)
    List&lt;Transaction&gt; findBy(@Param(&quot;idAccount&quot;) Long idAccount,
                             @Param(&quot;fromDate&quot;) Date fromDate,
                             @Param(&quot;toDate&quot;) Date toDate);
  • Here is sample project which I set up with your entity except account as 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

    https://github.com/kavi-kanap/stackoverflow-63212441

huangapple
  • 本文由 发表于 2020年8月2日 12:39:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/63212441.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定