CODEWITHSUNDEEP
javaspringpostgresqlspring-data-jpajpql
degath
degath

Reputation: 1621

Creating JPA query with Date in Spring

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)

Upvotes: 2

Views: 3449

Answers (2)

Saquib Ali
Saquib Ali

Reputation: 120

Make a specification class and write the below specification method in it.

import javax.persistence.criteria.Predicate;
import org.springframework.data.jpa.domain.Specification;

public class RegistrationSpecification {
public static Specification<Registration > registrationSpecForDate(
      LocalDate invoiceDate ) {
    return (root, cq, cb) -> {

      List<Predicate> predicates = new ArrayList<Predicate>();

      if (invoiceDate!=(null)) {
          predicates.add(cb.greaterThanOrEqualTo(root.get("date"), 
           invoiceDate));
        }

      return cb.and(predicates.toArray(new Predicate[0]));
    };
  }

Then in your repository inject this specification in JPA's findAll() method.

`public List<Registration> getRegistrations(LocalDate date) {
  return 
       registrationRepository.findAll
           (RegistrationSpecification.registrationSpecForDate(date));

`

Upvotes: 3

Plog
Plog

Reputation: 9622

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/

Upvotes: 2

Related Questions