JDBC Summary

Using JDBC in JSF requires injecting a JDBC resource from the application server into a . The resource is injected into a class by declaring a DataSource instance variable with a @Resource annotation:

   @Resource(name="resource_name")
   private DataSource dataSource;

Here, resource_name is the resource name. It is usually begins with "jdbc/". For example "jdbc/mydb".

JDBC access begins by sending a getConnection() message to the injected resource variable. To ensure that connections are returned to the connection pool when not in use, JDBC messages sent to the connection should be enclosed in a try…finally statement with the following form.

      Connection connection = dataSource.getConnection();
      try {
         // create statement object
         // set up statement
         // execute statement
         // process execution results
      } finally {
         conn.close();
      }

In addition to describing the JDBC methods needed for creating, setting up, and executing statements, and processing execution results, this web page has two examples:

      Connection connection = dataSource.getConnection();
      try {
         // create statement object
         // set up statement
         // execute statement
         // process execution results
      } finally {
         conn.close();
      }

Create Statement

A statement object is created in one of two ways depending on whether or not the intended SQL statement has parameters.

      Connection connection = dataSource.getConnection();
      try {
         // create statement object
         // set up statement
         // execute statement
         // process execution results
      } finally {
         conn.close();
      }

Set Up Statement

A statement object is set up in one of two ways depending on whether or not the intended SQL statement has parameters.

      Connection connection = dataSource.getConnection();
      try {
         // create statement object
         // set up statement
         // execute statement
         // process execution results
      } finally {
         conn.close();
      }

Execute Statement

A statement object is executed in one of three ways depending on the type of statement.

      Connection connection = dataSource.getConnection();
      try {
         // create statement object
         // set up statement object
         // execute statement object
         // process execution results
      } finally {
         conn.close();
      }

Process Results

The return value from executing a statement depends on the type of statement.

java.sql.ResultSet

A ResultSet object acts like an iterator by maintaining a cursor pointing to its current row of data. Initially the cursor is positioned before the first row.

The next() method moves the cursor to the next row. The next() method returns false when there are no more rows in the ResultSet object, so it can be used in a while loop to iterate through the result set:

    while (resultSet.next()) {
      // process columns of the current row
    }
      

There are numerous getters for fetching column values in the current row. The following are the most commonly used:

Occasionally a query is made just to determine if there are any rows meeting its WHERE conditions. In this case the while loop can be replaced by an if or if-else statement:

    if (resultSet.next()) {
      // row found processing
    } else {
      // row not found processing
    }
      

There are two statement interfaces in JDBC: java.sql.Statement and java.sql.PreparedStatement.

java.sql.Statement

When you send a createStatement() message to a java.sql.Connection object it returns an object that implements the java.sql.Statement interface.

The java.sql.Statement interface has methods that take a string representing an SQL command and executes it against the database:

All of these methods have return values but the only one that is commonly used is the java.sql.ResultSet returned by executeQuery.

java.sql.PreparedStatement

When you send a prepareStatement() message to a java.sql.Connection object it returns an object that implements the java.sql.PreparedStatement interface.

The java.sql.PreparedStatement interface extends java.sql.Statement. It is intended for SQL statements with parameters that can be specified in Java code.

An SQL statement with parameters has some values, usually in WHERE clauses, replaced by question marks ("?"). In addition to the java.sql.Statement methods, the java.sql.PreparedStatement interface has methods such as setString(int position, String value) and setInt(int position, int value) that are used to set the values for the statement parameters. The position parameter for these methods is the parameter number. Parameter numbering starts at 1.

Reading SQL Text from a File

A simple SQL query such as:

   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.

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();
    }
  }
      

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.