Lesson Weekend

Cool! We are moving along nicely. Next we'll implement our DAO and Sql2o files, and get tests straightened out. This should leave our application in the same backend state we recently left our To Do List. After that, we'll begin transforming our application from an app with both a backend and frontend to an API. Let's get started.

Understanding our Project structure

You will complete much of this work on your own - take your time, and don't rush through it. You can do this!

Pull up your Trello board, and move these tasks to the In Progress column:

M. As an admin, I want to add a Foodtype so a Restaurant can be associated with it
D. As a user, I want to see which kind of Foodtypes a single Restaurant is associated with
E. As a user, I want to see all Restaurants of a specific Foodtype (say, Pho, or Brunch) so I can browse through them

Pull up your working copy of To Do List to refresh your memory of the structure of this app. In To Do List we had:

  • Class files to represent our data model
  • TaskDao and CategoryDao interfaces to list, name, and remind us what functionality (ie: CRUD) we need to implement, and which arguments must be provided.
  • Sql2oTaskDao and Sql2oCategoryDao files to contain the logic behind the interface methods. (The files that actually connected our application to Postgres.)

You'll create the same structure for the three classes in our current Jadle project, too.

Adding files to Jadle - the finished product

By the time you are done, you should have the following file structure:

jadle-midpoint-proj-structure

While this lesson will depict what our DAO files should look like, we want you to try building out your application using your own To Do List project from last week and Trello board as a guide.

Begin by listing the methods needed in your interface files: add(), findById(), getAll() - and what else? You should have one method per user story, except for user stories satisfied by getters.

Getting our DAO interfaces off the ground

Here are our DAO interface files.

src/main/java/dao/FoodtypeDao
package dao;

import models.Foodtype;
import java.util.List;

public interface FoodtypeDao {

  //create
  void add(Foodtype foodtype);
  //void addFoodtypeToRestaurant(Foodtype foodtype, Restaurant restaurant);

  //read
  List<Foodtype> getAll();
  // List<Restaurant> getAllRestaurantsForAFoodtype(int id);

  //update
  //omit for now

  //delete
  void deleteById(int id);
  void clearAll();
}
src/main/java/dao/RestaurantDao
package dao;

import models.Restaurant;
import java.util.List;

public interface RestaurantDao {

  //create
  void add (Restaurant restaurant);
  // void addRestaurantToFoodType(Restaurant restaurant, Foodtype foodtype)

  //read
  List<Restaurant> getAll();
  Restaurant findById(int id);
  // List<Foodtype> getAllFoodtypesForARestaurant(int restaurantId);

  //update
  void update(int id, String name, String address, String zipcode, String phone, String website, String email);

  //delete
  void deleteById(int id);
  void clearAll();
}
src/main/java/dao/ReviewDao
package dao;

import models.Review;
import java.util.List;

public interface ReviewDao {

  //create
  void add(Review review);

  //read
  List<Review> getAll();
  List<Review> getAllReviewsByRestaurant(int restaurantId);

  //update
  //omit for now

  //delete
  void deleteById(int id);
  void clearAll();
}

Adding our Sql2o implementation files

Once you have implemented these methods in the corresponding Sql2o implementation files, move the cards from "In Progress" to "Done". Repeat with the relevant cards as you go along.

Here is one of our Sql2o implementation files as a guide. It's up to you to make the other two.

src/main/java/dao/Sql2oReviewDao
package dao;

import models.Review;
import org.sql2o.Connection;
import org.sql2o.Sql2o;
import org.sql2o.Sql2oException;
import java.util.List;

public class Sql2oReviewDao implements ReviewDao {
  private final Sql2o sql2o;
  public Sql2oReviewDao(Sql2o sql2o) { this.sql2o = sql2o; }

  @Override
  public void add(Review review) {
    String sql = "INSERT INTO reviews (writtenby, content, rating, restaurantid) VALUES (:writtenBy, :content, :rating, :restaurantId)"; //if you change your model, be sure to update here as well!
    try (Connection con = sql2o.open()) {
      int id = (int) con.createQuery(sql, true)
              .bind(review)
              .executeUpdate()
              .getKey();
      review.setId(id);
    } catch (Sql2oException ex) {
      System.out.println(ex);
    }
  }

  @Override
  public List<Review> getAll() {
    try (Connection con = sql2o.open()) {
      return con.createQuery("SELECT * FROM reviews")
              .executeAndFetch(Review.class);
    }
  }

  @Override
  public List<Review> getAllReviewsByRestaurant(int restaurantId) {
    try (Connection con = sql2o.open()) {
      return con.createQuery("SELECT * FROM reviews WHERE restaurantId = :restaurantId")
              .addParameter("restaurantId", restaurantId)
              .executeAndFetch(Review.class);
    }
  }

  @Override
  public void deleteById(int id) {
    String sql = "DELETE from reviews 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 clearAll() {
    String sql = "DELETE from reviews";
    try (Connection con = sql2o.open()) {
      con.createQuery(sql).executeUpdate();
    } catch (Sql2oException ex) {
      System.out.println(ex);
    }
  }
}

You won't be able to have methods to find all Restaurants by Foodtype, or all Foodtypes for a Restaurant yet, as we haven't addressed this. Unlike our To Do List, Restaurants will have multiple Foodtypes, as opposed to a Task only belonging to one Category.

You are already familiar with one-to-many relationships, like the relationship between Tasks and Categorys. But this application will require something called a many-to-many relationship. This is because many Restaurants offer many Foodtypes, and Foodtypes can report which Restaurants offer them. In a many-to-many relationship no object contains the id of another object, as that would require one to be present before the other. Instead, a special database table called a join table is the sole element creating an association between the two objects, and we haven't created that yet.

Reviews and Restaurants will still have a one-to-many relationship. For now, you can comment out any methods and tests that have to do with this functionality, such as getAllRestaurantsForAFoodtype() and addFoodTypeToRestaurant() in the RestaurantDao interface, and the counterparts in the FoodDao interface. We'll address this issue shortly.

Adding Sql2o implementation tests

Because we are not building a frontend for our app that uses hbs files, it is absolutely crucial that we have solid tests in place. Here is a test file for the review Sql2o implementation you can use as a guide. Try your hand at creating the missing two.

src/test/java/dao/Sql2oReviewDaoTest.java
package dao;

import models.Restaurant;
import models.Review;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.sql2o.Connection;
import org.sql2o.Sql2o;

import static org.junit.Assert.*;

public class Sql2oReviewDaoTest {
  private Connection conn;
  private Sql2oReviewDao reviewDao;
  private Sql2oRestaurantDao restaurantDao;

  @Before
  public void setUp() throws Exception {
    String connectionString = "jdbc:h2:mem:testing;INIT=RUNSCRIPT from 'classpath:db/create.sql'";
    Sql2o sql2o = new Sql2o(connectionString, "", "");
    reviewDao = new Sql2oReviewDao(sql2o);
    restaurantDao = new Sql2oRestaurantDao(sql2o);
    conn = sql2o.open();
  }

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

  @Test
  public void addingReviewSetsId() throws Exception {
    Review testReview = setupReview();
    assertEquals(1, testReview.getId());
  }

  @Test
  public void getAll() throws Exception {
    Review review1 = setupReview();
    Review review2 = setupReview();
    assertEquals(2, reviewDao.getAll().size());
  }

  @Test
  public void getAllReviewsByRestaurant() throws Exception {
    Restaurant testRestaurant = setupRestaurant();
    Restaurant otherRestaurant = setupRestaurant(); //add in some extra data to see if it interferes
    Review review1 = setupReviewForRestaurant(testRestaurant);
    Review review2 = setupReviewForRestaurant(testRestaurant);
    Review reviewForOtherRestaurant = setupReviewForRestaurant(otherRestaurant);
    assertEquals(2, reviewDao.getAllReviewsByRestaurant(testRestaurant.getId()).size());
  }

  @Test
  public void deleteById() throws Exception {
    Review testReview = setupReview();
    Review otherReview = setupReview();
    assertEquals(2, reviewDao.getAll().size());
    reviewDao.deleteById(testReview.getId());
    assertEquals(1, reviewDao.getAll().size());
  }

  @Test
  public void clearAll() throws Exception {
    Review testReview = setupReview();
    Review otherReview = setupReview();
    reviewDao.clearAll();
    assertEquals(0, reviewDao.getAll().size());
  }

  //helpers

  public Review setupReview() {
    Review review = new Review("great", "Kim", 4, 555);
    reviewDao.add(review);
    return review;
  }

  public Review setupReviewForRestaurant(Restaurant restaurant) {
    Review review = new Review("great", "Kim", 4, restaurant.getId());
    reviewDao.add(review);
    return review;
  }

  public Restaurant setupRestaurant() {
    Restaurant restaurant = new Restaurant("Fish Witch", "214 NE Broadway", "97232", "503-402-9874", "http://fishwitch.com", "[email protected]");
    restaurantDao.add(restaurant);
    return restaurant;
  }
}

This is a LOT of work, and it is easy to get lost, so take your time, but we're sure you can do it!

If you get totally, irretrievably stuck, check the example repo. But know there will be no example repo at your future workplace, so try to get as far as you can before referencing the example.

Awesome. This was a ton of work, but the data model is set up, database is ready, DAO interface and Sql2o implementation files are up and running, methods are written and tested, and Restaurants and Reviewss are associated correctly. Kudos!

Next, we'll tackle connecting Foodtypes to Restaurants in the next lesson, when we learn about implementing many-to-many relationships in our code.

Example GitHub Repo for Jadle at this stage