Exercise Monday

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 tasks N, D and E to the In Progress column.

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. You see the letter in the comment behind the method? Can you figure out what that means? Check the trello board.

src/main/java/dao/FoodtypeDao
public interface FoodtypeDao {

   //create
   void add(Foodtype foodtype); // N
   //void addFoodTypeToRestaurant(Foodtype foodtype, Restaurant restaurant); // D

   //read
   List<Foodtype> getAll(); // we may need this in the future. We can use it to retrieve all Foodtypes.
   // List<Restaurant> getAllRestaurantsForAFoodtype(int id); //E we will implement this soon.

   //update
   //omit for now

   //delete
   void deleteById(int id); //see above
}
src/main/java/dao/RestaurantDao
public interface RestaurantDao {

   //create
   void add (Restaurant restaurant); //J
   // void addRestaurantToFoodType(Restaurant restaurant, Foodtype foodtype) //D & E

   //read
   List<Restaurant> getAll(); //A
   // List<Foodtype> Restaurant(int restaurantId); //D & E - we will implement this soon.

   Restaurant findById(int id); //B & C

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

   //delete
   void deleteById(int id); //K
}
src/main/java/dao/ReviewDao
public interface ReviewDao {

   //create
   void add(Review review); //F

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

   //update
   //omit for now

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

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

}

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 foodtype Sql2o implementation you can use as a guide. Try your hand at creating the missing two.

src/test/java/dao/Sql2oReviewDaoTest
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 {
        Restaurant testRestaurant = setupRestaurant();
        restaurantDao.add(testRestaurant);
        Review testReview = new Review("Captain Kirk", 3, "foodcoma!",testRestaurant.getId());
        reviewDao.add(testReview);
        assertEquals(1,testReview.getId());
    }

    @Test
    public void getAllReviewsByRestaurant() throws Exception {

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

        Restaurant newRestaurant = setupRestaurant(); //add in some extra data to see if it interferes
        restaurantDao.add(newRestaurant);

        Review testReview = new Review("Captain Kirk", 3, "foodcoma!",testRestaurant.getId());
        reviewDao.add(testReview);

        Review otherReview = new Review("Mr. Spock", 1, "passable", testRestaurant.getId());
        reviewDao.add(otherReview);


        assertEquals(2, reviewDao.getAllReviewsByRestaurant(testRestaurant.getId()).size());
        assertEquals(0, reviewDao.getAllReviewsByRestaurant(newRestaurant.getId()).size()); //why is this a good idea as a safety check?
    }

    //helpers

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

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