CODEWITHSUNDEEP
javamysqlsqldatespring-data
Rached Anis
Rached Anis

Reputation: 349

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 java.util.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?

Upvotes: 2

Views: 5791

Answers (3)

lrother
lrother

Reputation: 402

Did you generate your db scheme? Have you tried to generate it?

If it is possible to generate (with drop), set following property in your application.properties.

spring.jpa.hibernate.ddl-auto=create-drop

Potentially you have different data types (e.g. date vs. datetime) in you DB and used by spring data. I've recreated your project, and everithing worked fine whith H2 and MySQL.

EDIT:

Try to update your column description as follows:

@Column(name = "creation", columnDefinition="TIMESTAMP(6)")
@Temporal(TemporalType.TIMESTAMP)
private Date creation;

There might be different precisions. See my code:

Order class.

@Entity
@Table(name = "Ordr")
public class Order implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @Column(name = "creation", columnDefinition="TIMESTAMP(6)")
    @Temporal(TemporalType.TIMESTAMP)
    private Date creation;

    // getters and setters
}

Test bean class.

@Component
public class SomeBean {

    @Autowired
    private OrderRepository orderRepository;

    @PostConstruct
    public void init() {

        Date date = new Date();

        Order order = new Order();
        order.setId(1L);
        order.setCreation(date);

        orderRepository.save(order);

        Date creationFromDB = orderRepository.findOne(1L).getCreation();

        System.out.println("eq? : "+ new Date(creationFromDB.getTime()).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());

    }
}

Result: eq? : true ods.size: 1, ods2.size: 1

DB query:

mysql> select * from ordr;
+----+----------------------------+
| id | creation                   |
+----+----------------------------+
|  1 | 2016-08-03 15:15:12.386000 |
+----+----------------------------+
1 row in set (0,00 sec)

Upvotes: 1

ujulu
ujulu

Reputation: 3309

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.

Upvotes: 1

Abdysamat
Abdysamat

Reputation: 1

I could be wrong:

  1. You need describe "creation" property

    @Column(name = "db_column_name")
    private Date creation;

  2. I recomennded use Timestamp in class and in DB too. Timestamp more useful.

I hope to help

Upvotes: 0

Related Questions