Searching in HashMap instead of database

Question

I have to look up prices depending on 1 value in my database. The database table contains 40000 entries with id, name, minvalue, maxvalue, price. I look up the correct price for value 2 with e.g.

SELECT name, price FROM priceTable WHERE minvalue <= 2 AND maxvalue >= 2;

Because i have to look up many prices (10000 and more) i thought about using something like a HashMap, with all the table data inside, so I would only have 1 database request and the rest would be done locally.

Has anyone experience with this? Is it possible to "convert" the SQL-request into a efficient HashMap-lookup? I cannot use the value as keys to look up, because e.g. the matching entry for the example above has minvalue 1 and maxvalue 3. So there would not be a key "2".

The terms you are looking for are ORM and level 2 cache (in case of hibernate). — John, Nov 09 '15 at 12:49
You can configure your database such that certain keys are hashed. — Willem Van Onsem, Nov 09 '15 at 12:49
actually, you can do it, but correct database querys are more efficient than store into java objects — Jordi Castilla, Nov 09 '15 at 13:00

score 1 · Answer 1 · edited May 23 '17 at 12:15

Java offers the NavigableMap interface that provides some interesting navigation methods including:

subMap(fromKey::inclusive, toKey::exclusive)

headMap(toKey, boolean inclusiveOrNot)

tailMap(fromKey, boolean inclusiveOrNot)

It will work for one type of key though (for convenience let's say it is somehow equivalent to an sql index; you need two indices here). Thus, in your case, you will need to store the priceTable using two different maps, one for minValue and one for maxValue eg:

NavigableMap<Double, List<Product>> minValueMap = new TreeMap<Double, List<Product>>();
NavigableMap<Double, List<Product>> maxValueMap = new TreeMap<Double, List<Product>>();

Then, to get minvalue <= 2 AND maxvalue >= 2 you have to run:

Map<Double, List<Product>> minConditionMap = minValueMap.headMap(2, true);
Map<Double, List<Product>> maxConditionMap = maxValueMap.tailMap(2, true);

Your result is the intersection of these maps (see here)

Searching in HashMap instead of database

1 Answers1