So far, we've looked at how to interact directly with the database using SQL.

Few users care to interact directly with data and

we probably don't want them to anyway.

So we'll need a way to write an application that accesses a database

using Java.

Exposing the features that we, as application designers and

developers, see fit.

In Java, the standard medium through which a database is accessed is called JDBC.

JDBC stands for Java Database Connectivity and

consists of a detailed interface that database vendors.

Such as SQLite, which we'll use here, should implement.

This API is contained in the java.sql and javax.sql packages and

is included in any Java Standard Edition Environment.

Like the one we have here.

Because it provides a lightweight and portable file based solution for

databases, we'll be using SQLite in this particular demo.

Let's switch to workspaces to connect to a SQLite database in Java.

I'm in the workspace provided for this video and

looking at the directory structure, you can see that it has three items.

The first is a Contact class which we'll return to in a bit.

The next is the JdbsMain class where we`ll be writing our code.

And the last is a SQLite jar file.

This jar file is the vendor specific implementation

of JDBC that I was referring to earlier.

It's also called the driver.

In my JdbcMain class you'll see a main method with a bunch of comments

begging for your code.

Let's not wait any longer.

Under this first to do comment we'll load the proper class into the JVM.

We'll do this by calling the static forName method in the class Class.

And the name of the class is org.sqlite.jdbc.

This will come from that jar file that we'll include on the class path.

Really all this line of code does is initialize any static behavior.

If you look at the code of org.sqlite.jdbc.java, which I have

linked to in the teacher's notes, you'll see a static call to the driver managers.

Register Driver method.

This will create a new JDBC object which will now be used for

any communication with the SQLite database.

Next, we'll attempt to get a connection to the database.

You notice that this piece is inside a try catch.

Right here,

this is because any interaction with our database may throw a SQL exception,

including connecting to the database as well as running queries on it.

So we catch the exception and proceed accordingly.

You can scroll down to reveal that catch block right there.

Let's try to establish a database connection,

so I'll replace null with the following.

DriverManager.getConnection.

And then I'll specify jdbc:sqlite: and

I'm gonna name this database contactmgr,

abbreviated for Contact Manager, .db.

Here, we use the try with resources convention,

by initializing the connection in parentheses after try.

In these parentheses we can initialize something that implements the auto

closable interface.

Which means it has implemented a close method.

This close method will be called as soon as the try block is done executing.

Now, since the connection interface extends auto closable, we can use it here.

Pretty convenient.

The string specified as a parameter in the getConnection method

is what's called a connection string.

Here, you can see that jdbc will be used to connect to a sqlite database.

The driver manager will know to use the sqlite jdbc class we provided

because of the prefix jdbc:sqlite.

Now, I'll save this file here and

we can test our database connection by compiling and running JdbcMain.

Let's do that now.

First, we'll compile with the standard javac command,

so javac.JdbcMain .java.

This should generate the byte code for the jvm contained in JdbcMain.class.

If it doesn't pop up in the side bar, you can right-click the project and

choose Refresh.

And then, we see the byte code file that was generated

as a result of compiling JdbcMain.class.

And next, we'll run the class with the java command.

java JdbcMain.

What we see here is a ClassNotFoundException.

And in this case it happens on line 13 if I go back up to my

source code I see this right here is line 13..

Now, we get this exception because the class we want to load is contained in

the jar file, that org.sqlite.jdvc class is contained in the jar file.

The sqlite jar file right here.

But we haven't added the jar file to the class path.

You'll recall from previous courses that we can use the CP option on the command

line while compiling, or while running, to specify which path or

paths should be included on the class path.

Let's run this class with the sqlite jar file contained in the class path.

Let me clear my console first and I'll use the Java command and here's the CP or

class path option.

And I can specify the entire name of that jar file to include it on

the class path, followed by a colon and a .JdbcMain.

Now, notice the colon and dot that I used there.

The colon allows us to specify multiple paths.

The two paths being the jar file and the dot or the current directory.

After running this command, as long as we haven't made any coding errors, we should

be able to refresh the workspace directory and see that a new file has been created.

And there it is, contactmgr.db.

This is the single file database that is created by SQLite

since one didn't exist already.

So we've now verified the creation of a database.

Let's create the same contacts table that we did earlier.

And add a couple rows.

We can start by creating a java.sql.statement object and

then execute updates as we please.

So I'll declare a statement object.

And create a statement from the connection.

We'll start by creating the database table with

the statement.executeUpdate call and

inside here, I am going to first drop the table.

So that running this code multiple times doesn't create an issue,

I will use the drop table if it already exists.

So drop table if exists contacts.

Then, I'll create the contacts table just like we did in the SQL playground.

But again, using the executeUpdate call.

So here we have CREATE TABLE contacts.

I'll put this all on one line since Java doesn't support multiline strings.

So I wanna create an ID that is the integer primary key.

Let me fix my casing there.

SQL's case sensitive but I like to keep consistency here.

That's our primary key.

We have a first name which is a STRING.

A last name which is a STRING.

An email which is a STRING.

And a phone which is a ten digit integer.

Great.

Now, let's add a couple rows to the table just like we did earlier.

So under this to do comment, I'll do a statement.executeUpdate.

And this is where I'll drop my INSERT INTO command.

So remember specify the column names first.

first name, last name, email, and phone.

And then, the values, which in this case will be my first and last name.

Do my email address here, feel free to use your own information.

And then, a phone number, 773-555

is always a great phone number, 4535, cool.

I'm gonna copy and paste it just like I did earlier to save myself some typing.

I'll throw James Gosling back in there as a nod to Java.

Let's capitalize that G there.

Of course, his fake email address james@java.com.

5555557788.

Good enough.

Because we won't see the results upon inserting them,

let's run a select statement fetching all rows from the contacts table.

Since this query will return results,

we store the return value into what's called a ResultSet.

So I'll create that here.

ResultSet all abbreviated rs= statement.execute.

Now, instead of update, we'll say executeQuery.

And we'll list our SQL statement here.

I'll select all columns from the contacts table.

Notice that these SQL statements are identical to what we used in

the SQL playground.

It's just that now, we're using Java to actually execute them.

So next we want to display the results.

So I'll introduce here the standard way of iterating over a SQL ResultSet.

We use a standard while loop calling rs.next.

So I'll do that under the next TO DO comment.

while(rs.next) opening and closing curly braces.

A SQL ResultSet is kind of like a music app which not only stores your music but

also remembers what song you were last listening to

as well as exactly which part of the song you were on.

In terms of database results, the ResultSet hangs on to the data returned

as well as maintaining what's called a cursor, which remembers

which result you're currently working with kinda like a Java iterator.

A Java iterator is usually used when defining a custom collection class that

you want to be able to iterate over using a for-each loop.

In order to be able to use a for-each loop on an object,

that object must have implemented the iterable interface.

Which requires that you code an iterator, managing its position or

cursor as repeated calls are made to the next method to advance the cursor or

to the hasNext method to see if any more elements exist.

Here with the result set calling RS.next moves the cursor to the next position.

Which, in our case,

is the next row in the contacts table that came back from our select statement.

Now, inside the loop, we can grab column values of the row

the cursor is pointing at by calling methods like getInt or getString.

Let's do that now.

Inside this loop, I wanna grab the int id and the string first name and last name.

So I'm going to make variables to hold those values.

int id, String firstName, and String lastName.

To get the id of the result set's current row that it's pointing at,

I use the resultset.getInt and

specify the column name that contains that value, id, in this case.

And then, I can do the same for firstName, except in this case,

it's going to be getString and again, the column name.

I'll do the same for lastName.

Finally, I'll display a formatted line of output that contains the firstName,

lastName and id in parentheses.

So I'll use System.out.printf and

this will be the placeholder for the firstName, placeholder for

the lastName and in parentheses a placeholder for the integer id.

firstName, lastName and id.

Now, let's compile and run this application.

So I'll compile it using javac JbdcMain.java.

Looks like all compiled well, so

let's execute this as a Java application, specifying the classpath so

that we can list the jar file to be included on the classpath.

Then, JdbcMain.

And there are our results, though,

my display could probably use a little better formatting.

Though this barely scratches a surface of what you can do with JDBC, it gives you

an idea of one of the most basic ways to connect to a database using Java.