Statements and Executing Queries

The Statement: Your SQL Messenger

Once you have a connection, you need a way to send SQL commands to the database. That's where Statement comes in — it's your messenger that delivers SQL to the database and brings back results.

Types of Statements

JDBC provides three types of statements, each with specific use cases:

Type	Best For	Performance	SQL Injection Risk
`Statement`	Static SQL, DDL	Basic	HIGH ⚠️
`PreparedStatement`	Parameterized queries	Better (cached)	LOW ✓
`CallableStatement`	Stored procedures	Varies	LOW ✓

⚠️ Warning: Never use Statement with user input — use PreparedStatement instead to prevent SQL injection attacks.

Creating and Using a Statement

Basic Statement

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {
    
    // Execute queries here
    
} // Statement automatically closed

Three Ways to Execute SQL

Statements offer three execution methods, each for different scenarios:

┌─────────────────────────────────────────────────────────────────┐
│                    Statement Execution Methods                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  executeQuery()  → Returns ResultSet   → SELECT statements      │
│                                                                 │
│  executeUpdate() → Returns int (rows)  → INSERT, UPDATE, DELETE │
│                                                                 │
│  execute()       → Returns boolean     → Any SQL (flexible)     │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

1. executeQuery() — For SELECT

Returns a ResultSet containing the query results:

String sql = "SELECT id, name, email FROM users WHERE active = true";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    
    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        
        System.out.printf("User: %d - %s (%s)%n", id, name, email);
    }
}

2. executeUpdate() — For INSERT, UPDATE, DELETE

Returns the number of affected rows:

// INSERT
String insertSql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@email.com')";
int rowsInserted = stmt.executeUpdate(insertSql);
System.out.println("Inserted " + rowsInserted + " row(s)");

// UPDATE
String updateSql = "UPDATE users SET active = true WHERE created_at > '2024-01-01'";
int rowsUpdated = stmt.executeUpdate(updateSql);
System.out.println("Updated " + rowsUpdated + " row(s)");

// DELETE
String deleteSql = "DELETE FROM users WHERE last_login < '2023-01-01'";
int rowsDeleted = stmt.executeUpdate(deleteSql);
System.out.println("Deleted " + rowsDeleted + " row(s)");

3. execute() — For Any SQL

Returns true if the result is a ResultSet, false if it's an update count:

String sql = "SELECT * FROM users";  // Could be any SQL

boolean hasResultSet = stmt.execute(sql);

if (hasResultSet) {
    try (ResultSet rs = stmt.getResultSet()) {
        // Process results
    }
} else {
    int updateCount = stmt.getUpdateCount();
    System.out.println("Affected rows: " + updateCount);
}

DDL Operations: Creating and Altering Tables

Use executeUpdate() for Data Definition Language (DDL) statements:

// CREATE TABLE
String createTable = """
    CREATE TABLE IF NOT EXISTS products (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(10, 2),
        stock INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    """;

stmt.executeUpdate(createTable);
System.out.println("Table created successfully");

// ALTER TABLE
String alterTable = "ALTER TABLE products ADD COLUMN category VARCHAR(50)";
stmt.executeUpdate(alterTable);

// DROP TABLE
String dropTable = "DROP TABLE IF EXISTS old_products";
stmt.executeUpdate(dropTable);

Getting Generated Keys

When inserting rows with auto-generated IDs, retrieve them like this:

String sql = "INSERT INTO users (name, email) VALUES ('Bob', 'bob@email.com')";

// Tell the driver we want generated keys
int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

// Retrieve the generated key
try (ResultSet keys = stmt.getGeneratedKeys()) {
    if (keys.next()) {
        long newId = keys.getLong(1);
        System.out.println("New user ID: " + newId);
    }
}

Statement Options and Configuration

Fetch Size

Control how many rows are fetched at a time from the database:

stmt.setFetchSize(100);  // Fetch 100 rows at a time

// Useful for large result sets to manage memory

Query Timeout

Prevent queries from running forever:

stmt.setQueryTimeout(30);  // 30 seconds max

try {
    ResultSet rs = stmt.executeQuery(longRunningSql);
} catch (SQLTimeoutException e) {
    System.err.println("Query timed out!");
}

Max Rows

Limit the number of rows returned:

stmt.setMaxRows(1000);  // Return max 1000 rows

// Alternative: use LIMIT in SQL (preferred)
// "SELECT * FROM users LIMIT 1000"

Complete CRUD Example

Here's a practical example showing all CRUD operations:

public class ProductDAO {
    private final DataSource dataSource;
    
    public ProductDAO(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    // CREATE
    public void createProduct(String name, double price) throws SQLException {
        String sql = "INSERT INTO products (name, price) VALUES ('" + name + "', " + price + ")";
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            
            if (rows > 0) {
                try (ResultSet keys = stmt.getGeneratedKeys()) {
                    if (keys.next()) {
                        System.out.println("Created product with ID: " + keys.getLong(1));
                    }
                }
            }
        }
    }
    
    // READ
    public void listProducts() throws SQLException {
        String sql = "SELECT id, name, price FROM products ORDER BY name";
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            System.out.println("Products:");
            System.out.println("-".repeat(50));
            
            while (rs.next()) {
                System.out.printf("  [%d] %s - $%.2f%n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getDouble("price"));
            }
        }
    }
    
    // UPDATE
    public void updatePrice(int productId, double newPrice) throws SQLException {
        String sql = "UPDATE products SET price = " + newPrice + " WHERE id = " + productId;
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rows = stmt.executeUpdate(sql);
            System.out.println("Updated " + rows + " product(s)");
        }
    }
    
    // DELETE
    public void deleteProduct(int productId) throws SQLException {
        String sql = "DELETE FROM products WHERE id = " + productId;
        
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            
            int rows = stmt.executeUpdate(sql);
            System.out.println("Deleted " + rows + " product(s)");
        }
    }
}

The SQL Injection Problem

⚠️ The code above is vulnerable to SQL injection!

Look at this dangerous scenario:

// User input
String userInput = "'; DROP TABLE products; --";

// Building SQL with string concatenation
String sql = "SELECT * FROM products WHERE name = '" + userInput + "'";

// The actual SQL becomes:
// SELECT * FROM products WHERE name = ''; DROP TABLE products; --'
// 😱 Table deleted!

🛡️ Solution: Use PreparedStatement (next lesson) for any SQL with parameters!

When to Use Statement

Use Statement only for:

Scenario	Example
Static SQL without parameters	`SELECT COUNT(*) FROM users`
DDL statements	`CREATE TABLE`, `ALTER TABLE`
Administrative scripts	Database setup/migration
Trusted, internal queries	Reporting with fixed SQL

For everything else, use PreparedStatement!

Use executeQuery() for SELECT (returns ResultSet), executeUpdate() for INSERT/UPDATE/DELETE/DDL (returns affected rows count), execute() for any SQL. Always close statements with try-with-resources. Use Statement.RETURN_GENERATED_KEYS to get auto-generated IDs. Set timeouts to prevent runaway queries. Never use Statement with user input—use PreparedStatement instead.