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

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

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