1
00:00:00,460 --> 00:00:05,050
So far, we've looked at how to interact
directly with the database using SQL.

2
00:00:05,050 --> 00:00:07,850
Few users care to interact
directly with data and

3
00:00:07,850 --> 00:00:10,050
we probably don't want them to anyway.

4
00:00:10,050 --> 00:00:14,150
So we'll need a way to write
an application that accesses a database

5
00:00:14,150 --> 00:00:15,390
using Java.

6
00:00:15,390 --> 00:00:18,630
Exposing the features that we,
as application designers and

7
00:00:18,630 --> 00:00:20,010
developers, see fit.

8
00:00:21,440 --> 00:00:27,040
In Java, the standard medium through which
a database is accessed is called JDBC.

9
00:00:27,040 --> 00:00:30,330
JDBC stands for
Java Database Connectivity and

10
00:00:30,330 --> 00:00:33,625
consists of a detailed interface
that database vendors.

11
00:00:33,625 --> 00:00:37,199
Such as SQLite, which we'll use here,
should implement.

12
00:00:37,199 --> 00:00:41,875
This API is contained in the java.sql and
javax.sql packages and

13
00:00:41,875 --> 00:00:46,172
is included in any
Java Standard Edition Environment.

14
00:00:46,172 --> 00:00:47,092
Like the one we have here.

15
00:00:48,452 --> 00:00:51,482
Because it provides a lightweight and
portable file based solution for

16
00:00:51,482 --> 00:00:55,242
databases, we'll be using
SQLite in this particular demo.

17
00:00:55,242 --> 00:00:58,572
Let's switch to workspaces to connect
to a SQLite database in Java.

18
00:01:00,672 --> 00:01:02,822
I'm in the workspace provided for
this video and

19
00:01:02,822 --> 00:01:06,740
looking at the directory structure,
you can see that it has three items.

20
00:01:06,740 --> 00:01:10,410
The first is a Contact class
which we'll return to in a bit.

21
00:01:10,410 --> 00:01:14,414
The next is the JdbsMain class
where we`ll be writing our code.

22
00:01:14,414 --> 00:01:17,190
And the last is a SQLite jar file.

23
00:01:17,190 --> 00:01:20,340
This jar file is the vendor
specific implementation

24
00:01:20,340 --> 00:01:23,130
of JDBC that I was referring to earlier.

25
00:01:23,130 --> 00:01:25,040
It's also called the driver.

26
00:01:25,040 --> 00:01:29,760
In my JdbcMain class you'll see a main
method with a bunch of comments

27
00:01:29,760 --> 00:01:31,420
begging for your code.

28
00:01:31,420 --> 00:01:33,490
Let's not wait any longer.

29
00:01:33,490 --> 00:01:37,455
Under this first to do comment we'll
load the proper class into the JVM.

30
00:01:37,455 --> 00:01:44,140
We'll do this by calling the static
forName method in the class Class.

31
00:01:44,140 --> 00:01:48,430
And the name of the class
is org.sqlite.jdbc.

32
00:01:48,430 --> 00:01:52,680
This will come from that jar file
that we'll include on the class path.

33
00:01:52,680 --> 00:01:57,170
Really all this line of code does
is initialize any static behavior.

34
00:01:57,170 --> 00:02:01,950
If you look at the code of
org.sqlite.jdbc.java, which I have

35
00:02:01,950 --> 00:02:06,140
linked to in the teacher's notes, you'll
see a static call to the driver managers.

36
00:02:06,140 --> 00:02:08,330
Register Driver method.

37
00:02:08,330 --> 00:02:11,690
This will create a new JDBC
object which will now be used for

38
00:02:11,690 --> 00:02:13,952
any communication with
the SQLite database.

39
00:02:13,952 --> 00:02:17,690
Next, we'll attempt to get
a connection to the database.

40
00:02:17,690 --> 00:02:21,590
You notice that this piece
is inside a try catch.

41
00:02:21,590 --> 00:02:23,150
Right here,

42
00:02:23,150 --> 00:02:27,980
this is because any interaction with
our database may throw a SQL exception,

43
00:02:27,980 --> 00:02:31,800
including connecting to the database
as well as running queries on it.

44
00:02:31,800 --> 00:02:35,515
So we catch the exception and
proceed accordingly.

45
00:02:35,515 --> 00:02:38,380
You can scroll down to reveal
that catch block right there.

46
00:02:39,450 --> 00:02:41,560
Let's try to establish
a database connection,

47
00:02:41,560 --> 00:02:44,270
so I'll replace null with the following.

48
00:02:44,270 --> 00:02:46,640
DriverManager.getConnection.

49
00:02:50,060 --> 00:02:54,182
And then I'll specify jdbc:sqlite: and

50
00:02:54,182 --> 00:02:58,418
I'm gonna name this database contactmgr,

51
00:02:58,418 --> 00:03:02,550
abbreviated for Contact Manager, .db.

52
00:03:04,570 --> 00:03:08,170
Here, we use the try with
resources convention,

53
00:03:08,170 --> 00:03:13,110
by initializing the connection
in parentheses after try.

54
00:03:13,110 --> 00:03:17,280
In these parentheses we can initialize
something that implements the auto

55
00:03:17,280 --> 00:03:19,080
closable interface.

56
00:03:19,080 --> 00:03:22,740
Which means it has
implemented a close method.

57
00:03:22,740 --> 00:03:27,250
This close method will be called as
soon as the try block is done executing.

58
00:03:27,250 --> 00:03:33,060
Now, since the connection interface
extends auto closable, we can use it here.

59
00:03:33,060 --> 00:03:33,760
Pretty convenient.

60
00:03:34,780 --> 00:03:39,620
The string specified as a parameter
in the getConnection method

61
00:03:39,620 --> 00:03:42,720
is what's called a connection string.

62
00:03:42,720 --> 00:03:48,750
Here, you can see that jdbc will be
used to connect to a sqlite database.

63
00:03:48,750 --> 00:03:53,080
The driver manager will know to use
the sqlite jdbc class we provided

64
00:03:53,080 --> 00:03:58,990
because of the prefix jdbc:sqlite.

65
00:03:58,990 --> 00:04:01,520
Now, I'll save this file here and

66
00:04:01,520 --> 00:04:06,300
we can test our database connection
by compiling and running JdbcMain.

67
00:04:06,300 --> 00:04:07,670
Let's do that now.

68
00:04:07,670 --> 00:04:13,231
First, we'll compile with
the standard javac command,

69
00:04:13,231 --> 00:04:16,100
so javac.JdbcMain .java.

70
00:04:16,100 --> 00:04:21,544
This should generate the byte code for
the jvm contained in JdbcMain.class.

71
00:04:21,544 --> 00:04:25,024
If it doesn't pop up in the side bar,
you can right-click the project and

72
00:04:25,024 --> 00:04:25,980
choose Refresh.

73
00:04:25,980 --> 00:04:29,690
And then, we see the byte
code file that was generated

74
00:04:29,690 --> 00:04:34,200
as a result of compiling JdbcMain.class.

75
00:04:34,200 --> 00:04:37,850
And next,
we'll run the class with the java command.

76
00:04:37,850 --> 00:04:43,294
java JdbcMain.

77
00:04:43,294 --> 00:04:47,340
What we see here is
a ClassNotFoundException.

78
00:04:48,620 --> 00:04:52,720
And in this case it happens on
line 13 if I go back up to my

79
00:04:52,720 --> 00:04:57,264
source code I see this
right here is line 13..

80
00:04:58,480 --> 00:05:02,880
Now, we get this exception because
the class we want to load is contained in

81
00:05:02,880 --> 00:05:09,140
the jar file, that org.sqlite.jdvc
class is contained in the jar file.

82
00:05:09,140 --> 00:05:11,010
The sqlite jar file right here.

83
00:05:12,010 --> 00:05:15,540
But we haven't added the jar
file to the class path.

84
00:05:15,540 --> 00:05:19,660
You'll recall from previous courses that
we can use the CP option on the command

85
00:05:19,660 --> 00:05:24,360
line while compiling, or while running,
to specify which path or

86
00:05:24,360 --> 00:05:27,590
paths should be included
on the class path.

87
00:05:27,590 --> 00:05:33,920
Let's run this class with the sqlite
jar file contained in the class path.

88
00:05:33,920 --> 00:05:38,250
Let me clear my console first and I'll
use the Java command and here's the CP or

89
00:05:38,250 --> 00:05:39,880
class path option.

90
00:05:39,880 --> 00:05:44,812
And I can specify the entire name
of that jar file to include it on

91
00:05:44,812 --> 00:05:49,110
the class path,
followed by a colon and a .JdbcMain.

92
00:05:49,110 --> 00:05:52,290
Now, notice the colon and
dot that I used there.

93
00:05:52,290 --> 00:05:54,990
The colon allows us to
specify multiple paths.

94
00:05:54,990 --> 00:06:00,470
The two paths being the jar file and
the dot or the current directory.

95
00:06:00,470 --> 00:06:04,340
After running this command, as long as we
haven't made any coding errors, we should

96
00:06:04,340 --> 00:06:09,510
be able to refresh the workspace directory
and see that a new file has been created.

97
00:06:10,840 --> 00:06:14,890
And there it is, contactmgr.db.

98
00:06:14,890 --> 00:06:17,780
This is the single file database
that is created by SQLite

99
00:06:17,780 --> 00:06:20,250
since one didn't exist already.

100
00:06:20,250 --> 00:06:23,580
So we've now verified
the creation of a database.

101
00:06:23,580 --> 00:06:26,770
Let's create the same contacts
table that we did earlier.

102
00:06:26,770 --> 00:06:28,650
And add a couple rows.

103
00:06:28,650 --> 00:06:33,070
We can start by creating
a java.sql.statement object and

104
00:06:33,070 --> 00:06:36,240
then execute updates as we please.

105
00:06:36,240 --> 00:06:38,040
So I'll declare a statement object.

106
00:06:40,600 --> 00:06:43,430
And create a statement
from the connection.

107
00:06:46,670 --> 00:06:49,648
We'll start by creating
the database table with

108
00:06:49,648 --> 00:06:56,950
the statement.executeUpdate call and

109
00:06:56,950 --> 00:07:02,510
inside here,
I am going to first drop the table.

110
00:07:02,510 --> 00:07:06,350
So that running this code multiple
times doesn't create an issue,

111
00:07:06,350 --> 00:07:10,360
I will use the drop table
if it already exists.

112
00:07:10,360 --> 00:07:15,290
So drop table if exists contacts.

113
00:07:15,290 --> 00:07:19,520
Then, I'll create the contacts table
just like we did in the SQL playground.

114
00:07:19,520 --> 00:07:22,950
But again, using the executeUpdate call.

115
00:07:26,070 --> 00:07:29,720
So here we have CREATE TABLE contacts.

116
00:07:31,050 --> 00:07:35,379
I'll put this all on one line since
Java doesn't support multiline strings.

117
00:07:36,700 --> 00:07:40,480
So I wanna create an ID that
is the integer primary key.

118
00:07:40,480 --> 00:07:42,735
Let me fix my casing there.

119
00:07:42,735 --> 00:07:46,287
SQL's case sensitive but
I like to keep consistency here.

120
00:07:50,042 --> 00:07:51,710
That's our primary key.

121
00:07:51,710 --> 00:07:54,680
We have a first name which is a STRING.

122
00:07:56,500 --> 00:07:58,380
A last name which is a STRING.

123
00:08:00,790 --> 00:08:02,210
An email which is a STRING.

124
00:08:03,770 --> 00:08:07,380
And a phone which is a ten digit integer.

125
00:08:08,430 --> 00:08:09,190
Great.

126
00:08:09,190 --> 00:08:12,170
Now, let's add a couple rows to
the table just like we did earlier.

127
00:08:13,470 --> 00:08:18,001
So under this to do comment,
I'll do a statement.executeUpdate.

128
00:08:21,713 --> 00:08:27,320
And this is where I'll drop
my INSERT INTO command.

129
00:08:29,700 --> 00:08:32,600
So remember specify
the column names first.

130
00:08:32,600 --> 00:08:39,131
first name, last name, email, and phone.

131
00:08:39,131 --> 00:08:45,061
And then, the values, which in this
case will be my first and last name.

132
00:08:51,717 --> 00:08:56,646
Do my email address here,
feel free to use your own information.

133
00:08:56,646 --> 00:09:02,287
And then, a phone number, 773-555

134
00:09:02,287 --> 00:09:08,848
is always a great phone number,
4535, cool.

135
00:09:08,848 --> 00:09:13,200
I'm gonna copy and paste it just like I
did earlier to save myself some typing.

136
00:09:13,200 --> 00:09:17,445
I'll throw James Gosling back
in there as a nod to Java.

137
00:09:20,075 --> 00:09:22,420
Let's capitalize that G there.

138
00:09:25,278 --> 00:09:28,905
Of course,
his fake email address james@java.com.

139
00:09:32,189 --> 00:09:36,051
5555557788.

140
00:09:36,051 --> 00:09:37,780
Good enough.

141
00:09:37,780 --> 00:09:40,400
Because we won't see the results
upon inserting them,

142
00:09:40,400 --> 00:09:44,930
let's run a select statement fetching
all rows from the contacts table.

143
00:09:44,930 --> 00:09:46,690
Since this query will return results,

144
00:09:46,690 --> 00:09:51,050
we store the return value into
what's called a ResultSet.

145
00:09:51,050 --> 00:09:52,820
So I'll create that here.

146
00:09:52,820 --> 00:09:58,403
ResultSet all abbreviated
rs= statement.execute.

147
00:09:58,403 --> 00:10:01,080
Now, instead of update,
we'll say executeQuery.

148
00:10:02,670 --> 00:10:05,270
And we'll list our SQL statement here.

149
00:10:05,270 --> 00:10:09,060
I'll select all columns
from the contacts table.

150
00:10:10,390 --> 00:10:13,140
Notice that these SQL statements
are identical to what we used in

151
00:10:13,140 --> 00:10:14,570
the SQL playground.

152
00:10:14,570 --> 00:10:18,540
It's just that now,
we're using Java to actually execute them.

153
00:10:18,540 --> 00:10:20,480
So next we want to display the results.

154
00:10:20,480 --> 00:10:25,560
So I'll introduce here the standard
way of iterating over a SQL ResultSet.

155
00:10:25,560 --> 00:10:30,370
We use a standard while
loop calling rs.next.

156
00:10:30,370 --> 00:10:33,160
So I'll do that under
the next TO DO comment.

157
00:10:35,155 --> 00:10:41,320
while(rs.next) opening and
closing curly braces.

158
00:10:43,578 --> 00:10:47,880
A SQL ResultSet is kind of like a music
app which not only stores your music but

159
00:10:47,880 --> 00:10:50,500
also remembers what song
you were last listening to

160
00:10:50,500 --> 00:10:54,260
as well as exactly which part
of the song you were on.

161
00:10:54,260 --> 00:10:58,840
In terms of database results, the
ResultSet hangs on to the data returned

162
00:10:58,840 --> 00:11:03,140
as well as maintaining what's
called a cursor, which remembers

163
00:11:03,140 --> 00:11:06,640
which result you're currently working
with kinda like a Java iterator.

164
00:11:07,930 --> 00:11:12,130
A Java iterator is usually used when
defining a custom collection class that

165
00:11:12,130 --> 00:11:15,570
you want to be able to iterate
over using a for-each loop.

166
00:11:16,650 --> 00:11:19,700
In order to be able to use
a for-each loop on an object,

167
00:11:19,700 --> 00:11:23,620
that object must have implemented
the iterable interface.

168
00:11:23,620 --> 00:11:27,390
Which requires that you code an iterator,
managing its position or

169
00:11:27,390 --> 00:11:32,470
cursor as repeated calls are made to
the next method to advance the cursor or

170
00:11:32,470 --> 00:11:35,950
to the hasNext method to see
if any more elements exist.

171
00:11:37,220 --> 00:11:43,810
Here with the result set calling RS.next
moves the cursor to the next position.

172
00:11:43,810 --> 00:11:44,800
Which, in our case,

173
00:11:44,800 --> 00:11:50,150
is the next row in the contacts table
that came back from our select statement.

174
00:11:50,150 --> 00:11:55,500
Now, inside the loop,
we can grab column values of the row

175
00:11:55,500 --> 00:12:01,200
the cursor is pointing at by calling
methods like getInt or getString.

176
00:12:01,200 --> 00:12:02,840
Let's do that now.

177
00:12:02,840 --> 00:12:08,060
Inside this loop, I wanna grab the int id
and the string first name and last name.

178
00:12:08,060 --> 00:12:10,670
So I'm going to make variables
to hold those values.

179
00:12:10,670 --> 00:12:17,880
int id, String firstName,
and String lastName.

180
00:12:17,880 --> 00:12:23,720
To get the id of the result set's
current row that it's pointing at,

181
00:12:23,720 --> 00:12:26,020
I use the resultset.getInt and

182
00:12:27,170 --> 00:12:31,770
specify the column name that contains
that value, id, in this case.

183
00:12:31,770 --> 00:12:36,250
And then, I can do the same for
firstName, except in this case,

184
00:12:36,250 --> 00:12:40,550
it's going to be getString and
again, the column name.

185
00:12:42,300 --> 00:12:43,452
I'll do the same for lastName.

186
00:12:48,913 --> 00:12:53,585
Finally, I'll display a formatted line
of output that contains the firstName,

187
00:12:53,585 --> 00:12:55,490
lastName and id in parentheses.

188
00:12:55,490 --> 00:12:59,350
So I'll use System.out.printf and

189
00:12:59,350 --> 00:13:02,060
this will be the placeholder for
the firstName, placeholder for

190
00:13:02,060 --> 00:13:06,655
the lastName and in parentheses
a placeholder for the integer id.

191
00:13:07,920 --> 00:13:11,560
firstName, lastName and id.

192
00:13:13,490 --> 00:13:17,240
Now, let's compile and
run this application.

193
00:13:17,240 --> 00:13:24,340
So I'll compile it using
javac JbdcMain.java.

194
00:13:24,340 --> 00:13:27,200
Looks like all compiled well, so

195
00:13:27,200 --> 00:13:32,000
let's execute this as a Java application,
specifying the classpath so

196
00:13:32,000 --> 00:13:36,580
that we can list the jar file
to be included on the classpath.

197
00:13:36,580 --> 00:13:39,410
Then, JdbcMain.

198
00:13:39,410 --> 00:13:41,590
And there are our results, though,

199
00:13:41,590 --> 00:13:44,660
my display could probably use
a little better formatting.

200
00:13:45,860 --> 00:13:49,970
Though this barely scratches a surface of
what you can do with JDBC, it gives you

201
00:13:49,970 --> 00:13:55,040
an idea of one of the most basic ways
to connect to a database using Java.