☕ Java

Statement

java.sql.Statement is the base JDBC interface for sending SQL to a database and retrieving results, representing the simplest way to execute a static SQL string with no parameters. It is created from a Connection and supports three execution styles depending on whether the SQL returns rows, modifies data, or could be either, plus batch execution for running many statements efficiently in one round trip. This entry covers the executeQuery/executeUpdate/execute distinction, batch processing, and why Statement's lack of parameterization makes it unsuitable for most application code that handles user input, motivating PreparedStatement instead.

Creating a Statement and Choosing the Right Execute Method

A Statement is obtained by calling createStatement() on a Connection, and it represents a vessel for sending one SQL string at a time to the database — the SQL itself is supplied as a plain String to whichever execute method is called, not bound at creation time, so the same Statement object can in principle be reused to run different SQL strings sequentially (though in practice a fresh Statement per logical operation, closed promptly, is the more common pattern). JDBC offers three execution methods because SQL statements fall into three categories with different result shapes: executeQuery(sql) is for SELECT statements and always returns a ResultSet, even if that result set turns out to have zero rows; executeUpdate(sql) is for INSERT, UPDATE, DELETE, and DDL statements (CREATE TABLE, ALTER TABLE) and returns an int representing the number of rows affected (or 0 for DDL, which doesn't affect rows in the same sense). The generic execute(sql) method is for cases where the SQL's type isn't known ahead of time — for example, executing a string built dynamically, or running a stored procedure call that might return either a result set or an update count — and it returns a boolean indicating whether the first result is a ResultSet (true) or an update count (false), after which getResultSet() or getUpdateCount() retrieves the actual result. Calling the wrong method for a given SQL type throws a SQLException at runtime (e.g. calling executeQuery() with an UPDATE statement), so matching the method to the statement type is not just a style preference but a correctness requirement.
Java
import java.sql.*;

public class StatementBasics {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret");
             Statement stmt = conn.createStatement()) {

            // ── executeQuery: always for SELECT, always returns a ResultSet ──
            try (ResultSet rs = stmt.executeQuery("SELECT id, name, price FROM products")) {
                while (rs.next()) {
                    System.out.printf("%d: %s ($%.2f)%n",
                            rs.getLong("id"), rs.getString("name"), rs.getDouble("price"));
                }
            }

            // ── executeUpdate: for INSERT/UPDATE/DELETE/DDL, returns affected-row count ──
            int rowsUpdated = stmt.executeUpdate(
                    "UPDATE products SET price = price * 1.1 WHERE category = 'electronics'");
            System.out.println(rowsUpdated + " rows updated");

            int ddlResult = stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS audit_log (id SERIAL PRIMARY KEY, msg TEXT)");
            System.out.println("DDL update count (typically 0): " + ddlResult);

            // ── execute: generic, for when the SQL's result type isn't known ahead ──
            boolean isResultSet = stmt.execute("SELECT COUNT(*) FROM products");
            if (isResultSet) {
                try (ResultSet rs = stmt.getResultSet()) {
                    if (rs.next()) System.out.println("Count: " + rs.getInt(1));
                }
            } else {
                System.out.println("Update count: " + stmt.getUpdateCount());
            }
        }
    }
}

Batch Execution for Efficient Multi-Statement Operations

Executing many similar statements one at a time — each a separate round trip to the database — is inefficient when dozens, hundreds, or thousands of similar operations need to run together, such as inserting a large set of rows. Statement supports batching via addBatch(sql), which queues a SQL string without executing it immediately, and executeBatch(), which sends every queued statement to the database in a single network round trip and returns an int array where each element is the update count for the corresponding statement in the batch, in the order they were added. Batching reduces network round-trip overhead substantially compared to issuing the same statements individually, though the actual performance gain depends heavily on the driver and database — some databases and drivers can rewrite a batch of similar INSERT statements into a single more efficient multi-row INSERT under the hood, while others simply pipeline the individual statements. It's important to call clearBatch() after executeBatch() if the same Statement object will be reused for a new, unrelated batch, since otherwise previously executed statements could be unintentionally queued again. If any statement in the batch fails, the driver may throw a BatchUpdateException, which extends SQLException and additionally exposes getUpdateCounts() to report which statements in the batch succeeded before the failure occurred — exact behavior here (does the whole batch roll back, or do successful statements before the failure stick) is driver- and database-specific and should be verified rather than assumed.
Java
import java.sql.*;

public class BatchExecutionExample {
    public static void main(String[] args) throws SQLException {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5432/storedb", "app_user", "secret");
             Statement stmt = conn.createStatement()) {

            conn.setAutoCommit(false);  // batch + manual transaction is the common combination

            stmt.addBatch("INSERT INTO products (name, price) VALUES ('Widget A', 9.99)");
            stmt.addBatch("INSERT INTO products (name, price) VALUES ('Widget B', 14.99)");
            stmt.addBatch("INSERT INTO products (name, price) VALUES ('Widget C', 19.99)");

            try {
                int[] updateCounts = stmt.executeBatch();
                System.out.println("Statements executed: " + updateCounts.length);
                for (int count : updateCounts) {
                    System.out.println("  rows affected: " + count);
                }
                conn.commit();
            } catch (BatchUpdateException e) {
                conn.rollback();
                int[] partial = e.getUpdateCounts();
                System.err.println("Batch failed after " + partial.length + " statements: " + e.getMessage());
            } finally {
                stmt.clearBatch();  // avoid re-queuing on a future call to this same Statement
            }
        }
    }
}

Why Statement Is Unsuitable for Dynamic or User-Supplied Values

Statement has no concept of parameters — every piece of the SQL, including any value that varies per call, must be assembled into the SQL string itself before it's passed to executeQuery() or executeUpdate(). This is fine when the SQL is entirely static and contains no externally-influenced data, but becomes dangerous the moment any part of the string is built by concatenating user input, because a malicious or malformed input can alter the structure of the SQL itself rather than just supplying a value — the canonical SQL injection vulnerability. Even setting security aside, string-concatenated SQL is also error-prone for purely correctness reasons: values need manual escaping for quotes, dates and numbers need manual formatting in a way the database expects, and the database has no opportunity to pre-compile and cache an execution plan for a query whose text changes on every call. This is precisely the gap PreparedStatement fills: it lets the SQL structure be fixed and supplied once, with placeholder markers for the parts that vary, while the actual values are bound separately and safely by the driver — never concatenated into the SQL string at all. As a practical rule, Statement should be reserved for genuinely static, parameter-free SQL (schema DDL, fixed administrative queries), and any SQL that incorporates a variable value — regardless of whether that value originates from user input, configuration, or another part of the program — should use PreparedStatement instead.
Java
import java.sql.*;

public class StatementDangerVsPreparedStatement {
    // ── DANGEROUS: string concatenation with Statement — SQL injection risk ──
    public static void unsafeLookup(Connection conn, String userSuppliedName) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // If userSuppliedName is: ' OR '1'='1
            // the resulting query returns ALL rows, not just a matching name —
            // and a more malicious payload could run entirely different SQL.
            String sql = "SELECT * FROM products WHERE name = '" + userSuppliedName + "'";
            try (ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    System.out.println(rs.getString("name"));
                }
            }
        }
    }

    // ── SAFE: PreparedStatement — value is bound, never woven into SQL text ──
    public static void safeLookup(Connection conn, String userSuppliedName) throws SQLException {
        String sql = "SELECT * FROM products WHERE name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, userSuppliedName);  // bound as data, not parsed as SQL syntax
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println(rs.getString("name"));
                }
            }
        }
    }
}

Related Topics in Java Database Connectivity (JDBC)

JDBC Architecture
JDBC (Java Database Connectivity) is the standard Java API for connecting to and executing operations against relational databases, defined as a set of interfaces in java.sql and javax.sql that database vendors implement with concrete driver classes. Its architecture is deliberately layered to decouple application code from any specific database product: applications code against interfaces like Connection, Statement, and ResultSet, while a pluggable driver underneath translates those calls into the vendor-specific network protocol the database actually understands. This entry covers the two-layer (API/driver) architecture, the four historical driver types, the role of the JDBC driver manager and connection pooling layers, and how the pieces fit together at runtime.
JDBC Drivers
A JDBC driver is the vendor-specific library that implements the java.sql/javax.sql interfaces for a particular database product, translating standard JDBC calls into that database's native network protocol. Choosing, loading, registering, and configuring the correct driver is a prerequisite for any JDBC code to run, and the mechanics of how a driver gets discovered and instantiated have evolved significantly since JDBC's introduction. This entry covers driver registration via the Service Provider Interface (SPI) versus the older explicit Class.forName() approach, JDBC URL syntax and structure, driver-specific configuration properties, and how to choose and manage driver dependencies in a real project.
DriverManager
java.sql.DriverManager is the original, classic entry point into JDBC: a static factory class that maintains a registry of available Driver implementations and hands back a live Connection when given a JDBC URL and credentials. It predates the javax.sql.DataSource abstraction and connection pooling, and while it remains fully functional and is still commonly used for simple applications, scripts, and tests, production server-side code generally prefers DataSource-based connection pooling for the reasons covered below. This entry covers how DriverManager locates and selects a driver, its core methods, login timeout and logging configuration, and where it fits (and doesn't fit) in modern application architecture.
Connection
java.sql.Connection represents a single session with a specific database — the live link through which all statements, transactions, and metadata queries flow, and the factory from which Statement, PreparedStatement, and CallableStatement objects are created. A Connection is a relatively expensive, stateful resource that wraps an underlying network socket and server-side session, which is why its lifecycle, transaction settings, and proper closing matter so much in practice. This entry covers obtaining and closing connections, transaction control (auto-commit, commit, rollback, savepoints, isolation levels), creating statements from a connection, and connection metadata and health checks.
Prev
Connection
Next
PreparedStatement