Lesson Monday

Alright! It’s time to pick up where we left off and finish off switching our To Do List project over to a database.

In this lesson we’ll implement tests and methods to use a DAO and test database, instead of creating objects in memory. Then, we’ll update our App.java with new routes that use our DAO object methods.

Let’s begin by peeking at our TaskDao.java interface to determine which methods we are missing and will need to implement:

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); //i may have changed


   //delete
//   void deleteById(int id); //me too

//   void clearAllTasks();

}

Writing Tests for Our Remaining Methods

Cool. We need to test and write methods to update, delete a single Task, and delete all Tasks. Let’s uncomment the methods in our DAO first, then write our tests, then finally implement our methods, just like we did before.

Try and see if you can come up with tests for these three methods on your own, using your previous experience writing tests as a guide. Check your work against the cheat sheet when you are done.

How did you do? I’m sure you’re getting so much better at writing tests now that you’ve had a lot of practice!

Let’s move on to implementing our methods. Here are our missing methods for the remainder of our CRUD functionality. Let’s start with UPDATE, which is the trickiest one.

Update

src/test/java/dao/Sql2oTaskDao.java
...

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

...

Before moving on, make sure you and your partner answer/discuss the following important questions:

  • Why do we need both the description and the id here?
  • What does addParameter do again?

Delete

Next up, let's add our delete functionality:

src/test/java/dao/Sql2oTaskDao.java
...

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

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

As you can see, these two are simpler, but do make sure you understand what is happening here before you move on!

Awesome! We have now successfully completed our switch to the DAO pattern in the backend of our application! Now all we need to do is update our frontend routes, which we'll tackle in the next lesson.

If you are having trouble implementing the concepts covered in this lesson, make sure to reference the example repo below, and compare it to your own code.


Example GitHub Repo for To Do List

...
@Test
public void updateChangesTaskContent() throws Exception {
   String initialDescription = "mow the lawn";
   Task task = new Task (initialDescription);
   taskDao.add(task);

   taskDao.update(task.getId(),"brush the cat");
   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 = new Task ("mow the lawn");
       taskDao.add(task);
       taskDao.deleteById(task.getId());
       assertEquals(0, taskDao.getAll().size());
   }

   @Test
   public void clearAllClearsAll() throws Exception {
       Task task = new Task ("mow the lawn");
       Task otherTask = new Task("brush the cat");
       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. 
   }
}