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