Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Up 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.
Documentation for SQLite's JDBC Class
Looking at the documentation for org.sqlite.JDBC, you'll see that it declares a static member as follows:
static {
try {
DriverManager.registerDriver(new JDBC());
}
catch (SQLException e) {
e.printStackTrace();
}
}
Later, when JDBC is used for establishing a connection, the acceptsURL(String) method is used for determining which of the registered drivers should be used with the supplied connection string. Following the call to isValidURL(String), you can see that as long as the connection string begins with "jdbc:sqlite:" (see PREFIX), this SQLite implementation of java.sql.Driver will be used.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up