How to do between or between query with Criteria API

Question

how I can do a query with Criteria API with between or between for this plain sql query:

SELECT * FROM
  documents
WHERE (register_date BETWEEN to_date('02.07.2017', 'DD.MM.YYYY') AND to_date('02.07.2018', 'DD.MM.YYYY'))
OR (sign_date BETWEEN to_date('02.07.2017', 'DD.MM.YYYY') AND to_date('02.07.2018', 'DD.MM.YYYY'));

in postgres?

It's not a dublicate because I'm doing a query with TWO between and a question in the link below hasn't criteria api as I can see

I need to make something like this

CriteriaBuilder cb = this.getCSession().getCriteriaBuilder();
CriteriaQuery<Documents> documentsCriteria = cb.createQuery(model_class);

Root<Documents> DocumentsRoot = documentsCriteria.from(model_class);
documentsCriteria.select(DocumentsRoot);

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

if (register_start_date != null && register_end_date != null) {
    predicates.add(cb.between(DocumentsRoot.get("register_date"), register_start_date, register_end_date));
    predicates.add(cb.between(DocumentsRoot.get("sign_date"), register_start_date, register_end_date));
}

documentsCriteria.where(cb.or(predicates.toArray(new Predicate[predicates.size()])));

List<Order> orderList = new ArrayList<>();
orderList.add(cb.asc(DocumentsRoot.get("register_date")));
orderList.add(cb.asc(DocumentsRoot.get("sign_date")));

documentsCriteria.orderBy(orderList);

Query q = this.getCSession().createQuery(documentsCriteria);

return (List<Documents>) q.getResultList();

Possible duplicate of [JPQL SELECT between date statement](https://stackoverflow.com/questions/5350994/jpql-select-between-date-statement) — michaeak, Nov 07 '18 at 08:20
Would be great if you can show your attempt. There are lot of similar questions floating around such as [this](https://stackoverflow.com/questions/41806152/add-criteriabuilder-betweendate-to-predicate). — Samuel Kok, Nov 07 '18 at 08:20

aurelius · Answer 1 · 2018-11-07T08:41:24.467

4

you create the criteria based on the entity manager and then you add the between restriction to it.

Criteria criteria = entityManager.unwrap(Session.class).createCriteria(EntityDao.class);
criteria.add(Restrictions.between("your_date_column", date1, date2));

between or between

criteria.add(Restrictions.or(Restrictions.between("your_date_column", date1, date2), Restrictions.between("your_date_column", date3, date4)));

edited Nov 07 '18 at 08:41

answered Nov 07 '18 at 08:29

aurelius

3,946
7
40
73

Ok, for one between It's ok! how to make between or between?! – Nesquik27 Nov 07 '18 at 08:31
you add multiple between restrictions to it – aurelius Nov 07 '18 at 08:31
If I create next between It will be between AND between, but I need between OR between – Nesquik27 Nov 07 '18 at 08:32
criteria.add(Restrictions.or(Restrictions.between("your_date_column", new Date(), new Date()), Restrictions.between("your_date_column", new Date(), new Date()))); – aurelius Nov 07 '18 at 08:33
I need to put something like this in my documentsCriteria.where If I will make predicates.add(cb.or(cb.between(DocumentsRoot.get("register_date"), register_start_date, register_end_date), cb.between(DocumentsRoot.get("sign_date"), register_start_date, register_end_date))); than the count of the rows is not a same compare with a plain sql – Nesquik27 Nov 07 '18 at 08:37
might you get away with a java statement? – aurelius Nov 07 '18 at 08:42
OK, it works correct, It's my mistake, Thanks everyone! – Nesquik27 Nov 07 '18 at 08:50
you should approve it then – aurelius Nov 23 '18 at 10:52

How to do between or between query with Criteria API

1 Answers1