Example
A CategoriesDao class loads every category from the database into a list, and Main prints each category's name.
Journeyman · Java · SQL · Updated
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: theCategoriesDaoto 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. Seafoodwherever 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.