Hibernate query cache based on current time

Question

I have a DAO method which executes the following query to fetch results:

SELECT new com.Person() FROM Person AS person 
WHERE (person.start <= now()) AND (person.expires > now()) ORDER BY person.start ASC

The above is a PostgreSQL query. What can I do to enable query caching on the above? If I simply do query.setQueryCache(true), that wouldn't work because the now() will be different each time the above is executed. Is there a best practice to implement such functionality?

How do you want it to get cached? After all, now will change all the time? — Jens Schauder, Jun 02 '15 at 20:19
Just out of curiosity, how do you expect caching such a query would work, as in when do you expect cache to kick instead of data being retrieved from database? For example, if data was retrieved like within the latest 15 minutes than that's good enough? — Filip, Jun 02 '15 at 20:22
No, that's not good enough... However your suggestion of flooring works with the filtering done in the app itself. However, I'm interested to know if hibernate can provide such a capability out of the box. — Sam, Jun 02 '15 at 21:23
Honestly I'm still kinda confused regarding the actual capability you're looking for as out of the box in Hibernate. This is how Hibernate stores the query in its [cache](https://goo.gl/qT5Pce). As you can see Hibernate can't make any sort of logical evaluations of the _quality_ of the sql query. That which is a meaningful query to you is just a hash to the cache engine so you'll have to put in some specific bits to make your special case cache ready. There's no feature to accommodate such a behavior out of the box and I honestly don't think there ever will be :( — Filip, Jun 02 '15 at 21:58

score 1 · Answer 1 · edited May 23 '17 at 11:51

Basically you should use discrete values instead of using directly the value of now(), which is always a new one and incompatible with any caching strategy I've heard of :).

So say that you're actually looking to cache data each 15 minutes. You'd basically have to floor the value of now() to the closest quarter of an hour and use the floored value in the SQL query instead.

You can check out this article on stack for implementing such a thing How to round time to the nearest quarter hour in java?

Hibernate query cache based on current time

1 Answers1