1 00:00:00,450 --> 00:00:02,700 All right so let's set up our database. 2 00:00:02,700 --> 00:00:06,650 In an effort to show off other Java tools I thought that I'd give you the chance to 3 00:00:06,650 --> 00:00:09,950 explore a lightweight Java database framework SQL2o. 4 00:00:09,950 --> 00:00:15,550 Now SQL2o is not quite an object relational mapper, or ORM, but what it 5 00:00:15,550 --> 00:00:20,560 does allow you to do is run SQL statements and have them populate your model objects. 6 00:00:20,560 --> 00:00:24,950 In a standard Java database connectivity, or JDBC application, 7 00:00:24,950 --> 00:00:29,040 you the developer write a SQL statement and get back a result set object 8 00:00:29,040 --> 00:00:32,810 which you then use to create new objects and populate them manually. 9 00:00:32,810 --> 00:00:34,920 Now it's fairly time consuming and 10 00:00:34,920 --> 00:00:39,420 you end up writing a lot of boiler plate code over and over again. 11 00:00:39,420 --> 00:00:42,860 Now the other end of the spectrum is using an ORM. 12 00:00:42,860 --> 00:00:45,380 In this scenario you decorate your model objects and 13 00:00:45,380 --> 00:00:48,750 the ORM will generate the appropriate the SQL statement and 14 00:00:48,750 --> 00:00:51,935 then populate your model object behind the scenes. 15 00:00:51,935 --> 00:00:56,110 Now the downside of this is the amount of configuration is quite large and 16 00:00:56,110 --> 00:00:59,780 time consuming and often it has a fairly steep learning curve to 17 00:00:59,780 --> 00:01:01,825 even realize what the ORM is capable of. 18 00:01:01,825 --> 00:01:05,380 SQL2o sits right there in the middle, 19 00:01:05,380 --> 00:01:10,790 it sits on top of JDBC and you write SQL, which is good, I want you to practice. 20 00:01:10,790 --> 00:01:15,150 But instead of having to loop over the result set, you give it a class blueprint 21 00:01:15,150 --> 00:01:18,720 and it creates filled out objects based on the query results. 22 00:01:18,720 --> 00:01:20,510 No configuration needed. 23 00:01:20,510 --> 00:01:23,160 So let's go build out our data access object interface and 24 00:01:23,160 --> 00:01:24,270 practice our SQL jobs. 25 00:01:25,770 --> 00:01:28,300 Okay, so first let's make DAO interfaces. 26 00:01:28,300 --> 00:01:30,230 Let's start with the course DAO. 27 00:01:30,230 --> 00:01:34,500 So right here under Java, I'm gonna go ahead and say New > Java Class, and 28 00:01:34,500 --> 00:01:39,779 I'm gonna flip the kind to Interface, and I am going to type here 29 00:01:39,779 --> 00:01:45,050 com.teamtreehouse.courses.dao.CourseDao. 30 00:01:45,050 --> 00:01:47,900 So that's gonna make the new DAO package for us. 31 00:01:47,900 --> 00:01:50,410 Okay, so let's just worry about adding and retrieving for now. 32 00:01:50,410 --> 00:01:51,490 So let's do add. 33 00:01:51,490 --> 00:01:55,300 So we'll have a method that returns nothing called add. 34 00:01:55,300 --> 00:01:56,580 And it's gonna take a course. 35 00:01:58,550 --> 00:02:02,510 And we should also make sure that any time something attempts to save changes that it 36 00:02:02,510 --> 00:02:04,340 has the ability to throw an exception. 37 00:02:04,340 --> 00:02:08,060 We wanna keep this fairly generic, right, because this is an interface. 38 00:02:08,060 --> 00:02:12,990 So let's call it DaoException, though we're gonna say this throws 39 00:02:12,990 --> 00:02:19,770 a DaoException okay so we're gonna go ahead and say that that is that. 40 00:02:19,770 --> 00:02:24,370 And we're gonna leave DaoException undefined just for a second cuz we also 41 00:02:24,370 --> 00:02:29,900 wanna be able to return from this method a list of courses. 42 00:02:29,900 --> 00:02:31,750 And we'll call that findAll. 43 00:02:34,320 --> 00:02:35,970 Okay so it is java.util List. 44 00:02:37,660 --> 00:02:40,490 Okay so let's go ahead and let's create this DaoException. 45 00:02:40,490 --> 00:02:44,660 If you just come over here, you can say create class DaoException. 46 00:02:44,660 --> 00:02:47,481 Let's throw it in a package called exc for exceptions, 47 00:02:47,481 --> 00:02:49,769 we'll throw all of our exceptions in there. 48 00:02:52,254 --> 00:02:57,616 Okay, great so it automatically extends Exception and what we wanna do is we wanna 49 00:02:57,616 --> 00:03:03,570 capture any sort of exception that might be thrown from an implementation of our DAO. 50 00:03:03,570 --> 00:03:06,480 So we wanna make sure that we keep the original one around. 51 00:03:06,480 --> 00:03:07,630 That's one of the things that I like to do. 52 00:03:07,630 --> 00:03:12,620 So we'll say that when you create one of these DaoExceptions, that you pass 53 00:03:12,620 --> 00:03:17,200 it the original exception, and then you pass wherever message you want. 54 00:03:17,200 --> 00:03:23,020 It's getting a little bit long there, okay. 55 00:03:23,020 --> 00:03:27,510 So we will call super and passing the original message and 56 00:03:27,510 --> 00:03:34,090 that's constructing the original, I guess I call it msg didn't I, delicious msg. 57 00:03:34,090 --> 00:03:39,052 Okay and then we're going to set a new field called originalException equal to 58 00:03:39,052 --> 00:03:41,909 the originalException that was passed in and 59 00:03:41,909 --> 00:03:46,213 it of course doesn't know about it let's go ahead and create that. 60 00:03:49,253 --> 00:03:49,753 Great. 61 00:03:51,470 --> 00:03:55,890 And now let's do a similar thing for our reviews, so we'll add a new review DAO. 62 00:03:55,890 --> 00:03:59,620 So we'll come over here say new java class. 63 00:03:59,620 --> 00:04:00,940 Say ReviewDao. 64 00:04:00,940 --> 00:04:07,210 Okay so very similarly we're gonna wanna add something and 65 00:04:07,210 --> 00:04:10,377 we don't really need to return anything but we're gonna add a new review. 66 00:04:11,720 --> 00:04:13,840 And that is gonna throw our new exception that we created. 67 00:04:15,390 --> 00:04:17,140 Great and we're talking about the review. 68 00:04:17,140 --> 00:04:21,280 Let's see what he's complaining about here. 69 00:04:21,280 --> 00:04:24,430 I said class by accident, I wanna make this say interface. 70 00:04:24,430 --> 00:04:28,450 There we go. 71 00:04:28,450 --> 00:04:31,420 Okay so then we'll do a list of reviews. 72 00:04:33,120 --> 00:04:37,290 And we will call that findAll, just like we did in our other interface. 73 00:04:37,290 --> 00:04:43,252 And then we'll also make one that you can get by a specific course ID. 74 00:04:43,252 --> 00:04:49,820 We wanna search for that by course ID, which was on the model. 75 00:04:49,820 --> 00:04:50,520 Great. 76 00:04:53,486 --> 00:04:56,315 Okay, so now we need to write the implementation for 77 00:04:56,315 --> 00:05:00,292 those DAO interfaces that we just did, so let's get SQL2o installed. 78 00:05:00,292 --> 00:05:03,980 So if we hit the site www.sql2o.org and we come over to the download and 79 00:05:03,980 --> 00:05:07,630 install, we can see here that the Maven repository, 80 00:05:07,630 --> 00:05:11,080 we can see the dependency section here and we can see what our string is. 81 00:05:11,080 --> 00:05:12,580 So I'm gonna go ahead and copy this over. 82 00:05:13,820 --> 00:05:20,667 I'm gonna come over to our build.gradle and 83 00:05:20,667 --> 00:05:26,430 add compile org and that was sql2o and 84 00:05:26,430 --> 00:05:30,944 the version was 1.5.4. 85 00:05:30,944 --> 00:05:34,197 I'm gonna go ahead and click okay to apply the suggestion. 86 00:05:37,162 --> 00:05:39,650 And then we will refresh our Gradle project. 87 00:05:39,650 --> 00:05:40,930 So, if we go over here and we click refresh. 88 00:05:43,705 --> 00:05:46,910 Okay, so here's one of those naming conventions that you should just embrace 89 00:05:46,910 --> 00:05:49,220 and follow whatever is in place wherever you are. 90 00:05:49,220 --> 00:05:51,030 Now personally I like to follow the standard naming 91 00:05:51,030 --> 00:05:52,890 conventions brought forward by the Java core. 92 00:05:52,890 --> 00:05:56,490 So, let's take a look really quick at this list interface. 93 00:05:56,490 --> 00:05:59,400 So the interface is called List. 94 00:05:59,400 --> 00:06:03,390 And its implementations are called ArrayList or LinkedList. 95 00:06:03,390 --> 00:06:08,073 Notice how the interface is the suffix and the implementation is the prefix, 96 00:06:08,073 --> 00:06:11,472 the interface, and the implementation is the prefix. 97 00:06:11,472 --> 00:06:16,315 Check the teacher's notes for more on this and how other patterns exist. 98 00:06:16,315 --> 00:06:21,096 Okay, so, in our DAO package here, 99 00:06:21,096 --> 00:06:27,899 let's create a new class called Sql2oCourseDao. 100 00:06:27,899 --> 00:06:32,613 So it has our interface at the end, and we're implementing it with SQL2o. 101 00:06:32,613 --> 00:06:36,140 And we are going to close this gradle window for some more space. 102 00:06:36,140 --> 00:06:40,310 We're going to say implements CourseDao. 103 00:06:43,230 --> 00:06:46,830 Now of course that got angry at us because we haven't yet implemented the methods. 104 00:06:46,830 --> 00:06:48,310 So if we choose the intention action and 105 00:06:48,310 --> 00:06:51,380 chose implement methods it's going to go ahead and say do you want these two? 106 00:06:51,380 --> 00:06:52,110 And yes we do. 107 00:06:53,450 --> 00:06:56,090 It's awesome, so it built out what we needed there. 108 00:06:56,090 --> 00:06:57,140 Okay, so that's looking great. 109 00:06:57,140 --> 00:07:00,240 So now we need to get access to our SQL2o objects. 110 00:07:00,240 --> 00:07:03,390 So when you create one of these SQL2o objects, you need to configure it. 111 00:07:03,390 --> 00:07:05,100 You need to tell it where the database is and 112 00:07:05,100 --> 00:07:06,440 what you want it to connect to another sorts of settings. 113 00:07:06,440 --> 00:07:10,560 Now we could definitely hard code those configuration settings in a class here, 114 00:07:10,560 --> 00:07:14,130 but we have to do that in every single one of our DAO implementations. 115 00:07:14,130 --> 00:07:15,760 And right now there's only these two. 116 00:07:15,760 --> 00:07:17,210 These can go pretty quick. 117 00:07:17,210 --> 00:07:21,590 Therefore why don't we allow users of our implementation to specify their database. 118 00:07:21,590 --> 00:07:25,330 Not only will that allow us to use the same configured SQL2o object for 119 00:07:25,330 --> 00:07:28,490 all of our database implementations, but it will also allow us to 120 00:07:28,490 --> 00:07:32,570 test these implementations easier because we can isolate things, remember? 121 00:07:32,570 --> 00:07:34,460 The database here is a dependency and 122 00:07:34,460 --> 00:07:38,400 we can inject it into our object at run time when we create it. 123 00:07:38,400 --> 00:07:42,478 Let's take the approach of adding a constructor that requires a SQL2o object. 124 00:07:43,640 --> 00:07:45,070 So let's go ahead and add a constructor here. 125 00:07:45,070 --> 00:07:49,770 Then we'll say public SQL2oCourseDao, and 126 00:07:49,770 --> 00:07:53,610 we'll make it take a SQL2o object. 127 00:07:53,610 --> 00:07:57,259 Wow, that is really annoying [LAUGH] that showing up right in front of you there, 128 00:07:57,259 --> 00:07:58,140 sorry about that. 129 00:07:58,140 --> 00:08:02,814 Sql2o sql2o object. 130 00:08:02,814 --> 00:08:04,090 Cool. 131 00:08:04,090 --> 00:08:07,760 And then we will set that, sql2o. 132 00:08:10,340 --> 00:08:11,620 Cool. So 133 00:08:11,620 --> 00:08:14,710 make sure that's talking about the right thing which it is. 134 00:08:14,710 --> 00:08:19,654 And we will make this be a private field that will let the intention action 135 00:08:19,654 --> 00:08:20,880 generate for us. 136 00:08:20,880 --> 00:08:25,910 Perfect All right. 137 00:08:25,910 --> 00:08:29,530 So now we can assume that we have access to a configured SQL2o object. 138 00:08:29,530 --> 00:08:32,440 So again this is a super thin wrap around JDBC, and 139 00:08:32,440 --> 00:08:35,790 they've done a great job on their API, eliminating most of the boiler plate 140 00:08:35,790 --> 00:08:38,045 you'll encounter doing this kind of raw SQL. 141 00:08:39,710 --> 00:08:42,200 Now we haven't even chosen our database implementation yet, but 142 00:08:42,200 --> 00:08:44,390 we can start using the SQL2o object here. 143 00:08:44,390 --> 00:08:47,260 So let's go ahead and implement the add method in the CourseDao. 144 00:08:49,370 --> 00:08:52,680 So, typically the way you work through things in SQL2o is by 145 00:08:52,680 --> 00:08:53,940 using name parameters. 146 00:08:53,940 --> 00:08:58,190 So, in a table that we've yet to create, it's called courses, 147 00:08:58,190 --> 00:09:00,060 we wanna enter a name and a url. 148 00:09:00,060 --> 00:09:01,360 So we're gonna say something like this. 149 00:09:01,360 --> 00:09:07,690 So it's gonna look like, generate this sql equals INSERT INTO courses. 150 00:09:07,690 --> 00:09:11,400 We're gonna put the name and the url and 151 00:09:11,400 --> 00:09:16,139 the values are going to be :name and 152 00:09:16,139 --> 00:09:19,990 :url, so those are our named parameters there. 153 00:09:22,200 --> 00:09:25,380 So in order to use SQL2o, you've gotta make a new connection. 154 00:09:25,380 --> 00:09:29,400 And it's closable, so we're gonna do the try with resources pattern. 155 00:09:29,400 --> 00:09:33,603 So we're gonna say Connection con = sql2o.open. 156 00:09:33,603 --> 00:09:36,660 Now when this is all over no matter what it will 157 00:09:36,660 --> 00:09:38,140 close that connection which is great. 158 00:09:39,660 --> 00:09:44,660 Okay so SQL2o uses a very nice fluent chainable API. 159 00:09:44,660 --> 00:09:46,170 So let's create the query object. 160 00:09:46,170 --> 00:09:49,210 So what you do is go to con.createQuery. 161 00:09:49,210 --> 00:09:51,810 We're gonna pass in our SQL string. 162 00:09:57,000 --> 00:10:00,630 Try to give us a little bit more space so we don't run all over each other there. 163 00:10:00,630 --> 00:10:05,816 So, see, we got con.createQuery(sql) and then we're gonna do .bind. 164 00:10:05,816 --> 00:10:10,690 We're gonna pass in the course and what this does is it 165 00:10:10,690 --> 00:10:14,940 takes those name parameters from the property and replaces them. 166 00:10:14,940 --> 00:10:18,650 So it will push the result of getName() into the name here and 167 00:10:18,650 --> 00:10:23,820 it will push the result of getUrl() into the url property here. 168 00:10:23,820 --> 00:10:24,936 It's pretty nice right? 169 00:10:26,952 --> 00:10:29,090 And then we're going to execute the query. 170 00:10:29,090 --> 00:10:34,600 And now because we're doing an insert, oops I spelled that wrong, executeUpdate. 171 00:10:34,600 --> 00:10:37,990 Because we're doing an insert we can get back the key that was created because it's 172 00:10:37,990 --> 00:10:39,440 an insert saver right, the primary key. 173 00:10:39,440 --> 00:10:44,190 So we're gonna say .getKey and what that does is that returns an integer or 174 00:10:44,190 --> 00:10:45,290 actually it returns an object. 175 00:10:45,290 --> 00:10:48,740 It can be kind of whatever it is but we know that we made those IDs earlier. 176 00:10:48,740 --> 00:10:51,595 So we're gonna say int id equals, 177 00:10:51,595 --> 00:10:56,490 we're gonna cast that from an integer, there we go. 178 00:10:56,490 --> 00:10:59,100 And finally we should store it. 179 00:10:59,100 --> 00:11:01,440 Let's update the object. 180 00:11:01,440 --> 00:11:05,476 So let's say course.setId(id). 181 00:11:05,476 --> 00:11:08,100 So now when it comes through it's there. 182 00:11:08,100 --> 00:11:13,365 Now just in case things fail, let's have that catch a SQL2o exception. 183 00:11:13,365 --> 00:11:16,855 This is what happens any time some sort of SQL exception happens, 184 00:11:16,855 --> 00:11:18,145 no matter what it is. 185 00:11:18,145 --> 00:11:22,285 It throws a SQL2o exception and then we're gonna have it throw that DAO object 186 00:11:22,285 --> 00:11:26,400 we just created, that DaoException. 187 00:11:26,400 --> 00:11:30,310 And we'll pass in the original exception and we'll say 188 00:11:30,310 --> 00:11:34,375 just a really generic message that we can send later, problem adding course. 189 00:11:34,375 --> 00:11:37,100 It's right there, add course. 190 00:11:37,100 --> 00:11:38,060 Wow, okay. 191 00:11:38,060 --> 00:11:39,620 Now how do we know if all this works? 192 00:11:39,620 --> 00:11:42,090 I mean, right now it's pretty hypothetical at this point, isn't it? 193 00:11:43,240 --> 00:11:48,240 Things are looking good, and they probably are working, but how can we be sure? 194 00:11:48,240 --> 00:11:50,730 Now we’ve made our data access object interfaces 195 00:11:50,730 --> 00:11:53,070 to allow us to do any sort of implementation. 196 00:11:53,070 --> 00:11:55,900 And the first implementation we created was one that will use a database. 197 00:11:57,050 --> 00:11:59,310 Here we used a library that wraps JDBC, 198 00:11:59,310 --> 00:12:02,550 but we haven't even chosen our database implementation. 199 00:12:02,550 --> 00:12:06,420 Why don't we go ahead and choose our DB and then get a unit test written to prove 200 00:12:06,420 --> 00:12:09,850 that our implementation of the add method works, right after this quick break.