2.1.6 Sending SQL Statements

Once a connection is established, it is used to pass SQL statements to its underlying database. The JDBC API does not put any restrictions on the kinds of SQL statements that can be sent; this provides a great deal of flexibility, allowing the use of database-specific statements or even non-SQL statements. It requires, however, that the user be responsible for making sure that the underlying database can process the SQL statements being sent and suffer the consequences if it cannot. For example, an application that tries to send a stored procedure call to a DBMS that does not support stored procedures will be unsuccessful and will generate an exception.
The JDBC API provides three interfaces for sending SQL statements to the database, and corresponding methods in the Connection interface create instances of them. The interfaces for sending SQL statements and the Connection methods that create them are as follows:

1. Statement-created by the Connection.createStatement methods. A Statement object is used for sending SQL statements with no parameters.
2. PreparedStatement-created by the Connection.prepareStatement methods. A PreparedStatement object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters). PreparedStatement has a group of methods that set the value of IN parameters, which are sent to the database when the statement is executed. PreparedStatement extends Statement and therefore includes Statement methods. A PreparedStatement object has the potential to be more efficient than a Statement object because it has been precompiled and stored for future use. Therefore, in order to improve performance, a PreparedStatement object is sometimes used for an SQL statement that is executed many times.
3. CallableStatement-created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures-a group of SQL statements that is called by name, much like invoking a function. A CallableStatement object inherits methods for handling IN parameters from PreparedStatement; it adds methods for handling OUT and INOUT parameters.

The following list gives a quick way to determine which Connection method is appropriate for creating different types of SQL statements:

• createStatement methods-for a simple SQL statement (no parameters)
• createStatement()
• createStatement(
int resultSetType, int resultSetConcurrency)
• createStatement(
int resultSetType, int resultSetConcurrency,
int resultlSetHoldability)

• prepareStatement methods-for an SQL statement that is executed frequently
• prepareStatement(String sql)
• prepareStatement(String sql,
int resultSetType, int resultSetConcurrency)
• prepareStatement(String sql,
int resultSetType, int resultSetConcurrency,
int resultlSetHoldability)

• prepareCall methods-for a call to a stored procedure
• prepareCall(String sql)
• prepareCall(String sql,
int resultSetType, int resultSetConcurrency)
• prepareCall(String sql,
int resultSetType, int resultSetConcurrency,
int resultlSetHoldability)

The versions of these methods that take no ResultSet constants as arguments create statements that will produce default ResultSet objects; that is, they produce result sets that are not scrollable, that cannot be updated, and that will not be held open after the Connection methods commit or rollback are called. It is possible to create statements that will produce result sets that do have these capabilities by using the versions of the methods createStatement, prepareStatement, and prepareCall that take additional parameters for specifying the type, the concurrency level, and the holdability of the ResultSet objects that will be produced by the statements being created. In Chapter 6, "ResultSet," the section on the types of ResultSet objects on page 70 explains the different types of ResultSet objects and the constants that specify them. The section "Concurrency Types" on page 71 does the same for concurrency levels, and the section "Holdability," on page 72 explains holdability and how to specify that a ResultSet object stays open. "Creating Different Types of Result Sets" on page 74 gives examples of how to create ResultSet objects using the new versions of the Connection methods for creating statements.