Lesson Monday

In this lesson we are going to learn how to access and interact with databases through Spark applications. To do this, we'll walk through adding a database to the existing To Do List application. If you do not have the starter code for the To Do List app, make sure to revisit the Weekly Project: To Do List lesson explaining how to access it.

This lesson will specifically focus on adding database support to our existing Task class. Then, the next lesson will walk through adding similar support to the Category class, including associating a Task with a specific Category. After that, we'll alter our Spark routes to utilize this new functionality. We will also complete a very important switch: We will retool our apps to use a Data Access Object, as described in last week's lessons. Be sure to revisit the Data Access Object lesson if you only skimmed it at the time.

Also, for the most part, we are going to leave our Postgres console behind beyond this point, in favor of a different approach: Instead of manually creating a Postgres database on our machines, then connecting an app to it, we will use a library called H2 that allows us to write databases directly into our computer’s memory without creating a full-fledged Postgres database. This will make working with databases on different machines (home/school/grading!) easier and more efficient.

Setup

Let's get started! First, we need to add two new dependencies to our build.gradle. It should now look like this:

build.gradle
group 'com.epicodus.todolist' //may be different
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.8

repositories {
   mavenCentral()
}

dependencies {
   testCompile group: 'junit', name: 'junit', version: '4.12'
   compile "com.sparkjava:spark-core:2.6.0"
   compile "com.sparkjava:spark-template-handlebars:2.5.5"
   compile 'org.slf4j:slf4j-simple:1.7.21'
   compile 'org.sql2o:sql2o:1.5.4'
   compile group: 'com.h2database', name: 'h2', version: '1.4.191'
}

The order is not important here. Make sure Gradle refreshes its imports so we have the most up-to-date code.

Here, Sql2o is the library we'll use to create and manage connections to our database, and H2 is the library that will allow us to write databases into the machine’s memory quickly and easily.

Now, lets create a new folder in the src/main/java/resources directory, and we’ll call it db.

Inside that folder, let’s create a sql file called create.sql

Open that file up in IntelliJ, and insert the following code:

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
);

Important Note: Be sure to also remove .sql from any .gitignore files you may be using. Your app will not run otherwise.

As you can see, the code above is very similar to the SQL code we have already been practicing. This code will create a new tasks table (and its necessary columns) the first time it runs (ie: if the table doesn't exist yet).

This is going to make our lives really easy! We can also set our project up to auto-load this file to set up our database for us. Then, if we later want to change our database, all we need to do is update the code above. Sweet!

Let’s start by setting things up to make our big shift to using H2 in the manner described above. We’ll be jumping around a little bit, as restructuring our app will be a little complicated - fair warning!

Getting Ready

Our starter code already contains tests for our methods in TaskTest.java. And Task.java is where we currently implement methods like getAll(), clearAllTasks(), and so on - essential functionality for our app.

This organization in our Blog was okay, because it did not implement a DAO pattern. Instead, we were connecting our data model (class files) directly to our source of information (in this case, our information came from our user).

But remember, our To Do List is taking a different approach. It will have a different structure than the Blog, because this time we're inserting an additional layer of programming between our data model and database server: Our DAO! Therefore, it is actually our DAO file that should contain methods, and our DAO file that needs to be tested!

Even though it feels wrong, we need you to be brave and delete your TaskTest.java file. Don't worry, we’ll make a new test file soon!

Also, move to your Task.java and generate the following:

  • Setters for your id and description.
  • equals() and hashCode() methods that compares a Task's description, completion status, and id, but not creation date. Description should be non-null. (If you are not sure what this means, check the lesson on Comparing Objects and Type Casting before you proceed, as your tests might fail otherwise.)

After generating the content above, we'll make some additional changes to Task.java before moving on:

  • We'll remove the instances ArrayList as a field and from our constructor because our database will be responsible for keeping track of all Tasks.

  • We remove the id property from the Task constructor, because the database will assign each object we save into it a unique ID. Leave it as a field, however, as we'll use the database-assigned ID as our new id property, instead of creating one in the constructor.

  • Delete our getAll, clearAllTasks, findById, update and deleteTask methods as this is functionality that will move to our DAO.

Our class should now look like this, a simple POJO:

src/java/main/models/Task.java
package models;

import java.time.LocalDateTime;
import java.util.ArrayList;

public class Task {

   private String description;
   private boolean completed;
   private LocalDateTime createdAt;
   private int id;

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

   public void setDescription(String description) {
       this.description = description;
   }

   public void setId(int id) {
       this.id = id;
   }

   @Override
   public boolean equals(Object o) {
       if (this == o) return true;
       if (o == null || getClass() != o.getClass()) return false;

       Task task = (Task) o;

       if (completed != task.completed) return false;
       if (id != task.id) return false;
       return description != null ? description.equals(task.description) : task.description == null;
   }

   @Override
   public int hashCode() {
       int result = description != null ? description.hashCode() : 0;
       result = 31 * result + (completed ? 1 : 0);
       result = 31 * result + id;
       return result;
   }

   public String getDescription() {
       return this.description;
   }

   public boolean getCompleted(){
       return this.completed;
   }

   public LocalDateTime getCreatedAt() {
       return this.createdAt;
   }

   public int getId() {
       return this.id;
   }

}


Step 1: Testing Our DAO

Let’s create a new test file for our Sql2oDAO called Sql2oTaskDaoTest.java. Make a new package in src/test/java called dao and a new file within it called Sql2oTaskDaoTest.java. (We’ll create Sql2oDAO for our Task soon too.)

First, we'll implement some basics in our new file:

src/test/java/dao/Sql2oTaskDaoTest.java
import models.Task;
import models.dao.Sql2oTaskDao;
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

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

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

}

Let’s go through this. As you may have guessed, our @Before annotation runs before every test, and our @After annotation runs after every test.

In the @Before, we do the following:

  • Create a setUp() method we can call to prep the database for us.
  • Connect to JDBC. JDBC stands for Java Database Connectivity. This is an internal API that allows us to connect to a database.
  • Tell H2 to write the test database into memory, using the sql file we just created, which preps the database for us.
  • Instruct the code to make a new Sql2o object so we can run methods on it, such as open(), close() and more. This makes our database available in our entire file.

Then, in the @After, we:

  • Close the connection to start afresh for the next test.

This may seem like a lot of setting up and tearing down, but we want to be sure tests runs independently of each other, and that any data leftover from one test doesn’t interfere with the next test. It would really suck and make our app seem unreliable if there were no Tasks and some showed up!

But we clearly aren’t ready to run any tests yet - firstly, we haven’t actually written any, and secondly, we haven’t actually created a Sql2oTaskDao! We'll take care of that soon.

Let’s make the DAO first, then return back to this test file to actually write some tests that check our methods.

Step 2: Creating our DAO Interface

In this lesson, we went through how a DAO works conceptually. Now it’s time to actually code one with our project in mind. First, we need to create an interface to ensure that are always implementing the correct methods in our class responsible for accessing the database.

In our main package, create a new package called dao. We will store all of our DAO-related files here. Next, right-click in this directory, and create a new interface file by choosing New Java class > and setting the type to Interface. You should see the file show up with a little I icon in the filesystem.

Let’s pause on writing code for just a second, and think through which behaviors _ we need to see here in order to build out our app.

  • In order to add Tasks to our database we'll need an add() method. This method won't need to return anything.

  • In order to also retrieve all Tasks we need a getAll() method that retrieves a list of Task type.

  • Additionally, we’ll want to be able to retrieve a single Task and display it. This means we should probably also have a findById() method that returns a single Task type.

We'll tackle all of these methods now, by entering the following code:

src/java/main/dao/TaskDao.java
package models.dao;

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

public interface TaskDao {

  // CREATE
  void add (Task task);

  // READ
  List<Task> getAll();

  Task findById(int id);

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

  // DELETE
//  void deleteByid(int id);

//  void clearAllTasks();
}

As you can see, we are not implementing any logic here - we are simply letting our code know that we are agreeing to provide the methods above. We still need to both test and write them. I have also commented out the methods we’ll implement later on, so we won’t be informed that we are missing them by our interface!

Because we following BDD principles, we’re going to implement the tests for these methods first, see them fail, and then implement code to make them pass.

Let’s return to our Sql2oTaskDaoTest and write some new tests!

Step 3: Adding Tests for Our Methods

In Sql2oTaskDaoTest.java, right-click, select Generate, and choose New Test, as you may have done before.

The first test we might want to add should confirm that adding a Task successfully sets a Task’s ID. But why? Don’t we know how to do this already?

Because we are no longer storing all of our Tasks in an ArrayList, but instead in a database, it makes more sense to have the database set the ID for us. Also, our findById() definitely won’t work without this one, and we need some data in our database to successfully test our getAll() method too. Makes sense.

Add the following test for add():

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

@Test
public void addingCourseSetsId() throws Exception {
   Task task = new Task ("mow the lawn");
   int originalTaskId = task.getId();
   taskDao.add(task);
   assertNotEquals(originalTaskId, task.getId()); //how does this work?
}

...

As you can see, the dao field we declared at the top of the class and then instantiated as a Sql2oTaskDao is now being used, but we haven’t created it yet, so it’ll be a red squiggly line. The DAO takes care of saving and finding data, so we need to pass our actions through it.

It’s ok that it’s looking a bit red.

Let’s run our test and find out. Choose Run and then choose Sql2oTaskDaoTest.

Of course the test fails, as we haven’t written the methods yet to make them pass. Let’s implement a few more tests, then begin writing code to makes them pass, and begin creating our DAO file!

Here is a test for findById():

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

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

...

Let’s write some more.

For getAll(), we’ll want to test two different things:

a.) that all our tasks are found, and b.) that no tasks are found if there are none present.

We want you to practice writing tests on your own, so try to see if you can write your own tests for these two aspects of the above using the tests for findById() and add() as a guide. You can write as many tests as you like. If you get stuck, pop over to the cheat sheet to see the result.

Step 4: Creating Our DAO Class to Retrieve Information from Our Database

Now we can create the actual code that retrieves information from our database and make our tests pass. Finally we can deal with those red squiggles!

Create a new Java class (not an interface this time) inside the dao folder, and call it Sql2oTaskDao.java. Naming it after the database is useful here, as we know right away that this is the file that connects to a Sql database. If we had several different databases, we’d be able to tell the difference easily.

Let’s add the following code. Then, we'll walk through each of these new methods together:

to-do/src/main/java/dao/Sql2oTaskDao.java
package models.dao;

import models.Task;
import org.sql2o.*;
import java.util.List;

public class Sql2oTaskDao implements TaskDao { //implementing our interface

   private final Sql2o sql2o;

   public Sql2oTaskDao(Sql2o sql2o){
       this.sql2o = sql2o; //making the sql2o object available everywhere so we can call methods in it
   }

   @Override
   public void add(Task task) {
       String sql = "INSERT INTO tasks (description) VALUES (:description)"; //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) //map my argument onto the query so we can use information from it
                   .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!
       }
   }

   @Override
   public List<Task> getAll() {
       try(Connection con = sql2o.open()){
           return con.createQuery("SELECT * FROM tasks") //raw sql
                   .executeAndFetch(Task.class); //fetch a list
       }
   }

   @Override
   public Task findById(int id) {
       try(Connection con = sql2o.open()){
           return con.createQuery("SELECT * FROM tasks WHERE id = :id")
                   .addParameter("id", id) //key/value pair, key must match above
                   .executeAndFetchFirst(Task.class); //fetch an individual item
       }
   }
}

Note: If any getters and/or setters are highlighted red here, it's because they are missing. Be sure to add them in Task.java before moving forward.

Take a careful read through the code above, and make sure you understand how each method works. Look at them carefully. As you can see, they all share similarities.

Notice too that there are no semicolons on many of these longer methods. How come? It’s called method chaining whereby we are able to call (or "chain") multiple methods back-to-back in one line. We’ve actually seen this before:

String someString = Task.getAll().size();, for instance, is also a method chain.

Returning All Objects with .getAll()

Let's first direct our attention to the getAll() method we just created in the code above:

...
   @Override
   public List<Task> getAll() {
       try(Connection con = sql2o.open()){
           return con.createQuery("SELECT * FROM tasks") //raw sql
                   .executeAndFetch(Task.class); //fetch a list
       }
   }
...
  • Here, we create a sql statement requesting all id and description information from the tasks table.

  • We establish a connection with our database in another try block by calling open() on our sql2o object that we created at the top of this file.

  • We create a query by calling createQuery() on this connection, passing in our sql statement.

  • We chain the executeAndFetch() method on that, passing Task.class as an argument. This executes the SQL command and instructs Java to transform the information we receive into Task objects. This will create a List<Task> object, which we return.

Saving New Objects with .add()

Next, let's review the add() method we've just created:

...

   @Override
   public void add(Task task) {
       String sql = "INSERT INTO tasks (description) VALUES (:description)"; //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) //map my argument onto the query so we can use information from it
                   .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!
       }
   }

...
  • Again, we attempt to establish a connection with our database using the line sql2o.open().

  • We create a SQL query by calling createQuery() on our connection, passing in our SQL statement.

  • We retrieve information from our object by “binding” it, which means that the object’s fields are mapped onto the parameters outlined in the SQL statement.

  • We then run .executeUpdate() to run the query.

  • When the update has finished, meaning a new row has been written into the database, we retrieve the key (or row number) from that row with .getKey(), and set that as the id for our object with task.setId(id);. This ensures no two objects ever have the same id!

  • If we encounter an issue while failing, we handle this gracefully by catching it, and printing out the error message we encounter so we can debug.

Cool stuff.

Pinpointing Data with findById()

Finally, let's walk through our new findById() method:

...

   @Override
   public Task findById(int id) {
       try(Connection con = sql2o.open()){
           return con.createQuery("SELECT * FROM tasks WHERE id = :id")
                   .addParameter("id", id) //key/value pair, key must match above
                   .executeAndFetchFirst(Task.class); //fetch an individual item
       }
   }

...
  • Here, we construct a SQL query stating that we'd like to return the entries from the tasks table whose id property matches the id property we provide.

  • We use addParameter("id", id) to pass in the id argument to the SQL query

  • Then, we run executeAndFetchFirst(Task.class);. This will return the first item in the collection returned by our database, cast as a Task object. That is, it returns the Task whose id matches the id we provided as an argument to our findById() method. There should never be more than one, but this is another way we can be sure only one is ever returned.

Verify Passing Tests!

Comment out everything inside App.java’s main() method, so it won't complain when we run tests. Then run Sql2oTaskDaoTest.

You should have at least three passing tests for getAll(), findById(), and add()!

Check the repo below for a snapshot of code at this stage with two passing tests and a commented out App.java

Alright, that was a ton of work. Go ahead and stretch your legs and get a drink of water before you continue.


Example GitHub Repo for To Do List

Here’s how we might test our getAll() method:

@Test
public void addedTasksAreReturnedFromgetAll() throws Exception {
   Task task = new Task ("mow the lawn");
   taskDao.add(task);
   assertEquals(1, taskDao.getAll().size());
}

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