JDBC Basics

Accessing Databases with JDBC

Concepts

Connecting to databases: the seven basic steps
Updating data through transactions
Database Access Frameworks

Using JDBC

Load the JDBC driver.
Define the connection URL.
Establish the connection.
Create a Statement object.
Execute a query or update.
Process the results.
Close the connection.

Overview

1. Load the JDBC Driver

Once we have the drivers on the CLASSPATH we now need to load the drivers into RAM. This is done with the Class.forName method (API). We do this with the following code.

    try {
        Class.forName("FULL CLASS NAME OF DRIVER");
    } catch(ClassNotFoundException classNotFound) {
        classNotFound.printStackTrace();
    }

This is the code for Oracle.

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch(ClassNotFoundException classNotFound) {
        classNotFound.printStackTrace();
    }

And, for MySQL.

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
    } catch(ClassNotFoundException classNotFound) {
        classNotFound.printStackTrace();
    }

This code will load the class file into RAM and make it available to your code. Where will your program get the name of the driver that goes in the forName method?

2. Define the connection URL

Next, we have to have a URL that points to a database. Here are the ones we will use for project 4:

For Oracle.

    jdbc:oracle:thin:@198.150.15.156:1521:stu1

For MySQL.

    jdbc:mysql:///student

3. Establish the connection

To actually connect to the database we have to use this code:\

    Connection connection = null;

    ...

    try {
        connection = DriverManager.getConnection("Connection URL Here!");
        System.out.println("Connection made");
    } catch(SQLException sqlException) {
        sqlException.printStackTrace();
    }

4. Create a Statement object

Once we have a connection we then create a Statement object. This needs to be in the same try/catch block as above.\

    Statement statement = connection.createStatement();

5. Execute a query or update

Once we have a Statement object we can now run an actual SQL statement. First, we have to create a String that is a valid SQL statement. Here are some examples:

    String sql = "select first_name, last_name from employee";
    String sql = "update employee set last_name = 'Jones' where id = '4'";

Then we run the appropriate method in the Statement. For a query we use the executeQuery() method.

    ResultSet resultSet = statement.executeQuery(sql);

For an update or an insert we use the executeUpdate() method.

    int rowsAffected = statement.executeUpdate(sql);

6. Process the results

The ResultSet object that is returned from a query has the ability to access the returned rows.
NOTE: The ResultSet allows us to access the returned data, it does not actually hold all the data!
To access the return data we have to use a while loop. Each iteration of the while loop will let us get one row that is returned from the database. Here are two examples.

while (resultSet.next()) {
    System.out.println(resultSet.getString(1));
    System.out.println(resultSet.getString(2));
}

while (resultSet.next()) {
    System.out.println(resultSet.getString("first_name"));
    System.out.println(resultSet.getString("last_name"));
}

7. Close Everything!

Everything we open we have to close!

This is just as easy as we expect it to be.

resultSet.close();
statement.close();
connection.close();

All of the close statements need to be in a finally block.

Using Database Transactions

NOTE: You will NOT be doing this in project 4. This is just for your general information.

Database transactions in relational databases have the following pattern:

Open a transaction
Submit a series of SQL statements to the server
If all the SQL statements ran without error then issue a commit command to the database.
If one of the SQL statements failed then issue a rollback to the database.

In JDBC these steps are coded like this:

Create a try/catch block.
Get a connection to the database
Connections in JDBC have as the default behavior that every SQL statement is automatically committed. We start transactions by turning this off with this code.
```
connection.setAutoCommit(false);
```
Then run a series of SQL statements.
Next, issue a commit to the database with this code.
```
connection.commit();
```
If there was a problem then the code will throw a SQLException. In the catch clause you need to issue the rollback command.
```
connection.rollback();
```

The last step is to close everything in a finally block.

finally { 
    if (statement != null) {
        statement.close();
    }
    if (statement != null) {
        connection.close();
    }
}