1

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();
            }
        }
9
  • Why are you using list as key? Check this stackoverflow.com/questions/35560067/… Commented Oct 13, 2016 at 9:52
  • @Ashutosh Do you think using String rather than List<String> can reduce the execution time? Commented Oct 13, 2016 at 10:20
  • The thing is you cannot use List<String> as key, leave aside execution time. Key has to be immutable. Commented Oct 13, 2016 at 10:34
  • Ok. Since String is immutable, it is an alternative. Thanks for your tip. Regarding the initial question, any idea? Commented Oct 13, 2016 at 10:43
  • I don't completely get your question. If you can show some sample values and final output then i will be able to reply. If possible paste your exact working code. Commented Oct 13, 2016 at 10:48

2 Answers 2

1

Instead of using List as key. Use a class having List as its instance variable. Override equals very carefully.

Sign up to request clarification or add additional context in comments.

Comments

1

Why don't you simplify your key and make it a String that contains all the concatenated fields concatenated by a special character (say .)?

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.