I'd like to store in a hash table a result set coming from a query execution. The hash table is something like this
Map<List<String>,List<Object>>
where
List<String>, the hash table key, is a subset of the extracted fields
Object is a Java object corresponding to a database tuple (all fields)
So, first, data have to be grouped in order to create each key and group all the items sharing this key.
The pseudo-code related to my current approach is:
while(iterate){
while(rs.next){
if(key is empty)
// build REFERENCE KEY and delete rs entry
else
// build key for i-th rs entry and compare it with the REFERENCE key.
Eventually, get data and delete rs entry
}
rs.beforeFirst()
}
In other words, the result set is iterated many times and each time a new key is created, in order to compare the ramaining result set entries with it. Each time the processed entry is deleted to exit the outer loop.
Since the result set is very large (and also each List(Object) ), performance are poor (a very high loading time per key). Appending an order by clause to the query (in order to preliminarily group data) doesn't alleviate the problem.
Is there a more efficient approach?
Thanks everyone.
EDIT
Input ResultSet
---------------------------------------------------------------
| Field1 | Field2 | Field3 | Field4 | Field5 | Field6 | Field7 |
---------------------------------------------------------------
| X | A | val1_3 | val1_4 | val1_5 | val1_6 | val1_7 |
| X | A | val2_3 | val2_4 | val2_5 | val2_6 | val2_7 |
| Y | B | val3_3 | val3_4 | val3_5 | val3_6 | val3_7 |
| Z | C | val4_3 | val4_4 | val4_5 | val4_6 | val4_7 |
| Y | D | val5_3 | val5_4 | val5_5 | val5_6 | val5_7 |
----------------------------------------------------------------
Key_Fields : [Field1, Field2]
Output Map
-----------------------------------
| KEY | VALUE |
-----------------------------------
| [X,A] | [Object1, Object2] |
| [Y,B] | [Object3] |
| [Z,C] | [Object4] |
| [Y,D] | [Object5] |
-----------------------------------
I'm using List<String> for key because another ResultSet can have a Key_Fields of different lenght.
Here, my current time-consuming Java code
while(itera){
key = new ArrayList<String>();
values = new ArrayList<AbstractClass>();
while(rs.next()){
if(key.isEmpty()){
// build REFERENCE KEY
// add first OBJECT to List<AbstractClass>
// delete this data from ResultSet
}
else{
// Build KEY_TO_BE_COMPARED
List<String> row_to_be_compared = new ArrayList<String>();
// If this key equals to REFERENCE KEY
if(row_to_be_compared.equals(key)){
AbstractClass value_object = new AbstractClass();
...
rs.deleteRow();
}
// ORDERBY clause in query ensures that, if keys don't match, then all objects related to REFERENCE KEY have been collected
else{
break;
}
}
}
rs.beforeFirst();
map.put(key, values);
if(!rs.next() || items_loaded==max_hash_size)
itera = false;
else
rs.beforeFirst();
}
}
Stringrather thanList<String>can reduce the execution time?Stringis immutable, it is an alternative. Thanks for your tip. Regarding the initial question, any idea?