CODEWITHSUNDEEP
javaspring-boothibernatejpa
Sachin Mukherjee
Sachin Mukherjee

Reputation: 51

findByDate in spring boot jpa gives empty list

I'm using spring boot JPA for CRUD operations. I'm querying the database table with the field name created_at which is of type date.There are some rows in the table with the given date but JPA is giving zero result set. I'm using Oracle 11g

Here is my entity

import java.sql.Date;
@Entity
@Table(name="veev_json")
public class VeevJson {
    
    @Id
    @Column(name="ID")
    private int id;
    
    @Column(name="CREATED_AT")
    private Date createdDate;
}

My JPA Repository

import java.util.Date;
@Repository
public interface VeevJsonRepository extends JpaRepository<VeevJson, Integer> {
    public List<VeevJson> findByCreatedDate(Date date);
}

Calling the function in the service layer

Date date = new Date(); //taking current date of type java.util.Date
List<VeevJson> documents = veevJsonRepository.findByCreatedDate(date);

My DB table structure

ID  NUMBER(10,0)
CREATED_AT  DATE

SQL query generated by the hibernate:

select veevjson0_.ID as ID1_1_, veevjson0_.CREATED_AT as CREATED_AT2_1_, veevjson0_.JSON as JSON3_1_, veevjson0_.STATUS as STATUS4_1_ from veev_json veevjson0_ where veevjson0_.CREATED_AT=?

Upvotes: 0

Views: 709

Answers (1)

Paulo Ara&#250;jo
Paulo Ara&#250;jo

Reputation: 569

When using a field with type Date, you should also use the @Temporal annotation. The default value of @Temporal is TemporalType.TIMESTAMP, and your JPA implementation may get confused about dealing with a field of type java.util.Date, passing as argument of query the timestamp instead of date.

Please annotate your field as

import java.util.Date;

@Entity
@Table(name = "veev_json")
public class VeevJson {
    @Id
    @Column(name = "ID")
    private int id;

    @Temporal(TemporalType.DATE)
    @Column(name = "CREATED_AT")
    public Date createdDate;

...
}

Doing so will allow JPA implementation to send as queried value only the date (probably in 'YYYY-MM-dd' format) instead of timestamp or any other value.

If you prefer and your JDBC supports 4.1 version, you may exchange the java.util.Date for java8 time API types, but I guess this is out of scope here.

Upvotes: 1

Related Questions