It's feeling pretty good, isn't it? 0:00 We got a pretty sweet API right now, and 0:02 have all the tools that we need to build our the rest of our spec. 0:04 We learned a ton, and 0:07 let's take a moment to make sure it's all sticking in our brains. 0:08 Let's use building the reviews portion to practice the whole process again, 0:11 one more time, with feeling. 0:15 Okay, so first things first here. 0:17 We need to make a Sql2o implementation of our review DAO. 0:19 Now, why don't you take a swing at it first? 0:24 Now, don't forget the naming structure. 0:27 Lean on the other course DAO implementation for help. 0:29 It's not cheating. 0:33 Don't worry about it. 0:34 Look at how we did it and then do it over there, okay? 0:35 Pause me and come back after you have completed implementing the methods. 0:39 When you're all done implementing those methods, come back here, 0:42 I'll show you how I did it. 0:45 Ready? 0:46 Pause me. 0:46 Okay, ready. 0:49 How'd you do? 0:50 Here's what I did. 0:51 I created a new class in the dao here, 0:52 I created a new class called Sql2o, 0:56 the implementation first, and then the interface, ReviewDao. 1:00 And then, I clicked OK. 1:05 And, I told it implements the ReviewDao. 1:09 Of course, that gets angry, and then I said, why don't we implement the methods? 1:15 So, we'll implement all of those. 1:20 All right. 1:22 Now, first things first. 1:23 We need to add a constructor that's gonna take the Sql2o object so we can use it. 1:24 So, we're gonna say public Sql2oReviewDao, 1:30 and it's gonna take Sql2o object and store it 1:35 As a field, so that we can use that where we need to. 1:44 So, it will be a field here. 1:48 So, all of these methods can use that Sql20 that was passed in. 1:50 Now, remember we were doing that for the tests, basically. 1:53 And so, we can change that whenever. 1:56 So, now we are ready to implement those methods. 1:58 And it might feel like cheating. 2:00 But, it's definitely a good idea to copy the style from the other code. 2:02 So, let's go over and 2:07 take a peek at how the other Sql2o limitation happened when we did courses. 2:08 Okay? 2:14 So, let's do that. 2:15 Okay, so, when we did add, we did an insert and 2:18 we knew what the parameters were. 2:21 And then, we bound the course. 2:24 And you know what, this is almost exactly the same, isn't it? 2:25 Why don't I just copy the guts of this here, the guts of this method. 2:29 I'm gonna copy it. 2:34 I'm gonna pop over to where we were and I'm just gonna paste it in here. 2:36 Now, this might make you shudder. 2:40 This happens all the time. 2:43 The style of what's happening in here is 2:45 important to keep as other people are coming through. 2:48 So, I'm gonna come over here and 2:50 I'm gonna change this because it's not courses, right? 2:51 It's reviews. 2:54 So, INSERT INTO reviews. 2:55 And, we might need to remember what these fields were. 2:57 Now, we know that the columns are name and url for courses but 2:59 what was it for our review table? 3:02 Let's look. 3:04 So, we have a course id, a rating, and a comment. 3:04 Course id, rating, and comment. 3:07 So, I'm gonna pump those over here. 3:09 So, we have course id, rating, and comment, okay? 3:10 And then, we are going to, of course, name our parameters. 3:16 We want a course ID, rating, and comment. 3:20 And, we're gonna much those to be the fields that are on our review 3:25 that was passed in, right? 3:29 So, the review is passed in. 3:30 We're gonna bind to the review and then the review is gonna say set ID, okay? 3:31 And, look here, problem adding course. 3:38 So, we need to make that say review, so, 3:41 that is something that you need to be careful. 3:43 You need to look at every line if you do one of these copy and 3:45 paste maneuvers, okay? 3:48 Cool. And now let's add the find by course ID. 3:49 Now, that's so similar to the course DAO, find by ID. 3:52 So, again, let's jump over here and let's pilfer that. 3:56 Okay, so we're gonna grab all of this SQL, this find by ID. 3:58 So, we're gonna come in here, gonna copy that. 4:03 And then, we're gonna pop back into ours, and we're gonna pop in here and paste. 4:07 It says that it needs a course because it's gonna use it, 4:14 so, select star from reviews WHERE course_id is course_id. 4:21 And here, we'll say, course_id, so 4:28 we're going to add the parameter for a courseId, and 4:31 then, we're gonna look at what is left over here. 4:36 And, this is saying it's returning a course, that's right, 4:42 we want to return a review. 4:46 Now look, we've copied and pasted and it's still complaining and 4:48 it's expecting a list and that's because there's many reviews. 4:51 So, we want, executeAndFetch not executeAndFetchFirst. 4:54 That's one of those situations where it have might have been easier if we just 4:58 wrote that right? 5:02 So, we're gonna select everything from reviews where the course ID is what was 5:04 passed in the parameters course ID, and we're gonna execute and 5:07 fetch all of the things and turn them into reviews. 5:10 Sound good? So, finally the reviews. 5:14 Let's not copy and paste anymore, I think I got the style back. 5:16 So, I'm gonna say try (Connection con = sql20.open()) 5:19 And, we're just gonna return con.createQuery SELECT * FROM reviews. 5:28 So, that's every review in the system. 5:35 And, we're gonna say .executeAndFetch(Review.class). 5:39 We have double returns and it doesn't need that. 5:47 Awesome, so, we've got our methods implemented. 5:50 And now, we need to write some tests, don't we? 5:53 So, why don't you do that. 5:56 You're gonna need to add a new test. 5:57 So, in the before method, why don't you create a course. 6:00 And just write the first one that test if adding if working. 6:02 Much like the adding course one. 6:06 Go ahead and have at it. 6:08 And, if you get frustrated, just stop and pause me and we can watch it. 6:09 But why don't you give it a shot, see what you can do there. 6:12 So, in the before, make a new course and add a review to it. 6:14 Then make sure, just like we did in the other course, 6:18 that the id has been set on the review. 6:21 Sound good? 6:24 Okay, pause me. 6:25 Okay, ready? 6:27 Here's how I did it. 6:28 So, I am in my review dao. 6:29 If I press command shift t, I'm gonna create a new test, and that's the name. 6:32 I'm gonna give it a setup for before, and let's go in there. 6:38 So, the before is going to be very similar to the other test that we did, right? 6:43 And again, we're gonna look at how that was done. 6:48 We are going to copy the connectionString and the Sql2o object, 6:53 cuz that's exactly the same that we want. 6:58 Pop back over to our review here and paste those in. 7:00 Okay, so now, we wanna make sure that we keep an open connection, right? 7:04 That's true of these tests. 7:10 We'll say conn = sql2o.open, And, we'll go ahead, 7:11 and we'll create a field called conn that we can use to close later. 7:19 Actually, let's just go ahead and do that now while we're remembering it. 7:23 So, we'll say tearDown, After and we'll say conn.close. 7:25 There we go, so now there's a connection. 7:30 And, because every time all of the connections are close to the database, 7:32 it wipes the database. 7:36 We don't want that, we want the database to stick around for the entire test. 7:37 So, let's make a new courseDao and it will be a new Sql2oCourseDao. 7:40 And, it's gonna take that object that we passed in here, our test and 7:47 memory database. 7:51 And we are going to let CourseDao have a field, so 7:52 that we can use it should we need it in any of the other tests. 7:55 And, we're also gonna make a reviewDao here, that'll be a new Sql2oReviewDao. 7:59 We'll pass in on our sql2o object there. 8:06 Now, we're making reviews and what good is a review without having a course right? 8:09 So, let's go ahead and make a course that's accessible to all of our tests. 8:14 So, we'll say course, we want to share across all of them. 8:18 So, we're going to say new course. 8:22 Title is Test and it's http, poor guys at test.com, getting hit again. 8:24 All right, 8:31 so, yes, this is a course and we will make this course be available. 8:35 Okay, and then finally, we need to make sure that we add that right. 8:41 So, we're gonna say courseDao.add course. 8:46 That's gonna go ahead and commit. 8:49 Do that insert statement on the course, so 8:52 there will be at least one course in every one of these databases that we're running, 8:54 and we have access to it by the field course and the test. 8:57 So, now, we are finally ready to make sure that we do that add test. 9:01 So, what we are going to do is we are going to do just like we did for 9:06 the course test. 9:08 We're gonna make sure that the ideas changed. 9:09 So, let's do that. 9:12 So, we make a new test. 9:15 And, we'll say, addingReviewSetsNewId. 9:18 So, we'll say Review review = new Review. 9:25 And, we're gonna use the course. 9:31 We're gonna get its ID that was created for it, 9:32 and, the test course was definitely a five and we'll give a test comment. 9:34 Okay, we're arranging, we're gonna pull off the original ID, it's gonna be one but 9:41 let's just go ahead and incase that changes and if you wanna set that later. 9:45 And now, we will add a review that's how we gonna act on this. 9:50 And then, we're gonna assert that it's changed, right? 9:56 That the original id is not the newer id, 9:59 it's like I spelled original wrong. 10:08 Original id, there we go. 10:14 All right, I think we go it. 10:17 Let's go ahead, let's run our test. 10:19 Boom, got it, it's working. 10:25 All right, so let's try out our other method. 10:27 Our find by course ID. 10:31 So, we're gonna need to do here is we're gonna need to add a couple reviews 10:33 first, right? 10:36 So we'll say, 10:37 multipleReviewsAreFoundWhenTheyExistForAC- ourse. 10:40 Test names can be as long as you need them to be, don't be afraid of that. 10:50 Okay, so we're gonna use our reviewDao and we're gonna add a new review, 10:54 and we're say, course.getId, and somebody thought this was great. 11:00 Let's say test comment 1. 11:05 Then, we'll go ahead and I will duplicate this line. 11:09 And, we're just going to say that they didn't like it. 11:14 I gave it a 1 and there's 2. 11:19 Okay, so that's all the arrangement that we need to do and 11:20 then we'll pull off this list of reviews called reviews. 11:25 And we'll say, let me scroll this up for you, 11:30 say reviewDao we're going to act by find by course id, 11:35 of course we're using our course.getId and 11:42 we'll say, assertEquals 2, so 11:47 that's making sure we got our one to many. 11:51 I chose the wrong list in here. 12:01 Up hold. 12:04 We do not want the javac.util list. 12:07 That is wrong. 12:10 So, this list here, we want java.util. 12:13 There we go. 12:18 What is going on here? 12:25 Could not map course_ID to any property. 12:27 Hm. Let's take a look and 12:31 see what is going on over there. 12:33 In our review, here we have findByCourseId. 12:36 Right, so what's gonna happen is, it's gonna return something and 12:40 it doesn't know what course_id is, right? 12:43 How could it properly know that? 12:46 So, there's a thing in sql2o called, column mapping. 12:48 So, we're gonna say .add column mapping and what we're gonna say is that, 12:52 when you encounter course_id, 12:56 I'd like you to set course_id, so that solves that problem. 13:00 I wanted you to see that error. 13:05 Very common when using sql2o. 13:06 And, let's run that again. 13:08 And blammo, we got it. 13:12 Let's make sure our foreign key constraint is still working. 13:14 Let's make sure that, when we add a review, 13:17 that we throw an error if the add fails, right? 13:19 If the course doesn't exist. 13:22 That's what we were supposed to be taking a look at, right? 13:23 So, let's see. 13:25 Okay, so let's make a new test. 13:28 We'll generate a new test, and we'll say 13:30 addingAReviewToANonExistingCourseFails just like we would think it would, 13:34 and we are going to actually use an exception. 13:41 We're gonna expect that a DaoException is thrown, okay, so, here we go. 13:46 So, we'll just make a new review. 13:53 Review equals review new Review, we'll just throw some random ID in there. 13:55 5, Test comment. 14:02 And, when we try to add that, it should catch our problem. 14:08 And, that's good, right? 14:11 Cuz we don't want those child reviews, if the course is under this course 42 doesn't 14:12 exist, what would a review of that be? 14:16 I forgot to put an equal sign here. 14:19 Equals, here we go, so, lets run that and see. 14:21 Awesome. 14:26 Okay, so, lets take a quick break. 14:28 You deserve it. 14:31 Why don't you stand up and walk around. 14:31 Do some jumping jacks, and then come back here, and then we'll switch back over, and 14:33 we'll finish up the API for the reviews. 14:36 Awesome. 14:38