Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Build a REST API in Spark!
You have completed Build a REST API in Spark!
Preview
Let's get our database implementation in place and write a test to an in-memory version of it.
SQL DDL (Data Definition Language)
CREATE TABLE IF NOT EXISTS courses (
id int PRIMARY KEY auto_increment,
name VARCHAR,
url VARCHAR
);
CREATE TABLE IF NOT EXISTS reviews (
id INTEGER PRIMARY KEY auto_increment,
course_id INTEGER,
rating INTEGER,
comment VARCHAR,
FOREIGN KEY(course_id) REFERENCES public.courses(id)
);
Gradle Dependencies
compile 'com.h2database:h2:1.4.190'
Read more
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
When we created our Sql2o DAO
implementation, we made so
0:00
we can pass in a Sql2o
instance on creation.
0:04
This approach is constructor
dependency injection, and
0:07
we are about to get to see it in action.
0:10
Since SQL is a common language for
databases, and
0:13
JDBC is the abstraction that allows
Java to talk to any database,
0:16
what we've written so far should work with
any database that's supported by JDBC.
0:20
That's pretty cool, right?
0:24
We haven't even chosen a database yet
and hypothetically it should just work.
0:26
I'd like for us to use a complete Java
database implementation named H2.
0:31
It allows us to have a file base version,
as well as a complete in-memory version.
0:36
Now the in-memory version is great for
tests, and since we wrote our data access
0:40
object, to take a Sql2o object,
we can just inject an in-memory version.
0:45
In unit testing world we call
this is test double, or a fake.
0:50
So let's write some tests and
build out the rest of our DAOs.
0:53
Okay so I used the awesome site
Maven Repository to search for H2, and
0:58
I found it here.
1:02
And if we look down here
at the dependencies,
1:03
if we click here under Gradle,
we can grab this.
1:07
We'll copy that and
we'll put that in our build.gradle file.
1:09
So we'll come over to build.gradle.
1:13
And we will add compile, and we'll add
the database there, minus those slashes.
1:16
So we're gonna click Gradle and
the refresh, and
1:25
now we will have our database dependency.
1:28
Awesome.
1:33
So H2 is now installed.
1:33
So in the interest of time, I went
ahead and I created this sql DDL, or
1:37
the Data Definition Language that we're
gonna use to create our database tables.
1:42
It's in the teacher's notes, go ahead and
copy it now and I'll do the same.
1:45
And what we'll do is come over here to
the project and under resources here and
1:48
we're gonna right-click here and
make a new directory.
1:54
And we're gonna call that directory,
2:00
db, we can put any kind of
scripts in here that we want.
2:01
So inside the db script, we were going
to add a new file called init.sql.
2:04
And we'll click OK.
2:11
Now I'm going to paste in the ddl from
the teacher's notes, and then we'll walk
2:14
through it a little bit and just make
sure that we understand what's going on.
2:18
So this first line here is nice.
2:24
It's one of those things where
if this database exists and
2:28
the table courses exist,
it's not gonna try to create it.
2:31
It won't do it if it already exists.
2:34
What that means is if you
wanna make changes to it,
2:35
you need to delete the table and
then bring it back.
2:37
So this first line here,
that is the unique identifier and
2:41
it is marked as a primary key,
it's an integer, and it auto increments.
2:45
And then again, of course it has names and
URLs which we have here, but
2:51
this is the primary key for the course.
2:55
So then we have the child table which
is reviews, and it too has an ID, and
2:57
it also is a primary key and
it auto-increments.
3:02
So we don't need to put it there,
it will automatically happen.
3:04
So if we put in another review and it was
on two, it will now be three, four, and
3:07
five automatically for us.
3:11
Course ID.
3:13
That is gonna refer to the parent course,
so one course, many reviews.
3:14
And then we have the rating and
the comment.
3:18
This line here is the foreign key,
3:23
what it's saying is make sure that
courses and reviews are tied together.
3:25
More about this in the teacher's notes,
but what this for
3:31
sure guarantees is that we cannot
create a review without a course.
3:33
We don't want them just dangling up there.
3:37
So now we basically have
our database set up.
3:40
So let's test out our
Sql2oCourseDao implementation.
3:44
So from the file over here,
we can go ahead and
3:48
do Cmd+Shift+t or Shift+Ctrl+t in Windows.
3:51
And we'll create a new test and
we will call it that exactly.
3:55
And let's go ahead and
let's make sure that we generate a setup.
4:00
Cool, all right.
4:05
So in our setup let's build
out a connection string, and
4:06
this is where the magic of JDBC happens.
4:09
So we're gonna say
String connectionString.
4:12
We're gonna specify that it's jdbc and
that we wanna use h2.
4:15
And then we're gonna specify
some parameters to that,
4:22
we wanna use an in-memory version so
we say mem.
4:25
And then you can name it,
and we do wanna name it.
4:27
We're gonna name this testing.
4:29
Okay, there is some additional
set up that h2 lets you do.
4:32
And you can do this, so you can say on
INIT you wanna run the script from,
4:36
and because we put that in our resources
we can say from the classpath:db/init.sql.
4:43
Now what does is that it allows us to
reference that file inside the package,
4:52
wherever that may be.
4:57
So, when it starts up on
initialization it will run
5:00
that script which creates our tables,
which is exactly what we want.
5:04
So we'll make a new Sql2o object,
and we will call it sql2o and
5:08
we'll pass down our connection string.
5:14
And this takes two
parameters of username and
5:18
password, we're not gonna
worry about that right now.
5:21
That's if you wanted to secure this,
this is a test, it doesn't really matter.
5:22
And what we'll do is we'll create our DAO,
now remember,
5:28
you just passed in the DAO,
you passed in any Sql2o object and
5:33
now this is gonna work, right?
5:38
Cuz we are passing in a Sql2o object
that is going to an in-memory database.
5:41
So we have successfully made a fake there,
awesome.
5:46
Now let's go ahead and make a private
field for this so everybody can use it.
5:50
That's the point of this test.
5:54
Here's something that might
come as a surprise to you.
5:56
When working with an in-memory database,
5:59
when all its connections that are coming
to it are closed, it gets erased.
6:01
It gets wiped out, so we don't want that.
6:04
Since each of our methods open and close a
connection, one thing that we can do, and
6:06
it's a bit of a trick, one thing we
can do here is open up the connection.
6:11
So let's go ahead and
let's comment first, we're gonna say,
6:15
Keep connection open through entire
test so that it isn't wiped out.
6:20
And we'll say, conn = Sql2o.open,
and that's how you open them up.
6:27
And obviously it doesn't
know what it is yet,
6:33
let's make this also a field
that we can access throughout.
6:35
Specifically where we want to access
this is after the test is over.
6:39
So remember we can do that,
6:43
we can generate a teardown method or
an after method.
6:44
And we'll just say
conn.close(); beautiful.
6:47
So now we have a connection
that's going to open,
6:52
and when it opens it's gonna initialize
and run and create those tables.
6:56
So therefore we should be able to
run through that code that we wrote.
7:00
Remember that code that we wrote
over in the Sql2oCourseDao,
7:05
when we added something we set the ID.
7:10
So let's go ahead and let's test and
make sure that that happens.
7:13
So let's flip back to our test.
7:15
We'll make a new test that is
7:21
called addingCourseSetsId.
7:25
We want to make sure we can properly add,
let's do that.
7:30
So first let's arrange things.
7:35
We'll make a new course,
just a course model.
7:37
Plain old object, = new Course.
7:40
And it has a name which is Test and
7:43
it has a URL which is http://test.com.
7:46
Boring test data there, but
that's not really important right.
7:51
So let's go ahead and we'll store that ID.
7:55
So we'll say,
int originalCourseId = course.getId.
7:59
Now remember, we're not setting that, so
by default, an integer's value is zero.
8:06
So most likely that's gonna be zero, but
instead of leaving a magical number,
8:10
let's specifically state
what it is in the test.
8:13
So, I think we have everything
all arranged so it's time to add.
8:17
Let's test what we're actually testing.
8:20
So we're gonna add the course.
8:23
And that's actually all we're doing.
8:27
And then we're going to assert
that it's not equal, so
8:29
there is a method called assertNotEquals.
8:33
We want to assert that the original course
Id is not equal to what the current Id is.
8:37
So we're verifying basically
that things got changed.
8:46
So we are all ready, let's go ahead and
over here under java choose Run 'All',
8:48
so test Java,
we're gonna choose Run 'All Tests'.
8:53
If any of that look new make sure that
you've taken the Unit Testing course.
8:57
Awesome, we passed the test and
we know that it ran.
9:04
See here it says addingCourseSetsId.
9:08
Hey, you know what?
9:12
We're in a test right now and
we haven't written the code yet
9:13
that performs the implementation
of getting all courses.
9:16
That find all method right,
we just left it blank.
9:19
Should we do a little taste of test
driven development, what do you say?
9:22
I think it sounds good.
9:25
Okay, so
we know that if we add a course it should
9:27
show up in the find all call
that we haven't yet implemented.
9:30
So let's do it, let's generate
a test that sees if that happens.
9:33
Now if that happens it would be magical,
but it shouldn't right,
9:36
cuz we haven't written that code yet,
so let's see what happens.
9:39
So we'll say,
addedCoursesAreReturnedFromFindAll,
9:42
that's pretty specific.
9:49
And we are going to
again make a new course.
9:52
This is feeling a little
duplicate-y isn't it.
9:56
Course and we'll call it Test, and
of course, we'll test.com.
9:58
What the price on test.com is, so
10:05
many people must hit that
all the time in their tests.
10:07
So we're going to do dao again,
add(course);,
10:09
that's how we're gonna act, and
let's go ahead and see what happens.
10:15
We wanna make sure that it's 1 and
that our findAll size is that.
10:20
So, we are going to do a findAll,
which should return a list of courses, and
10:30
then we'll do a size.
10:33
So, let's see what happens.
10:35
Let's go ahead and
run all of our tests again.
10:37
Doh!
10:41
The dreaded NullPointerException class.
10:42
Let's go ahead and
let's see why it's throwing a null.
10:45
So it's saying that, let's go ahead and
10:49
we'll look at this implementation,
find out why.
10:51
Well, because it's returning a null,
right?
10:54
Calling size on null made it blow chunks.
10:57
So, let's fix that by
implementing the method.
11:01
Let's go ahead and we're just gonna come
in here and we will say we wanna try
11:04
to open a connection Sql2o.open.
11:10
Let me close this, so
we can get some more space here.
11:15
And remember that the connection
is auto closable.
11:20
So, because in try all
resources it will close.
11:24
And Sql2o retrieval is pretty awesome,
it's really powerful.
11:28
So we'll just return everything
that comes back from this and
11:33
what we'll say is we'll
say con.createQuery and
11:35
we'll say SELECT * FROM courses,
now you remember what it does right,
11:39
it selects all of the columns
that are in courses.
11:45
And then, what we do is we will chain and
we'll say, executeAndFetch.
11:49
And this will return a list of whatever
object that you pass in there.
11:56
So it's gonna return a list of courses.
12:02
And you just pass in the class and
it does the setters and getters.
12:05
So this is where it shines.
12:08
Normally you'd have to build all your own
objects by looping over a resultset and
12:09
population them.
12:12
This is a lot cleaner right, cool.
12:13
So now, does our test pass?
12:16
Let's go ahead and run that again.
12:19
Yes it does, awesome.
12:23
So we went from red to green.
12:25
You know what?
12:27
Let's make sure we never return
a null from this, that was gross.
12:28
So let's check our cardinality
boundary of none.
12:32
So what we'll do is we'll
make a new test in here.
12:35
We'll do, oops, that's not the test.
12:38
In the test we'll make a new test
let's say, new test method and will
12:42
do we'll say noCoursesReturnsEmptyList
which is what we want.
12:49
We don't want it to return null,
that doesn't make any sense,
12:54
people are probably gonna try to iterate
over these courses when they call it, so
12:58
we'll just say, assertEquals(0) when we
do a findAll, and we'll call size on it.
13:03
Of course, that would again,
blow up if find all returned null.
13:08
So let's go ahead and do it.
13:13
Run the test.
13:17
Boom, three passing tests, and
now, because they're all green,
13:18
we know that it will always
return an empty list.
13:21
Awesome.
13:24
All right, so, now we have a working DAO.
13:26
So that means we are ready to
expose that data over our REST API.
13:28
So what do you say we get our Spark server
up and running and handling those request.
13:33
I mean, it's why you're here, right?
13:37
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up