Statement Interface

The Statement interface runs SQL statements in a database and returns the result sets. A Statement object is obtained from a Connection object with the overloaded createStatement() method. Before enumerating the different createStatement() methods, we will discuss about the result set type, result set concurrency, and result set holdability. There are three result set types:

The TYPE_FORWARD_ONLY result set is not scrollable. Its cursor moves only in the forward direction. The rows in the result set satisfies the query, either at the time when the query is executed, or when the rows are retrieved.

The TYPE_SCROLL_INSENSITIVE result set is scrollable. The rows in the result set do not reflect the changes made in the database. The rows in the result set satisfy the query, either at the time when the query is executed, or when the rows are retrieved.

The TYPE_SCROLL_SENSITIVE result set is scrollable, and reflects the changes made to the database while the result set is open.

Result set concurrency specifies the level of updatability. There are two concurrency levels:

CONCUR_READ_ONLY is the default concurrency level. The CONCUR_READ_ONLY concurrency specifies a result set that is not updatable, and CONCUR_UPDATABLE concurrency specifies a result set that is updatable.

Holdability specifies that the result set objects are to be kept open when the commit() method is invoked. There are two holdability values:

If HOLD_CURSORS_OVER_COMMIT is specified, the result set objects (that is cursors) are kept open after the commit() method is called. If CLOSE_CURSORS_AT_COMMIT is specified, the result set objects are closed at the commit() method.

The different createStatement() methods, which are used to create a Statement object from a Connection object are discussed in following table:

Different execute() methods are available to run an SQL statement that may return multiple results. The execute(String sqlStatement) method runs an SQL statement and returns a boolean, which indicates whether the first result is a ResultSet object, or an update count. If true is returned, the first result is a ResultSet object. If false is returned, the first result is an update count. If the first result is a ResultSet object, then the ResultSet object can be obtained with the getResultSet() method. If the first result is an update count, then the update count can be obtained with the getUpdateCount() method:

Statement stmt=connection.createStatement();
boolean resultType=stmt.execute("SQL Statement");
if(resultType==true)
ResultSet resultSet=stmt.getResultSet();
else
int updateCount=stmt.getUpdateCount();

Multiple results can be returned by the execute() method. To obtain additional results, invoke the getMoreResults() method. The return value of the getMoreResults() method is similar to that of the execute() method. JDBC 3.0 introduced the getMoreResults(int) method to specify whether the current result set should be closed before opening a new result set. The getMoreResults(int) method parameter value can be CLOSE_ALL_RESULTS, CLOSE_CURRENT_RESULT, or KEEP_CURRENT_RESULT. If the parameter value is CLOSE_ALL_RESULTS, then all the previously opened ResultSet objects would be closed. If the value is CLOSE_CURRENT_RESULT, only the current ResultSet object is closed. If the value is KEEP_CURRENT_RESULT, the current ResultSet object is not closed.

The setQueryTimeout(int) method specifies the timeout, in seconds, for a Statement object to execute. The executeQuery(String sql) executes an SQL query and returns a single ResultSet object. The executeUpdate(String sql) method executes an SQL statement, which is either a DML (INSERT, UPDATE, or DELETE) statement or a DDL statement. If the SQL string is a DML statement, the executeUpate(String) method returns the number of rows modified. If the SQL string is a DDL statement, the method returns the value, "0". SQL statements can also be run in a batch with the executeBatch() method. Add SQL commands to run a batch with the addBatch(String sql) method:

stmt.addBatch("SQL command");
stmt.executeBatch();

The executeBatch() method returns an int[] value of update counts. The batch SQL commands can be cleared with the clearBatch() method. If a Statement object is not being used, it is closed automatically. It is recommended to close the Statement object with the close() method:

stmt.close();

When a Statement object is closed, the database and the JDBC resources associated with that object are also closed. Further, the ResultSet object associated with the Statement object is also closed.

In JDBC 4.0, the new methods discussed in following table have been added to the Statement interface: