java - 按日期过滤(忽略一天中的时间)

Question

我没有使用 JPA 获取日期过滤器。

例如，我的数据库表数据使用此日期格式:

id=22001     2012-05-24 01:18:44.459000  -3.72562176     -38.55413138
id=22002     2012-05-24 01:19:58.369000  -3.72556951     -38.55412081
id=22003     2012-05-24 01:20:11.509000  -3.72554203     -38.5541338
id=22004     2012-05-24 01:20:43.832000  -3.72556247     -38.55411875
id=22005     2012-05-27 21:31:31.179000  -3.73500586     -38.5359234
id=22006     2012-05-28 22:41:21.072000  -3.76651343     -38.49477246
id=22007     2012-05-28 22:44:37.100000  -3.76582333     -38.49650576
id=22008     2012-05-28 22:46:47.878000  -3.76732246     -38.49688336
id=22009     2012-05-28 22:48:11.118000  -3.76437084     -38.50487202
id=22010     2012-05-28 22:48:30.419000  -3.76383159     -38.50810391
id=22011     2012-05-28 22:50:21.972000  -3.76130886     -38.50585614
id=22012     2012-05-28 22:51:56.787000  -3.75217442     -38.50331619
id=22013     2012-05-28 22:52:59.405000  -3.7531888  -38.50264043
id=22014     2012-05-28 22:53:48.185000  -3.75311701     -38.50296631
id=22015     2012-05-28 22:54:53.602000  -3.75311704     -38.5029654

这是我过滤行的代码:

public List<GeoLocation> getAll(Date date) {

     javax.persistence.Query q = entityManager
        .createQuery("SELECT g FROM  GeoLocation g where g.criationDate = :data");      
     q.setParameter("data", date,TemporalType.DATE);


     @SuppressWarnings("unchecked")
     List<GeoLocation> result  =  q.getResultList();

     return result;
 }

这是我的实体。日期字段有 TemporalTime 注释

package br.com.drover.entity;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class GeoLocation {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column(nullable=false)
private Double latitude;

@Column(nullable=false)
private Double longitude;

@Column(nullable=false)
@Temporal(TemporalType.DATE)
private Date creationDate;


//..Some getters and setters

}

Answer 1

假设这将是一个常见查询，我使用的方法是添加第二个“仅限日期聚会”列，该列可以是 TemporalType.DATE，其中一天中的时间归零。这使您可以进行精确的过滤，这将使您从数据存储区获得良好的查询性能，但代价是在添加数据时需要进行一对额外的索引写入。