What type of data structure should I use to hold table rows?

Question

I'm new to Java and just getting into querying databases. So far I have my results in a ResultSetMetaData. I'm think that for each row in the dataset I should add it to some form of collection? Can anyone tell me the best practice for this?

Thanks,
Jonesy

Answer 1

I had this issue with a ResultSet that had a few dozen columns, writing a class with so many members was way too much work for lazy me. So I iterated the ResultSet each field into a HashMap, the column label way the key and the field being the value. Then each put all the hashmaps from each row into a seperate list and all the lists into a master list.

Worked like a charm.

private ArrayList<ArrayList<HashMap<String, Object>>> allRecords = new ArrayList<>();

public MyTable getRecords()throws IOException, SQLException{
    try{
        String query = new Utils().readResourceFile("MyQuery.sql");
        ResultSet rs = DBUtils.dbConnect().createStatement().executeQuery(query);
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        while (rs.next()){
            ArrayList<HashMap<String, Object>> row = new ArrayList<>();
            for (int i = 1; i < columnCount + 1; i++) {
                HashMap<String, Object> data = new HashMap<>();
                Object field = rs.getObject(i);
                data.put(rsmd.getColumnLabel(i), rs.wasNull()? "": field);
                row.add(data);
            }
            allRecords.add(row);
        }
    }catch (IOException | ClassNotFoundException | SQLException e){
        if(e instanceof SQLException){
            DBUtils.printSQLException((SQLException) e);}
        e.printStackTrace();
        throw e;
    }
    return this;
}

And here is how I filtered the data:

 public MyTable makeChanges(){
    for(int i = 0; i < allRecords.size(); i++){
        Date startDate = (Date) allRecords.get(i).stream().filter((HashMap<String, Object> field) -> field.containsKey("StartDate")).findFirst().get().get("StartDate");
        int product = (int) allRecords.get(i).stream().filter((HashMap<String, Object> field) -> field.containsKey("pk_Product")).findFirst().get().get("pk_Product");
        // now do something....
    }
    return this;
}

Answer 2

Many of the answers above advice creating a class to hold the columns of a row and create Array list of the Class Objects. Shouldn't we also worry if the result set is huge though only fewer rows are being processed, would it not over consume memory unless the garbage collector reclaims at the same pace at which the objects are being created.

Answer 3

Usually we have a class with fields that correspond to a table. Then, whenever we have a (full) row in a result set, we create an instance of this class.

Example:

Consider a table created like this:

CREATE TABLE customer (First_Name char(50), Last_Name char(50),
   Address char(50), City char(50), Country char(25), Birth_Date date);

A model class would be like this:

public class Customer {
  private String firstName;
  private String lastName;
  private String address;
  private String city;
  private String country;
  private Date date;


  public String getFirstName() {
    return firstName;
  }
  // getters for all fields

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  // setters for all fields

  public String toString() {
    return String.format("[%s, %s, %s, %s, %s, %s]", firstName,
             lastName, address, city, country, date);
  }
}

Now if you read data and have a ResultSet, you would create a new customer object and set the fields:

List<Customer> customers = new ArrayList<Customer>();
ResultSet rs = stmt.executeQuery("SELECT * from CUSTOMER;");
while (rs.next()) {
  Customer customer = new Customer();
  customer.setFirstName(rs.get("First_Name"));
  // ... and so on

  customers.add(customer);
}

Answer 4

I usually follow the same pattern as Andreas_D describes.

The object used to contain each row of data (in this case, the Customer class) is referred to as Data Transfer Object (TO).

The code that gets the database connection, queries the db, populates the TOs and returns them (typically in a List), is referred to as a Data Access Object (DAO).

You can read more about this design pattern here

Answer 5

Create an object to hold the data. Loop through the resultset, creating an object for each one, and store them in an ArrayList or HashMap, depending on how you want to use the data. This allows you to close the database, and it gives you good objects on which you can build methods to manipulate the data.

It also allows you to write code that uses the object that doesn't need to rely on the database. If you ever want to pull out the database later and switch to text files or whatever, it's easy to do and you can still use the same objects and methods.

Answer 6

A List seems quite logical. If you are not going to be having duplicates, and you are not bothered about the order of the results, then perhaps a Set.

A relevant implementation of List:

• ArrayList: This is backed by an array, so lookups on particular indices should be quick

Relevant implementations of Set:

• HashSet: Backed by a HashMap so O(1) insertion time
• TreeSet: Respects the ordering of the data (using the compareTo method) - so iterating over the data will be in order - the trade off is O(log n) insertion time

Answer 7

First, the ResultSetMetaData class holds "information about the types and properties of the columns in a ResultSet object." So the results from your query are in the ResultSet, not in the ResultSetMetaData object.

You can see the Retrieving Values from Result Sets Java tutorial for information on how to extract your data from a ResultSet. You should be able to just loop through the ResultSet as shown and put your records in a List or Map, depending on how you want to access the data later.

Answer 8

You can create class which represents real world entities. Later if you wish to choose ORM technology/tool like hibernate you can use same classes.