Exercise Tuesday

Setup

Let's walk through implementing a basic many-to-many relationship into our Jadle application. When someone uses our API, we want the API to return data about both which Foodtypes a Restaurant serves, and all Restaurants that serve a certain Foodtype.

Editing our Database Structure

Let's begin by editing our database structure to implement a join table to link Restaurants to Foodtypes. It's convention to name the join table after the two types of data it will join. We'll have one id column that auto-increments as before, one column that tracks the foodtype, and one that tracks the restaurant.

We'll need to add a 4th table to our app to hold the data that connects these objects. Add this to your create.sql

CREATE TABLE IF NOT EXISTS restaurants_foodtypes (
 id int PRIMARY KEY auto_increment,
 foodtypeid INTEGER,
 restaurantid INTEGER
);

Cool! Very simple.

Getting DAO Files Ready

In each of our Dao Interfaces that are involved in the many to many, we have two methods each. One that sets the relationship and will write to the join table, one that gets the relationship and will retrieve information from the join table. We need to test all 4 of these methods separately.

Let's uncomment them in our RestaurantDao and FoodtypeDao files.

src/main/java/dao/RestaurantDao.java
...
//create
void add (Restaurant restaurant); //L
void addRestaurantToFoodtype(Restaurant restaurant, Foodtype foodtype); //D

//read
List<Restaurant> getAll(); //A
List<Foodtype> getAllFoodtypesByRestaurant(int restaurantId); //D - we will implement this NOW! :)
...
src/main/java/dao/FoodtypeDao.java
...
//create
void add(Foodtype foodtype); // Q
void addFoodtypeToRestaurant(Foodtype foodtype, Restaurant restaurant); // E

//read
List<Foodtype> getAll();
List<Restaurant> getAllRestaurantsForAFoodtype(int id); //E we will implement this NOW :)
...

Then, let's pop over to Sql2oRestaurantDao.java, the file that implements the interface. We should have a red squiggle, as we are not implementing the methods that we just uncommented correctly yet. Let's get some basic code in place so we can have a failing test.

Add the following code to your Sql2oRestaurantDao.java file, and add the following:

src/main/java/dao/Sql2oRestaurantDao.java.java
...

@Override
public void addRestaurantToFoodtype(Restaurant restaurant, Foodtype foodtype){
//do stuff here.
}

@Override
public List<Foodtype> getAllFoodtypesByRestaurant(int restaurantId){
    List<Foodtype> foodtypes = new ArrayList(); //empty list
    return foodtypes;
}
...

Move over to Sql2oFoodtypeDao.java and repeat the process for the relevant method there:

src/main/java/dao/Sql2oFoodtypeDao.java.java
...

@Override
public void addFoodtypeToRestaurant(Foodtype foodtype, Restaurant restaurant){
   //do stuff here.
}

@Override
public List<Restaurant> getAllRestaurantsForAFoodtype(int foodtypeId) {
   List<Restaurant> restaurants = new ArrayList();
   return restaurants;
}

...

Nice. We'll add functioning code to this as soon as we see failing tests - we're really sticking with this BDD thing!

Testing

Let's add tests to Sql2oFoodtypeDaoTest to test our methods. We don't really have a way to test addFoodtypeToRestaurant in isolation, as we can't verify it works without our method to retrieve the data, so in this case, we will test two methods with one test.

src/test/java/dao/Sql2oFoodtypeDaoTest.java
@Test
public void addFoodTypeToRestaurantAddsTypeCorrectly() throws Exception {

   Restaurant testRestaurant = setupRestaurant();
   Restaurant altRestaurant = setupAltRestaurant();

   restaurantDao.add(testRestaurant);
   restaurantDao.add(altRestaurant);

   Foodtype testFoodtype = setupNewFoodtype();

   foodtypeDao.add(testFoodtype);

   foodtypeDao.addFoodtypeToRestaurant(testFoodtype, testRestaurant);
   foodtypeDao.addFoodtypeToRestaurant(testFoodtype, altRestaurant);

   assertEquals(2, foodtypeDao.getAllRestaurantsForAFoodtype(testFoodtype.getId()).size());
}

We should see the following assertion error when we run this test:

java.lang.AssertionError:
Expected :2
Actual   :0

Great. Our methods are getting called correctly, they just don't quite have the functionality yet to pass.

Let's do the same thing for our Sql2oRestaurantDaoTest:

src/test/java/dao/Sql2oRestaurantDaoTest.java.java
@Test
public void RestaurantReturnsFoodtypesCorrectly() throws Exception {
       Foodtype testFoodtype  = new Foodtype("Seafood");
       foodtypeDao.add(testFoodtype);

       Foodtype otherFoodtype  = new Foodtype("Bar Food");
       foodtypeDao.add(otherFoodtype);

       Restaurant testRestaurant = setupRestaurant();
       restaurantDao.add(testRestaurant);
       restaurantDao.addRestaurantToFoodtype(testRestaurant,testFoodtype);
       restaurantDao.addRestaurantToFoodtype(testRestaurant,otherFoodtype);

       Foodtype[] foodtypes = {testFoodtype, otherFoodtype}; //oh hi what is this?

       assertEquals(Arrays.asList(foodtypes), restaurantDao.getAllFoodtypesByRestaurant(testRestaurant.getId()));
   }

But wait! What's happening here? I'm actually doing something new and clever here - add this trick to your testing arsenal for sure. Instead of getting the size of the List returned by my dao and checking that that is correct, I'm being much more precise: I am manually making a list of expected foodtypes, and then comparing my result to it! Arrays.asList is a static method on the Array class that allows us to compare ArrayList and Array easily, even though they are two different datatypes. Nice and clean.

We should see this when we run the test:

java.lang.AssertionError:
Expected :[[email protected], [email protected]]
Actual   :[]
 

Next, we'll work on implementing the code that will a.) appropriately save foodtypes and restaurants to the join table and b.) return them when we are are ready to retrieve that data.

Let's take a quick breather, and let this sink in before we move on to implementing the code to make this work.

Example GitHub Repo for At this Stage

Welcome back! It's time for us to put the finishing touches on our many-to-many relationship between Foodtypes and Restaurants. So far, we created a new database table called a join table that stores the data we need to create a link between our two classes, created the minimum code possible for our DAO interfaces to be happy, and written tests that failed in the appropriate way. Good job. Now we can tackle creating and deleting new records on our join table, as we'll want to make sure our data stays consistent for our records should something be deleted or changed.

What Are We Looking at?

Before we proceed, let's visualize what our database tables currently look like if there were some basic data in it.

restaurants
------

 id | name    | address | zipcode | phone | website | email
 ---+-------------+-------------------+-------+-----------------------+--------------------------+---------------
  1 | Fish Witch | 214 NE Broadway | 97232 | 503-402-9874 | http://fishwitch.com | [email protected]
  2 | Cali Roll Sushi | 6600 NE Multnomah St | 97232 | 971-218-7228 | http://calirollsushi.com | [email protected]

foodtypes
------

 id | name     
---+----------
  1 | Sushi
  2 | Bar Food

restaurants_foodtypes
------

 id | restaurantid | foodtypeid
---+------------+-----------
  1 | 1 | 1
  2 | 2 | 1
  3 | 1 | 2

Fish Witch and Cali Roll Sushi are both seafood restaurants, but Fish Witch is additionally categorized as bar food, because it's where pirate types hang out and drink beer of large, oversized mugs shaped like ships. Take a look at the table above and make sure you understand what is going on here. It makes sense that the join table has more rows than the individual ones.

Inserting the record into the join table is fairly easy, but we're going to have to be a bit clever when we query the join table. Because the join table doesn't hold any data apart from ids, we'll have to first get that data, and then use our list of ids to query the table that holds the "rest" of our object(s), and add those to a collection that we then return. See if you can make sense of what is happening below.

Let's start with Foodtype. Add the following to the addFoodtypeToRestaurant and getAllRestaurantsForAFoodtype method shells, respectively.

src/main/java/dao/Sql2oFoodtypeDao.java.java

@Override
public void addFoodtypeToRestaurant(Foodtype foodtype, Restaurant restaurant){
   String sql = "INSERT INTO restaurants_foodtypes (restaurantid, foodtypeid) VALUES (:restaurantId, :foodtypeId)";
   try (Connection con = sql2o.open()) {
       con.createQuery(sql)
               .addParameter("restaurantId", restaurant.getId())
               .addParameter("foodtypeId", foodtype.getId())
               .executeUpdate();
   } catch (Sql2oException ex){
       System.out.println(ex);
   }
}

@Override
public List<Restaurant> getAllRestaurantsForAFoodtype(int foodtypeId) {

   ArrayList<Restaurant> restaurants = new ArrayList<>();

   String joinQuery = "SELECT restaurantid FROM restaurants_foodtypes WHERE foodtypeid = :foodtypeId";

   try (Connection con = sql2o.open()) {
       List<Integer> allRestaurantIds = con.createQuery(joinQuery)
               .addParameter("foodtypeId", foodtypeId)
               .executeAndFetch(Integer.class); //what is happening in the lines above?
       for (Integer restaurantId : allRestaurantIds){
           String restaurantQuery = "SELECT * FROM restaurants WHERE id = :restaurantId";
           restaurants.add(
                   con.createQuery(restaurantQuery)
                           .addParameter("restaurantId", restaurantId)
                           .executeAndFetchFirst(Restaurant.class));
       } //why are we doing a second sql query - set?
   } catch (Sql2oException ex){
       System.out.println(ex);
   }
   return restaurants;
}


This second method is bumping up the complexity level a bit. It has multiple SQL queries, a for loop, and has a fairly nested syntax that makes it harder to read. We recommend you comment this line-by-line until you feel comfortable in your understanding.

Run your tests for this file and - hooray! We have a passing test. Let's move on to implementing our methods in our Sql2oRestaurantDao class in the same way:

src/main/java/dao/Sql2oRestaurantDao.java
@Override
public void addRestaurantToFoodtype(Restaurant restaurant, Foodtype foodtype){
   String sql = "INSERT INTO restaurants_foodtypes (restaurantid, foodtypeid) VALUES (:restaurantId, :foodtypeId)";
   try (Connection con = sql2o.open()) {
       con.createQuery(sql)
               .addParameter("restaurantId", restaurant.getId())
               .addParameter("foodtypeId", foodtype.getId())
               .executeUpdate();
   } catch (Sql2oException ex){
       System.out.println(ex);
   }
}

@Override
public List<Foodtype> getAllFoodtypesByRestaurant(int restaurantId) {
   ArrayList<Foodtype> foodtypes = new ArrayList<>();

   String joinQuery = "SELECT foodtypeid FROM restaurants_foodtypes WHERE restaurantid = :restaurantId";

   try (Connection con = sql2o.open()) {
       List<Integer> allFoodtypesIds = con.createQuery(joinQuery)
               .addParameter("restaurantId", restaurantId)
               .executeAndFetch(Integer.class);
       for (Integer foodId : allFoodtypesIds){
           String foodtypeQuery = "SELECT * FROM foodtypes WHERE id = :foodtypeId";
           foodtypes.add(
                   con.createQuery(foodtypeQuery)
                           .addParameter("foodtypeId", foodId)
                           .executeAndFetchFirst(Foodtype.class));
       }
   } catch (Sql2oException ex){
       System.out.println(ex);
   }
   return foodtypes;
}

Now we should have passing tests for both our Sql2oDao files - Foodtype and Restaurant! Awesome.

You probably noticed both pairs of methods are very similar and, as far as the add methods go, syntactically identical. You could make a convincing case that this isn't very DRY - and you'd be right, there is some code repetition here. To fix that, we could have begun by writing just one add method that works for both adding Foodtypes to Restaurants and vice versa, and we could also come up with a more generic method that allows us to retrieve either Foodtypes by Restaurant or the opposite. But as we are still learning, and this is our first many-to-many relationship, we wanted to keep things as consistent and straightforward as possible. You have the rest of your career to improve on this process!

Deleting with Many-to-Many

Towards the beginning of our discussion of many-to-many data relationships, I mentioned that one area where we need to do some more work is in the D of CRUD: When we delete a Restaurant or a Foodtype, we shouldn't just be deleting from the individual table that stores individual data, we should also be deleting from the join table, too! If we don't delete from our join table, it will get longer and longer, full of Foodtypes and Restaurants that no longer exist.

We should also consider that a Restaurant may want to update the Foodtypes it serves - but in order to move on to a different aspect we'll backburner that for now. (Remember that Trello board you created? We'll be checking back in on that in a minute.)

Let's write two new tests to confirm we're deleting shared records correctly, then edit our delete methods before we move on.

Let's write a new test in Sql2oFoodtypeDaoTest:

src/test/java/dao/Sql2oFoodtypeDaoTest.java

@Test
public void deleteingRestaurantAlsoUpdatesJoinTable() throws Exception {
   Foodtype testFoodtype  = new Foodtype("Seafood");
   foodtypeDao.add(testFoodtype);

   Restaurant testRestaurant = setupRestaurant();
   restaurantDao.add(testRestaurant);

   Restaurant altRestaurant = setupAltRestaurant();
   restaurantDao.add(altRestaurant);

   restaurantDao.addRestaurantToFoodtype(testRestaurant,testFoodtype);
   restaurantDao.addRestaurantToFoodtype(altRestaurant, testFoodtype);

   restaurantDao.deleteById(testRestaurant.getId());
   assertEquals(0, restaurantDao.getAllFoodtypesByRestaurant(testRestaurant.getId()).size());
}

This is pretty detailed - I'm checking to see that all relevant records are getting updated here. If our method deletes one row in the join table, but not the other, we would have a problem!

And our test fails, as expected:

java.lang.AssertionError:
Expected :0
Actual   :1

Now let's go ahead and edit our code to make this pass in Sql2oRestaurantDao :

src/main/java/dao/Sql2oRestaurantDao.java
@Override
public void deleteById(int id) {
   String sql = "DELETE from restaurants WHERE id = :id";
   String deleteJoin = "DELETE from restaurants_foodtypes WHERE restaurantid = :restaurantId";
   try (Connection con = sql2o.open()) {
       con.createQuery(sql)
               .addParameter("id", id)
               .executeUpdate();
       con.createQuery(deleteJoin)
               .addParameter("restaurantId", id)
               .executeUpdate();

   } catch (Sql2oException ex){
       System.out.println(ex);
   }
}

As you can see, we can elegantly add just a small amount of code to our delete method to take care of deleting those additional records in the join table. Try and do the same for Foodtype on your own - I encourage you to be really precise by adding more than one Foodtype to a Restaurant and double checking that they all get deleted correctly. Take a look at the cheat sheet if you get stuck. Start by writing a test, then editing the method.

This was a ton of work, but now we have some great functionality built out and thoroughly tested. We now have the skills to design and build an app with data persistence, varied data relationships, testing, routing, frontend, backend. We've learned about the DAO pattern, interfaces, object inheritance, and a ton more!

This is awesome stuff. Next, we'll learn about how to build our the routing portion of our API, before we see it in action. We'll also learn more about what Exceptions are, and finally get more information on try/catch blocks, as well as some advanced SQL skills.

Here is our test for Foodtype:

src/test/java/dao/Sql2oFoodtypeDaoTest.java

@Test
public void deleteingFoodtypeAlsoUpdatesJoinTable() throws Exception {

   Restaurant testRestaurant = setupRestaurant();

   restaurantDao.add(testRestaurant);

   Foodtype testFoodtype = setupNewFoodtype();
   Foodtype otherFoodType = new Foodtype("Japanese");

   foodtypeDao.add(testFoodtype);
   foodtypeDao.add(otherFoodType);

   foodtypeDao.addFoodtypeToRestaurant(testFoodtype, testRestaurant);
   foodtypeDao.addFoodtypeToRestaurant(otherFoodType,testRestaurant);

   foodtypeDao.deleteById(testRestaurant.getId());
   assertEquals(0, foodtypeDao.getAllRestaurantsForAFoodtype(testFoodtype.getId()).size());
}

And here is our edited deleteById method:

src/main/java/dao/Sql2oFoodtypeDao.java
@Override
public void deleteById(int id) {
   String sql = "DELETE from foodtypes WHERE id=:id";
   String deleteJoin = "DELETE from restaurants_foodtypes WHERE foodtypeid = :foodtypeId";
   try (Connection con = sql2o.open()) {
       con.createQuery(sql)
               .addParameter("id", id)
               .executeUpdate();

       con.createQuery(deleteJoin)
               .addParameter("foodtypeId", id)
               .executeUpdate();

   } catch (Sql2oException ex){
       System.out.println(ex);
   }
}


Example GitHub Repo for At this Stage