15.2 SQL Execution: `Statement`, `PreparedStatement`, and `CallableStatement`

SQL Execution: Statement, PreparedStatement, and CallableStatement

Once a database connection is established, the application interacts with the database engine by sending SQL commands. The JDBC API provides three primary statement interfaces organized in an inheritance hierarchy:

             +-----------------------+
             |   java.sql.Statement   |
             +-----------------------+
                         ^
                         |
          +------------------------------+
          |  java.sql.PreparedStatement  |
          +------------------------------+
                         ^
                         |
          +------------------------------+
          |   java.sql.CallableStatement |
          +------------------------------+

• Statement: Created via conn.createStatement(). Used for executing simple, static SQL queries without parameters. Susceptible to SQL injection if used with dynamic string concatenation.
• PreparedStatement: Created via conn.prepareStatement(sql). Extends Statement. Precompiles SQL statements with parameter placeholders (?), providing security against SQL injection and performance gains through database query plan caching.
• CallableStatement: Created via conn.prepareCall(sql). Extends PreparedStatement. Used to invoke database stored procedures and stored functions supporting IN, OUT, and INOUT parameters.

---

Statement vs. PreparedStatement and SQL Injection

Consider a user login authentication query implemented with a standard Statement:

// VULNERABLE IMPLEMENTATION - NEVER USE IN PRODUCTION!
String user = request.getParameter("user"); // Attacker inputs: admin' --
String pass = request.getParameter("pass");
String sql = "SELECT * FROM users WHERE username = '" + user + "' AND password = '" + pass + "'";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    if (rs.next()) {
        // User successfully authenticated without knowing the password!
    }
}

Because the raw SQL string is concatenated, the attacker's input alters the logical syntax of the query. The resulting command executes as:

SELECT * FROM users WHERE username = 'admin' --' AND password = '...'

The -- comment operator truncates the remainder of the query, bypassing authentication entirely.

The PreparedStatement Defense

PreparedStatement neutralizes SQL injection by transmitting the SQL template and the user values across separate protocol channels or escaping them internally:

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, user); // Bound strictly as literal data
    pstmt.setString(2, pass); // Cannot alter the parsed query tree
    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) {
            // Safe authentication
        }
    }
}

When conn.prepareStatement(sql) is invoked, the database engine compiles the SQL string into an executable query plan. When setString() is called, the value is treated strictly as a literal data parameter, making syntax hijacking impossible.

---

The Three Core Execution Methods

The JDBC statement interfaces provide three distinct execution methods. Choosing the incorrect execution method results in a runtime SQLException.

Working with execute()

The generic execute() method is used when the type of SQL statement is unknown at compile time or when executing commands that produce multiple result sets:

boolean isResultSet = stmt.execute(sqlString);

if (isResultSet) {
    try (ResultSet rs = stmt.getResultSet()) {
        while (rs.next()) {
            System.out.println("Data: " + rs.getString(1));
        }
    }
} else {
    int updateCount = stmt.getUpdateCount();
    System.out.println("Rows modified: " + updateCount);
}

Parameter Binding Rules and 1-Based Indexing

In PreparedStatement, parameters are designated with positional question mark placeholders (?). The JDBC API provides strongly typed setter methods for binding values:

• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setDouble(int parameterIndex, double x)
• setString(int parameterIndex, String x)
• setDate(int parameterIndex, java.sql.Date x)
• setNull(int parameterIndex, int sqlType) (e.g., java.sql.Types.VARCHAR)

The 1-Based Indexing Rule

Unlike standard Java collections and arrays, which are 0-indexed, all JDBC parameter indices are 1-based:

String sql = "UPDATE employees SET salary = ?, department = ? WHERE emp_id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setDouble(1, 85000.50);  // 1st placeholder: salary
    pstmt.setString(2, "ENGINEERING"); // 2nd placeholder: department
    pstmt.setInt(3, 1042);         // 3rd placeholder: emp_id

    int rowsAffected = pstmt.executeUpdate();
    System.out.println("Updated rows: " + rowsAffected);
}

If code attempts to bind index 0 (e.g., pstmt.setInt(0, 1042);) or an index exceeding the count of placeholders (e.g., pstmt.setInt(4, 1042);), the driver throws a runtime java.sql.SQLException: Parameter index out of range.

---

CallableStatement and Stored Procedures

Database stored procedures and functions encapsulate business logic inside the database engine. JDBC accesses them using CallableStatement via standard SQL escape syntax:

{call procedure_name(?, ?, ...)}
{? = call function_name(?, ...)}

Stored procedures support three parameter modes:

1. IN: Supplies input data from Java to the stored procedure.
2. OUT: Returns output data from the stored procedure to Java.
3. INOUT: Supplies input data and receives updated output data through the same parameter.

Registering OUT Parameters

Before executing a CallableStatement, any OUT or INOUT parameter must be registered with its corresponding SQL data type from java.sql.Types. After execution, the values are retrieved using getter methods:

// Stored procedure signature: calculate_bonus(IN emp_id INT, OUT bonus DOUBLE, INOUT rating VARCHAR)
String callSql = "{call calculate_bonus(?, ?, ?)}";

try (CallableStatement cstmt = conn.prepareCall(callSql)) {
    // Parameter 1: IN parameter (emp_id)
    cstmt.setInt(1, 1042);

    // Parameter 2: OUT parameter (bonus) - Must be registered before execution!
    cstmt.registerOutParameter(2, java.sql.Types.DOUBLE);

    // Parameter 3: INOUT parameter (rating) - Bound as input AND registered as output!
    cstmt.setString(3, "EXCELLENT");
    cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);

    // Execute the stored procedure
    cstmt.execute();

    // Retrieve the OUT results using 1-based parameter indices
    double bonus = cstmt.getDouble(2);
    String finalRating = cstmt.getString(3);

    System.out.println("Bonus: $" + bonus + ", Final Rating: " + finalRating);
}