Statement::JAVA-JDBC1

The Statement Interface

The Statement interface defines methods for executing SQL statements that do not
contain parameter markers. The PreparedStatement interface adds methods for
setting input parameters, and the CallableStatement interface adds methods for
retrieving output parameter values returned from stored procedures.

Creating Statements

Statement objects are created by Connection objects, as is done in
CODE EXAMPLE

Connection conn = dataSource.getConnection(user, passwd);
Statement stmt = conn.createStatement()

CODE EXAMPLE Creating a Statement object Each Connection object can create multiple Statement objects that may be usedconcurrently by the program. This is demonstrated in CODE EXAMPLE

// get a connection from the DataSource object ds
Connection conn = ds.getConnection(user, passwd);
// create two instances of Statement
Statement stmt1 = conn.createStatement();
Statement stmt2 = conn.createStatement();

CODE EXAMPLE Creating multiple Statement objects from a single connection'

Setting ResultSet Characteristics

Additional constructors may be used to set the type and concurrency or the type, concurrency, and holdability of any result sets produced by a statement. See

CODE EXAMPLE creates a Statement object that returns result sets that are scrollable, that are insensitive to changes made while the ResultSet object is open, that can be updated, and that do not close the ResultSet objects when commit is called.

Connection conn = ds.getConnection(user, passwd);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE,
ResultSet.HOLD_CURSOR_OVER_COMMIT);

CODE EXAMPLE Creating a scrollable, insensitive, updatable result set that stays open
after the method commit is called

Executing Statement Objects

The method used to execute a Statement object depends on the type of SQL statement being executed. If the Statement object represents an SQL query returning a ResultSet object, the method executeQuery should be used. If the SQL is known to be a DDL statement or a DML statement returning an update count, the method executeUpdate should be used. If the type of the SQL statement is not known, the method execute should be used.

Returning a ResultSet object

CODE EXAMPLE shows the execution of an SQL string returning a ResultSet object.

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(“select TITLE, AUTHOR, ISBN " +
"from BOOKLIST”);
while (rs.next()){
...
}

CODE EXAMPLE Executing a Statement object that returns a ResultSet object If the SQL string being executed does not return a ResultSet object, the method executeQuery throws an SQLException.

Returning an Update Count

In CODE EXAMPLE the SQL statement being executed returns the number of rows affected by the update.

Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(“update STOCK set ORDER = ‘Y’ " +
"where SUPPLY = 0”);
if (rows > 0) {
...
}

CODE EXAMPLE Executing a Statement object that returns an update count The method executeUpdate throws an SQLException if the SQL string being executed does not return an update count.

Using the Method execute

The method execute should be used only when the SQL string being executed could return either an update count or a ResultSet object. The execute method returns true if the first result is a ResultSet object and false if it is an update count. Additional methods must be called to retrieve the ResultSet object or update count or to retrieve additional results, if any.

String sql;
...
Statement stmt = conn.createStatement();
boolean b = stmt.execute(sql);
if (b == true) {
// b is true if a ResultSet is returned
ResultSet rs;
rs = stmt.getResultSet();
while (rs.next()) {
...
}
} else {
// b is false if an update count is returned
int rows = stmt.getUpdateCount();
if (rows > 0) {
...
}
}

CODE EXAMPLE Executing a Statement object that may return an update count or a ResultSet object When the SQL string being executed returns a ResultSet object, the method
getUpdateCount returns -1. If the SQL string being executed returns an update
count, the method getResultSet returns null.

Closing Statement Objects

An application calls the method Statement.close to indicate that it has finished processing a statement. All Statement objects will be closed when the connection that created them is closed. However, it is good coding practice for applications to close statements as soon as they have finished processing them. This allows any external resources that the statement is using to be released immediately. Closing a Statement object will close and invalidate any instances of ResultSet produced by that Statement object. The resources held by the ResultSet object
may not be released until garbage collection runs again, so it is a good practice to explicitly close ResultSet objects when they are no longer needed. These comments about closing Statement objects apply to PreparedStatement and CallableStatement objects as well.