Spring Data Querying DateTime with only Date

Question

I have this Data Model

public class CustomerModel{

  @Column
  @Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
  private DateTime membershipDate;
  //Other properties and getters
}

And the following repo

public interface CustomerRepo  extends Repository<CustomerModel, Long>{}

What I want to do is. Retrieve all users on a given date eg(Members that Joined in August 1 2013) however the problem is that on my DB the membershipDate has a time with it. how can I ignore the time and retrieve all users on a given date?

Answer 1

Unfortunately with JodaTime the only way around this is using the Between keyword and use two DateTime instances making up the day.

interface CustomerRepo extends Repository<CustomerModel, Long>{

  List<CustomerModel> findByMemberShipDateBetween(DateTime start, DateTime end);
}

If your domain model used Java Dates internally you could've used this style:

interface CustomerRepo extends Repository<CustomerModel, Long>{

  List<CustomerModel> findByMemberShipDate(@Temporal(TemporalType.DATE) Date date);
}

Not the @Temporal annotation is a custom Spring Data JPA one as the plain JPA one is currently not allowed on parameters. The reason that this only works with Java Dates unfortunately is a limitation of the current JPAPIs. The setParameter(…) method on Query only takes a TemporalType for parameters of type Date. We could try converting the JodaTime objects on parameter binding but I guess the persistence providers will reject that due to the type mismatch then (Date VS. DateTime).

Answer 2

You can do some workaround: create DateTime dayBegin and DateTime dayEnd which are e.g. 2013-07-04 00:00:00 and 2013-07-04 23:59:59 and fetch needed objects by query:

List<CustomerModel> findByMembershipBetween(DateTime dayBegin, DateTime dayEnd);

Answer 3

jODA and spring & JPA actually good friends, just take alook at :

http://blog.netgloo.com/2015/04/06/spring-boot-using-joda-time-on-jpa-entity-with-hibernate/

enjoy