Setting ResultSet into an ArryList

[garfild Baram]

Hi,
My main question (before I mess your head) is load resultset into an ArrayList Genericly.
Since ArrayList is faster then Vector and hashtable objects I descided to use it as a collection of records i retrieve from my MySql db.
The resultSet may contain up to 500,000 records.
Synchronization is not importand in my case.
I have a routine that gets a resultset and set it into a hashtable and I want to replace it with an ArrayList.

public Hashtable[] makeHashTable(ResultSet ars_data)
    {
      int li_columns  = 0;
      int li_rowcount = 0;
      Hashtable[] lht_results = new Hashtable[20];

      try
      {                
        ResultSetMetaData lmeta_data  =           ars_data.getMetaData();
     li_columns = lmeta_data.getColumnCount();
            if (li_columns > 0)
            {      lht_results[li_rowcount] = columnOrder(lmeta_data,li_columns);
                   li_rowcount++;
            }
            while (ars_data.next())
            {
     if (li_rowcount == lht_results.length)
{
Hashtable[] lht_temp = new Hashtable[lht_results.length + 20];
for (int li_loop = 0; li_loop < lht_results.length ; li_loop++)
{
lht_temp[li_loop] = lht_results[li_loop];
}
lht_results = lht_temp;
}
Hashtable lht_row = new Hashtable(1);
for ( int i = 1; i <= li_columns; i++)
{
Object luo_value = null;
try
{
luo_value = ars_data.getObject(i);
}
catch(Exception e){}
if (luo_value ==null) luo_value = new String("");
lht_row.put(lmeta_data.getColumnLabel(i),luo_value);
}
lht_results[li_rowcount] = lht_row;
li_rowcount++;
            }
      }
      catch(SQLException e)
      {
      }

      if (lht_results[0] == null) 
      {
            return null;
      }

      return lht_results;
    }

Can I do the same using ArrayList only?
Thanks
[ April 07, 2003: Message edited by: Thomas Paul ]

[Dirk Schreckmann]

Please edit your post to preserve formatting using the [code] and [/code] UBB tags. It would make it a lot easier to read the code example.

[Dirk Schreckmann]

Your example code actually makes a Hashtable array. Were you trying to figure out making an ArrayList array, or simply a single ArrayList?

[garfild Baram]

Hi Dirk,
Sorry for the unedited code that i displayed here, though I can understand you figured it out.
Yes, my perpose is to use an ArrayList that will be filled by a resultset without knowing number and type of fields,If you have an example that will show me how you insert data into that arraylist and how to retrieve it (it should be as generic as possible).
Thanks again
Garfild

[Peter den Haan]

    /** Retrieves the results from the ResultSet and puts them in a List
     * of Maps.
     * @param rs The ResultSet to retrieve results from
     * @param query The query that has just been executed
     * @throws SQLException If there was a problem retrieving results
     * @return An ArrayList of Map objects, one Map per result record.
     */    
    private ArrayList getResult(ResultSet rs, Query query)
    throws SQLException {
        String[] columnNames = getResultColumNames(rs.getMetaData());
        
        ArrayList list = new ArrayList();
        while (rs.next()) {
            Map map = new HashMap();
            for (int i = 0; i < columnNames.length; i++) {
                map.put(columnNames[i], rs.getObject(i + 1));
            }
            list.add(Collections.unmodifiableMap(map));
        }
        return list;
    }
    /**
     * Retrieves the column names for a result.
     * @param rsMeta The ResultSet metadata to retrieve names from
     * @throws SQLException If there was a problem retrieving results
     * @return An array of name Strings
     */    
    private String[] getResultColumNames(ResultSetMetaData rsMeta) throws SQLException {
        String[] columnNames = new String[rsMeta.getColumnCount()];
        for (int i = 0; i < columnNames.length; i++) {
            columnNames[i] = rsMeta.getColumnName(i + 1);
            if (config.getDatabaseConfig().getConvertCase()) {
                columnNames[i] = columnNames[i].toLowerCase();
            }
        }
        return columnNames;
    }

- Peter

[garfild Baram]

great Peter,
Thanks.....

[Peter den Haan]

You're welcome. It's a chunk of code from a fully tested, high-volume production system. But it should only need very minor changes to adapt to your needs.
- Peter

[garfild Baram]

Peter,
Can you help me with how to retrieve data out of this array?
Let say if we take the array that contain the HashMap collections, send each Hash to the routine bellow and set all data into variables
such as what this routine trys to do (with no success):
private void SetFieldvalues_out_from_rs(HashMap hash)
{
Object [] Fieldvalues_out;
try
{
Fieldvalues_out[0]= (String )hash.get ("Product.Product_Num" );
Fieldvalues_out[1] = (Integer )hash.get("Product.Led_state" );
Fieldvalues_out[2]= (String )hash.get("Product.Active_screen" );
Fieldvalues_out[3] = (Integer )hash.get("Product.Label_type");
Fieldvalues_out[4] = (Integer )hash.get("Product.Sale_mode");
Fieldvalues_out[5]= (String )hash.get("Product.Sticker_type");
Fieldvalues_out[6]= (String )hash.get("Product.Def_Sticke_type");
InitClassParams();
}
catch(Exception ex)
{
}
}
How do I get data out of that thing??
Thanks

[Peter den Haan]

The column names are probably not what you expected them to be. Try dumping out the contents of the HashMap -- simply call toString() on it -- to see what the names are.
- Peter

[garfild Baram]

Peter,
Sorry for not leaving you alone,
I have trubles retrieving the hash object from the Array.
trying....
HashMap hash=null;
hash = (HashMap)arr.get(0);
Raises an Error.
How do I retrieve the hashMap from the Array and use that hashmap to get all data available?
Thanks mate
Garfild

[Peter den Haan]

Originally posted by garfild Baram:
[...]hash = (HashMap)arr.get(0);
Raises an Error. [...]

What kind of error? What's the stack trace? 'Cause this is still the right way to go about it. Assuming that the array has at least one result row, that is
- Peter

[garfild Baram]

Peter,
the error I'm getting is:
Type:
ClassCastException
Value:
java.util.Collections$UnmodifiableMap
It may be stupid but still dont know of a way to overcome it.
Thanks

[Peter den Haan]

D'oh. Brain was disengaged. You are casting to HashMap; avoid this where possible. When working with the Collections framework, always program to interface (Map) rather than implementation (HashMap).

Map map = (Map)arr.get(0);

This is a case in point. My code gives you an unmodifiable Map rather than a HashMap. The reason this is done is probably not very relevant for you -- it's being done because we cache the results and don't want any thread to mess up the results and spoil things for another thread. When you program to interface, code can fine-tune the implementations it uses without affecting other code. When, on the other hand, you hardwire classes everywhere you introduce lots of hard, tight, brittle couplings and the flexibility of the code is reduced to zero.
- Peter
[ April 10, 2003: Message edited by: Peter den Haan ]

[garfild Baram]

Peter,
I will study all this a bit more, i'm rather green in all that, thanks for the help and your time and I hope you will be available for me later on because I KNOW i will have stupid question in the future.
bye
Garfild

[Peter den Haan]

You are most welcome, and remember: the only stupid questions are the ones you don't ask
- Peter

[Peter den Haan]

Originally posted by garfild Baram:
I created 3 classes (each for one table) that containes parameters that represent each field in the table.
If I have a query that retrieves many records from all tables how do I elegantly draws them from the hashMap that contains all data?

A set of Java classes representing the objects in your problem, and the relationships between them, is called an object model. If the final destination for your data is an object model, then putting the query results in a List of Maps would be a very roundabout way to go about it.
You can build something yourself, either using the Java Reflection API, or (cruder) factory methods or factory classes that take a search criterion and return a List of objects (or one object, if the search is on a primary key or candidate primary key). But ultimately you'll end up spending a large amount of effort on an inferior version of an object/relational mapper.
Object/relational mapping appears simple at first, you just map each database table to a class. But things rapidly get complicated as you start to include object relationships and inheritance. An object-relational mapper is a generic tool which will shovel data to and fro between your object model on one hand, and a relational database such as MySQL on the other. It takes care of the actual database access, the mappings between tables and objects, and the relationships between objects. Rather than reading a row at a time, you can read entire object graphs from the database in one operation. Today, the buzzword in O/R mapping is JDO (Java Data Objects); all mappers are either compliant with it or working towards compliance. One well-known open-source name in this area is ObjectRelational Bridge.
If you're doing this as a learning experience, then try the factory class route. Such factory classes are known as Data Access Objects or DAOs. For example, to find FooBar objects you could have a FooBarDAO with a method findByPrimaryKey(int id) returning a single FooBar object, and a method findByBar(Bar bar) returning a List of FooBar objects. A Google search should provide a wealth of information.
- Peter

[garfild Baram]

Hi Peter,
I do not do this for my personal experiance unfortunatly.
Before diving in would you recommand me to take the ObJectRelationalBridge (its free as I understand ,i have encauntered FirstSQL which is not as I understand)?
Is it a good idea generaly speaking to use the object model to your knowledge? and if you can mensioned a good source (sample) for studying because time is limited for this project, I will be even more greatfull.
Thanks
Garfild

[garfild Baram]

I forgot to mension something else important.
We might have to take parts of an old application that was written in VB and used Access, and to translate all the queries that were used to resultsets in Mysql. Only parts will be taken but still we will need to plant new resultsets into the old Gui project.
If its relevant to what we were talking about, then I guess it is an important issue for you to know.
Cheers...
Garfild

[Peter den Haan]

Is this an assignment? Apparently you're expected to interface with the database on the SQL/JDBC level. In that case, if you want to go the object model route, hand-rolled DAOs would be most appropriate. This Google search includes some promising results.
Whether you should use an object model or not, I can't say. In simple systems that do little more than maintain data in a couple of database tables, direct database manipulation can be easier to code and understand. As a system grows larger, or comes to include more complicated behaviour, an object model becomes indispensable however. Your system is probably pretty simple. Still you may want to go the object model route because you want to learn about some big-system techniques in a manageable context.
- Peter
[ April 14, 2003: Message edited by: Peter den Haan ]

[garfild Baram]

I'll consider your advise carefully Peter,
Thanks mate

[garfild Baram]

Hi Peter,
I'm still here asking....
Because of the fact my application should not be complicated with its dialog and needs from the db it will be best as you said to use direct manipulation to/from the db (as I understand, it means using queries statments and manipulate the resultsets).Still my main question refers to the following senario:
Select Product.*,Label.*,Data.* from Product,Label,Data where (Product.ProductNum = Label.ProductNum) and Product.ProductNum=Data.ProductNum) and Product.ProductNum = "1111";
If I use HashMap as a collection how do I retrieve all the data let say all values of field DataType in Data table related to the specified ProductNum.
If not using Object module how do I store records of father and sons tables using HashMap???
Thanks
Garfild