CODEWITHSUNDEEP
javagoogle-app-enginejpagoogle-cloud-datastore
rafaelreuber
rafaelreuber

Reputation: 123

Filtering by date (ignoring time-of-day)

I'm not getting date filter using JPA.

For example my database table data are using this date format:

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

This is my code that filter the rows:

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;
 }

This is my Entity. The date field have TemporalTime annotation

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

}

Upvotes: 0

Views: 1508

Answers (1)

Dave W. Smith
Dave W. Smith

Reputation: 24966

Assuming that this is going to be a common query, the approach that I'd use would be to add a second 'date party only' column, which could be a TemporalType.DATE with time of day zeroed out. This lets you do an exact filter, which is going to get you good query performance from the datastore at the expense of an extra pair of index writes when you add data.

Upvotes: 1

Related Questions