Spring Data JPA - custom @Query with "@Param Date" doesn't work

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.

does your Date involves also time, hours, minutes, seconds, miliseconds etc? Are you sure these values are exact the sames? — pleft, Nov 15 '16 at 15:16
Can you add the details of date format present in database and dat eformat expDate from java side ? — Vasu, Nov 15 '16 at 15:25
In MySQL database I set field as "datetime". And values are exact same when I retrieve it from database. — Aleksandar, Nov 15 '16 at 15:26
try putting @Temporal(TemporalType.DATE) on the field in the entity — prem kumar, Nov 15 '16 at 15:55
Error appears "..for column expiration_date. Found: datetime, expected: date". But I need column with "datetime" type. — Aleksandar, Nov 15 '16 at 16:03

score 1 · Answer 1 · answered Nov 15 '16 at 16:32

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

This solution does not work for me. I don't know why. Surely, this is very helpful example which should be taken into consideration, thanks. — Aleksandar, Nov 16 '16 at 10:44

score 1 · Answer 2 · edited Jan 13 '17 at 21:10

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);

edited Jan 13 '17 at 21:10

Alexei - check Codidact

22,016
16
145
164

answered Jan 13 '17 at 20:52

Antwon Anderson

11
1

score 0 · Answer 3 · edited May 23 '17 at 12:08

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);

Spring Data JPA - custom @Query with "@Param Date" doesn't work

3 Answers3

Linked