Example

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

Journeyman · Java · SQL · Updated

By

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

Main.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

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:

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