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:

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

Let’s start with the basics!

1. Using the Statement Interface

Key Methods

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

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

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

Common Errors

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!