The Java DataBase Connectivity (JDBC) API hides from a Java application the details of an external database.

This presentation will introduce JDBC by developing a web application that accesses the Cars database.

Architecture Driver Manager Statements Result Sets Connection Leaks Query Strings Prepared Statements Data Tables Revisited Cars Web App
JDBC accomplishes Java/database separation with a driver manager.

Simply by changing driver managers, a Java application can change the kind of database (Apache, MySQL, Oracle...) with which it communicates:

The Statement interface has methods that take a string representing an SQL command and executes it against the database:
Example
Suppose we want to retrieve a list of vehicle identification numbers (vins) from the Cars database under Java program control.

If the instance field carsSource is of type DataSource and has been suitably injected to access the Cars database (see Data Sources), then we can do: 

    String query = "SELECT vin FROM car";
    Connection carsConnection = carsSource.getConnection();
    Statement statement = carsConnection.createStatement();
    ResultSet resultSet = statement.executeQuery(query);
      // use the result here
    carsConnection.close();
A ResultSet object is returned by the executeQuery method.

Result sets have iterators attached to them that allow you to create, for example, a java.util.List.

Suppose that columnName names a column in the result set of VARCHAR type: 

    List<String> vinList = new ArrayList<String>();
    while (resultSet.next()) {
      vinList.add(resultSet.getString(columnName));
    }
The list can then be used to populate a JSF input selection component: 
      <h:selectOneListbox value="#{ownerQuery.vin}">
        <f:selectItems value="#{database.vinList}"/>
      </h:selectOneListbox>
Note that the result set might have column values that are not strings.

In that case you would use the appropriate ResultSet method: getInt, getDouble, getBlob, etc.

Opening and closing database connections using data sources involves connection pools.

Each JSF cycle that uses a database must open and close a connection; you can't hold onto a connection across multiple JSF cycles.

If there are SQLExceptions during database processing and they aren't properly caught, impatient users can repeatedly make doomed requests that cause database connection leaks.

You can make sure that a connection is properly closed by wrapping the call to close in a finally block.

The call to close can itself throw a (checked) SQLException which must either be caught or declared.

Recommendation: make each method that deals with the database have the following form: 

  someDBMethod() throws SQLException {
    String query = ... ;
    Connection carsConnection = carsSource.getConnection();
    try {
      // make the query and process result
    } finally {
      carsConnection.close();
    }
  }
A simple SQL query such as our first example: 
   SELECT vin FROM car
is easy enough to include in Java code, but most queries are not so simple. Consider: 
   SELECT
    car.vin as "VIN",
    manufacturer."name" AS "Manufacturer",
    model."name" AS "Model"
   FROM car, manufacturer, model
   WHERE
     car.vin = 0123456789abcdefg
     AND car.model = model.id
     AND model.manufacturer = manufacturer.id
It is better to separate query strings from Java code by storing them in files, as we show in this section.
A Utility Class Using It Exception Handling
The Cars web application has a folder under source packages called sql.
Instead of coding query strings directly in Java, use the static method getSQL in the SQL class.

SQL.getSQL takes a string argument that should name a file that contains an SQL command:

Suppose the query 
   SELECT vin FROM car
is contained in vin-query.sql. Then our statement example from before should have: 
      String query = SQL.getSQL("vin-query");
         ...
      ResultSet resultSet = statement.executeQuery(query);
You are encouraged to use the the SQL.java class in your own web apps.
Since SQL.getSQL is dealing with files, it could possibly throw an IOException, which can be handled like this: 
  someDBMethod() throws SQLException, IOException {
    String query = SQL.getSQL(...) ;
    Connection carsConnection = carsSource.getConnection();
    try {
      // make the query and process result
    } finally {
      carsConnection.close();
    }
  }
The complete contents of an SQL query often cannot be known before web app run time.

This section shows how to handle this with prepared statements.

Problem Solution How To Use Example
In the Cars application the user chooses a vin from a selection list.

Suppose the chosen vin is 0123456789abcdefg. Then to get the vehicle's owner we would need a file, say owner-query.sql, that contains: 

   SELECT person."name" FROM person, ownership, car
   WHERE
     ownership.owner = person.id
     AND ownership.car = car.id
     AND car.vin = 0123456789abcdefg
But the vin cannot be known at the time this file is created.
A PreparedStatement object represents a "parameterized" SQL statement.

That is, the statement can have occurrences of "?" that are intended to be replaced by values at run time: 

   SELECT person."name" FROM person, ownership, car
   WHERE
     ownership.owner = person.id
     AND ownership.car = car.id
     AND car.vin = ?
Suppose owner-query.sql contains the parameterized statement just shown, and vin is a string containing a specific vin: 
    String query = SQL.getSQL("owner-query");
    Connection carsConnection = carsSource.getConnection();
    try {
      PreparedStatement statement =
          carsConnection.prepareStatement(query);
      statement.setString(1, vin);
      ResultSet resultSet = statement.executeQuery();
        // use the result here
    } finally {
      carsConnection.close();
    }
Recall that the JSF h:dataTable tag can take as a value the result returned by an SQL query of a database.

This section re-presents the relevant part of Chapter 6.

Database Tables