Testing Our Implementations13:38 with Craig Dennis
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) );
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 lets 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 teachers 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 makes 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 all it Test and course 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 lets 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 says, 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 up