Making a Statement with JDBC
JDBC provides three methods for executing SQL statements: normal statements, prepared statements, and callable statements. The first method is to obtain a Statement interface handle by calling dbConnection.createStatement(). Then a ResultSet can be obtained using ResultSet resultSet = statement.executeQuery(''select * from member");. The executeQuery() method is used for, big surprise, queries; while the executeUpdate() method is used to execute insert, update, and delete statements. The Java documentation calls this "static SQL" due to the hardcoded nature of the SQL statement. Others often refer to this as dynamic SQL because the query must be dynamically created by the database instead of being precompiled.
Precompiled statements are known as prepared statements in JDBC. They are more efficient than regular statements if you are executing similar statements multiple times. Prepared statements can take up to 70 percent of the time required by raw queries (see "JDBC and Database Connection Pooling" in Marty Hall's book Core Servlets and Java Server Pages). When a PreparedStatement is created, the SQL statement is sent to the database for precompilation. The statement declaration includes query parameters denoted by the question mark symbol. Methods such as setString are provided to set the parameters like this:
PreparedStatement statement =
dbConnection.prepareStatement("select * from member
where firstname = ?");
Statement.setString( 1, "John");
The same parameters will remain in force for repeated use of a statement until clearParameters() is called. Currently, the precompiled form of a prepared statement is cached only as long as the PreparedStatement instance is alive, so the performance benefits are only obtained when reusing the same PreparedStatement instance, setting different parameters on each invocation. However, the current proposal for JDBC 3.0 specifies pooling prepared statements transparently. No syntax changes would be necessary, according to the proposal, in order to have prepared statements seamlessly cached across instances of PreparedStatement lifetimes.
The other type of statement available in JDBC is a callable statement. It's used to execute SQL stored procedures and allows them to be called in the same way for all RDBMSs. Stored procedures can have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (INOUT parameters). Like prepared statements, callable statements also use a question mark as a placeholder for a parameter. The CallableStatement interface extends PreparedStatement, which extends the Statement interface. CallableStatement has methods dealing with OUT and INOUT parameters. Methods inherited from PreparedStatement are used for setting IN parameters. Procedures that return multiple result sets are handled with methods inherited from Statement. For procedures with OUT or INOUT parameters, the type of the parameter must be registered before executing the statement. A basic stored procedure invocation will resemble the following:
CallableStatement statement = dbCon- nection.prepareCall
("{? = call getDepartmentID(?)}");
statement.registerOutParameter(1, java.sql.Types.INTEGER);
statement.setInt(2, employeeID);
ResultSet resultSet = statement.execute- Query();
int departmentID = statement.getInt(1);
— T.S.
