Create Search Specification with Dates range

Question

I want to create a Search specification where I can select data based on Date range. I tried this:

@Getter
@Setter
public class BillingSummarySearchParams {

    private LocalDateTime startDate;

    private LocalDateTime endDate;
}

Search Specification

public List<BillingSummaryFullDTO> findBillingInvoicesSummary(BillingSummarySearchParams params)
    {
        Specification<BillingSummary> spec = (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (params.getStartDate() != null | params.getEndDate() != null) {
                predicates.add(cb.like(cb.lower(root.get("startDate")), "%" + params.getStartDate() + "%"));
            }
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return billingSummaryService.findAll(spec).stream().map(billingSummaryMapper::toFullDTO).collect(Collectors.toList());
    }

Search SQL

public List<BillingSummary> findAll(Specification<BillingSummary> spec)
    {
        String hql = "select e from " + BillingSummary.class.getName() + " e where e.createdAt BETWEEN :startDate AND :endDate ORDER BY e.createdAt DESC";
        TypedQuery<BillingSummary> query = entityManager.createQuery(hql, BillingSummary.class).setParameter("startDate", spec).setParameter("endDate", spec);
        List<BillingSummary> list = query.getResultList();
        return list;
    }

It's not clear to me how I can build the specification with 2 dates to work and with only one.

What is the proper way to get the dates from the spec object?

Answer 1

I think you are missunderstanding what Specifications are used for. They aren't meant to be used in combination with "old" typed queries. If you set a Specification as parameter for them it should result in errors because the jpa provider has no idea how to work with that object.

Instead they are implemented to have an easier time to create and use criteria queries in spring data. The first step to make them work would be to implement JpaSpecificationExecutor in your repository. The interface contains methods like findAll(Specification<T> spec) and spring will automatically create these methods for your repository. Spring generates the criteria query in the background and adds the predicate you create in your specification to it.

@Repository
public interface BillingSummaryRepository extends JpaRepository<BillingSummary, Long>, JpaSpecificationExecutor<BillingSummary> {
    //Other methods
}

Specification<BillingSummary> spec = (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        if (params.getStartDate() != null | params.getEndDate() != null) {
            predicates.add(cb.like(cb.lower(root.get("startDate")), "%" + params.getStartDate() + "%"));
        }
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
};
List<BillingSummary> summaries = billingSummaryService.findAll(spec, Sort.by(Sort.Direction.DESC, "createdAt"));