1
00:00:00,520 --> 00:00:04,500
When we created our Sql2o DAO
implementation, we made so

2
00:00:04,500 --> 00:00:07,660
we can pass in a Sql2o
instance on creation.

3
00:00:07,660 --> 00:00:10,600
This approach is constructor
dependency injection, and

4
00:00:10,600 --> 00:00:12,100
we are about to get to see it in action.

5
00:00:13,160 --> 00:00:16,090
Since SQL is a common language for
databases, and

6
00:00:16,090 --> 00:00:20,820
JDBC is the abstraction that allows
Java to talk to any database,

7
00:00:20,820 --> 00:00:24,910
what we've written so far should work with
any database that's supported by JDBC.

8
00:00:24,910 --> 00:00:26,610
That's pretty cool, right?

9
00:00:26,610 --> 00:00:30,160
We haven't even chosen a database yet
and hypothetically it should just work.

10
00:00:31,260 --> 00:00:36,250
I'd like for us to use a complete Java
database implementation named H2.

11
00:00:36,250 --> 00:00:40,790
It allows us to have a file base version,
as well as a complete in-memory version.

12
00:00:40,790 --> 00:00:45,194
Now the in-memory version is great for
tests, and since we wrote our data access

13
00:00:45,194 --> 00:00:50,070
object, to take a Sql2o object,
we can just inject an in-memory version.

14
00:00:50,070 --> 00:00:53,058
In unit testing world we call
this is test double, or a fake.

15
00:00:53,058 --> 00:00:57,160
So let's write some tests and
build out the rest of our DAOs.

16
00:00:58,610 --> 00:01:02,790
Okay so I used the awesome site
Maven Repository to search for H2, and

17
00:01:02,790 --> 00:01:03,780
I found it here.

18
00:01:03,780 --> 00:01:07,320
And if we look down here
at the dependencies,

19
00:01:07,320 --> 00:01:09,980
if we click here under Gradle,
we can grab this.

20
00:01:09,980 --> 00:01:13,040
We'll copy that and
we'll put that in our build.gradle file.

21
00:01:13,040 --> 00:01:16,000
So we'll come over to build.gradle.

22
00:01:16,000 --> 00:01:22,548
And we will add compile, and we'll add
the database there, minus those slashes.

23
00:01:25,197 --> 00:01:28,962
So we're gonna click Gradle and
the refresh, and

24
00:01:28,962 --> 00:01:33,070
now we will have our database dependency.

25
00:01:33,070 --> 00:01:33,890
Awesome.

26
00:01:33,890 --> 00:01:37,520
So H2 is now installed.

27
00:01:37,520 --> 00:01:42,050
So in the interest of time, I went
ahead and I created this sql DDL, or

28
00:01:42,050 --> 00:01:45,620
the Data Definition Language that we're
gonna use to create our database tables.

29
00:01:45,620 --> 00:01:48,932
It's in the teacher's notes, go ahead and
copy it now and I'll do the same.

30
00:01:48,932 --> 00:01:54,454
And what we'll do is come over here to
the project and under resources here and

31
00:01:54,454 --> 00:01:59,600
we're gonna right-click here and
make a new directory.

32
00:02:00,780 --> 00:02:01,910
And we're gonna call that directory,

33
00:02:01,910 --> 00:02:04,350
db, we can put any kind of
scripts in here that we want.

34
00:02:04,350 --> 00:02:11,507
So inside the db script, we were going
to add a new file called init.sql.

35
00:02:11,507 --> 00:02:14,980
And we'll click OK.

36
00:02:14,980 --> 00:02:18,126
Now I'm going to paste in the ddl from
the teacher's notes, and then we'll walk

37
00:02:18,126 --> 00:02:21,283
through it a little bit and just make
sure that we understand what's going on.

38
00:02:24,142 --> 00:02:28,868
So this first line here is nice.

39
00:02:28,868 --> 00:02:31,277
It's one of those things where
if this database exists and

40
00:02:31,277 --> 00:02:34,040
the table courses exist,
it's not gonna try to create it.

41
00:02:34,040 --> 00:02:35,200
It won't do it if it already exists.

42
00:02:35,200 --> 00:02:37,560
What that means is if you
wanna make changes to it,

43
00:02:37,560 --> 00:02:39,490
you need to delete the table and
then bring it back.

44
00:02:41,240 --> 00:02:45,775
So this first line here,
that is the unique identifier and

45
00:02:45,775 --> 00:02:51,758
it is marked as a primary key,
it's an integer, and it auto increments.

46
00:02:51,758 --> 00:02:55,380
And then again, of course it has names and
URLs which we have here, but

47
00:02:55,380 --> 00:02:57,470
this is the primary key for the course.

48
00:02:57,470 --> 00:03:02,140
So then we have the child table which
is reviews, and it too has an ID, and

49
00:03:02,140 --> 00:03:04,440
it also is a primary key and
it auto-increments.

50
00:03:04,440 --> 00:03:07,390
So we don't need to put it there,
it will automatically happen.

51
00:03:07,390 --> 00:03:11,090
So if we put in another review and it was
on two, it will now be three, four, and

52
00:03:11,090 --> 00:03:13,810
five automatically for us.

53
00:03:13,810 --> 00:03:14,550
Course ID.

54
00:03:14,550 --> 00:03:18,790
That is gonna refer to the parent course,
so one course, many reviews.

55
00:03:18,790 --> 00:03:21,710
And then we have the rating and
the comment.

56
00:03:23,760 --> 00:03:25,869
This line here is the foreign key,

57
00:03:25,869 --> 00:03:31,390
what it's saying is make sure that
courses and reviews are tied together.

58
00:03:31,390 --> 00:03:33,300
More about this in the teacher's notes,
but what this for

59
00:03:33,300 --> 00:03:37,130
sure guarantees is that we cannot
create a review without a course.

60
00:03:37,130 --> 00:03:38,410
We don't want them just dangling up there.

61
00:03:40,900 --> 00:03:44,280
So now we basically have
our database set up.

62
00:03:44,280 --> 00:03:48,340
So let's test out our
Sql2oCourseDao implementation.

63
00:03:48,340 --> 00:03:51,929
So from the file over here,
we can go ahead and

64
00:03:51,929 --> 00:03:55,912
do Cmd+Shift+t or Shift+Ctrl+t in Windows.

65
00:03:55,912 --> 00:04:00,120
And we'll create a new test and
we will call it that exactly.

66
00:04:00,120 --> 00:04:02,370
And let's go ahead and
let's make sure that we generate a setup.

67
00:04:05,030 --> 00:04:06,100
Cool, all right.

68
00:04:06,100 --> 00:04:09,910
So in our setup let's build
out a connection string, and

69
00:04:09,910 --> 00:04:12,230
this is where the magic of JDBC happens.

70
00:04:12,230 --> 00:04:15,760
So we're gonna say
String connectionString.

71
00:04:15,760 --> 00:04:22,030
We're gonna specify that it's jdbc and
that we wanna use h2.

72
00:04:22,030 --> 00:04:25,010
And then we're gonna specify
some parameters to that,

73
00:04:25,010 --> 00:04:27,610
we wanna use an in-memory version so
we say mem.

74
00:04:27,610 --> 00:04:29,890
And then you can name it,
and we do wanna name it.

75
00:04:29,890 --> 00:04:31,020
We're gonna name this testing.

76
00:04:32,700 --> 00:04:36,758
Okay, there is some additional
set up that h2 lets you do.

77
00:04:36,758 --> 00:04:43,733
And you can do this, so you can say on
INIT you wanna run the script from,

78
00:04:43,733 --> 00:04:52,351
and because we put that in our resources
we can say from the classpath:db/init.sql.

79
00:04:52,351 --> 00:04:57,535
Now what does is that it allows us to
reference that file inside the package,

80
00:04:57,535 --> 00:04:59,080
wherever that may be.

81
00:05:00,310 --> 00:05:04,400
So, when it starts up on
initialization it will run

82
00:05:04,400 --> 00:05:07,250
that script which creates our tables,
which is exactly what we want.

83
00:05:08,910 --> 00:05:14,310
So we'll make a new Sql2o object,
and we will call it sql2o and

84
00:05:14,310 --> 00:05:18,730
we'll pass down our connection string.

85
00:05:18,730 --> 00:05:21,010
And this takes two
parameters of username and

86
00:05:21,010 --> 00:05:22,840
password, we're not gonna
worry about that right now.

87
00:05:22,840 --> 00:05:26,390
That's if you wanted to secure this,
this is a test, it doesn't really matter.

88
00:05:28,050 --> 00:05:33,083
And what we'll do is we'll create our DAO,
now remember,

89
00:05:33,083 --> 00:05:38,317
you just passed in the DAO,
you passed in any Sql2o object and

90
00:05:38,317 --> 00:05:41,050
now this is gonna work, right?

91
00:05:41,050 --> 00:05:46,580
Cuz we are passing in a Sql2o object
that is going to an in-memory database.

92
00:05:46,580 --> 00:05:48,960
So we have successfully made a fake there,
awesome.

93
00:05:50,680 --> 00:05:54,290
Now let's go ahead and make a private
field for this so everybody can use it.

94
00:05:54,290 --> 00:05:56,380
That's the point of this test.

95
00:05:56,380 --> 00:05:59,170
Here's something that might
come as a surprise to you.

96
00:05:59,170 --> 00:06:01,370
When working with an in-memory database,

97
00:06:01,370 --> 00:06:04,570
when all its connections that are coming
to it are closed, it gets erased.

98
00:06:04,570 --> 00:06:06,550
It gets wiped out, so we don't want that.

99
00:06:06,550 --> 00:06:11,050
Since each of our methods open and close a
connection, one thing that we can do, and

100
00:06:11,050 --> 00:06:15,400
it's a bit of a trick, one thing we
can do here is open up the connection.

101
00:06:15,400 --> 00:06:20,746
So let's go ahead and
let's comment first, we're gonna say,

102
00:06:20,746 --> 00:06:26,600
Keep connection open through entire
test so that it isn't wiped out.

103
00:06:27,630 --> 00:06:33,810
And we'll say, conn = Sql2o.open,
and that's how you open them up.

104
00:06:33,810 --> 00:06:35,860
And obviously it doesn't
know what it is yet,

105
00:06:35,860 --> 00:06:39,540
let's make this also a field
that we can access throughout.

106
00:06:39,540 --> 00:06:43,060
Specifically where we want to access
this is after the test is over.

107
00:06:43,060 --> 00:06:44,480
So remember we can do that,

108
00:06:44,480 --> 00:06:47,530
we can generate a teardown method or
an after method.

109
00:06:47,530 --> 00:06:52,820
And we'll just say
conn.close(); beautiful.

110
00:06:52,820 --> 00:06:56,390
So now we have a connection
that's going to open,

111
00:06:56,390 --> 00:07:00,360
and when it opens it's gonna initialize
and run and create those tables.

112
00:07:00,360 --> 00:07:05,480
So therefore we should be able to
run through that code that we wrote.

113
00:07:05,480 --> 00:07:10,610
Remember that code that we wrote
over in the Sql2oCourseDao,

114
00:07:10,610 --> 00:07:13,530
when we added something we set the ID.

115
00:07:13,530 --> 00:07:15,940
So let's go ahead and let's test and
make sure that that happens.

116
00:07:15,940 --> 00:07:21,170
So let's flip back to our test.

117
00:07:21,170 --> 00:07:25,863
We'll make a new test that is

118
00:07:25,863 --> 00:07:30,954
called addingCourseSetsId.

119
00:07:30,954 --> 00:07:34,700
We want to make sure we can properly add,
let's do that.

120
00:07:35,960 --> 00:07:37,840
So first let's arrange things.

121
00:07:37,840 --> 00:07:40,620
We'll make a new course,
just a course model.

122
00:07:40,620 --> 00:07:43,170
Plain old object, = new Course.

123
00:07:43,170 --> 00:07:46,400
And it has a name which is Test and

124
00:07:46,400 --> 00:07:51,310
it has a URL which is http://test.com.

125
00:07:51,310 --> 00:07:55,650
Boring test data there, but
that's not really important right.

126
00:07:55,650 --> 00:07:59,080
So let's go ahead and we'll store that ID.

127
00:07:59,080 --> 00:08:06,010
So we'll say,
int originalCourseId = course.getId.

128
00:08:06,010 --> 00:08:10,140
Now remember, we're not setting that, so
by default, an integer's value is zero.

129
00:08:10,140 --> 00:08:13,840
So most likely that's gonna be zero, but
instead of leaving a magical number,

130
00:08:13,840 --> 00:08:17,340
let's specifically state
what it is in the test.

131
00:08:17,340 --> 00:08:20,730
So, I think we have everything
all arranged so it's time to add.

132
00:08:20,730 --> 00:08:23,420
Let's test what we're actually testing.

133
00:08:23,420 --> 00:08:25,853
So we're gonna add the course.

134
00:08:27,725 --> 00:08:29,340
And that's actually all we're doing.

135
00:08:29,340 --> 00:08:33,700
And then we're going to assert
that it's not equal, so

136
00:08:33,700 --> 00:08:37,280
there is a method called assertNotEquals.

137
00:08:37,280 --> 00:08:43,509
We want to assert that the original course
Id is not equal to what the current Id is.

138
00:08:46,120 --> 00:08:48,600
So we're verifying basically
that things got changed.

139
00:08:48,600 --> 00:08:53,718
So we are all ready, let's go ahead and
over here under java choose Run 'All',

140
00:08:53,718 --> 00:08:57,390
so test Java,
we're gonna choose Run 'All Tests'.

141
00:08:57,390 --> 00:09:00,410
If any of that look new make sure that
you've taken the Unit Testing course.

142
00:09:04,630 --> 00:09:08,120
Awesome, we passed the test and
we know that it ran.

143
00:09:08,120 --> 00:09:11,095
See here it says addingCourseSetsId.

144
00:09:12,690 --> 00:09:13,860
Hey, you know what?

145
00:09:13,860 --> 00:09:16,450
We're in a test right now and
we haven't written the code yet

146
00:09:16,450 --> 00:09:19,790
that performs the implementation
of getting all courses.

147
00:09:19,790 --> 00:09:22,810
That find all method right,
we just left it blank.

148
00:09:22,810 --> 00:09:25,370
Should we do a little taste of test
driven development, what do you say?

149
00:09:25,370 --> 00:09:27,260
I think it sounds good.

150
00:09:27,260 --> 00:09:30,010
Okay, so
we know that if we add a course it should

151
00:09:30,010 --> 00:09:33,390
show up in the find all call
that we haven't yet implemented.

152
00:09:33,390 --> 00:09:36,540
So let's do it, let's generate
a test that sees if that happens.

153
00:09:36,540 --> 00:09:39,340
Now if that happens it would be magical,
but it shouldn't right,

154
00:09:39,340 --> 00:09:42,193
cuz we haven't written that code yet,
so let's see what happens.

155
00:09:42,193 --> 00:09:49,170
So we'll say,
addedCoursesAreReturnedFromFindAll,

156
00:09:49,170 --> 00:09:52,460
that's pretty specific.

157
00:09:52,460 --> 00:09:56,100
And we are going to
again make a new course.

158
00:09:56,100 --> 00:09:58,660
This is feeling a little
duplicate-y isn't it.

159
00:09:58,660 --> 00:10:04,901
Course and we'll call it Test, and
of course, we'll test.com.

160
00:10:05,930 --> 00:10:07,341
What the price on test.com is, so

161
00:10:07,341 --> 00:10:09,390
many people must hit that
all the time in their tests.

162
00:10:09,390 --> 00:10:15,150
So we're going to do dao again,
add(course);,

163
00:10:15,150 --> 00:10:20,520
that's how we're gonna act, and
let's go ahead and see what happens.

164
00:10:20,520 --> 00:10:27,290
We wanna make sure that it's 1 and
that our findAll size is that.

165
00:10:30,140 --> 00:10:33,890
So, we are going to do a findAll,
which should return a list of courses, and

166
00:10:33,890 --> 00:10:34,680
then we'll do a size.

167
00:10:35,780 --> 00:10:37,180
So, let's see what happens.

168
00:10:37,180 --> 00:10:41,930
Let's go ahead and
run all of our tests again.

169
00:10:41,930 --> 00:10:42,600
Doh!

170
00:10:42,600 --> 00:10:45,170
The dreaded NullPointerException class.

171
00:10:45,170 --> 00:10:49,660
Let's go ahead and
let's see why it's throwing a null.

172
00:10:49,660 --> 00:10:51,210
So it's saying that, let's go ahead and

173
00:10:51,210 --> 00:10:54,800
we'll look at this implementation,
find out why.

174
00:10:54,800 --> 00:10:57,990
Well, because it's returning a null,
right?

175
00:10:57,990 --> 00:11:01,000
Calling size on null made it blow chunks.

176
00:11:01,000 --> 00:11:04,690
So, let's fix that by
implementing the method.

177
00:11:04,690 --> 00:11:10,830
Let's go ahead and we're just gonna come
in here and we will say we wanna try

178
00:11:10,830 --> 00:11:15,550
to open a connection Sql2o.open.

179
00:11:15,550 --> 00:11:20,805
Let me close this, so
we can get some more space here.

180
00:11:20,805 --> 00:11:24,370
And remember that the connection
is auto closable.

181
00:11:24,370 --> 00:11:26,789
So, because in try all
resources it will close.

182
00:11:28,350 --> 00:11:33,020
And Sql2o retrieval is pretty awesome,
it's really powerful.

183
00:11:33,020 --> 00:11:35,630
So we'll just return everything
that comes back from this and

184
00:11:35,630 --> 00:11:39,490
what we'll say is we'll
say con.createQuery and

185
00:11:39,490 --> 00:11:45,382
we'll say SELECT * FROM courses,
now you remember what it does right,

186
00:11:45,382 --> 00:11:49,380
it selects all of the columns
that are in courses.

187
00:11:49,380 --> 00:11:56,510
And then, what we do is we will chain and
we'll say, executeAndFetch.

188
00:11:56,510 --> 00:12:02,020
And this will return a list of whatever
object that you pass in there.

189
00:12:02,020 --> 00:12:04,050
So it's gonna return a list of courses.

190
00:12:05,130 --> 00:12:08,410
And you just pass in the class and
it does the setters and getters.

191
00:12:08,410 --> 00:12:09,800
So this is where it shines.

192
00:12:09,800 --> 00:12:12,880
Normally you'd have to build all your own
objects by looping over a resultset and

193
00:12:12,880 --> 00:12:13,500
population them.

194
00:12:13,500 --> 00:12:16,790
This is a lot cleaner right, cool.

195
00:12:16,790 --> 00:12:19,030
So now, does our test pass?

196
00:12:19,030 --> 00:12:20,087
Let's go ahead and run that again.

197
00:12:23,284 --> 00:12:25,340
Yes it does, awesome.

198
00:12:25,340 --> 00:12:27,140
So we went from red to green.

199
00:12:27,140 --> 00:12:28,430
You know what?

200
00:12:28,430 --> 00:12:32,220
Let's make sure we never return
a null from this, that was gross.

201
00:12:32,220 --> 00:12:35,710
So let's check our cardinality
boundary of none.

202
00:12:35,710 --> 00:12:38,810
So what we'll do is we'll
make a new test in here.

203
00:12:38,810 --> 00:12:42,300
We'll do, oops, that's not the test.

204
00:12:42,300 --> 00:12:49,160
In the test we'll make a new test
let's say, new test method and will

205
00:12:49,160 --> 00:12:54,630
do we'll say noCoursesReturnsEmptyList
which is what we want.

206
00:12:54,630 --> 00:12:58,387
We don't want it to return null,
that doesn't make any sense,

207
00:12:58,387 --> 00:13:03,118
people are probably gonna try to iterate
over these courses when they call it, so

208
00:13:03,118 --> 00:13:08,290
we'll just say, assertEquals(0) when we
do a findAll, and we'll call size on it.

209
00:13:08,290 --> 00:13:13,350
Of course, that would again,
blow up if find all returned null.

210
00:13:13,350 --> 00:13:14,613
So let's go ahead and do it.

211
00:13:17,337 --> 00:13:18,910
Run the test.

212
00:13:18,910 --> 00:13:21,940
Boom, three passing tests, and
now, because they're all green,

213
00:13:21,940 --> 00:13:24,910
we know that it will always
return an empty list.

214
00:13:24,910 --> 00:13:26,190
Awesome.

215
00:13:26,190 --> 00:13:28,590
All right, so, now we have a working DAO.

216
00:13:28,590 --> 00:13:33,230
So that means we are ready to
expose that data over our REST API.

217
00:13:33,230 --> 00:13:37,010
So what do you say we get our Spark server
up and running and handling those request.

218
00:13:37,010 --> 00:13:38,240
I mean, it's why you're here, right?