JAVA - Storing result set in hash table by grouping data efficiently
<p>I'd like to store in a hash table a result set coming from a query execution.
The hash table is something like this</p>

<pre><code>Map&lt;List&lt;String&gt;,List&lt;Object&gt;&gt;
</code></pre>

<p>where </p>

<pre><code>List&lt;String&gt;, the hash table key, is a subset of the extracted fields
Object is a Java object corresponding to a database tuple (all fields)
</code></pre>

<p>So, first, data have to be grouped in order to create each key and group all the items sharing this key.</p>

<p>The pseudo-code related to my current approach is:</p>

<pre><code>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()
}
</code></pre>

<p>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.</p>

<p>Since the result set is very large (and also each List(Object) ), performance are poor (a very high loading time per key).
Appending an <strong>order by</strong> clause to the query (in order to preliminarily group data) doesn't alleviate the problem.</p>

<p>Is there a more efficient approach?</p>

<p>Thanks everyone.</p>

<p>EDIT</p>

<pre><code>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]            |
-----------------------------------
</code></pre>

<p>I'm using <code>List&lt;String&gt;</code> for key because another <code>ResultSet</code> can have a <code>Key_Fields</code> of different lenght.</p>

<p>Here, my current time-consuming Java code</p>

<pre><code>while(itera){

            key = new ArrayList&lt;String&gt;();
            values = new ArrayList&lt;AbstractClass&gt;();

            while(rs.next()){

                if(key.isEmpty()){
                     // build REFERENCE KEY
                     // add first OBJECT to List&lt;AbstractClass&gt;
                     // delete this data from ResultSet
                }
                else{
                    // Build KEY_TO_BE_COMPARED
                    List&lt;String&gt; row_to_be_compared = new ArrayList&lt;String&gt;();
                   // 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();
            }
        }
</code></pre>