Lesson Monday

We should now have a To Do List application up and running. That's awesome! However, do you remember learning about the theory behind 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. Both the logic and corresponding tests 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, and create a constructor that takes on String, name, as an argument:

to-do/src/main/java/models/Category.java
public class Category {

  private String name;
  private int id;
}

Also, make sure to generate setters and getters, the equals() and hashCode()` methods, and set the name to non-nullable.

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 different 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:

to-do/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.

to-do/src/main/java/dao/CategoryDao.java
public interface CategoryDao {

   //create
   void add (Category category);

   //read
   List<Category> getAll();
   List<Task> getAllTasksByCategory(int categoryId);

   Category findById(int id);

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

   //delete
   void deleteById(int id);
   void clearAllCategories();

}

While we are in our dao folder, let's also make a small change to our TaskDao file, as we need to be able to update the categoryId property for any Task that we edit.

to-do/src/main/java/dao/TaskDao.java
public interface TaskDao {

   //create
   void add (Task task);
   //read
   List<Task> getAll();

   Task findById(int id);
   //update
   void update(int id, String content, int categoryId);
   //delete
   void deleteById(int id);
   void clearAllTasks();

}

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, review the Cheat Sheet section 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 cheat sheet if something terrible happens.

Connecting Objects

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:

to-do/src/main/java/models/Task.java
public class Task {

   private String description;
   private boolean completed;
   private int id;
   private int categoryId;

   public Task(String description, int categoryId){
       this.description = description;
       this.completed = false;
       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.

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.

Updating Tests

Next, skip back to Sql2oTaskDaoTest.java and let’s update our tests to account for Tasks requiring a Category to belong to. It’s also a good idea to write a short function to handle creating new Task objects for us, so just in case we change the constructor again:

to-do/src/test/java/dao/Sql2oTaskDaoTest.java
...
@Test
public void myTestName() 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:

to-do/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());
}
...

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. If something gets terribly butchered and you really can't fix it, fork and clone this project before you move on.


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 addingCourseSetsId() throws Exception {
       Category category = setupNewCategory();
       int originalCategoryId = category.getId();
       categoryDao.add(category);
       assertNotEquals(originalCategoryId, category.getId());
   }

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

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

   @Test
   public void noCategorysReturnsEmptyList() 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 clearAllClearsAll() 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.


       assertTrue(categoryDao.getAllTasksByCategory(categoryId).size() == 2);
       assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(newTask));
       assertTrue(categoryDao.getAllTasksByCategory(categoryId).contains(otherTask));
       assertFalse(categoryDao.getAllTasksByCategory(categoryId).contains(thirdTask)); //things are accurate!
   }

   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.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 org.junit.Assert.assertNotEquals;
import static org.junit.Assert.assertTrue;

public class Sql2oTaskDaoTest {

   private Sql2oTaskDao taskDao; //ignore me for now
   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

       //keep connection open through entire test so it does not get erased.
       conn = sql2o.open();
   }

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

   @Test
   public void addingCourseSetsId() 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",1);
       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);
   }

}