Using JDBC

Using JDBC in JSF requires injecting a JDBC resource from the application server into a JSF managed bean. The resource is injected into the bean 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". The resource must be configured on the server for this to work. Server configuration is described elsewhere.

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 with resources statement with the following form.

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

In addition to describing the JDBC methods needed for creating, setting up, and executing statements, and processing execution results, this web page describes how to read SQL text from a file, and has two examples:

The Core JSF database example illustrates, among other things, how a results from a SELECT query can be displayed in a <h:dataTable> tag.
The Cars example illustrates a database with foreign references.

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

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

For a SQL statement with no parameters:

      Statement statement = connection.createStatement();

For a SQL statement with parameters:

      PreparedStatement statement = connection.prepareStatement();

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

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

For a SQL statement with no parameters: no set up is needed.

For a SQL statement with parameters:

      PreparedStatement statement = connection.prepareStatement();

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

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

For a CREATE TABLE statement:
```
      statement.execute(sql_text);
```

For an INSERT, an UPDATE, or a DELETE statement:

      int rowCount = statement.executeUpdate(sql_text);

For a SELECT statement:

      ResultSet resultSet = statement.executeQuery(sql_text);

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

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

For a CREATE TABLE statement: the statement.execute() method does not return a value that is useful for a CREATE TABLE statement.
For an INSERT, an UPDATE, or a DELETE statement: the statement.executeUpdate() method returns the number of rows that were modified. This may be used to catch certain types of SQL errors.
For a SELECT statement: the statement.executeQuery() method returns a java.sql.ResultSet.
Result sets have iterators attached to them that allow you to step through the rows of the result table. They also have methods for retrieving columns of the current row.

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:

Blob getBlob(String columnLabel)
boolean getBoolean(String columnLabel)
Clob getClob(String columnLabel)
Date getDate(String columnLabel)
double getDouble(String columnLabel)
int getInt(String columnLabel)
String getString(String columnLabel)

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:

execute: for table creation (CREATE)
executeUpdate: for table updating (INSERT, UPDATE, and DELETE)
executeQuery: for table query (SELECT)

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.

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.model = model.id
     AND model.manufacturer = manufacturer.id
     AND car.vin = 0123456789abcdefg

If you enter this statement directly into a Java code file as quoted text then you will have to escape all of the double quotes. This makes the statement difficult to read and thus more prone to errors. It is better to separate query strings from Java code by storing them in files, as we show in this section.

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:

The file must have a .sql suffix
The file must be in the same folder as SQL.java
The argument to SQL.getSQL should not include the .sql suffix

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 Java database code.

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(...) ;
    try(Connection carsConnection = carsSource.getConnection()) {
      // make the query and process result
    }
  }

java.sql.ResultSet

java.sql.Statement

java.sql.PreparedStatement

SQL Text