"Build a Weather App (2015)" was retired on June 6, 2018. You are now viewing the recommended replacement.
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