I have one HUGE (legacy) lookup table that consists of say three columns (a, b, c) where a and b are the lookup key and c is the desired value. Columns a,b are not a primary key, so that for any a,b a set of many c's may be returned.
Without Hibernate (in non-JEE) I read this whole table with one select, create an instance of an Entry class per line and put this entry into a HashMap (hashCode/equals overridden so that it uses a and b). So I once initialize the map with one (complicated, long running) select, fetch all the rows it returns, put them into the HashMap (ArrayLists for multiple c's as values) and after that do *many* lookups with maximum speed by
ArrayList<C> lookupValues=hashMap.get(buildKey(a, b)).
Now I'm looking for a way to realize this in Hibernate efficiently. Basically this is a question about performance, as I found many (less efficient) ways to retrieve the data so far.
Things evaluated so far:
1) Composite Pattern (like in
http://www.researchkitchen.co.uk/blog/archives/57): Mapping a base class for columns a and b, and a derived class for a,b,c. The method getChildren() returns the c's. This is fine, because one can map two classes onto the existing legacy table. Problem: Many selects. Would be a great solution if one could instantiate all objects immediately with just one select.
2) Queries: A simple createQuery("from Entries") would return everything as needed in one select, and would be cached as such (result cache). But in fact I need a createQuery("from Entries where a=:a and b=:b") for *lots* of a/b combinations, *without* Hibernate generating a separate select for each combination. Every single a/b combination is accessed just once, so the result cache does not help much.
3) More or less without Hibernate: Stateless Session Bean that holds a HashMap like in non-JEE-solution. HashMap initialized e.g. in @PostConstruct by iterating over result of createQuery("from Entries"). Then limit the pool size of that bean to 1 (to avoid redundant HashMap creation/initialization).
I wonder how one could use Hibernates second-level cache. Because basically it is some kind of HashMap, so if I could manage to get every row from the select into the second-level cache and access that by a given (a,b)-key, that could be the solution. (Problem: It's not a lookup by primary key, because a,b isn't unique.)
Sole requirement is speed, as tables are so large and number of lookups is great.
Any ideas are greatly appreciated!
Login
FAQ
Search