Advanced Java Tutorial

Statement, PreparedStatement, and CallableStatement

JDBC gives you three interfaces for sending SQL to the database, and choosing the right one is one of the most important decisions in any JDBC-based application — both for correctness and for security. Each is created from a Connection and each has a distinct purpose.

Statement — Raw, Unparameterized SQL

Statement executes a SQL string exactly as given. It has no concept of parameters, so any dynamic value must be concatenated directly into the SQL text. This makes Statement appropriate only for static SQL with no user-supplied values (e.g. DDL like CREATE TABLE). Concatenating user input into a Statement's SQL opens the door to SQL injection.

PreparedStatement — Parameterized and Precompiled

PreparedStatement takes SQL containing ? placeholders, which you bind with typed setter methods like setString(1, value) or setInt(2, value). The database (or driver) can precompile the statement's execution plan once and reuse it across many executions with different parameter values, which is both faster for repeated queries and, critically, treats bound values strictly as data — never as executable SQL syntax. This eliminates SQL injection for anything passed through a parameter.

ps.setString(1, username) — binds a String parameter at position 1.
ps.setInt(2, userId) — binds an int parameter at position 2.
ps.executeQuery() — runs a SELECT and returns a ResultSet.
ps.executeUpdate() — runs an INSERT/UPDATE/DELETE and returns the affected row count.

CallableStatement — Calling Stored Procedures

CallableStatement is used to invoke a stored procedure or function already defined in the database, using JDBC's escape syntax: {call procedureName(?, ?)}. It extends PreparedStatement, so it supports IN parameters the same way, but it also supports OUT and INOUT parameters via registerOutParameter(...), letting the database return values (or even a result set) beyond a normal query result.

execute, executeQuery and executeUpdate

All three statement types offer the same three ways to run SQL, and picking the right one makes the code say what it expects:

executeQuery() for a SELECT. It returns a ResultSet, and throws an exception if the SQL does not produce one.
executeUpdate() for INSERT, UPDATE, DELETE and DDL. It returns the number of rows changed, which is worth checking: an UPDATE that returns 0 usually means the WHERE clause matched nothing, for example because the ID does not exist.
execute() when you do not know in advance, or for stored procedures. It returns true if the first result is a ResultSet; read it with getResultSet() or the count with getUpdateCount().

What Placeholders Can and Cannot Replace

A ? stands for a value only: a string, a number, a date. It cannot stand for a table name, a column name, a keyword such as ASC/DESC, or a list of values in IN (?). When those parts must vary, for example a "sort by" option chosen by the user, never paste the user's text into the SQL. Check it against a fixed list of allowed values instead (Map.of("price", "price", "name", "name")) and use only the value from your own list. For IN lists, generate one ? per element and bind each one.

Batching Many Inserts

Inserting 10,000 rows with 10,000 separate executeUpdate() calls means 10,000 round trips to the database. PreparedStatement can collect rows with addBatch() and send them together with executeBatch(), which returns one update count per row. Combined with a transaction (covered in the transactions lesson), batching is usually the single biggest speed-up for bulk loads.

Examples

Why string concatenation is dangerous, and how PreparedStatement fixes it (run with java -cp h2.jar InjectionDemo.java)

Java

import java.sql.*;

public class InjectionDemo {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop", "sa", "")) {
            try (Statement st = conn.createStatement()) {   // Statement is fine for fixed SQL
                st.execute("CREATE TABLE users (id INT PRIMARY KEY, username VARCHAR(40), role VARCHAR(20))");
                st.execute("INSERT INTO users VALUES (1, 'admin', 'ADMIN'), (2, 'asha', 'USER'), (3, 'ravi', 'USER')");
            }

            String username = "admin' OR '1'='1";   // what an attacker types into a login form

            // UNSAFE: the input becomes part of the SQL text
            String unsafeSql = "SELECT username, role FROM users WHERE username = '" + username + "'";
            System.out.println("SQL sent: " + unsafeSql);
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery(unsafeSql)) {
                int rows = 0;
                while (rs.next()) {
                    rows++;
                    System.out.println("  " + rs.getString("username") + " (" + rs.getString("role") + ")");
                }
                System.out.println("Statement rows: " + rows);
            }

            // SAFE: the input is bound as one value
            try (PreparedStatement ps = conn.prepareStatement(
                    "SELECT username, role FROM users WHERE username = ?")) {
                ps.setString(1, username);
                try (ResultSet rs = ps.executeQuery()) {
                    int rows = 0;
                    while (rs.next()) rows++;
                    System.out.println("PreparedStatement rows: " + rows);
                }
                ps.setString(1, "asha");          // same statement, new value
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    System.out.println("Reused for 'asha': " + rs.getString("role"));
                }
            }

            // executeUpdate returns the number of rows changed
            try (PreparedStatement ps = conn.prepareStatement("UPDATE users SET role = ? WHERE role = ?")) {
                ps.setString(1, "MEMBER");
                ps.setString(2, "USER");
                System.out.println("Rows updated: " + ps.executeUpdate());
            }

            // Parameter positions start at 1
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
                ps.setInt(0, 1);
            } catch (SQLException e) {
                System.out.println("setInt(0, ..): " + e.getMessage());
            }
        }
    }
}

Output

SQL sent: SELECT username, role FROM users WHERE username = 'admin' OR '1'='1'
  admin (ADMIN)
  asha (USER)
  ravi (USER)
Statement rows: 3
PreparedStatement rows: 0
Reused for 'asha': USER
Rows updated: 2
setInt(0, ..): Invalid value "0" for parameter "parameterIndex" [90008-240]

Calling a stored procedure with IN and OUT parameters (HSQLDB 2.7.4: java -cp hsqldb.jar ProcedureDemo.java)

Java

import java.sql.*;

public class ProcedureDemo {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:shop", "SA", "")) {
            try (Statement st = conn.createStatement()) {
                st.execute("CREATE TABLE orders (id INT PRIMARY KEY, user_id INT, total DECIMAL(10,2))");
                st.execute("INSERT INTO orders VALUES (1, 1042, 59.99), (2, 1042, 15.00), (3, 7, 99.00), (4, 1042, 25.01)");
                st.execute("""
                    CREATE PROCEDURE get_order_stats(IN p_user INT, OUT p_count INT, OUT p_spent DECIMAL(10,2))
                    READS SQL DATA
                    BEGIN ATOMIC
                        SELECT COUNT(*), COALESCE(SUM(total), 0) INTO p_count, p_spent
                        FROM orders WHERE user_id = p_user;
                    END""");
            }

            try (CallableStatement cs = conn.prepareCall("{call get_order_stats(?, ?, ?)}")) {
                cs.setInt(1, 1042);                                  // IN
                cs.registerOutParameter(2, Types.INTEGER);           // OUT
                cs.registerOutParameter(3, Types.DECIMAL);           // OUT
                cs.execute();
                System.out.println("Orders for user 1042: " + cs.getInt(2));
                System.out.println("Total spent: " + cs.getBigDecimal(3));
            }
        }
    }
}

Output

Orders for user 1042: 3
Total spent: 100.00

(The CREATE PROCEDURE syntax differs between databases; the Java side,
 {call ...}, setInt and registerOutParameter, stays the same.)

Common Mistakes

Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities.
Forgetting that PreparedStatement parameter indices are 1-based, not 0-based.
Reusing a PreparedStatement across unrelated queries by trying to change its SQL text — you must create a new PreparedStatement for different SQL.
Skipping registerOutParameter() because one driver tolerated it. The JDBC specification requires it, HSQLDB happens to return the value anyway, and other drivers throw, so the code breaks when the database changes.
Trying to bind a table name, column name or sort direction with ?. Placeholders only take values; check such parts against an allow-list instead.
Ignoring the count returned by executeUpdate(), so an UPDATE that matched no rows is treated as a success.

Exercise

Using the lesson's Why string concatenation is dangerous, and how PreparedStatement fixes it (run with java -cp h2.jar InjectionDemo.java) example, create a small implementation or design sketch that demonstrates Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL.. Add a test case or scenario that would catch Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities., and describe the expected result.

Show hints

Start with Statement — Raw, Unparameterized SQL and compare your approach with Why string concatenation is dangerous, and how PreparedStatement fixes it (run with java -cp h2.jar InjectionDemo.java). Identify the decision or behavior the lesson recommends.
Walk through your scenario step by step. Confirm that it follows Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL. and that it would catch the failure described by Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities..

Quick quiz

1. Which choice is a mistake to avoid in Statement, PreparedStatement, and CallableStatement?

A. Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL.
B. Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities.
C. Forgetting that PreparedStatement parameter indices are 1-based, not 0-based.

Show answer

B. Avoid this approach: Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities. The lesson recommends: Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL.

Interview questions

How would you apply the guidance in Statement — Raw, Unparameterized SQL to a real task, and what failure would you guard against?

Show answer

Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL. Apply this in Statement — Raw, Unparameterized SQL; the example Why string concatenation is dangerous, and how PreparedStatement fixes it (run with java -cp h2.jar InjectionDemo.java) shows one way to do it. Avoid Building SQL by concatenating user input into a Statement, which is the single most common cause of SQL injection vulnerabilities. and explain how your implementation or design prevents that failure.

Key Points to Remember

Statement executes raw SQL with no parameters and no injection protection — use it only for static, trusted SQL.
PreparedStatement uses ? placeholders and typed setters, is precompiled for reuse, and is the safe default for any query with dynamic values.
CallableStatement extends PreparedStatement to invoke stored procedures, supporting IN, OUT, and INOUT parameters.
Never build SQL with string concatenation from untrusted input — always parameterize it.
executeQuery() returns a ResultSet, executeUpdate() a row count, and execute() a boolean saying which kind of result came back.
addBatch()/executeBatch() sends many parameter sets in one round trip.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs plain Java 17. Frameworks such as Spring, and databases, are not available here. Your code for each topic is kept in this browser.

Open a blank playground

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a JDK project with their libraries on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering

About the authors More tutorials LinkedIn