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)

pretty much the same way: objectdb.com/java/jpa/query/jpql/date — msp
– msp, Commented Jan 22, 2018 at 13:32
Does your JPA provider support functions MONTH and YEAR ? because they are not standard JPA functions. And when you've looked at documentation for JPA, what have you tried? — user8558216
– user8558216, Commented Jan 22, 2018 at 13:49
I didnt tried much yet. I noticed that my solution (native query works on hsqldb and do not work on postgresql). Im new in JPA thats why I'm asking you. Usually I just create queries using CRUD or JPA repository like FindByName(String name); etc. Thats why my knowladge is really basic. — degath
– degath, Commented Jan 22, 2018 at 13:54

Saquib Ali · Accepted Answer · 2018-01-23 08:54:52Z

3

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

`

edited Jan 23, 2018 at 8:54

answered Jan 22, 2018 at 13:56

Saquib Ali

1201 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

degath Over a year ago

can you say what kind of library Predicate is? I mean what kind of import (or maybe depedency) I need.

Saquib Ali Over a year ago

please check my answer again reposted it by adding imports.

Plog · Accepted Answer · 2018-01-22 13:59:46Z

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/

Collectives™ on Stack Overflow

Creating JPA query with Date in Spring

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related