Spring Data JPA - custom @Query with “@Param Date”

Question

I create custom query for getting Invoice data by Date, but it returns null. I'm sure that I set exactly the same Date for query that exists in database.

I use custom query because I want more advance query to write. But issue exist in this simple query. Here is my sample code:

@Query("select i from Invoice i where " +
        "i.expirationDate = :expDate")
Invoice findCompanyInvoiceByDate(@Param("expDate") Date expDate);

I tried this code but it does not work also:

 Invoice findByExpirationDate(Date expirationDate);

I also tried to add @Temporal(TemporalType.DATE) before Date and @Param but result is null.

Answer 1

I just used the date as a String and used nativeQuery = true. It worked well for me.

@Query(value ="select * from table st where st.created_date >= ?1", nativeQuery = true)
    List<YourObject> findAllByCreatedDate(@Param("createdDate") @DateTimeFormat(iso = ISO.DATE) String createdDate);

Answer 2

You should use @Temporal(TemporalType.TIMESTAMP) in your date column. If that still not enough (still return null), add columnDefinition in @Column annotation as well.

Full working example is here (Note the so-40613171 branch. Sorry for weird repository name, and class naming. It uses by a lot of case study). Rough example:

Employee.java

@Entity
public class Employee {

  @Id
  private Integer id;
  private String name;
  private String surname;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "birth_date", columnDefinition = "DATETIME")
  private Date birthDate;

  // Other fields, getter setter, etc.
}

EmployeeRepository.java

public interface EmployeeRepository 
extends JpaRepository<Employee, Integer> {

  @Query("from Employee e where e.birthDate = :birthDate")
  List<Employee> findEmployeeDataByBirthDate(@Param("birthDate") Date birthDate);
}

Sample Data

final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Company companySun = companyRepository.save(new Company(42, "Sun microsystems"));
Company companyGoogle = companyRepository.save(new Company(43, "Google"));

employeeRepository.save(new Employee(101, "James", "Gosling", dateFormat.parse("1970-01-01 17:05:05"), companySun));
employeeRepository.save(new Employee(102, "Paul", "Sheridan", dateFormat.parse("1970-01-01 17:05:05"), companySun));
employeeRepository.save(new Employee(103, "Patrick", "Naughton", dateFormat.parse("1970-01-01 17:05:05"), companySun));

employeeRepository.save(new Employee(201, "Lary", "Page", dateFormat.parse("1970-01-01 17:01:05"), companyGoogle));
employeeRepository.save(new Employee(202, "Sergey", "Brin", dateFormat.parse("1970-01-02 17:02:05"), companyGoogle));

Test Code Snippet

@Test
public void employeService_findByBirthDate() throws ParseException {
    final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    List<Employee> result = this.employeeService.findByBirthDate(dateFormat.parse("1970-01-01 17:05:05"));

    Assert.assertEquals(3, result.size());
}

If you run this, the test is passed.

HTH

Answer 3

I found the answer that was useful for this problem here.

If you use "date" type, I think the code from question should work, but for "datetime" you should use "between" in query or something similar. I found the solution that works for me, and this is the code:

@Query("select i from Invoice i where " +
        "i.expirationDate >= :todayMidnight and i.expirationDate < :tomorowMidnight " +
        "order by expirationDate DESC")
List<Invoice> findCompanyInvoiceByDate(@Param("todayMidnight") Date todayMidnight, @Param("tomorowMidnight") Date tomorowMidnight);