Lesson Tuesday

We should now have a To Do List application up and running. That's awesome! However, do you remember learning about objects-within-objects last week? By integrating objects within objects in our To Do List, we have an opportunity to improve our app even further!

Think about your own list of tasks you have to complete. Whether this list resides on paper, in an electronic device, or simply in your head, there's probably many different types of tasks on your list. There's stuff you have to do for school, like your Java homework. There are chores you need to tackle at home; like mow the lawn, or mop your floors. You may have other miscellaneous tasks too, like sending your brother a birthday card, or finishing your laundry. If you're currently employed you probably have work-related items to get done as well!

We should really include features that allow users to better organize their different varieties of tasks. In this lesson we'll create a Category class. Each Category object will represent different categories of tasks, like Work, Home, School, and Miscellaneous. And each will have many Task objects stored within it.

A user should be able to see a list of categories and select one to see all tasks in that specific category. We'll begin by creating the Category class and all its necessary functionalities, then we'll integrate the Category and Task classes to work together. Finally, we'll update our Spark user interface to use this new feature.

Creating a Parent Class

Let's start by creating a Category class. We will need to be able to save each instance of a Category with a name and id. We'll also need to associate Categorys with Tasks in some way.

We'll tackle the core Category class functionality first and then come back and add in the connection between categories and tasks. Both the logic and corresponding tests for Category will be very similar to the Task logic and tests we created a couple lessons ago.

If you'd feel like you need additional details or explanation about any of these methods review the Finding and Saving Objects lesson.

Create a Data Model

We'll begin by create a new Java class in our models package and call it Category.java. We'll give this new class the following properties:

src/main/java/models/Category.java
public class Category {
  private String name;
  private int id;
}

Now, on your own, create a constructor that takes one String, name, as an argument. Also, make sure to generate setters and getters and the equals() and hashCode()` methods. You'll also need to create a test file for this new model. See the repo link in the middle of this lesson if you want to check your work on the POJO Category model and its test file.

Setting Up Our Structure

As you may have guessed, we'll need a new test file for the JUnit tests specific to our Category class. But, because we are following the same DAO structure that we did with Task, we will follow the same path we took there too:

  • First we will make a CategoryDao interface, where we name the methods our DAO will implement.
  • Then, we will create our Sql2oCategoryDao.java file to hold those methods and connect to the database.
  • Finally, we will create our Sql2oCategoryDaoTest.java file and the tests it will contain.

Go ahead and create those three files in the appropriate directories now. Follow the example provided to you to the corresponding Task files.

Making Changes to our SQL File

Next, we need to make sure we have a place to store Category information in our database. We won’t be storing this in our tasks table, as this would make it very difficult to create data relationships between Category in Task. It might be useful to refresh your memory of data relationships at this point.

Let’s change our SQL in the following ways:

src/main/resources/db/create.sql
SET MODE PostgreSQL;

CREATE TABLE IF NOT EXISTS tasks (
  id int PRIMARY KEY auto_increment,
  description VARCHAR,
  completed BOOLEAN,
  categoryid INTEGER
);

CREATE TABLE IF NOT EXISTS categories (
  id int PRIMARY KEY auto_increment,
  name VARCHAR
);

Don’t change the spelling on the table names, as H2 is a little fussy about this and it can cause errors during testing.

Adding Methods to Our DAO

Let’s move to our DAO interface and decide which methods we want to provide, and what we want to call them. We’ll follow the same blueprint provided by the Task class. Later we'll update this to actually connects categories and tasks.

src/main/java/dao/CategoryDao.java
package dao;

import models.Category;
import models.Task;
import java.util.List;

public interface CategoryDao {

  //LIST
  List<Category> getAll();

  //CREATE
  void add (Category category);

  //READ
  Category findById(int id);

  //UPDATE
  void update(int id, String name);

  //DELETE
  void deleteById(int id);
  void clearAllCategories();
}

Creating Tests

Next, we’ll move to our Sql2oCategoryDaoTest.java file and begin by writing our first tests. Again, we can orient ourselves on our testing we completed for Sql2oTaskDao.java.

Try to take your tests from Sql2oTaskDaoTest.java and make them work for Category. Again, check the repo link in the middle of this lesson if you get stuck.

Implementing Methods

Then, move to Sql2oCategoryDao.java (create it if you haven't yet done so) and implement the methods we need, using Sql2oTaskDao.java as your guide. Again, check the repo link in the middle of this lesson if something terrible happens.

Running Tests

At this stage, run your tests for both Category and Task sql2oDao and confirm they all pass.

Nice work! Once again, everything we added above should be review. It's the same functionality and tests we added to our Task class in earlier lessons. If you don't feel comfortable with the concepts above, take time to review the Finding and Saving Objects lesson at this time.


Example To Do List GitHub Repo at this Stage


Note that you may need to comment out the contents of App.java again once you start modifying things below. We'll come back to fix up the routing in the next lesson.

Connecting Objects - Tasks

Now that we've finished testing and implementing the basic Category class and DAO files, we'll need to connect the Category and Task classes together. We will accomplish this by enforcing that a Task needs to be assigned a Category before it is added to the database. It should be impossible from this point forward for a Task to exist without an assigned Category.

Let’s jump to our Task class and add a field to hold the Category it is assigned to:

src/main/java/models/Task.java
...

public class Task {
    private String description;
    private boolean completed;
    private LocalDateTime createdAt;
    private int id;
    private int categoryId;

    public Task(String description, int categoryId) {
        this.description = description;
        this.completed = false;
        this.createdAt = LocalDateTime.now();
        this.categoryId = categoryId;
    }

...

Be sure to add getters and setters for categoryId, as well as re-generating your equals() and hashCode() to account for this update.

Updating Task Tests

Next, we need to update every existing test that creates a task, since we've changed the Task constructor to take an additional argument. For now, we can just hardcode in an id of 1 for every task, since the existing tests are testing creation of tasks, not creation of tasks within categories.

We can easily update all our tests at once in TaskTest.java since we use a helper method there. Simply change the helper method from return new Task("Mow the lawn"); to return new Task("Mow the lawn", 1);

We need to update all our tests in Sql2oTaskDaoTest.java as well. We can write a helper method there as well, and replace most calls to new Task with calls to our helper method:

src/test/java/dao/Sql2oTaskDaoTest.java
...

@Test
public void addingTaskSetsId() throws Exception {
  Task task = setupNewTask();
  int originalTaskId = task.getId();
  taskDao.add(task);
  assertNotEquals(originalTaskId, task.getId());
}

//define the following once and then call it as above in your tests.
public Task setupNewTask(){
   return new Task("mow the lawn", 1);
}

...

Great! But if we run our tests now, we see that they all fail. Why??

Updating Methods That Connect to Our Database

Well, now that we have updated our constructor for Task objects, we also need to update those methods in Sql2oTaskDao.java that connect that connect to our database and retrieve those kinds of objects for us.

Update your add() method as follows:

src/main/java/dao/Sql2oTaskDao.java
...

@Override
public void add(Task task) {
   String sql = "INSERT INTO tasks (description, categoryId) VALUES (:description, :categoryId)"; //raw sql
   try(Connection con = sql2o.open()){ //try to open a connection
       int id = (int) con.createQuery(sql, true) //make a new variable
               .bind(task)
               .executeUpdate() //run it all
               .getKey(); //int id is now the row number (row “key”) of db
       task.setId(id); //update object to set id now from database
   } catch (Sql2oException ex) {
       System.out.println(ex); //oops we have an error!
   }
}

...

We should now be able to run tests and see that they all pass! I also added in the following test to make sure our categoryId is stored and retrieved correctly. Add this into your file and run your tests again:

src/test/java/dao/Sql2oTaskDaoTest.java
...

@Test
public void categoryIdIsReturnedCorrectly() throws Exception {
   Task task = setupNewTask();
   int originalCatId = task.getCategoryId();
   taskDao.add(task);
   assertEquals(originalCatId, taskDao.findById(task.getId()).getCategoryId());
}
...

Let's add in the ability to update a Task categoryId as well, as we need to be able to update the categoryId property for any Task that we edit. We need to update the method signature in TaskDao interface, the test for update in Sql2oTaskDaoTest and its implementation in the Sql2oTaskDao class:

src/main/java/dao/TaskDao.java
public interface TaskDao {
...

  // UPDATE
  void update(int id, String content, int categoryId);

...
src/test/java/dao/Sql2oTaskDaoTest.java
public class Sql2oTaskDaoTest {
...

@Test
public void updateChangesTaskContent() throws Exception {
  String initialDescription = "mow the lawn";
  Task task = new Task (initialDescription, 1);
  taskDao.add(task);
  taskDao.update(task.getId(),"brush the cat", 1);
  Task updatedTask = taskDao.findById(task.getId()); //why do I need to refind this?
  assertNotEquals(initialDescription, updatedTask.getDescription());
}

...
src/main/java/dao/Sql2oTaskDao.java
public class Sql2oTaskDao {
...

@Override
public void update(int id, String newDescription, int newCategoryId){
    String sql = "UPDATE tasks SET (description, categoryId) = (:description, :categoryId) WHERE id=:id"; //raw sql
    try(Connection con = sql2o.open()){
        con.createQuery(sql)
                .addParameter("description", newDescription)
                .addParameter("categoryId", newCategoryId)
                .addParameter("id", id)
                .executeUpdate();
    } catch (Sql2oException ex) {
        System.out.println(ex);
    }
}

...

Connecting Objects - Categories

We can now create tasks with category id's, but we also want to be able to ask our CategoryDao to retrieve all Task objects that belong to a given Category.

Let's First add the method signature to the CategoryDao interface:

src/main/java/dao/CategoryDao.java
public interface CategoryDao {
...

  List<Task> getAllTasksByCategory(int categoryId);

...

Next the test:

src/test/java/dao/Sql2oCategoryDaoTest.java
public class Sql2oCategoryDaoTest {
...

@Test
public void getAllTasksByCategoryReturnsTasksCorrectly() throws Exception {
  Category category = setupNewCategory();
  categoryDao.add(category);
  int categoryId = category.getId();
  Task newTask = new Task("mow the lawn", categoryId);
  Task otherTask = new Task("pull weeds", categoryId);
  Task thirdTask = new Task("trim hedge", categoryId);
  taskDao.add(newTask);
  taskDao.add(otherTask); //we are not adding task 3 so we can test things precisely.
  assertEquals(2, categoryDao.getAllTasksByCategory(categoryId));
  assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(newTask));
  assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(otherTask));
  assertFalse(categoryDao.getAllTasksByCategory(categoryId).contains(thirdTask)); //things are accurate!
}

...

And, finally, the actual code to implement it:

src/main/java/dao/Sql2oCategoryDao.java
public class Sql2oCategoryDao {
...

@Override
public List<Task> getAllTasksByCategory(int categoryId) {
  try(Connection con = sql2o.open()){
    return con.createQuery("SELECT * FROM tasks WHERE categoryId = :categoryId")
            .addParameter("categoryId", categoryId)
            .executeAndFetch(Task.class);
  }
}

...

This was quite a lot. Don't worry if you have to go over things several times, or are slow in making changes. Reference the repo below if you get stuck, or something just won't work. It has working Objects in Objects, SQL, and tests.


Example To Do List GitHub Repo at this Stage

These are what our tests for Sql2oCategoryDaoTest should look like when they are complete.

package dao;

import models.Category;
import models.Task;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.sql2o.Connection;
import org.sql2o.Sql2o;

import static junit.framework.TestCase.assertEquals;
import static junit.framework.TestCase.assertFalse;
import static org.junit.Assert.assertNotEquals;
import static org.junit.Assert.assertTrue;

public class Sql2oCategoryDaoTest {
  private Sql2oCategoryDao categoryDao;
  private Sql2oTaskDao taskDao;
  private Connection conn;

  @Before
  public void setUp() throws Exception {
    String connectionString = "jdbc:h2:mem:testing;INIT=RUNSCRIPT from 'classpath:db/create.sql'";
    Sql2o sql2o = new Sql2o(connectionString, "", "");
    categoryDao = new Sql2oCategoryDao(sql2o);
    taskDao = new Sql2oTaskDao(sql2o);
    conn = sql2o.open();
  }

  @After
  public void tearDown() throws Exception {
    conn.close();
  }

  @Test
  public void addingCategorySetsId() throws Exception {
    Category category = setupNewCategory();
    int originalCategoryId = category.getId();
    categoryDao.add(category);
    assertNotEquals(originalCategoryId, category.getId());
  }

  @Test
  public void existingCategoriesCanBeFoundById() throws Exception {
    Category category = setupNewCategory();
    categoryDao.add(category);
    Category foundCategory = categoryDao.findById(category.getId());
    assertEquals(category, foundCategory);
  }

  @Test
  public void addedCategoriesAreReturnedFromGetAll() throws Exception {
    Category category = setupNewCategory();
    categoryDao.add(category);
    assertEquals(1, categoryDao.getAll().size());
  }

  @Test
  public void noCategoriesReturnsEmptyList() throws Exception {
    assertEquals(0, categoryDao.getAll().size());
  }

  @Test
  public void updateChangesCategoryContent() throws Exception {
    String initialDescription = "Yardwork";
    Category category = new Category (initialDescription);
    categoryDao.add(category);
    categoryDao.update(category.getId(),"Cleaning");
    Category updatedCategory = categoryDao.findById(category.getId());
    assertNotEquals(initialDescription, updatedCategory.getName());
  }

  @Test
  public void deleteByIdDeletesCorrectCategory() throws Exception {
    Category category = setupNewCategory();
    categoryDao.add(category);
    categoryDao.deleteById(category.getId());
    assertEquals(0, categoryDao.getAll().size());
  }

  @Test
  public void clearAllClearsAllCategories() throws Exception {
    Category category = setupNewCategory();
    Category otherCategory = new Category("Cleaning");
    categoryDao.add(category);
    categoryDao.add(otherCategory);
    int daoSize = categoryDao.getAll().size();
    categoryDao.clearAllCategories();
    assertTrue(daoSize > 0 && daoSize > categoryDao.getAll().size());
  }

  @Test
  public void getAllTasksByCategoryReturnsTasksCorrectly() throws Exception {
    Category category = setupNewCategory();
    categoryDao.add(category);
    int categoryId = category.getId();
    Task newTask = new Task("mow the lawn", categoryId);
    Task otherTask = new Task("pull weeds", categoryId);
    Task thirdTask = new Task("trim hedge", categoryId);
    taskDao.add(newTask);
    taskDao.add(otherTask); //we are not adding task 3 so we can test things precisely.
    assertEquals(2, categoryDao.getAllTasksByCategory(categoryId).size());
    assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(newTask));
    assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(otherTask));
    assertFalse(categoryDao.getAllTasksByCategory(categoryId).contains(thirdTask)); //things are accurate!
  }

  // helper method
  public Category setupNewCategory(){
    return new Category("Yardwork");
  }
}

Here are all of our our methods we need to implement:

package dao;

import models.Category;
import models.Task;
import org.sql2o.Connection;
import org.sql2o.Sql2o;
import org.sql2o.Sql2oException;
import java.util.List;

public class Sql2oCategoryDao implements CategoryDao {

  private final Sql2o sql2o;

  public Sql2oCategoryDao(Sql2o sql2o){
    this.sql2o = sql2o;
  }

  @Override
  public void add(Category category) {
    String sql = "INSERT INTO categories (name) VALUES (:name)";
    try(Connection con = sql2o.open()){
      int id = (int) con.createQuery(sql, true)
              .bind(category)
              .executeUpdate()
              .getKey();
      category.setId(id);
    } catch (Sql2oException ex) {
      System.out.println(ex);
    }
  }

  @Override
  public List<Category> getAll() {
    try(Connection con = sql2o.open()){
      return con.createQuery("SELECT * FROM categories")
              .executeAndFetch(Category.class);
    }
  }

  @Override
  public Category findById(int id) {
    try(Connection con = sql2o.open()){
      return con.createQuery("SELECT * FROM categories WHERE id = :id")
              .addParameter("id", id)
              .executeAndFetchFirst(Category.class);
    }
  }

  @Override
  public void update(int id, String newName){
    String sql = "UPDATE categories SET name = :name WHERE id=:id";
    try(Connection con = sql2o.open()){
      con.createQuery(sql)
              .addParameter("name", newName)
              .addParameter("id", id)
              .executeUpdate();
    } catch (Sql2oException ex) {
      System.out.println(ex);
    }
  }

  @Override
  public void deleteById(int id) {
    String sql = "DELETE from categories WHERE id=:id"; //raw sql
    try (Connection con = sql2o.open()) {
      con.createQuery(sql)
              .addParameter("id", id)
              .executeUpdate();
    } catch (Sql2oException ex){
      System.out.println(ex);
    }
  }

  @Override
  public void clearAllCategories() {
    String sql = "DELETE from categories"; //raw sql
    try (Connection con = sql2o.open()) {
      con.createQuery(sql)
              .executeUpdate();
    } catch (Sql2oException ex){
      System.out.println(ex);
    }
  }

  @Override
  public List<Task> getAllTasksByCategory(int categoryId) {
    try(Connection con = sql2o.open()){
      return con.createQuery("SELECT * FROM tasks WHERE categoryId = :categoryId")
              .addParameter("categoryId", categoryId)
              .executeAndFetch(Task.class);
    }
  }
}

Here is Sql2oTaskDaoTest:

package dao;

import models.Task;
import org.sql2o.*;
import org.junit.*;
import static org.junit.Assert.*;

public class Sql2oTaskDaoTest {
  private Sql2oTaskDao taskDao; //ignore me for now. We'll create this soon.
  private Connection conn; //must be sql2o class conn

  @Before
  public void setUp() throws Exception {
    String connectionString = "jdbc:h2:mem:testing;INIT=RUNSCRIPT from 'classpath:db/create.sql'";
    Sql2o sql2o = new Sql2o(connectionString, "", "");
    taskDao = new Sql2oTaskDao(sql2o); //ignore me for now
    conn = sql2o.open(); //keep connection open through entire test so it does not get erased
  }

  @After
  public void tearDown() throws Exception {
    conn.close();
  }

  @Test
  public void addingTaskSetsId() throws Exception {
    Task task = setupNewTask();
    int originalTaskId = task.getId();
    taskDao.add(task);
    assertNotEquals(originalTaskId, task.getId()); //how does this work?
  }

  @Test
  public void existingTasksCanBeFoundById() throws Exception {
    Task task = setupNewTask();
    taskDao.add(task); //add to dao (takes care of saving)
    Task foundTask = taskDao.findById(task.getId()); //retrieve
    assertEquals(task, foundTask); //should be the same
  }

  @Test
  public void addedTasksAreReturnedFromgetAll() throws Exception {
    Task task = setupNewTask();
    taskDao.add(task);
    assertEquals(1, taskDao.getAll().size());
  }

  @Test
  public void noTasksReturnsEmptyList() throws Exception {
    assertEquals(0, taskDao.getAll().size());
  }

  @Test
  public void updateChangesTaskContent() throws Exception {
    String initialDescription = "mow the lawn";
    Task task = new Task (initialDescription, 1);
    taskDao.add(task);
    taskDao.update(task.getId(),"brush the cat", 1);
    Task updatedTask = taskDao.findById(task.getId()); //why do I need to refind this?
    assertNotEquals(initialDescription, updatedTask.getDescription());
  }

  @Test
  public void deleteByIdDeletesCorrectTask() throws Exception {
    Task task = setupNewTask();
    taskDao.add(task);
    taskDao.deleteById(task.getId());
    assertEquals(0, taskDao.getAll().size());
  }

  @Test
  public void clearAllClearsAll() throws Exception {
    Task task = setupNewTask();
    Task otherTask = new Task("brush the cat", 2);
    taskDao.add(task);
    taskDao.add(otherTask);
    int daoSize = taskDao.getAll().size();
    taskDao.clearAllTasks();
    assertTrue(daoSize > 0 && daoSize > taskDao.getAll().size()); //this is a little overcomplicated, but illustrates well how we might use `assertTrue` in a different way.
  }

  @Test
  public void categoryIdIsReturnedCorrectly() throws Exception {
    Task task = setupNewTask();
    int originalCatId = task.getCategoryId();
    taskDao.add(task);
    assertEquals(originalCatId, taskDao.findById(task.getId()).getCategoryId());
  }

  public Task setupNewTask(){
    return new Task("mow the lawn", 1);
  }
}