Rached Anis Rached Anis - 4 months ago 41
SQL Question

Spring data jpa findByDate is always returning an empty list

I'm using in my application spring-data-jpa 1.9.2, mysql-connector 5.1 and hibernate 4.3.11 Final.

My Order class has a "creation" attribute of type date.

@Entity
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private Date creation;
}


In my JpaRepository I have these two methods to get the orders by date:

List<Order> findByCreation(Date date);
@Query("select o from Order o where o.creation = ?1")
List<Order> findByCreation2(Date date);


I'm not having an exception, but always an empty list, this can help you understand:

Date date = new Date();
Order order = new Order(date);
orderRepository.save(order);
Date creationFromDB = orderRepository.findOne(1L).getCreation();
System.out.println("eq? : "+ creationFromDB.equals(order.getCreation()));
List<Order> ods = orderRepository.findByCreation(creationFromDB);
List<Order> ods2 = orderRepository.findByCreation2(creationFromDB;
System.out.println("\n ods.size: " + ods.size() +", ods2.size: "+ods2.size());


The output is :

eq? : true
ods.size: 0, ods2.size: 0


NOTE

The select request had executed correctly and twice:

DEBUG org.hibernate.SQL - select order0_.id as id2_4_, order0_.creation as creation3_4_ from Orders order0_ where order0_.creation=?


What am I missing?

Answer

Looking at your code, I assume you are using java.util.Date. If so, use the following in the entity:

 @Temporal(TemporalType.TIMESTAMP)
 private Date creation;

if your column in the database is defined to be of type timestamp. If you are using pure date part in the column use TemporalType.DATE.

This is not required if you are using java.sql.Date.

Comments