Creating JPA query with Date in Spring

Question

This is how my entity looks like:

@Entity
public class Registration {
@Id
@GeneratedValue
private Integer id;

@org.springframework.format.annotation.DateTimeFormat(pattern = "yyyy-MM-dd")
private LocalDate date;
}

This is how my repo could look like:

@Query(value = "SELECT * FROM registration WHERE MONTH(date) = ?1 AND YEAR(date) = ?2")
List<Registration> findAll(Integer month, Integer year);

And this will be service:

public List<Registration> getCurrentRegistration() {
    LocalDate today = LocalDate.now();
    return registrationRepository.findAll(today.getMonth().getValue(), today.getYear());
}
public List<Registration> getRegistrations(Integer month, Integer year) {
    return registrationRepository.findAll(month, year);
}

How can I change my native query to be JPA query? Will the JPA query able to work on postgresql and hsqldb? And why JPA queries are the best for spring apps? (or why they are not)

Answer 1

You could do this using QueryDSL JPA (https://github.com/querydsl/querydsl/tree/master/querydsl-jpa) to define a predicate:

Predicate createPredicate(Integer month, Integer year) {
    return QRegistration.date.year().eq(year).and(QRegistration.date.month().eq(month));
}

Then make your repo extend QueryDslPredicateExecutor:

public interface RegistrationRepository extends JpaRepository<Registration>, QueryDslPredicateExecutor {
  // Your query methods here
}

This contains a List<T> findAll(Predicate predicate) method which you can pass your predicate in to obtain the items you were after, e.g.:

registrationRepository.findAll(createPredicate(1, 1970));

See here for more info about using QueryDSL with Spring: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/