jdbc-statement-tutorial

Statement:
A Statement object is used to send SQL statements to a database. There are actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection: Statement, PreparedStatement, which inherits from Statement, and CallableStatement, which inherits from PreparedStatement. They are specialized for sending particular types of SQL statements: a Statement object is used to execute a simple SQL statement with no parameters; a PreparedStatement object is used to execute a precompiled SQL statement with or without IN parameters; and a CallableStatement object is used to execute a call to a database stored procedure.

The Statement interface provides basic methods for executing statements and retrieving results. The PreparedStatement interface adds methods for dealing with IN parameters; CallableStatement adds methods for dealing with OUT parameters.

Creating Statement Objects
Once a connection to a particular database is established, that connection can be used to send SQL statements. A Statement object is created with the Connection method createStatement, as in the following code fragment:

Connection con = DriverManager.getConnection(url, "sunny", "");
Statement stmt = con.createStatement();

The SQL statement that will be sent to the database is supplied as the argument to one of the methods for executing a Statement object:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2);

Executing Statements Using Statement objects:
The Statement interface provides three different methods for executing SQL statements, executeQuery, executeUpdate, and execute. The one to use is determined by what the SQL statement produces.

The method executeQuery is designed for statements that produce a single result set, such as SELECT statements.

The method executeUpdate is used to execute INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements like CREATE TABLE and DROP TABLE. The effect of an INSERT, UPDATE, or DELETE statement is a modification of one or more columns in zero or more rows in a table. The return value of executeUpdate is an integer indicating the number of rows that were affected (referred to as the update count). For statements such as CREATE TABLE or DROP TABLE, which do not operate on rows, the return value of executeUpdate is always zero.

The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two. Because it is an advanced feature that most programmers will never need, it is explained in its own section later in this overview.

All of the methods for executing statements close the calling Statement object's current result set if there is one open. This means that one needs to complete any processing of the current ResultSet object before re-executing a Statement object.

It should be noted that the PreparedStatement interface, which inherits all of the methods in the Statement interface, has its own versions of the methods executeQuery, executeUpdate and execute. Statement objects do not themselves contain an SQL statement; therefore, one must be provided as the argument to the Statement.execute methods. PreparedStatement objects do not supply an SQL statement as a parameter to these methods because they already contain a precompiled SQL statement. CallableStatement objects inherit the PreparedStatement forms of these methods. Using a query parameter with the PreparedStatement or CallableStatement versions of these methods will cause an SQLException to be thrown.

Statement Completion:
When a connection is in auto-commit mode, the statements being executed within it are committed or rolled back when they are completed. A statement is considered complete when it has been executed and all its results have been returned. For the method executeQuery, which returns one result set, the statement is completed when all the rows of the ResultSet object have been retrieved. For the method executeUpdate, a statement is completed when it is executed. In the rare cases where the method execute is called, however, a statement is not complete until all of the result sets or update counts it generated have been retrieved.

Some DBMSs treat each statement in a stored procedure as a separate statement; others treat the entire procedure as one compound statement. This difference becomes important when auto-commit is enabled because it affects when the method commit is called. In the first case, each statement is individually committed; in the second, all are committed together.

Closing Statement Objects:
Statement objects will be closed automatically by the Java garbage collector. Nevertheless, it is recommended as good programming practice that they be closed explicitly when they are no longer needed. This frees DBMS resources immediately and helps avoid potential memory problems.
Using the Method execute