# Example

A CategoriesDao class loads every category from the database into a list, and Main prints each category's name.

Journeyman · Java · SQL · Updated November 16, 2026

By Gregor Dzierzon

Here is the whole program: a CategoriesDao that loads the categories, and a Main class that prints them.

## Main.java

```java
import java.util.ArrayList;

public class Main
{
  public static void main(String[] args)
  {
    CategoriesDao dao = new CategoriesDao();

    ArrayList<Category> categories = dao.getAllCategories();

    for(Category category : categories)
    {
      System.out.println(category.getCategoryName());
    }
  }
}
```

## CategoriesDao.java

```java
import java.util.ArrayList;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class CategoriesDao
{
  public ArrayList<Category> getAllCategories()
  {
    ArrayList<Category> categories = new ArrayList<Category>();

    BasicDataSource dataSource = new BasicDataSource();

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

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String sql = """
        SELECT category_id
            , category_name
            , description
        FROM categories;
        """;

    SqlRowSet row = jdbcTemplate.queryForRowSet(sql);

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

    return categories;
  }
}
```

## Do it: build the category menu from the DAO

The store's console app shows a menu of categories, and the user types a category's id to choose it. `Main` never talks to the database: it asks the `CategoriesDao` for the categories, then uses them.

The method gets one value:

- `dao`: the `CategoriesDao` to ask.

The bottom of `Main.java` has the `Category` model and a stand-in `CategoriesDao`. The practice box can't reach MySQL, so the stand-in's categories are given to it in code when it's made, and its `getAllCategories()` returns them, in that order, as an `ArrayList<Category>`, like the lesson's DAO. This `Category` has a constructor, so the tests can make one in a line. Leave both classes as they are. The tests make a DAO like this:

```java
CategoriesDao dao = new CategoriesDao(
    new Category(1, "Beverages"),
    new Category(2, "Condiments"),
    new Category(3, "Confections"));
```

Each category becomes one line of the menu: its id, a dot and a space, then its name.

| Category | Its line |
|---|---|
| 1, Beverages | `1. Beverages` |
| 2, Condiments | `2. Condiments` |
| 3, Confections | `3. Confections` |

So `menu(dao)` returns the list `1. Beverages`, `2. Condiments`, `3. Confections`.

**Your job:** write the method `menu(CategoriesDao dao)` so it returns an `ArrayList<String>`.

- Get the categories from `dao.getAllCategories()`.
- For each category, in order, add one line: its id, `. ` (a dot and a space), then its name.
- Use each category's own id, not its place in the list: category 8, Seafood, is `8. Seafood` wherever it comes.
- When the DAO has no categories, return an empty list.

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

## Cheat sheets

- [Data Access Objects](https://www.ask-codey.com/cheat-sheets/mapping-data-to-models-data-access-objects-cheat-sheet)

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