Other JDBC Functionality | JAVA

Other JDBC Functionality

The JDBC provides functionality beyond the commonly used methods already discussed in terms of the following features:

• Transaction management
• Cursor support
• Stored procedure support
• Multiple result set processing

Transaction Management

JDBC implementations should default automatically to committing transactions unless the application otherwise requests that transactions require an explicit commitment. An application may toggle the automatic commit of the JDBC implementation it is using through the Connection.setAutoCommit() method. An example follows:

connection.setAutoCommit(false);

Of course, by not setting the AutoCommit attribute or by setting it to true, the JDBC implementation will make certain that the DBMS commits after each statement you send to the database. When set to false, however, the JDBC implementation requires specific commits from the application before a transaction is committed to the database. A series of statements executed as a single transaction would look like this:

public void add_comment(String comment) {


    try {

        Statement s;

        ResultSet r;

        int comment_id;


        connection.setAutoCommit(false);

        s = connection.createStatement();

        r = s.executeQuery("SELECT next_id " +

                    "FROM t_id " +

                           "WHERE id_name = 'comment_id'");


        if( !r.next() ) {

            throw new SQLException("No comment id exists " +
                                          &nbs p;          "in t_id table.");

        }


        comment_id = r.getInt(1) + 1;

               s.close();

                      s = connection.createStatement();

        s.executeUpdate("UPDATE t_id " +

            "SET comment_id = "
            + comment_id + " " +

                        "WHERE next_id = 'comment_id'");

        s.close();

        s = connection.createStatement();


        s.executeUpdate("INSERT INTO t_comment " +
                                         "(com ment_id, comment_text) " +

            "VALUES(" + comment_id + ", '" +

            comment + "')");
        connection.commit();

        }
    catch( SQLException e ) {

            e.printStackTrace();

            try {
            connection.rollback();

            } catch( SQLException e2 )  System.exit(-1);

        }

}

This method adds a comment to a comment table for some applications. To insert the new comment, the method needs to generate a new comment_id and then update the table for generating IDs so that the next one will be one greater than this one. Once the program has an ID for this comment, it then inserts the comment into the database and commits the entire transaction. If an error occurs at any time, the entire transaction is rolled back.
JDBC currently has no support for a two-phase commit. Applications written against distributed databases require extra support to allow for a two-phase commit.

Cursor Support

JDBC provides limited cursor support. It enables an application to associate a cursor with a result set through the ResultSet.getCursorName() method. The application can then use the cursor name to perform positioned UPDATE or DELETE statements.

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database that enable faster execution of SQL. JDBC supports stored procedures through the CallableStatement class. In the counter applet, you could have used a stored procedure to update the page hit count in the following way:

CallableStatement s = connection.prepareCall(
            "{call sp_upd_hit_count[?, ?]}");

s.setStringParameter(1, "file");

s.setIntParameter(2, count);

s.executeUpdate();

Multiple Result Sets

In some cases, especially with stored procedures, an application can find a statement by returning multiple result sets. JDBC handles this through the method Statement.getMoreResults(). Although result sets are left to be processed, this method returns true. The application can then obtain the next ResultSet object by calling Statement.getResultSet(). Processing multiple result sets simply involves looping through as long as Statement.getMoreResults() returns a value of true.