Should I go for ArrayList of ArrayLists / ArrayList / Something else?

[akalex]

To mimic ResultSet, which one offers better performance -
An ArrayList of ArrayLists OR
A single ArrayList OR
Something else ?

I am trying to mimic the jdbc ResultSet using ArrayList of ArrayLists. i.e. Each of the ResultSet row becomes an ArrayList with column values populated in it. All these ArrayLists (each representing a row) is stuffed into another ArrayList (which acts similar to a ResultSet object)

An another approach is to have a single ArrayList with column values populated into it one row after the other. i.e. The first row column values will be populated first, followed by the second row & so on.

I would like to know when thousands of records are retrieved, which one of the above two offers maximum performance. Would anyone suggest any other data structure other than the ones above to mimic the ResultSet. My primary concern is performance in terms of speed, memory, etc etc

Thanks in advance

Alex

 

[wushutwist]

Create a data object to model your database table and then create an ArrayList of these data objects? This is the standard way to deal with this problem. These data objects are typically referred to in J2EE as Data Transfer Objects or DTOs.

Take this table:

table item {
  id number primary key;
  description varchar;
}

Your DTO would be similar to this:

public class ItemDTO {
  private int _id;
  private String _description;

  public ItemDTO(int id, String description) {
    _id = id;
    _description = description; 
  }

  public int getId() {
    return _id; 
  }

  public String getDescription {
    return _description; 
  }

  public boolean equals(Object o) {
    if (o instanceof Item) {
      return getId() == ((Item)o).getId();
    }
    return false;
  }

  public int hashCode() {
    return getId();
  }

}

Make sense?

 

[akalex]

Hi wushutwist,

Thanks for the info. But I cannot always assume that I will be getting a definite set of columns. The number of columns may change due to dynamic query generation. So my idea is to have a generic component similar to the ResultSet so that I can pass it on to the JSPs thro' HttpServletRequest object. Also if I go for DTOs, & just in case a change occurs to the table structure (may be a bad design) I would have to edit the DTOs & recompile them, where as the generic component doesn't require any change.

If you deviate from my view above, you are most welcome to bringforth your view & correct me (if I am wrong )

Hence I would like to precisely know when thousands of records are retrieved, whether ArrayList of ArrayLists OR a single ArrayList offers maximum performance in terms of speed & memory usage. What I have read is that retrieval time from an ArrayList is done in constant time. i.e the time taken to retrieve the 1st & the 1000th element are the same. But I am not sure what will be the consequence if I go for a single ArrayList having (10000 elements = 1000 rows with 10 column values each). I would appreciate if you could throw some light on this.

Thanks

Alex

 

[wushutwist]

You don't need to retrieve all the columns to fill the DTO. Just fill the columns that you need. Though I would argue that you should fill everything. Do not performance-tune an application until you have determined performance is an issue.

I feel your coupling concern is a weak argument. The application is always going to be coupled to the database at some point. Are you telling me that if you add or delete a field from the database then application will automagically know what to do? Of course not. If you add/delete a field from the database the application is going to have to be modified to use or not use that field. The difference between are two designs is where the coupling takes place.

Your design will be unnecessarily coupling the UI of the application with the database design. The UI is going to have to know the ordering of fields in your ArrayList or at least the field name. Change the ordering and you break the UI. Delete a field from the database and you break the UI, only the compiler isn't going to be able to help you find where it is broken.

With DTOs the UI doesn't know anything about the database, it only knows about the DTO. Your DTO may or may not be a mirror of the database table, depends on the situation. DTOs also offer strong compile time checking. Delete a field from the database, delete it from the DTO and let the compiler find all the dangling references. Make the compiler earn its keep, it is much better at finding mistakes then we are.

Let me say again, the type of design I am recommending is standard for J2EE. It revolves around the Model-View-Controller Pattern popular for many years in SmallTalk circles. If you don't trust me, many people have written books on this subject recommending the same.

 

[ChrisCarroll]

I'd like to second wushuwit on this. Trying to uncouple an application from data doesn't really work in the end.

The exception is where you are writing an application to query unknown databases; an adhoc query tool for instance. In which case, if you're feeling brave, you could look at

http://www.sourceforge.net

for the code for Squirrel SQL Client.

But to answer your speed issue - I can only suggest you go ahead and write the code, and let us know ;-)