The PreparedStatement interface provides the following set methods for setting the values of marker parameters:
void setString(int index, String value)
Sets a String value for the parameter designated by the index.
void setBoolean(int index, boolean value)
Sets a boolean value for the parameter designated by the index.
void setInt(int index, int value)
Sets an int value for the parameter designated by the index.
void setLong(int index, long value)
Sets a long value for the parameter designated by the index.
void setDouble(int index, double value)
Sets a double value for the parameter designated by the index.
void setBigDecimal(int index, BigDecimal value)
Sets a BigDecimal value for the parameter designated by the index.
void setDate(int index, Date value)
Sets a Date value for the parameter designated by the index.
void setObject(int index, Object value, int sqlType)
Sets any Object value for the parameter designated by the index, and the required SQL type which is a named constant in the java.sql.Types class—for example, Types.INTEGER, Types.DATE, or Types.VARCHAR.
Example 24.2 Executing a Prepared Statement
package dbDemo;
import java.sql.*;
public class XQTPreparedStatement {
public static void main(String[] args) {
final String jdbcURL = “jdbc:derby:musicDB”;
String sql = “select * from compositions where duration > ?”; // (1)
try (Connection connection = DriverManager.getConnection(jdbcURL); // (2)
PreparedStatement pStatement = connection.prepareStatement(sql);) { // (3)
pStatement.setInt(1, 200); // (4)
boolean result = pStatement.execute(); // (5)
System.out.println(result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Probable output:
true
The procedure to use a prepared statement is illustrated by Example 24.2. The numbered comments below correspond to the numbered lines of code in the example.
- Formulate a SQL operation with marker parameters.
- Create a Connection with the DriverManager.getConnection() method.
- Create a PreparedStatement from the connection by calling its prepareStatement() method and passing the SQL operation as a parameter.
- Set the values of all marker parameters.
- Call the appropriate execute method of the prepared statement.
Note that a prepared statement can be reused by substituting different values for its marker parameters. In the try-with-resources statement in Example 24.2, we can include the following code to change the condition in the WHERE clause at (1) by substituting another value for the marker parameter before executing the prepared statement:
pStatement.setInt(1, 100);
result = pStatement.execute();
Substitutional parameterization allows SQL operations to be precompiled and reused, leading to more efficient execution. Supplying values using marker parameters is more flexible, faster, and safer than using a basic statement because the prepared statement is precompiled and marker parameters are not prone to SQL injection.
Example 24.3 illustrates prepared statements to execute INSERT, UPDATE, and DELETE statements with marker parameters that are defined first. A database connection and three prepared statements are created in the header of the try-with-resources statement, respectively. The marker parameters are set in each prepared statement, before the statement is executed using the executeUpdate() method of the Prepared-Statement interface. The program prints three integers that indicate the number of rows affected by each SQL operation, which of course is dependent on the data in the compositions table.
Example 24.3 Prepared Statement to Execute INSERT, UPDATE, and DELETE
package dbDemo;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PreparedStatementExecuteUpdate {
public static void main(String[] args) {
final String insSql = “insert into compositions VALUES(?, ?, ?)”;
final String updSql = “update compositions set title = ? where title = ?”;
final String delSql = “delete from compositions where duration = ?”;
final String jdbcURL = “jdbc:derby:musicDB”;
try (var connection = DriverManager.getConnection(jdbcURL);
var pStatement1 = connection.prepareStatement(insSql);
var pStatement2 = connection.prepareStatement(updSql);
var pStatement3 = connection.prepareStatement(delSql)) {
pStatement1.setInt(3, 150);
pStatement1.setString(2, “Java Jazz”);
pStatement1.setString(1, “ushm91736991”);
int result1 = pStatement1.executeUpdate();
System.out.println(result1);
pStatement2.setString(1, “Java Jive”);
pStatement2.setString(2, “Java Jazz”);
int result2 = pStatement2.executeUpdate();
System.out.println(result2);
pStatement3.setInt(1, 200);
int result3 = pStatement3.executeUpdate();
System.out.println(result3);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Probable output:
1
1
0
