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
We will build out our Data Access Object interfaces and install a JDBC wrapper known as Sql2o.
Dependencies
compile 'org.sql2o:sql2o:1.5.4'
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
All right so let's set up our database.
0:00
In an effort to show off other Java tools
I thought that I'd give you the chance to
0:02
explore a lightweight Java
database framework SQL2o.
0:06
Now SQL2o is not quite an object
relational mapper, or ORM, but what it
0:09
does allow you to do is run SQL statements
and have them populate your model objects.
0:15
In a standard Java database connectivity,
or JDBC application,
0:20
you the developer write a SQL statement
and get back a result set object
0:24
which you then use to create new
objects and populate them manually.
0:29
Now it's fairly time consuming and
0:32
you end up writing a lot of boiler
plate code over and over again.
0:34
Now the other end of
the spectrum is using an ORM.
0:39
In this scenario you decorate
your model objects and
0:42
the ORM will generate
the appropriate the SQL statement and
0:45
then populate your model
object behind the scenes.
0:48
Now the downside of this is the amount
of configuration is quite large and
0:51
time consuming and often it has
a fairly steep learning curve to
0:56
even realize what the ORM is capable of.
0:59
SQL2o sits right there in the middle,
1:01
it sits on top of JDBC and you write SQL,
which is good, I want you to practice.
1:05
But instead of having to loop over the
result set, you give it a class blueprint
1:10
and it creates filled out objects
based on the query results.
1:15
No configuration needed.
1:18
So let's go build out our data
access object interface and
1:20
practice our SQL jobs.
1:23
Okay, so first let's make DAO interfaces.
1:25
Let's start with the course DAO.
1:28
So right here under Java, I'm gonna go
ahead and say New > Java Class, and
1:30
I'm gonna flip the kind to Interface,
and I am going to type here
1:34
com.teamtreehouse.courses.dao.CourseDao.
1:39
So that's gonna make
the new DAO package for us.
1:45
Okay, so let's just worry about adding and
retrieving for now.
1:47
So let's do add.
1:50
So we'll have a method that
returns nothing called add.
1:51
And it's gonna take a course.
1:55
And we should also make sure that any time
something attempts to save changes that it
1:58
has the ability to throw an exception.
2:02
We wanna keep this fairly generic,
right, because this is an interface.
2:04
So let's call it DaoException,
though we're gonna say this throws
2:08
a DaoException okay so we're gonna
go ahead and say that that is that.
2:12
And we're gonna leave DaoException
undefined just for a second cuz we also
2:19
wanna be able to return from
this method a list of courses.
2:24
And we'll call that findAll.
2:29
Okay so it is java.util List.
2:34
Okay so let's go ahead and
let's create this DaoException.
2:37
If you just come over here,
you can say create class DaoException.
2:40
Let's throw it in a package called exc for
exceptions,
2:44
we'll throw all of our
exceptions in there.
2:47
Okay, great so it automatically extends
Exception and what we wanna do is we wanna
2:52
capture any sort of exception that might
be thrown from an implementation of our DAO.
2:57
So we wanna make sure that we
keep the original one around.
3:03
That's one of the things
that I like to do.
3:06
So we'll say that when you create one
of these DaoExceptions, that you pass
3:07
it the original exception, and
then you pass wherever message you want.
3:12
It's getting a little bit long there,
okay.
3:17
So we will call super and
passing the original message and
3:23
that's constructing the original, I guess
I call it msg didn't I, delicious msg.
3:27
Okay and then we're going to set a new
field called originalException equal to
3:34
the originalException
that was passed in and
3:39
it of course doesn't know about it
let's go ahead and create that.
3:41
Great.
3:49
And now let's do a similar thing for our
reviews, so we'll add a new review DAO.
3:51
So we'll come over here
say new java class.
3:55
Say ReviewDao.
3:59
Okay so very similarly we're
gonna wanna add something and
4:00
we don't really need to return anything
but we're gonna add a new review.
4:07
And that is gonna throw our
new exception that we created.
4:11
Great and we're talking about the review.
4:15
Let's see what he's
complaining about here.
4:17
I said class by accident,
I wanna make this say interface.
4:21
There we go.
4:24
Okay so then we'll do a list of reviews.
4:28
And we will call that findAll,
just like we did in our other interface.
4:33
And then we'll also make one that
you can get by a specific course ID.
4:37
We wanna search for that by course ID,
which was on the model.
4:43
Great.
4:49
Okay, so now we need to
write the implementation for
4:53
those DAO interfaces that we just did,
so let's get SQL2o installed.
4:56
So if we hit the site www.sql2o.org and
we come over to the download and
5:00
install, we can see here
that the Maven repository,
5:03
we can see the dependency section here and
we can see what our string is.
5:07
So I'm gonna go ahead and copy this over.
5:11
I'm gonna come over to
our build.gradle and
5:13
add compile org and that was sql2o and
5:20
the version was 1.5.4.
5:26
I'm gonna go ahead and
click okay to apply the suggestion.
5:30
And then we will refresh
our Gradle project.
5:37
So, if we go over here and
we click refresh.
5:39
Okay, so here's one of those naming
conventions that you should just embrace
5:43
and follow whatever is in
place wherever you are.
5:46
Now personally I like to
follow the standard naming
5:49
conventions brought
forward by the Java core.
5:51
So, let's take a look really
quick at this list interface.
5:52
So the interface is called List.
5:56
And its implementations are called
ArrayList or LinkedList.
5:59
Notice how the interface is the suffix and
the implementation is the prefix,
6:03
the interface, and
the implementation is the prefix.
6:08
Check the teacher's notes for
more on this and how other patterns exist.
6:11
Okay, so, in our DAO package here,
6:16
let's create a new class
called Sql2oCourseDao.
6:21
So it has our interface at the end,
and we're implementing it with SQL2o.
6:27
And we are going to close this
gradle window for some more space.
6:32
We're going to say implements CourseDao.
6:36
Now of course that got angry at us because
we haven't yet implemented the methods.
6:43
So if we choose the intention action and
6:46
chose implement methods it's going to
go ahead and say do you want these two?
6:48
And yes we do.
6:51
It's awesome, so
it built out what we needed there.
6:53
Okay, so that's looking great.
6:56
So now we need to get access
to our SQL2o objects.
6:57
So when you create one of these SQL2o
objects, you need to configure it.
7:00
You need to tell it where
the database is and
7:03
what you want it to connect
to another sorts of settings.
7:05
Now we could definitely hard code those
configuration settings in a class here,
7:06
but we have to do that in every single
one of our DAO implementations.
7:10
And right now there's only these two.
7:14
These can go pretty quick.
7:15
Therefore why don't we allow users of our
implementation to specify their database.
7:17
Not only will that allow us to use
the same configured SQL2o object for
7:21
all of our database implementations,
but it will also allow us to
7:25
test these implementations easier
because we can isolate things, remember?
7:28
The database here is a dependency and
7:32
we can inject it into our object
at run time when we create it.
7:34
Let's take the approach of adding a
constructor that requires a SQL2o object.
7:38
So let's go ahead and
add a constructor here.
7:43
Then we'll say public SQL2oCourseDao, and
7:45
we'll make it take a SQL2o object.
7:49
Wow, that is really annoying [LAUGH] that
showing up right in front of you there,
7:53
sorry about that.
7:57
Sql2o sql2o object.
7:58
Cool.
8:02
And then we will set that, sql2o.
8:04
Cool.
So
8:10
make sure that's talking about
the right thing which it is.
8:11
And we will make this be a private field
that will let the intention action
8:14
generate for us.
8:19
Perfect All right.
8:20
So now we can assume that we have
access to a configured SQL2o object.
8:25
So again this is a super
thin wrap around JDBC, and
8:29
they've done a great job on their API,
eliminating most of the boiler plate
8:32
you'll encounter doing
this kind of raw SQL.
8:35
Now we haven't even chosen our
database implementation yet, but
8:39
we can start using the SQL2o object here.
8:42
So let's go ahead and
implement the add method in the CourseDao.
8:44
So, typically the way you work
through things in SQL2o is by
8:49
using name parameters.
8:52
So, in a table that we've yet
to create, it's called courses,
8:53
we wanna enter a name and a url.
8:58
So we're gonna say something like this.
9:00
So it's gonna look like, generate
this sql equals INSERT INTO courses.
9:01
We're gonna put the name and the url and
9:07
the values are going to be :name and
9:11
:url, so
those are our named parameters there.
9:16
So in order to use SQL2o,
you've gotta make a new connection.
9:22
And it's closable, so we're gonna
do the try with resources pattern.
9:25
So we're gonna say
Connection con = sql2o.open.
9:29
Now when this is all over
no matter what it will
9:33
close that connection which is great.
9:36
Okay so SQL2o uses a very
nice fluent chainable API.
9:39
So let's create the query object.
9:44
So what you do is go to con.createQuery.
9:46
We're gonna pass in our SQL string.
9:49
Try to give us a little bit more space so
we don't run all over each other there.
9:57
So, see, we got con.createQuery(sql) and
then we're gonna do .bind.
10:00
We're gonna pass in the course and
what this does is it
10:05
takes those name parameters from
the property and replaces them.
10:10
So it will push the result of
getName() into the name here and
10:14
it will push the result of getUrl()
into the url property here.
10:18
It's pretty nice right?
10:23
And then we're going to execute the query.
10:26
And now because we're doing an insert,
oops I spelled that wrong, executeUpdate.
10:29
Because we're doing an insert we can get
back the key that was created because it's
10:34
an insert saver right, the primary key.
10:37
So we're gonna say .getKey and what
that does is that returns an integer or
10:39
actually it returns an object.
10:44
It can be kind of whatever it is but
we know that we made those IDs earlier.
10:45
So we're gonna say int id equals,
10:48
we're gonna cast that from an integer,
there we go.
10:51
And finally we should store it.
10:56
Let's update the object.
10:59
So let's say course.setId(id).
11:01
So now when it comes through it's there.
11:05
Now just in case things fail,
let's have that catch a SQL2o exception.
11:08
This is what happens any time some
sort of SQL exception happens,
11:13
no matter what it is.
11:16
It throws a SQL2o exception and then
we're gonna have it throw that DAO object
11:18
we just created, that DaoException.
11:22
And we'll pass in the original
exception and we'll say
11:26
just a really generic message that we
can send later, problem adding course.
11:30
It's right there, add course.
11:34
Wow, okay.
11:37
Now how do we know if all this works?
11:38
I mean, right now it's pretty
hypothetical at this point, isn't it?
11:39
Things are looking good, and they probably
are working, but how can we be sure?
11:43
Now we’ve made our data
access object interfaces
11:48
to allow us to do any
sort of implementation.
11:50
And the first implementation we created
was one that will use a database.
11:53
Here we used a library that wraps JDBC,
11:57
but we haven't even chosen
our database implementation.
11:59
Why don't we go ahead and choose our DB
and then get a unit test written to prove
12:02
that our implementation of the add method
works, right after this quick break.
12:06
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