Executing Statements with JDBC

Learn how to run SQL queries, updates, and parameterized operations

Introduction to JDBC Statements

In JDBC, a Statement is an object that sends SQL commands to a database. There are three types:

Statement: For static SQL queries (no parameters).
PreparedStatement: For dynamic queries with parameters (prevents SQL injection).
CallableStatement: For executing stored procedures (we’ll cover this later).

Let’s start with the basics!

1. Using the Statement Interface

Key Methods

Method	Purpose
`executeQuery()`	Execute `SELECT` (returns `ResultSet`).
`executeUpdate()`	Execute `INSERT`, `UPDATE`, `DELETE` (returns rows affected).
`execute()`	Generic execution (e.g., `CREATE TABLE`).

Example 1: Creating a Table

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    String sql = "CREATE TABLE employees (" +
                 "id INT PRIMARY KEY AUTO_INCREMENT," +
                 "name VARCHAR(50)," +
                 "salary DECIMAL(10,2))";

    stmt.execute(sql); // Execute any SQL
    System.out.println("Table created!");
} catch (SQLException e) {
    e.printStackTrace();
}

Example 2: Inserting Data

String sql = "INSERT INTO employees (name, salary) VALUES ('Alice', 75000.50)";
int rowsAffected = stmt.executeUpdate(sql);
System.out.println(rowsAffected + " row(s) inserted.");

Example 3: Querying Data

String sql = "SELECT * FROM employees";
try (ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
        String name = rs.getString("name");
        double salary = rs.getDouble("salary");
        System.out.println(name + " earns $" + salary);
    }
}

2. Using PreparedStatement for Security

Why PreparedStatement?

Prevents SQL injection (e.g., malicious ' OR '1'='1 in inputs).
Improves performance for repeated queries.

Step-by-Step Example

String sql = "INSERT INTO employees (name, salary) VALUES (?, ?)"; // Placeholders

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    // Set parameters (index starts at 1)
    pstmt.setString(1, "Bob");     // Replace first '?'
    pstmt.setDouble(2, 90000.75);  // Replace second '?'

    int rowsAffected = pstmt.executeUpdate();
    System.out.println(rowsAffected + " row(s) inserted.");
}

3. Statement vs. PreparedStatement

Aspect	Statement	PreparedStatement
SQL Injection	Vulnerable	Safe (parameterized queries)
Performance	Slower for repeated queries	Faster (precompiled SQL)
Use Case	Static SQL (no variables)	Dynamic inputs (forms, user data)

4. Batch Processing (Optional)

Run multiple queries in one database roundtrip for efficiency:

try (PreparedStatement pstmt = conn.prepareStatement("INSERT INTO employees VALUES (?, ?)")) {
    pstmt.setString(1, "Charlie");
    pstmt.setDouble(2, 82000.0);
    pstmt.addBatch(); // Add to batch

    pstmt.setString(1, "Diana");
    pstmt.setDouble(2, 95000.0);
    pstmt.addBatch();

    int[] results = pstmt.executeBatch(); // Execute all
    System.out.println("Batch complete!");
}

Best Practices

Always Use PreparedStatement for User Input.
Close Resources: Use try-with-resources for Statement, PreparedStatement, and ResultSet.
Sanitize Inputs: Even with PreparedStatement, validate data types/ranges.

Common Errors

Error	Solution
`SQLSyntaxErrorException`	Check SQL spelling and quotes.
`Parameter index out of range`	Ensure all `?` placeholders are set.
`ResultSet closed`	Don’t close the `Statement` before processing `ResultSet`.

Full Example: CRUD with JDBC

public class JdbcCrud {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/jdbc_practice";
        String user = "root";
        String password = "root123";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // Create
            PreparedStatement insert = conn.prepareStatement(
                "INSERT INTO employees (name, salary) VALUES (?, ?)"
            );
            insert.setString(1, "Eve");
            insert.setDouble(2, 88000.0);
            insert.executeUpdate();

            // Read
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }

            // Update
            PreparedStatement update = conn.prepareStatement(
                "UPDATE employees SET salary = ? WHERE name = ?"
            );
            update.setDouble(1, 92000.0);
            update.setString(2, "Eve");
            update.executeUpdate();

            // Delete
            Statement delete = conn.createStatement();
            delete.executeUpdate("DELETE FROM employees WHERE name = 'Eve'");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Summary

Use Statement for static SQL and PreparedStatement for dynamic inputs.
Always parameterize user input to prevent SQL injection.
Batch processing improves efficiency for bulk operations.

Next Up: Processing Results with ResultSet – Learn how to navigate and extract data from query results!