- Course Introduction 1:52
- Introduction to Data Persistence 3:18
- Review of DB Concepts 3 questions
- Review of SQL 7:19
- SQL Review 5 questions
- Using Java to Connect to a Database 13:55
- Using JDBC to Connect to a Database 2 objectives
- Save a Java Object to a Database 6:27
- The Case for Object-Relational Mapping 2:43
Preview
Video Player
00:00
00:00
Using Java to Connect to a Database
13:55Up to this point, you have interacted only directly with a database using SQL. You will now tackle the task of using Java for connecting to a database.
So far, we've looked at how to interact
directly with the database using SQL.
0:00
Few users care to interact
directly with data and
0:05
we probably don't want them to anyway.
0:07
So we'll need a way to write
an application that accesses a database
0:10
using Java.
0:14
Exposing the features that we,
as application designers and
0:15
developers, see fit.
0:18
In Java, the standard medium through which
a database is accessed is called JDBC.
0:21
JDBC stands for
Java Database Connectivity and
0:27
consists of a detailed interface
that database vendors.
0:30
Such as SQLite, which we'll use here,
should implement.
0:33
This API is contained in the java.sql and
javax.sql packages and
0:37
is included in any
Java Standard Edition Environment.
0:41
Like the one we have here.
0:46
Because it provides a lightweight and
portable file based solution for
0:48
databases, we'll be using
SQLite in this particular demo.
0:51
Let's switch to workspaces to connect
to a SQLite database in Java.
0:55
I'm in the workspace provided for
this video and
1:00
looking at the directory structure,
you can see that it has three items.
1:02
The first is a Contact class
which we'll return to in a bit.
1:06
The next is the JdbsMain class
where we`ll be writing our code.
1:10
And the last is a SQLite jar file.
1:14
This jar file is the vendor
specific implementation
1:17
of JDBC that I was referring to earlier.
1:20
It's also called the driver.
1:23
In my JdbcMain class you'll see a main
method with a bunch of comments
1:25
begging for your code.
1:29
Let's not wait any longer.
1:31
Under this first to do comment we'll
load the proper class into the JVM.
1:33
We'll do this by calling the static
forName method in the class Class.
1:37
And the name of the class
is org.sqlite.jdbc.
1:44
This will come from that jar file
that we'll include on the class path.
1:48
Really all this line of code does
is initialize any static behavior.
1:52
If you look at the code of
org.sqlite.jdbc.java, which I have
1:57
linked to in the teacher's notes, you'll
see a static call to the driver managers.
2:01
Register Driver method.
2:06
This will create a new JDBC
object which will now be used for
2:08
any communication with
the SQLite database.
2:11
Next, we'll attempt to get
a connection to the database.
2:13
You notice that this piece
is inside a try catch.
2:17
Right here,
2:21
this is because any interaction with
our database may throw a SQL exception,
2:23
including connecting to the database
as well as running queries on it.
2:27
So we catch the exception and
proceed accordingly.
2:31
You can scroll down to reveal
that catch block right there.
2:35
Let's try to establish
a database connection,
2:39
so I'll replace null with the following.
2:41
DriverManager.getConnection.
2:44
And then I'll specify jdbc:sqlite: and
2:50
I'm gonna name this database contactmgr,
2:54
abbreviated for Contact Manager, .db.
2:58
Here, we use the try with
resources convention,
3:04
by initializing the connection
in parentheses after try.
3:08
In these parentheses we can initialize
something that implements the auto
3:13
closable interface.
3:17
Which means it has
implemented a close method.
3:19
This close method will be called as
soon as the try block is done executing.
3:22
Now, since the connection interface
extends auto closable, we can use it here.
3:27
Pretty convenient.
3:33
The string specified as a parameter
in the getConnection method
3:34
is what's called a connection string.
3:39
Here, you can see that jdbc will be
used to connect to a sqlite database.
3:42
The driver manager will know to use
the sqlite jdbc class we provided
3:48
because of the prefix jdbc:sqlite.
3:53
Now, I'll save this file here and
3:58
we can test our database connection
by compiling and running JdbcMain.
4:01
Let's do that now.
4:06
First, we'll compile with
the standard javac command,
4:07
so javac.JdbcMain .java.
4:13
This should generate the byte code for
the jvm contained in JdbcMain.class.
4:16
If it doesn't pop up in the side bar,
you can right-click the project and
4:21
choose Refresh.
4:25
And then, we see the byte
code file that was generated
4:25
as a result of compiling JdbcMain.class.
4:29
And next,
we'll run the class with the java command.
4:34
java JdbcMain.
4:37
What we see here is
a ClassNotFoundException.
4:43
And in this case it happens on
line 13 if I go back up to my
4:48
source code I see this
right here is line 13..
4:52
Now, we get this exception because
the class we want to load is contained in
4:58
the jar file, that org.sqlite.jdvc
class is contained in the jar file.
5:02
The sqlite jar file right here.
5:09
But we haven't added the jar
file to the class path.
5:12
You'll recall from previous courses that
we can use the CP option on the command
5:15
line while compiling, or while running,
to specify which path or
5:19
paths should be included
on the class path.
5:24
Let's run this class with the sqlite
jar file contained in the class path.
5:27
Let me clear my console first and I'll
use the Java command and here's the CP or
5:33
class path option.
5:38
And I can specify the entire name
of that jar file to include it on
5:39
the class path,
followed by a colon and a .JdbcMain.
5:44
Now, notice the colon and
dot that I used there.
5:49
The colon allows us to
specify multiple paths.
5:52
The two paths being the jar file and
the dot or the current directory.
5:54
After running this command, as long as we
haven't made any coding errors, we should
6:00
be able to refresh the workspace directory
and see that a new file has been created.
6:04
And there it is, contactmgr.db.
6:10
This is the single file database
that is created by SQLite
6:14
since one didn't exist already.
6:17
So we've now verified
the creation of a database.
6:20
Let's create the same contacts
table that we did earlier.
6:23
And add a couple rows.
6:26
We can start by creating
a java.sql.statement object and
6:28
then execute updates as we please.
6:33
So I'll declare a statement object.
6:36
And create a statement
from the connection.
6:40
We'll start by creating
the database table with
6:46
the statement.executeUpdate call and
6:49
inside here,
I am going to first drop the table.
6:56
So that running this code multiple
times doesn't create an issue,
7:02
I will use the drop table
if it already exists.
7:06
So drop table if exists contacts.
7:10
Then, I'll create the contacts table
just like we did in the SQL playground.
7:15
But again, using the executeUpdate call.
7:19
So here we have CREATE TABLE contacts.
7:26
I'll put this all on one line since
Java doesn't support multiline strings.
7:31
So I wanna create an ID that
is the integer primary key.
7:36
Let me fix my casing there.
7:40
SQL's case sensitive but
I like to keep consistency here.
7:42
That's our primary key.
7:50
We have a first name which is a STRING.
7:51
A last name which is a STRING.
7:56
An email which is a STRING.
8:00
And a phone which is a ten digit integer.
8:03
Great.
8:08
Now, let's add a couple rows to
the table just like we did earlier.
8:09
So under this to do comment,
I'll do a statement.executeUpdate.
8:13
And this is where I'll drop
my INSERT INTO command.
8:21
So remember specify
the column names first.
8:29
first name, last name, email, and phone.
8:32
And then, the values, which in this
case will be my first and last name.
8:39
Do my email address here,
feel free to use your own information.
8:51
And then, a phone number, 773-555
8:56
is always a great phone number,
4535, cool.
9:02
I'm gonna copy and paste it just like I
did earlier to save myself some typing.
9:08
I'll throw James Gosling back
in there as a nod to Java.
9:13
Let's capitalize that G there.
9:20
Of course,
his fake email address james@java.com.
9:25
5555557788.
9:32
Good enough.
9:36
Because we won't see the results
upon inserting them,
9:37
let's run a select statement fetching
all rows from the contacts table.
9:40
Since this query will return results,
9:44
we store the return value into
what's called a ResultSet.
9:46
So I'll create that here.
9:51
ResultSet all abbreviated
rs= statement.execute.
9:52
Now, instead of update,
we'll say executeQuery.
9:58
And we'll list our SQL statement here.
10:02
I'll select all columns
from the contacts table.
10:05
Notice that these SQL statements
are identical to what we used in
10:10
the SQL playground.
10:13
It's just that now,
we're using Java to actually execute them.
10:14
So next we want to display the results.
10:18
So I'll introduce here the standard
way of iterating over a SQL ResultSet.
10:20
We use a standard while
loop calling rs.next.
10:25
So I'll do that under
the next TO DO comment.
10:30
while(rs.next) opening and
closing curly braces.
10:35
A SQL ResultSet is kind of like a music
app which not only stores your music but
10:43
also remembers what song
you were last listening to
10:47
as well as exactly which part
of the song you were on.
10:50
In terms of database results, the
ResultSet hangs on to the data returned
10:54
as well as maintaining what's
called a cursor, which remembers
10:58
which result you're currently working
with kinda like a Java iterator.
11:03
A Java iterator is usually used when
defining a custom collection class that
11:07
you want to be able to iterate
over using a for-each loop.
11:12
In order to be able to use
a for-each loop on an object,
11:16
that object must have implemented
the iterable interface.
11:19
Which requires that you code an iterator,
managing its position or
11:23
cursor as repeated calls are made to
the next method to advance the cursor or
11:27
to the hasNext method to see
if any more elements exist.
11:32
Here with the result set calling RS.next
moves the cursor to the next position.
11:37
Which, in our case,
11:43
is the next row in the contacts table
that came back from our select statement.
11:44
Now, inside the loop,
we can grab column values of the row
11:50
the cursor is pointing at by calling
methods like getInt or getString.
11:55
Let's do that now.
12:01
Inside this loop, I wanna grab the int id
and the string first name and last name.
12:02
So I'm going to make variables
to hold those values.
12:08
int id, String firstName,
and String lastName.
12:10
To get the id of the result set's
current row that it's pointing at,
12:17
I use the resultset.getInt and
12:23
specify the column name that contains
that value, id, in this case.
12:27
And then, I can do the same for
firstName, except in this case,
12:31
it's going to be getString and
again, the column name.
12:36
I'll do the same for lastName.
12:42
Finally, I'll display a formatted line
of output that contains the firstName,
12:48
lastName and id in parentheses.
12:53
So I'll use System.out.printf and
12:55
this will be the placeholder for
the firstName, placeholder for
12:59
the lastName and in parentheses
a placeholder for the integer id.
13:02
firstName, lastName and id.
13:07
Now, let's compile and
run this application.
13:13
So I'll compile it using
javac JbdcMain.java.
13:17
Looks like all compiled well, so
13:24
let's execute this as a Java application,
specifying the classpath so
13:27
that we can list the jar file
to be included on the classpath.
13:32
Then, JdbcMain.
13:36
And there are our results, though,
13:39
my display could probably use
a little better formatting.
13:41
Though this barely scratches a surface of
what you can do with JDBC, it gives you
13:45
an idea of one of the most basic ways
to connect to a database using Java.
13:49