Java reference

PreparedStatement

A precompiled SQL query with ? parameters. Protects against SQL injection.

Available since: Java 1.1

Syntax

public interface PreparedStatement extends Statement

Examples

Inserting and selecting with parameters. Parameter numbers start at 1:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE users (name TEXT, age INTEGER)");
        }
 
        try (PreparedStatement insert = conn.prepareStatement(
                "INSERT INTO users (name, age) VALUES (?, ?)")) {
            insert.setString(1, "Anna");
            insert.setInt(2, 30);
            insert.executeUpdate();
            insert.setString(1, "Oleg");
            insert.setInt(2, 17);
            insert.executeUpdate();
        }
 
        try (PreparedStatement query = conn.prepareStatement(
                "SELECT name FROM users WHERE age >= ?")) {
            query.setInt(1, 18);
            try (ResultSet rs = query.executeQuery()) {
                while (rs.next()) {
                    IO.println(rs.getString("name"));
                }
            }
        }
    }
}

Output:

Anna

Why not concatenate strings: user input changes the meaning of the query, a parameter does not:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
         Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE users (name TEXT, password TEXT)");
        st.executeUpdate("INSERT INTO users VALUES ('Anna', 'a1'), ('Oleg', 'o2')");
 
        String input = "x' OR '1'='1";
 
        String sql = "SELECT COUNT(*) FROM users WHERE password = '" + input + "'";
        try (ResultSet rs = st.executeQuery(sql)) {
            rs.next();
            IO.println("Concatenated: " + rs.getInt(1));
        }
 
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT COUNT(*) FROM users WHERE password = ?")) {
            ps.setString(1, input);
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();
                IO.println("Parameter: " + rs.getInt(1));
            }
        }
    }
}

Output:

Concatenated: 2
Parameter: 0

A batch insert: addBatch() collects rows, executeBatch() sends them at once:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE tags (name TEXT)");
        }
        try (PreparedStatement ps = conn.prepareStatement("INSERT INTO tags VALUES (?)")) {
            for (String tag : List.of("java", "sql", "jdbc")) {
                ps.setString(1, tag);
                ps.addBatch();
            }
            int[] counts = ps.executeBatch();
            IO.println(counts.length + " rows inserted");
        }
    }
}

Output:

3 rows inserted

Notes

A ? replaces only a value — a table or column name cannot be passed as a parameter. Values are set with setString(), setInt(), setLong(), setBigDecimal(), setObject(), and NULL with setNull(index, Types.VARCHAR). executeUpdate() returns the number of changed rows, executeQuery() returns a ResultSet. A prepared statement can run many times with different parameters, and the database does not parse the SQL again.

Related recipes

In the tutorial

Prepared statements — PreparedStatement in JDBC — ? parameters in queries, protection against SQL injection, inserting, updating and deleting rows with executeUpdate, and getting generated keys.

Thanks for reading!

PreparedStatement

Syntax

Examples

Notes

See also

Related recipes

In the tutorial

Thanks for reading!