24.4 Creating and Executing SQL Statements
The JDBC API defines three interfaces that represent SQL statements:
- java.sql.Statement represents a basic statement (p. 1523). This type of statement can be used to execute any SQL operation, such as CRUD or DDL operations. However, it is associated with performance and security issues. Thus it is not generally recommended, except for very simple operations that do not require dynamic parameterization and that are not reused within a program.
- java.sql.PreparedStatement is a subinterface of the Statement interface, that represents a statement capable of substitutional parameterization (p. 1526). This type of statement has the same capabilities as a basic statement, but is considered to be better from both performance and security perspectives, and thus is a recommended choice for representing SQL operations in general.
- java.sql.CallableStatement is a subinterface of the PreparedStatement interface, that represents a statement that invokes a stored procedure or function (p. 1530) and is also capable of substitutional parameterization.
The result of executing a SELECT statement is a table of rows represented by the following interface:
- java.sql.ResultSet that represents a table whose contents can be read and processed
All statements are created using a previously initialized JDBC connection object. A statement is created using one of the methods of the Connection interface shown below. Depending on the method, a specific statement is returned. Note that the statement object returned is AutoCloseable, and should be used in a try-with-resources statement to ensure proper closing of recourses such objects represent.
The following methods are defined in the Connection interface:
Statement createStatement()
PreparedStatement prepareStatement(String sql)
CallableStatement prepareCall(String sql)
For overloaded versions of these methods in the Connection interface that can be used to customize the result set, see p. 1541.
Basic Statement
We first look at how to create and execute a basic statement. A Statement object should first be obtained from the Connection object. Note the declarations in the header of the try-with-resources statement that obtain a Connection object and create a Statement object, respectively. The try-with-resources statement will ensure that these resources are closed in the right order—that is, the reverse of their declarations.
try (Connection connection = DriverManager.getConnection(jdbcURL);
Statement statement
= connection.createStatement()) { // Obtain a Statement object.
/* execute a query */
} catch (SQLException e) {
e.printStackTrace();
}
Once a statement has been obtained, the following methods of the Statement interface can be used to execute and process a SQL query.
boolean execute(String sql)
The execute() method can be called to execute any operation, returning a boolean value which is true for the SELECT statement, false otherwise.
ResultSet executeQuery(String sql)
The executeQuery() method can only be called to execute SELECT statements, returning a ResultSet that is the result of executing the query. A ResultSet is also AutoCloseable and must be closed when done. Passing a non-SELECT operation will throw a SQLException.
int executeUpdate(String sql)
The executeUpdate() method can be called to execute any operations, except SELECT operations, returning an int value indicating the number of rows affected by the non-SELECT operation. Passing a SELECT operation will throw a SQLException.
ResultSet getResultSet()
Returns the ResultSet that was the result of executing a SELECT query using the current statement.
int getUpdateCount()
Returns the number of rows that were affected by the execution of a non-SELECT query using the current statement.
An appropriate execute method can be called depending on the type of SQL statement and on passing the SQL operation as a String parameter. The code below executes a SELECT query by calling the execute() method of the Statement interface, as shown at (1). This method returns a boolean value. The output from the code at (2) indicates that a SELECT query was processed.
final String jdbcURL = “jdbc:derby:musicDB”;
try (Connection connection = DriverManager.getConnection(jdbcURL);
Statement statement
= connection.createStatement()) { // Obtain a Statement object.
String sql = “select * from compositions”; // SELECT query: select all rows.
boolean isSelectStmt = statement.execute(sql); // (1) Execute the query
System.out.println(“SELECT statement? ” + isSelectStmt);// (2) SELECT statement?
// true
} catch (SQLException e) {
e.printStackTrace();
}
