# 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 November 16, 2026

By Gregor Dzierzon

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

```java
// 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:

```sql
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:

```java
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

- [Mapping Data to Models](https://www.ask-codey.com/cheat-sheets/mapping-data-to-models-cheat-sheet)

Source: https://www.ask-codey.com/lessons/mapping-data-to-models
