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

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 2

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);
}