Let’s keep developing our To Do List application. In this lesson, we'll complete the switch to using a Database Access Object (or, DAO) pattern.

In this lesson, we’ll focus on updating our App.java file to work with our new structure. Here’s our current App.java. (Yours is likely commented out at the moment so that you could run tests without receiving error messages.):

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import models.Task;
import spark.ModelAndView;
import spark.template.handlebars.HandlebarsTemplateEngine;
import static spark.Spark.*;

public class App {
    public static void main(String[] args) { //type “psvm + tab” to autocreate this
        staticFileLocation("/public");

        //get: delete all tasks
        get("/tasks/delete", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            Task.clearAllTasks(); //change
            res.redirect("/");
            return null;
        }, new HandlebarsTemplateEngine());

        //get: delete an individual task
        get("/tasks/:id/delete", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            int idOfTaskToDelete = Integer.parseInt(req.params("id"));
            Task deleteTask = Task.findById(idOfTaskToDelete); //change
            deleteTask.deleteTask();
            res.redirect("/");
            return null;
        }, new HandlebarsTemplateEngine());

        //get: show all tasks
        get("/", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            ArrayList<Task> tasks = Task.getAll(); //change
            model.put("tasks", tasks);
            return new ModelAndView(model, "index.hbs");
        }, new HandlebarsTemplateEngine());

        //get: show new task form
        get("/tasks/new", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            return new ModelAndView(model, "task-form.hbs");
        }, new HandlebarsTemplateEngine());

        //task: process new task form
        post("/tasks", (req, res) -> { //URL to make new task on POST route
            Map<String, Object> model = new HashMap<>();
            String description = req.queryParams("description");
            Task newTask = new Task(description); //change
            res.redirect("/");
            return null;
        }, new HandlebarsTemplateEngine());

        //get: show an individual task
        get("/tasks/:id", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            int idOfTaskToFind = Integer.parseInt(req.params("id"));
            Task foundTask = Task.findById(idOfTaskToFind); //change
            model.put("task", foundTask);
            return new ModelAndView(model, "task-detail.hbs");
        }, new HandlebarsTemplateEngine());

        //get: show a form to update a task
        get("/tasks/:id/update", (req, res) -> {
            Map<String, Object> model = new HashMap<>();
            int idOfTaskToEdit = Integer.parseInt(req.params("id"));
            Task editTask = Task.findById(idOfTaskToEdit); //change
            model.put("editTask", editTask);
            return new ModelAndView(model, "task-form.hbs");
        }, new HandlebarsTemplateEngine());

        //task: process a form to update a task
        post("/tasks/:id", (req, res) -> { //URL to update task on POST route
            Map<String, Object> model = new HashMap<>();
            String newContent = req.queryParams("description");
            int idOfTaskToEdit = Integer.parseInt(req.params("id"));
            Task editTask = Task.findById(idOfTaskToEdit); //change
            editTask.update(newContent); //change
            res.redirect("/");
            return null;
        }, new HandlebarsTemplateEngine());
    }
}

Setting Up a Production Database and Frontend DAO

But before we can change our routes to use a our Sql2oTaskDao, we need to create an instance of it - and for that, if you remember, we need a Sql2o object itself. Here’s what we had to do in our tests file in order to be able to create a DAO object:

public class Sql2oTaskDaoTest {

   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, "", "");
       taskDao = new Sql2oTaskDao(sql2o);
       //keep connection open through entire test so it does not get erased.
       conn = sql2o.open();
   }

...

We’ll need to do something similar in App.java, so that we can create a DAO object, and tell that to add, get, update and delete data. We can’t do anything much without it, and we need to supply some connectivity information to create one. Let’s do that now.

Enter this code at the top of your main() method in your App.java class:

...

public static void main(String[] args) {
   staticFileLocation("/public");
   String connectionString = "jdbc:h2:~/todolist.db;INIT=RUNSCRIPT from 'classpath:db/create.sql'";
   Sql2o sql2o = new Sql2o(connectionString, "", "");
   Sql2oTaskDao taskDao = new Sql2oTaskDao(sql2o);

...

(Don't forget to add the required imports.)

As you can see, this looks reeaaaally similar to the setup we use for our test database, but is subtly different. We’re making a new Sql2o object, the constructor of which takes specifics about our database in order to instantiate. We use that object to make a new DAO object, which, as we know, serves as the middleman between our local code and database.

There is one key difference here though: Whereas we were previously writing the test database into the machine’s memory (a temporary postgres database was literally created in memory and then erased when the test ended), we are now writing the database into a file on the machine’s hard drive. You could call it whatever you wanted, but I opted to call this file todolist.db.

Switching Route Handlers to Use the DAO

Let’s get our route handlers switched over so they don’t call methods on Task objects directly, but instead on the intermediary taskDao instance.

Take a look above, thre should be a comment on each line that needs to be changed in order to account for our new DAO driven app structure.

Work through this top to bottom, and try and see if you can make these changes yourself. Reference the tests we wrote together if you are unsure how to call a method.

When you are done, check the cheat sheet to compare your work, then boot up your app to localhost! You shouldn’t see any difference in how it performs, but now your app is being stored in a database. This is so cool. Stop the server or reboot it, and you’ll see your data persist! Fantastic. Now you have a blueprint on how to switch a.) any non DAO based app to a DAO structure, b.) a non-persistent site to a persistent one. Go career!

Next, we’ll add the ability to to structure objects hierarchically - Tasks will soon belong to Categorys. This makes things a whole lot more powerful and closer to life. We still have a ways to go, but we are a good bit closer to having an actual project!

Example GitHub Repo for This Lesson