Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Let's practice a little recall with the Reviews portion of our API. You got this!
-
0:00
It's feeling pretty good, isn't it?
-
0:02
We got a pretty sweet API right now, and
-
0:04
have all the tools that we need to build our the rest of our spec.
-
0:07
We learned a ton, and
-
0:08
let's take a moment to make sure it's all sticking in our brains.
-
0:11
Let's use building the reviews portion to practice the whole process again,
-
0:15
one more time, with feeling.
-
0:17
Okay, so first things first here.
-
0:19
We need to make a Sql2o implementation of our review DAO.
-
0:24
Now, why don't you take a swing at it first?
-
0:27
Now, don't forget the naming structure.
-
0:29
Lean on the other course DAO implementation for help.
-
0:33
It's not cheating.
-
0:34
Don't worry about it.
-
0:35
Look at how we did it and then do it over there, okay?
-
0:39
Pause me and come back after you have completed implementing the methods.
-
0:42
When you're all done implementing those methods, come back here,
-
0:45
I'll show you how I did it.
-
0:46
Ready?
-
0:46
Pause me.
-
0:49
Okay, ready.
-
0:50
How'd you do?
-
0:51
Here's what I did.
-
0:52
I created a new class in the dao here,
-
0:56
I created a new class called Sql2o,
-
1:00
the implementation first, and then the interface, ReviewDao.
-
1:05
And then, I clicked OK.
-
1:09
And, I told it implements the ReviewDao.
-
1:15
Of course, that gets angry, and then I said, why don't we implement the methods?
-
1:20
So, we'll implement all of those.
-
1:22
All right.
-
1:23
Now, first things first.
-
1:24
We need to add a constructor that's gonna take the Sql2o object so we can use it.
-
1:30
So, we're gonna say public Sql2oReviewDao,
-
1:35
and it's gonna take Sql2o object and store it
-
1:44
As a field, so that we can use that where we need to.
-
1:48
So, it will be a field here.
-
1:50
So, all of these methods can use that Sql20 that was passed in.
-
1:53
Now, remember we were doing that for the tests, basically.
-
1:56
And so, we can change that whenever.
-
1:58
So, now we are ready to implement those methods.
-
2:00
And it might feel like cheating.
-
2:02
But, it's definitely a good idea to copy the style from the other code.
-
2:07
So, let's go over and
-
2:08
take a peek at how the other Sql2o limitation happened when we did courses.
-
2:14
Okay?
-
2:15
So, let's do that.
-
2:18
Okay, so, when we did add, we did an insert and
-
2:21
we knew what the parameters were.
-
2:24
And then, we bound the course.
-
2:25
And you know what, this is almost exactly the same, isn't it?
-
2:29
Why don't I just copy the guts of this here, the guts of this method.
-
2:34
I'm gonna copy it.
-
2:36
I'm gonna pop over to where we were and I'm just gonna paste it in here.
-
2:40
Now, this might make you shudder.
-
2:43
This happens all the time.
-
2:45
The style of what's happening in here is
-
2:48
important to keep as other people are coming through.
-
2:50
So, I'm gonna come over here and
-
2:51
I'm gonna change this because it's not courses, right?
-
2:54
It's reviews.
-
2:55
So, INSERT INTO reviews.
-
2:57
And, we might need to remember what these fields were.
-
2:59
Now, we know that the columns are name and url for courses but
-
3:02
what was it for our review table?
-
3:04
Let's look.
-
3:04
So, we have a course id, a rating, and a comment.
-
3:07
Course id, rating, and comment.
-
3:09
So, I'm gonna pump those over here.
-
3:10
So, we have course id, rating, and comment, okay?
-
3:16
And then, we are going to, of course, name our parameters.
-
3:20
We want a course ID, rating, and comment.
-
3:25
And, we're gonna much those to be the fields that are on our review
-
3:29
that was passed in, right?
-
3:30
So, the review is passed in.
-
3:31
We're gonna bind to the review and then the review is gonna say set ID, okay?
-
3:38
And, look here, problem adding course.
-
3:41
So, we need to make that say review, so,
-
3:43
that is something that you need to be careful.
-
3:45
You need to look at every line if you do one of these copy and
-
3:48
paste maneuvers, okay?
-
3:49
Cool. And now let's add the find by course ID.
-
3:52
Now, that's so similar to the course DAO, find by ID.
-
3:56
So, again, let's jump over here and let's pilfer that.
-
3:58
Okay, so we're gonna grab all of this SQL, this find by ID.
-
4:03
So, we're gonna come in here, gonna copy that.
-
4:07
And then, we're gonna pop back into ours, and we're gonna pop in here and paste.
-
4:14
It says that it needs a course because it's gonna use it,
-
4:21
so, select star from reviews WHERE course_id is course_id.
-
4:28
And here, we'll say, course_id, so
-
4:31
we're going to add the parameter for a courseId, and
-
4:36
then, we're gonna look at what is left over here.
-
4:42
And, this is saying it's returning a course, that's right,
-
4:46
we want to return a review.
-
4:48
Now look, we've copied and pasted and it's still complaining and
-
4:51
it's expecting a list and that's because there's many reviews.
-
4:54
So, we want, executeAndFetch not executeAndFetchFirst.
-
4:58
That's one of those situations where it have might have been easier if we just
-
5:02
wrote that right?
-
5:04
So, we're gonna select everything from reviews where the course ID is what was
-
5:07
passed in the parameters course ID, and we're gonna execute and
-
5:10
fetch all of the things and turn them into reviews.
-
5:14
Sound good? So, finally the reviews.
-
5:16
Let's not copy and paste anymore, I think I got the style back.
-
5:19
So, I'm gonna say try (Connection con = sql20.open())
-
5:28
And, we're just gonna return con.createQuery SELECT * FROM reviews.
-
5:35
So, that's every review in the system.
-
5:39
And, we're gonna say .executeAndFetch(Review.class).
-
5:47
We have double returns and it doesn't need that.
-
5:50
Awesome, so, we've got our methods implemented.
-
5:53
And now, we need to write some tests, don't we?
-
5:56
So, why don't you do that.
-
5:57
You're gonna need to add a new test.
-
6:00
So, in the before method, why don't you create a course.
-
6:02
And just write the first one that test if adding if working.
-
6:06
Much like the adding course one.
-
6:08
Go ahead and have at it.
-
6:09
And, if you get frustrated, just stop and pause me and we can watch it.
-
6:12
But why don't you give it a shot, see what you can do there.
-
6:14
So, in the before, make a new course and add a review to it.
-
6:18
Then make sure, just like we did in the other course,
-
6:21
that the id has been set on the review.
-
6:24
Sound good?
-
6:25
Okay, pause me.
-
6:27
Okay, ready?
-
6:28
Here's how I did it.
-
6:29
So, I am in my review dao.
-
6:32
If I press command shift t, I'm gonna create a new test, and that's the name.
-
6:38
I'm gonna give it a setup for before, and let's go in there.
-
6:43
So, the before is going to be very similar to the other test that we did, right?
-
6:48
And again, we're gonna look at how that was done.
-
6:53
We are going to copy the connectionString and the Sql2o object,
-
6:58
cuz that's exactly the same that we want.
-
7:00
Pop back over to our review here and paste those in.
-
7:04
Okay, so now, we wanna make sure that we keep an open connection, right?
-
7:10
That's true of these tests.
-
7:11
We'll say conn = sql2o.open, And, we'll go ahead,
-
7:19
and we'll create a field called conn that we can use to close later.
-
7:23
Actually, let's just go ahead and do that now while we're remembering it.
-
7:25
So, we'll say tearDown, After and we'll say conn.close.
-
7:30
There we go, so now there's a connection.
-
7:32
And, because every time all of the connections are close to the database,
-
7:36
it wipes the database.
-
7:37
We don't want that, we want the database to stick around for the entire test.
-
7:40
So, let's make a new courseDao and it will be a new Sql2oCourseDao.
-
7:47
And, it's gonna take that object that we passed in here, our test and
-
7:51
memory database.
-
7:52
And we are going to let CourseDao have a field, so
-
7:55
that we can use it should we need it in any of the other tests.
-
7:59
And, we're also gonna make a reviewDao here, that'll be a new Sql2oReviewDao.
-
8:06
We'll pass in on our sql2o object there.
-
8:09
Now, we're making reviews and what good is a review without having a course right?
-
8:14
So, let's go ahead and make a course that's accessible to all of our tests.
-
8:18
So, we'll say course, we want to share across all of them.
-
8:22
So, we're going to say new course.
-
8:24
Title is Test and it's http, poor guys at test.com, getting hit again.
-
8:31
All right,
-
8:35
so, yes, this is a course and we will make this course be available.
-
8:41
Okay, and then finally, we need to make sure that we add that right.
-
8:46
So, we're gonna say courseDao.add course.
-
8:49
That's gonna go ahead and commit.
-
8:52
Do that insert statement on the course, so
-
8:54
there will be at least one course in every one of these databases that we're running,
-
8:57
and we have access to it by the field course and the test.
-
9:01
So, now, we are finally ready to make sure that we do that add test.
-
9:06
So, what we are going to do is we are going to do just like we did for
-
9:08
the course test.
-
9:09
We're gonna make sure that the ideas changed.
-
9:12
So, let's do that.
-
9:15
So, we make a new test.
-
9:18
And, we'll say, addingReviewSetsNewId.
-
9:25
So, we'll say Review review = new Review.
-
9:31
And, we're gonna use the course.
-
9:32
We're gonna get its ID that was created for it,
-
9:34
and, the test course was definitely a five and we'll give a test comment.
-
9:41
Okay, we're arranging, we're gonna pull off the original ID, it's gonna be one but
-
9:45
let's just go ahead and incase that changes and if you wanna set that later.
-
9:50
And now, we will add a review that's how we gonna act on this.
-
9:56
And then, we're gonna assert that it's changed, right?
-
9:59
That the original id is not the newer id,
-
10:08
it's like I spelled original wrong.
-
10:14
Original id, there we go.
-
10:17
All right, I think we go it.
-
10:19
Let's go ahead, let's run our test.
-
10:25
Boom, got it, it's working.
-
10:27
All right, so let's try out our other method.
-
10:31
Our find by course ID.
-
10:33
So, we're gonna need to do here is we're gonna need to add a couple reviews
-
10:36
first, right?
-
10:37
So we'll say,
-
10:40
multipleReviewsAreFoundWhenTheyExistForAC- ourse.
-
10:50
Test names can be as long as you need them to be, don't be afraid of that.
-
10:54
Okay, so we're gonna use our reviewDao and we're gonna add a new review,
-
11:00
and we're say, course.getId, and somebody thought this was great.
-
11:05
Let's say test comment 1.
-
11:09
Then, we'll go ahead and I will duplicate this line.
-
11:14
And, we're just going to say that they didn't like it.
-
11:19
I gave it a 1 and there's 2.
-
11:20
Okay, so that's all the arrangement that we need to do and
-
11:25
then we'll pull off this list of reviews called reviews.
-
11:30
And we'll say, let me scroll this up for you,
-
11:35
say reviewDao we're going to act by find by course id,
-
11:42
of course we're using our course.getId and
-
11:47
we'll say, assertEquals 2, so
-
11:51
that's making sure we got our one to many.
-
12:01
I chose the wrong list in here.
-
12:04
Up hold.
-
12:07
We do not want the javac.util list.
-
12:10
That is wrong.
-
12:13
So, this list here, we want java.util.
-
12:18
There we go.
-
12:25
What is going on here?
-
12:27
Could not map course_ID to any property.
-
12:31
Hm. Let's take a look and
-
12:33
see what is going on over there.
-
12:36
In our review, here we have findByCourseId.
-
12:40
Right, so what's gonna happen is, it's gonna return something and
-
12:43
it doesn't know what course_id is, right?
-
12:46
How could it properly know that?
-
12:48
So, there's a thing in sql2o called, column mapping.
-
12:52
So, we're gonna say .add column mapping and what we're gonna say is that,
-
12:56
when you encounter course_id,
-
13:00
I'd like you to set course_id, so that solves that problem.
-
13:05
I wanted you to see that error.
-
13:06
Very common when using sql2o.
-
13:08
And, let's run that again.
-
13:12
And blammo, we got it.
-
13:14
Let's make sure our foreign key constraint is still working.
-
13:17
Let's make sure that, when we add a review,
-
13:19
that we throw an error if the add fails, right?
-
13:22
If the course doesn't exist.
-
13:23
That's what we were supposed to be taking a look at, right?
-
13:25
So, let's see.
-
13:28
Okay, so let's make a new test.
-
13:30
We'll generate a new test, and we'll say
-
13:34
addingAReviewToANonExistingCourseFails just like we would think it would,
-
13:41
and we are going to actually use an exception.
-
13:46
We're gonna expect that a DaoException is thrown, okay, so, here we go.
-
13:53
So, we'll just make a new review.
-
13:55
Review equals review new Review, we'll just throw some random ID in there.
-
14:02
5, Test comment.
-
14:08
And, when we try to add that, it should catch our problem.
-
14:11
And, that's good, right?
-
14:12
Cuz we don't want those child reviews, if the course is under this course 42 doesn't
-
14:16
exist, what would a review of that be?
-
14:19
I forgot to put an equal sign here.
-
14:21
Equals, here we go, so, lets run that and see.
-
14:26
Awesome.
-
14:28
Okay, so, lets take a quick break.
-
14:31
You deserve it.
-
14:31
Why don't you stand up and walk around.
-
14:33
Do some jumping jacks, and then come back here, and then we'll switch back over, and
-
14:36
we'll finish up the API for the reviews.
-
14:38
Awesome.
You need to sign up for Treehouse in order to download course files.
Sign up