storing sorted list inside ehcache

I'm new to ehcache and I can't seem to find exactly what I need.

I am using hibernate and have one large table (500k + items) that I need to query fast.

The user requires retrevial开发者_StackOverflow of this table in pages, sorted by 4 different attributes. My though was to pre-sort this table and have them stored in 4 different cached lists. The user also has the ability to search through this table.

How can I do this? I would like the data to be pre-fetched and updated from the database automatically.

Interesting question, actually Hibernate provides all you need out of the box, however the mechanics are a bit complex. First of all you need second level cache (L2). It stores each record as a single item by id. This means you can easily query your objects by id without touching the database.

This alone won't help you and here is where the query cache comes into play. Assume you have a query similar to this:

SELECT c1, c2, c3, c4
FROM table
ORDER BY c1 ASC
LIMIT 100,20

The first time you execute this query, Hibernate will run this against the database, but it will also store all the returned records in L2 and store the result itself (list of ids) in the query cache. Next time you execute the exact same query, Hibernate will find the list of ids in query cache and then fetch each record one after another from L2.

If you use slightly different query, Hibernate will run the SQL and store the result under different key. It uses separate cache for query cache and the key is formed by concatenating query and all parameters.

I am not sure whether Hibernate will store the full result in cache and apply paging in memory or maybe it will store single pages separately, so each query will have as many elements in cache as many distinct pages are possible. If you go this way, it would be great if you could enlighten me.

Also note that any modification done to any row in this table via Hibernate will invalidate all cached queried involving this table. BTW 500K is not such a big table ;-). If each record occupies 1 KiB, keeping the whole database in memory might be worthwhile.

500K records is not a large table by any means.

You're likely to get maximum performance out of this by adequately configuring your database (and underlying OS) so that your table and necessary indexes fit in (and stay in) the cache DB-side.

Hibernate's 2nd level cache + query cache will not be of much help here, especially with mutable data that has to be paginated and sorted in various ways.