It's feeling pretty good, isn't it?

We got a pretty sweet API right now, and

have all the tools that we need to build our the rest of our spec.

We learned a ton, and

let's take a moment to make sure it's all sticking in our brains.

Let's use building the reviews portion to practice the whole process again,

one more time, with feeling.

Okay, so first things first here.

We need to make a Sql2o implementation of our review DAO.

Now, why don't you take a swing at it first?

Now, don't forget the naming structure.

Lean on the other course DAO implementation for help.

It's not cheating.

Don't worry about it.

Look at how we did it and then do it over there, okay?

Pause me and come back after you have completed implementing the methods.

When you're all done implementing those methods, come back here,

I'll show you how I did it.

Ready?

Pause me.

Okay, ready.

How'd you do?

Here's what I did.

I created a new class in the dao here,

I created a new class called Sql2o,

the implementation first, and then the interface, ReviewDao.

And then, I clicked OK.

And, I told it implements the ReviewDao.

Of course, that gets angry, and then I said, why don't we implement the methods?

So, we'll implement all of those.

All right.

Now, first things first.

We need to add a constructor that's gonna take the Sql2o object so we can use it.

So, we're gonna say public Sql2oReviewDao,

and it's gonna take Sql2o object and store it

As a field, so that we can use that where we need to.

So, it will be a field here.

So, all of these methods can use that Sql20 that was passed in.

Now, remember we were doing that for the tests, basically.

And so, we can change that whenever.

So, now we are ready to implement those methods.

And it might feel like cheating.

But, it's definitely a good idea to copy the style from the other code.

So, let's go over and

take a peek at how the other Sql2o limitation happened when we did courses.

Okay?

So, let's do that.

Okay, so, when we did add, we did an insert and

we knew what the parameters were.

And then, we bound the course.

And you know what, this is almost exactly the same, isn't it?

Why don't I just copy the guts of this here, the guts of this method.

I'm gonna copy it.

I'm gonna pop over to where we were and I'm just gonna paste it in here.

Now, this might make you shudder.

This happens all the time.

The style of what's happening in here is

important to keep as other people are coming through.

So, I'm gonna come over here and

I'm gonna change this because it's not courses, right?

It's reviews.

So, INSERT INTO reviews.

And, we might need to remember what these fields were.

Now, we know that the columns are name and url for courses but

what was it for our review table?

Let's look.

So, we have a course id, a rating, and a comment.

Course id, rating, and comment.

So, I'm gonna pump those over here.

So, we have course id, rating, and comment, okay?

And then, we are going to, of course, name our parameters.

We want a course ID, rating, and comment.

And, we're gonna much those to be the fields that are on our review

that was passed in, right?

So, the review is passed in.

We're gonna bind to the review and then the review is gonna say set ID, okay?

And, look here, problem adding course.

So, we need to make that say review, so,

that is something that you need to be careful.

You need to look at every line if you do one of these copy and

paste maneuvers, okay?

Cool. And now let's add the find by course ID.

Now, that's so similar to the course DAO, find by ID.

So, again, let's jump over here and let's pilfer that.

Okay, so we're gonna grab all of this SQL, this find by ID.

So, we're gonna come in here, gonna copy that.

And then, we're gonna pop back into ours, and we're gonna pop in here and paste.

It says that it needs a course because it's gonna use it,

so, select star from reviews WHERE course_id is course_id.

And here, we'll say, course_id, so

we're going to add the parameter for a courseId, and

then, we're gonna look at what is left over here.

And, this is saying it's returning a course, that's right,

we want to return a review.

Now look, we've copied and pasted and it's still complaining and

it's expecting a list and that's because there's many reviews.

So, we want, executeAndFetch not executeAndFetchFirst.

That's one of those situations where it have might have been easier if we just

wrote that right?

So, we're gonna select everything from reviews where the course ID is what was

passed in the parameters course ID, and we're gonna execute and

fetch all of the things and turn them into reviews.

Sound good? So, finally the reviews.

Let's not copy and paste anymore, I think I got the style back.

So, I'm gonna say try (Connection con = sql20.open())

And, we're just gonna return con.createQuery SELECT * FROM reviews.

So, that's every review in the system.

And, we're gonna say .executeAndFetch(Review.class).

We have double returns and it doesn't need that.

Awesome, so, we've got our methods implemented.

And now, we need to write some tests, don't we?

So, why don't you do that.

You're gonna need to add a new test.

So, in the before method, why don't you create a course.

And just write the first one that test if adding if working.

Much like the adding course one.

Go ahead and have at it.

And, if you get frustrated, just stop and pause me and we can watch it.

But why don't you give it a shot, see what you can do there.

So, in the before, make a new course and add a review to it.

Then make sure, just like we did in the other course,

that the id has been set on the review.

Sound good?

Okay, pause me.

Okay, ready?

Here's how I did it.

So, I am in my review dao.

If I press command shift t, I'm gonna create a new test, and that's the name.

I'm gonna give it a setup for before, and let's go in there.

So, the before is going to be very similar to the other test that we did, right?

And again, we're gonna look at how that was done.

We are going to copy the connectionString and the Sql2o object,

cuz that's exactly the same that we want.

Pop back over to our review here and paste those in.

Okay, so now, we wanna make sure that we keep an open connection, right?

That's true of these tests.

We'll say conn = sql2o.open, And, we'll go ahead,

and we'll create a field called conn that we can use to close later.

Actually, let's just go ahead and do that now while we're remembering it.

So, we'll say tearDown, After and we'll say conn.close.

There we go, so now there's a connection.

And, because every time all of the connections are close to the database,

it wipes the database.

We don't want that, we want the database to stick around for the entire test.

So, let's make a new courseDao and it will be a new Sql2oCourseDao.

And, it's gonna take that object that we passed in here, our test and

memory database.

And we are going to let CourseDao have a field, so

that we can use it should we need it in any of the other tests.

And, we're also gonna make a reviewDao here, that'll be a new Sql2oReviewDao.

We'll pass in on our sql2o object there.

Now, we're making reviews and what good is a review without having a course right?

So, let's go ahead and make a course that's accessible to all of our tests.

So, we'll say course, we want to share across all of them.

So, we're going to say new course.

Title is Test and it's http, poor guys at test.com, getting hit again.

All right,

so, yes, this is a course and we will make this course be available.

Okay, and then finally, we need to make sure that we add that right.

So, we're gonna say courseDao.add course.

That's gonna go ahead and commit.

Do that insert statement on the course, so

there will be at least one course in every one of these databases that we're running,

and we have access to it by the field course and the test.

So, now, we are finally ready to make sure that we do that add test.

So, what we are going to do is we are going to do just like we did for

the course test.

We're gonna make sure that the ideas changed.

So, let's do that.

So, we make a new test.

And, we'll say, addingReviewSetsNewId.

So, we'll say Review review = new Review.

And, we're gonna use the course.

We're gonna get its ID that was created for it,

and, the test course was definitely a five and we'll give a test comment.

Okay, we're arranging, we're gonna pull off the original ID, it's gonna be one but

let's just go ahead and incase that changes and if you wanna set that later.

And now, we will add a review that's how we gonna act on this.

And then, we're gonna assert that it's changed, right?

That the original id is not the newer id,

it's like I spelled original wrong.

Original id, there we go.

All right, I think we go it.

Let's go ahead, let's run our test.

Boom, got it, it's working.

All right, so let's try out our other method.

Our find by course ID.

So, we're gonna need to do here is we're gonna need to add a couple reviews

first, right?

So we'll say,

multipleReviewsAreFoundWhenTheyExistForAC- ourse.

Test names can be as long as you need them to be, don't be afraid of that.

Okay, so we're gonna use our reviewDao and we're gonna add a new review,

and we're say, course.getId, and somebody thought this was great.

Let's say test comment 1.

Then, we'll go ahead and I will duplicate this line.

And, we're just going to say that they didn't like it.

I gave it a 1 and there's 2.

Okay, so that's all the arrangement that we need to do and

then we'll pull off this list of reviews called reviews.

And we'll say, let me scroll this up for you,

say reviewDao we're going to act by find by course id,

of course we're using our course.getId and

we'll say, assertEquals 2, so

that's making sure we got our one to many.

I chose the wrong list in here.

Up hold.

We do not want the javac.util list.

That is wrong.

So, this list here, we want java.util.

There we go.

What is going on here?

Could not map course_ID to any property.

Hm. Let's take a look and

see what is going on over there.

In our review, here we have findByCourseId.

Right, so what's gonna happen is, it's gonna return something and

it doesn't know what course_id is, right?

How could it properly know that?

So, there's a thing in sql2o called, column mapping.

So, we're gonna say .add column mapping and what we're gonna say is that,

when you encounter course_id,

I'd like you to set course_id, so that solves that problem.

I wanted you to see that error.

Very common when using sql2o.

And, let's run that again.

And blammo, we got it.

Let's make sure our foreign key constraint is still working.

Let's make sure that, when we add a review,

that we throw an error if the add fails, right?

If the course doesn't exist.

That's what we were supposed to be taking a look at, right?

So, let's see.

Okay, so let's make a new test.

We'll generate a new test, and we'll say

addingAReviewToANonExistingCourseFails just like we would think it would,

and we are going to actually use an exception.

We're gonna expect that a DaoException is thrown, okay, so, here we go.

So, we'll just make a new review.

Review equals review new Review, we'll just throw some random ID in there.

5, Test comment.

And, when we try to add that, it should catch our problem.

And, that's good, right?

Cuz we don't want those child reviews, if the course is under this course 42 doesn't

exist, what would a review of that be?

I forgot to put an equal sign here.

Equals, here we go, so, lets run that and see.

Awesome.

Okay, so, lets take a quick break.

You deserve it.

Why don't you stand up and walk around.

Do some jumping jacks, and then come back here, and then we'll switch back over, and

we'll finish up the API for the reviews.

Awesome.