Java Notes

Statement Interface in JDBC

Complete guide to JDBC Statement interface — executing static SQL, DDL operations, multiple results, batch execution, and understanding when to use Statement vs PreparedStatement.

What is Statement?

The Statement interface represents a static SQL statement that is sent to the database for execution. It's the simplest way to execute SQL but has limitations regarding security and performance.

java example

import java.sql.*;

public class StatementBasics {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC",
            "root", "password");
        
        // Create a Statement object
        Statement stmt = conn.createStatement();
        
        // Three execution methods:
        // 1. executeQuery() — for SELECT (returns ResultSet)
        ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
        
        // 2. executeUpdate() — for INSERT/UPDATE/DELETE/DDL (returns row count)
        int rowsAffected = stmt.executeUpdate(
            "UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering'");
        
        // 3. execute() — for any SQL (returns boolean)
        boolean hasResultSet = stmt.execute("SELECT * FROM employees");
        
        conn.close();
    }
}

Executing Updates (INSERT/UPDATE/DELETE)

java example

public class UpdateExamples {
    
    // INSERT
    public static void insertExample(Statement stmt) throws SQLException {
        String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
                     "VALUES ('Tom', 'Hardy', 'tom.h@company.com', 'Sales', 65000.00, '2024-01-15')";
        
        int rowsInserted = stmt.executeUpdate(sql);
        System.out.println(rowsInserted + " row(s) inserted");
    }
    
    // INSERT and get generated key
    public static void insertWithKey(Statement stmt) throws SQLException {
        String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
                     "VALUES ('Sarah', 'Connor', 'sarah.c@company.com', 'Security', 95000.00, '2024-06-01')";
        
        int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        
        if (rows > 0) {
            try (ResultSet keys = stmt.getGeneratedKeys()) {
                if (keys.next()) {
                    System.out.println("Generated ID: " + keys.getInt(1));
                }
            }
        }
    }
    
    // UPDATE
    public static void updateExample(Statement stmt) throws SQLException {
        String sql = "UPDATE employees SET salary = salary * 1.05 " +
                     "WHERE department = 'Engineering' AND is_active = TRUE";
        
        int rowsUpdated = stmt.executeUpdate(sql);
        System.out.println(rowsUpdated + " employee(s) got a 5% raise");
    }
    
    // DELETE
    public static void deleteExample(Statement stmt) throws SQLException {
        String sql = "DELETE FROM employees WHERE is_active = FALSE AND " +
                     "created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)";
        
        int rowsDeleted = stmt.executeUpdate(sql);
        System.out.println(rowsDeleted + " inactive employee record(s) removed");
    }
}

DDL Operations (CREATE, ALTER, DROP)

java example

public class DDLExamples {
    public static void createTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS departments (" +
            "id INT PRIMARY KEY AUTO_INCREMENT, " +
            "name VARCHAR(100) NOT NULL UNIQUE, " +
            "manager_id INT, " +
            "budget DECIMAL(12,2), " +
            "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
            "FOREIGN KEY (manager_id) REFERENCES employees(id)" +
            ")";
        
        stmt.executeUpdate(sql);
        System.out.println("Table created successfully");
    }
    
    public static void alterTable(Statement stmt) throws SQLException {
        // Add column
        stmt.executeUpdate("ALTER TABLE employees ADD COLUMN phone VARCHAR(20)");
        
        // Add index
        stmt.executeUpdate("CREATE INDEX idx_dept ON employees(department)");
        
        System.out.println("Table altered successfully");
    }
    
    public static void dropTable(Statement stmt) throws SQLException {
        stmt.executeUpdate("DROP TABLE IF EXISTS temp_data");
        System.out.println("Table dropped");
    }
}

Output

Table created successfully
Table altered successfully
Table dropped

The execute() Method (Generic)

Use execute() when you don't know the SQL type at compile time:

Example

public class GenericExecute { public static void executeAnySql(Statement stmt, String sql) throws SQLException { boolean isResultSet = stmt.execute(sql); if (isResultSet) { // It was a SELECT — get ResultSet try (ResultSet rs = stmt.getResultSet()) { ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); // Print column headers for (int i = 1; i <= cols; i++) { System.out.printf("%-20s", meta.getColumnName(i)); } System.out.println(); // Print rows while (rs.next()) { for (int i = 1; i <= cols; i++) { System.out.printf("%-20s", rs.getString(i)); } System.out.println(); } } } else { // It was an UPDATE/DDL — get affected rows int updateCount = stmt.getUpdateCount(); System.out.println("Rows affected: " + updateCount); } } }

SQL Injection Vulnerability ⚠️

This is why you should prefer PreparedStatement for user input:

java example

// ❌ DANGEROUS — SQL Injection vulnerable
public static void unsafeSearch(String userInput) throws SQLException {
    Statement stmt = conn.createStatement();
    // If userInput = "'; DROP TABLE employees; --"
    String sql = "SELECT * FROM employees WHERE last_name = '" + userInput + "'";
    // Becomes: SELECT * FROM employees WHERE last_name = ''; DROP TABLE employees; --'
    stmt.executeQuery(sql); // TABLE DROPPED!
}

// ✅ SAFE — Use PreparedStatement for any user-supplied data
public static void safeSearch(String userInput) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(
        "SELECT * FROM employees WHERE last_name = ?");
    pstmt.setString(1, userInput); // Properly escaped
    ResultSet rs = pstmt.executeQuery(); // Safe!
}

When Statement IS Appropriate

java example

// ✅ Static queries with no user input
stmt.executeQuery("SELECT COUNT(*) FROM employees");
stmt.executeUpdate("TRUNCATE TABLE temp_logs");

// ✅ DDL statements
stmt.executeUpdate("CREATE INDEX idx_email ON employees(email)");

// ✅ Database admin operations
stmt.execute("ANALYZE TABLE employees");

Statement Configuration Options

java example

public class StatementConfig {
    public static void demonstrate() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, pass);
        
        // Scrollable, updatable ResultSet
        Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,  // Can scroll back and forth
            ResultSet.CONCUR_READ_ONLY           // Read only
        );
        
        // Configure Statement
        stmt.setFetchSize(100);       // Fetch 100 rows at a time
        stmt.setMaxRows(1000);        // Maximum rows to return
        stmt.setQueryTimeout(60);     // Timeout in seconds
        stmt.setMaxFieldSize(1024);   // Max bytes per column
        
        ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
        
        // With scrollable ResultSet
        rs.last();
        System.out.println("Total rows: " + rs.getRow());
        rs.beforeFirst(); // Reset to beginning
        
        while (rs.next()) {
            // Process normally
        }
    }
}

Interview Questions

Q1: What are the three execute methods in Statement?

Answer: executeQuery(sql) — for SELECT, returns ResultSet. executeUpdate(sql) — for INSERT/UPDATE/DELETE/DDL, returns int (affected rows). execute(sql) — for any SQL, returns boolean (true if ResultSet available, false if update count).

Q2: When should you use Statement vs PreparedStatement?

Answer: Use Statement only for static SQL without user input (DDL, admin queries). Use PreparedStatement for all dynamic queries with parameters — it prevents SQL injection, enables query caching, and handles type conversion.

Q3: What is SQL injection and how does Statement enable it?

Answer: SQL injection is when malicious SQL is inserted through user input into string-concatenated queries. Statement doesn't escape or parameterize input, so attackers can modify query structure. PreparedStatement separates SQL structure from data, preventing injection.

Q4: How do you get auto-generated keys after an INSERT?

Answer: Pass Statement.RETURN_GENERATED_KEYS as the second argument to executeUpdate() or execute(), then call getGeneratedKeys() to get a ResultSet containing the generated keys.

Q5: What does setFetchSize() do?

Answer: It suggests to the driver how many rows to fetch from the database in each network round-trip. For large result sets, increasing fetch size reduces network calls but uses more memory. Default is driver-specific (often 10 for MySQL).

Q6: Can you execute multiple SQL statements in one execute() call?

Answer: By default, no. You can enable it with allowMultiQueries=true in the connection URL, but this is a security risk (enables multi-statement SQL injection). Use batch processing or separate calls instead.

Summary

In this chapter, we learned about Statement Interface in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Statement Interface in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, statement, interface

Continue learning this concept

JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCJDBC ProjectsHands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

PreviousResultSet in JDBC NextTransaction Management in JDBC

Java Notes

Statement Interface in JDBC

Complete guide to JDBC Statement interface — executing static SQL, DDL operations, multiple results, batch execution, and understanding when to use Statement vs PreparedStatement.

What is Statement?

The Statement interface represents a static SQL statement that is sent to the database for execution. It's the simplest way to execute SQL but has limitations regarding security and performance.

java example

import java.sql.*;

public class StatementBasics {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC",
            "root", "password");
        
        // Create a Statement object
        Statement stmt = conn.createStatement();
        
        // Three execution methods:
        // 1. executeQuery() — for SELECT (returns ResultSet)
        ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
        
        // 2. executeUpdate() — for INSERT/UPDATE/DELETE/DDL (returns row count)
        int rowsAffected = stmt.executeUpdate(
            "UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering'");
        
        // 3. execute() — for any SQL (returns boolean)
        boolean hasResultSet = stmt.execute("SELECT * FROM employees");
        
        conn.close();
    }
}

Executing Updates (INSERT/UPDATE/DELETE)

java example

public class UpdateExamples {
    
    // INSERT
    public static void insertExample(Statement stmt) throws SQLException {
        String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
                     "VALUES ('Tom', 'Hardy', 'tom.h@company.com', 'Sales', 65000.00, '2024-01-15')";
        
        int rowsInserted = stmt.executeUpdate(sql);
        System.out.println(rowsInserted + " row(s) inserted");
    }
    
    // INSERT and get generated key
    public static void insertWithKey(Statement stmt) throws SQLException {
        String sql = "INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) " +
                     "VALUES ('Sarah', 'Connor', 'sarah.c@company.com', 'Security', 95000.00, '2024-06-01')";
        
        int rows = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        
        if (rows > 0) {
            try (ResultSet keys = stmt.getGeneratedKeys()) {
                if (keys.next()) {
                    System.out.println("Generated ID: " + keys.getInt(1));
                }
            }
        }
    }
    
    // UPDATE
    public static void updateExample(Statement stmt) throws SQLException {
        String sql = "UPDATE employees SET salary = salary * 1.05 " +
                     "WHERE department = 'Engineering' AND is_active = TRUE";
        
        int rowsUpdated = stmt.executeUpdate(sql);
        System.out.println(rowsUpdated + " employee(s) got a 5% raise");
    }
    
    // DELETE
    public static void deleteExample(Statement stmt) throws SQLException {
        String sql = "DELETE FROM employees WHERE is_active = FALSE AND " +
                     "created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)";
        
        int rowsDeleted = stmt.executeUpdate(sql);
        System.out.println(rowsDeleted + " inactive employee record(s) removed");
    }
}

DDL Operations (CREATE, ALTER, DROP)

java example

public class DDLExamples {
    public static void createTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS departments (" +
            "id INT PRIMARY KEY AUTO_INCREMENT, " +
            "name VARCHAR(100) NOT NULL UNIQUE, " +
            "manager_id INT, " +
            "budget DECIMAL(12,2), " +
            "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, " +
            "FOREIGN KEY (manager_id) REFERENCES employees(id)" +
            ")";
        
        stmt.executeUpdate(sql);
        System.out.println("Table created successfully");
    }
    
    public static void alterTable(Statement stmt) throws SQLException {
        // Add column
        stmt.executeUpdate("ALTER TABLE employees ADD COLUMN phone VARCHAR(20)");
        
        // Add index
        stmt.executeUpdate("CREATE INDEX idx_dept ON employees(department)");
        
        System.out.println("Table altered successfully");
    }
    
    public static void dropTable(Statement stmt) throws SQLException {
        stmt.executeUpdate("DROP TABLE IF EXISTS temp_data");
        System.out.println("Table dropped");
    }
}

Output

Table created successfully
Table altered successfully
Table dropped

The execute() Method (Generic)

Use execute() when you don't know the SQL type at compile time:

Example

SQL Injection Vulnerability ⚠️

This is why you should prefer PreparedStatement for user input:

java example

// ❌ DANGEROUS — SQL Injection vulnerable
public static void unsafeSearch(String userInput) throws SQLException {
    Statement stmt = conn.createStatement();
    // If userInput = "'; DROP TABLE employees; --"
    String sql = "SELECT * FROM employees WHERE last_name = '" + userInput + "'";
    // Becomes: SELECT * FROM employees WHERE last_name = ''; DROP TABLE employees; --'
    stmt.executeQuery(sql); // TABLE DROPPED!
}

// ✅ SAFE — Use PreparedStatement for any user-supplied data
public static void safeSearch(String userInput) throws SQLException {
    PreparedStatement pstmt = conn.prepareStatement(
        "SELECT * FROM employees WHERE last_name = ?");
    pstmt.setString(1, userInput); // Properly escaped
    ResultSet rs = pstmt.executeQuery(); // Safe!
}

When Statement IS Appropriate

java example

// ✅ Static queries with no user input
stmt.executeQuery("SELECT COUNT(*) FROM employees");
stmt.executeUpdate("TRUNCATE TABLE temp_logs");

// ✅ DDL statements
stmt.executeUpdate("CREATE INDEX idx_email ON employees(email)");

// ✅ Database admin operations
stmt.execute("ANALYZE TABLE employees");

Statement Configuration Options

java example

public class StatementConfig {
    public static void demonstrate() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, pass);
        
        // Scrollable, updatable ResultSet
        Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,  // Can scroll back and forth
            ResultSet.CONCUR_READ_ONLY           // Read only
        );
        
        // Configure Statement
        stmt.setFetchSize(100);       // Fetch 100 rows at a time
        stmt.setMaxRows(1000);        // Maximum rows to return
        stmt.setQueryTimeout(60);     // Timeout in seconds
        stmt.setMaxFieldSize(1024);   // Max bytes per column
        
        ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
        
        // With scrollable ResultSet
        rs.last();
        System.out.println("Total rows: " + rs.getRow());
        rs.beforeFirst(); // Reset to beginning
        
        while (rs.next()) {
            // Process normally
        }
    }
}

Interview Questions

Q1: What are the three execute methods in Statement?

Q2: When should you use Statement vs PreparedStatement?

Q3: What is SQL injection and how does Statement enable it?

Q4: How do you get auto-generated keys after an INSERT?

Answer: Pass Statement.RETURN_GENERATED_KEYS as the second argument to executeUpdate() or execute(), then call getGeneratedKeys() to get a ResultSet containing the generated keys.

Q5: What does setFetchSize() do?

Q6: Can you execute multiple SQL statements in one execute() call?

Summary

In this chapter, we learned about Statement Interface in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Statement Interface in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, statement, interface

Continue learning this concept

PreviousResultSet in JDBC NextTransaction Management in JDBC