I got a range date picker with two dates: start
and end
, where both can be empty. I would like to filter a table, where the entity has exact one date: date
.
So, here are some examples. I'd like to match. Imaging the date to match is the current date (17/07/2016).
- null – 17/07/2016 -> match
- 17/07/2016 – null -> match
- 17/07/2016 – 17/07/2016 -> match
- null – null -> match all
Those are the edge cases in my opinion and the reason why I am struggling so much.
Actually my code looks like:
CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Transaction> cq = cb.createQuery(Transaction.class);
Root<Transaction> root = cq.from(Transaction.class);
List<Predicate> predicates = new ArrayList<>();
Predicate startPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), start);
Predicate endPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), end);
predicates.add(startPredicate);
predicates.add(endPredicate);
cq.select(root).where(predicates.toArray(new Predicate[] {}));
TypedQuery<Transaction> query = getEm().createQuery(cq);
query.setFirstResult(firstRow);
query.setMaxResults(maxRow);
List<Transaction> resultList = query.getResultList();
I'd like to get a query like this:
SELECT * FROM transaction
WHERE ((cast(date AS DATE) >= '2016-07-16' OR cast(date AS DATE) IS NULL))
AND ((cast(date AS DATE) <= '2016-07-17' OR cast(date AS DATE) IS NULL))
Please note: The static date is to simulate a start and end date. and date
is the table column.
I know that my code is wrong. It matches only ranges, without considering null values. Also, if start and end is the same day, I will get zero results.
Do you have you any idea? How can I edit my code to match all the mentioned patterns?
Best Answer
I have an existing database table named
discount
with two columns of typeTIMESTAMP
nameddiscount_start_date
anddiscount_end_date
in a MySQL database. So, please adjust your query according to the name of the table and respective columns in that table.The complete criteria query based on the SQL statement given in the question can be constructed as follows (I hope the code would be self-explanatory).
It produces the following SQL query of your interest (both fields are inclusive as you stated).
Tested on Hibernate 4.3.6 final but average ORM frameworks should produce the same query without any modifications.
In this method
setParameter(parameter, startDate, TemporalType.DATE)
, the last parameter i.e.TemporalType.DATE
is only needed, if you have a column of typeDATETIME
orTIMESTAMP
in your database and you want to compare dates ignoring the time portion of such columns. You can simply exclude that parameter, if your columns do not have a time portion likeDATE
(MySQL).You can, if necessary, also use other date (time) handling APIs like
java.time
orJodaTime
replacingDate
along withSimpleDateFormat