Accessing Data12:10 with Craig Dennis
We will build out our Data Access Object interfaces and install a JDBC wrapper known as Sql2o.
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 SQL2ois 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 play 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 generates 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 RGAO. 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 SquloCourseDao, 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 get name into the name here and 10:14 it will push the result of get url 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 up