Preview

Start a free Courses trial
to watch this video

Using Java to Connect to a Database

Name: Using Java to Connect to a Database
Uploaded: 2016-03-29T21:47:41Z
Duration: 835 s
Description: 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.

13:55 with Chris Ramacciotti

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.

Teacher's Notes
Video Transcript
Downloads

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.

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

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.

Hibernate Basics

Using Java to Connect to a Database

Documentation for SQLite's JDBC Class