Mapping Data to Models

Turn each row of a query result into a Category object and collect them in an ArrayList.

Journeyman · Java · SQL · Updated

By

Since each row in the categories table matches our model, we can create a new instance of a Category class for each row.

// Create an ArrayList to hold all categories
ArrayList<Category> categories = new ArrayList<Category>();

// 1. Connect to the database
BasicDataSource dataSource = new BasicDataSource();

dataSource.setUrl("jdbc:mysql://localhost:3306/northwind");
dataSource.setUsername("root");
dataSource.setPassword("your-root-password");

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

// 2. Define and execute the query
String sql = """
    SELECT category_id
        , category_name
        , description
    FROM categories;
    """;

SqlRowSet row = jdbcTemplate.queryForRowSet(sql);

// 3. Create a category for each row and
//    add it to the categories ArrayList
while(row.next())
{
    int id = row.getInt("category_id");
    String name = row.getString("category_name");
    String description = row.getString("description");

    Category category = new Category();

    category.setCategoryId(id);
    category.setCategoryName(name);
    category.setDescription(description);

    categories.add(category);
}

// Use the categories ArrayList

Do it: map the shippers' rows to Shipper objects

The shipping page wants Northwind's shippers as Shipper objects it can keep and use again after the query is done. The rows come from this query:

SELECT shipper_id
    , company_name
    , phone
FROM shippers;

Each column, and the Shipper setter it goes to:

  • shipper_id: a whole number, read with getInt, for setShipperId.
  • company_name: text, read with getString, for setCompanyName.
  • phone: text, read with getString, for setPhone.

The Shipper model is ready, at the bottom of Main.java. So is a stand-in for SqlRowSet, named StandInRowSet, because the practice box can't reach MySQL: its rows are written in code, but its next(), getInt and getString work as SqlRowSet's do. Leave both classes as they are. The tests make a row set like this:

StandInRowSet row = new StandInRowSet(
    new Object[] { 1, "Speedy Express", "(503) 555-9831" },
    new Object[] { 2, "United Package", "(503) 555-3199" },
    new Object[] { 3, "Federal Shipping", "(503) 555-9931" });

For those three rows, readShippers(row) returns a list of three Shipper objects, in the same order:

getShipperId() getCompanyName() getPhone()
1st 1 "Speedy Express" "(503) 555-9831"
2nd 2 "United Package" "(503) 555-3199"
3rd 3 "Federal Shipping" "(503) 555-9931"

Your job: write the method readShippers(StandInRowSet row) so it returns an ArrayList<Shipper>.

  • Read the rows one at a time, in order, until there are no more.
  • For each row, make a new Shipper, set its three values from the row's three columns, and add it to the list.
  • When there are no rows, return an empty list.

Practiced live on the lesson page: write the Java right there and check it.

Cheat sheets