Red Hat Web Application Framework Developer Guide
Prev	Chapter 6. Database API	Next

Developer's Guide to the DB API

This document describes how to use the Red Hat Web Application Framework wrappers for JDBC. Using JDBC is necessary for dynamic SQL and it may be used for static SQL, although you may prefer to use the SQLJ precompiler. It is important to use the wrappers provided by Red Hat Web Application Framework to ensure that queries are logged and that pooled database connections are used. We also describe how to obtain unique IDs using the Sequence classes.

Required Packages

You should use the ConnectionManager class in com.arsdigita.db to obtain Connections. You should include the statements

import com.arsdigita.db.ConnectionManager;

import java.sql.Connection;
import java.sql.ResultSet;
...

in any class that uses the database.

Using JDBC

Now you are ready to write methods that use the database. The first thing to do is get a connection from the pool.

Connection conn = ConnectionManager.getConnection();

Now, you can prepare an SQL statement using the connection.

PreparedStatement stmt = conn.prepareStatement(
                         "select user_id,\n" +
                         "       first_names,\n" +
                         "       last_name,\n" +
                         "       email\n" +
                         "from   users\n" +
                         "where  last_name = ?");

stmt.setString(1, lastName + "%");

While formatting your queries like this in Java is a little painful, it makes them much easier for others to read. Do not forget the new lines, otherwise the logs will not maintain your formatting.

We do not support named bind variables at this point, so you need to specify them positionally as shown.

Now you are ready to execute your query and get the results.

ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    BigDecimal userId = rs.getBigDecimal(1);
    String firstNames = rs.getString(2);
    String   lastName = rs.getString(3);
    String      email = rs.getString(4);

    output.append("<li><a href=\"/shared/community-member?userId="
        + userId + "\">" + lastName + ", " + firstNames + "("
        + email + ")</a>");
}

This is rather contrived, as you should never be directly accessing the database from a page script, but cut me some slack here.

Finally, do not forget to close your connection.

conn.close();

Using Sequences

The com.arsdigita.db.Sequences class contains the static methods getNextValue(String) and getCurrentValue(String). The String is the name of the sequence you wish to access. (This is optional, if you do not specify it, the Red Hat Web Application Framework object sequence is used.) You can also optionally specify a Connection to be used. So, for example,

BigDecimal newId = Sequences.getNextValue("acs_object_id_seq");