JDBC PreparedStatement Examples

PreparedStatement in JDBC allows executing parameterized queries with improved performance and security. Below are examples of common database operations using PreparedStatement:

1. Insert Data

Use the INSERT INTO SQL command with PreparedStatement to insert data into a table:


import java.sql.*;

public class InsertDataPreparedStatement {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            
            String insertQuery = "INSERT INTO Employees (ID, Name, Age, Department) VALUES (?, ?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
            
            preparedStatement.setInt(1, 1);  // Set ID
            preparedStatement.setString(2, "John Doe"); // Set Name
            preparedStatement.setInt(3, 30);  // Set Age
            preparedStatement.setString(4, "Engineering");  // Set Department
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) inserted.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. Update Data

Use the UPDATE SQL command with PreparedStatement to update existing data in a table:


import java.sql.*;

public class UpdateDataPreparedStatement {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            
            String updateQuery = "UPDATE Employees SET Age = ? WHERE ID = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
            
            preparedStatement.setInt(1, 31);  // Set new Age
            preparedStatement.setInt(2, 1);   // Set ID
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) updated.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Delete Data

Use the DELETE FROM SQL command with PreparedStatement to delete data from a table:


import java.sql.*;

public class DeleteDataPreparedStatement {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            
            String deleteQuery = "DELETE FROM Employees WHERE ID = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery);
            
            preparedStatement.setInt(1, 1);  // Set ID to delete
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) deleted.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Select Data

Use the SELECT SQL command with PreparedStatement to retrieve data from a table:


import java.sql.*;

public class SelectDataPreparedStatement {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            
            String selectQuery = "SELECT * FROM Employees WHERE Department = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
            
            preparedStatement.setString(1, "Engineering");  // Set Department to filter
            
            ResultSet resultSet = preparedStatement.executeQuery();
            
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("ID"));
                System.out.println("Name: " + resultSet.getString("Name"));
                System.out.println("Age: " + resultSet.getInt("Age"));
                System.out.println("Department: " + resultSet.getString("Department"));
            }
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Batch Update

Batch updates allow you to execute multiple SQL statements in a single batch. PreparedStatement is useful for batch processing:


import java.sql.*;

public class BatchUpdatePreparedStatement {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            
            String batchQuery = "INSERT INTO Employees (ID, Name, Age, Department) VALUES (?, ?, ?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(batchQuery);
            
            // Add first batch
            preparedStatement.setInt(1, 2);
            preparedStatement.setString(2, "Jane Smith");
            preparedStatement.setInt(3, 28);
            preparedStatement.setString(4, "HR");
            preparedStatement.addBatch();
            
            // Add second batch
            preparedStatement.setInt(1, 3);
            preparedStatement.setString(2, "Mark Lee");
            preparedStatement.setInt(3, 35);
            preparedStatement.setString(4, "Sales");
            preparedStatement.addBatch();
            
            // Execute batch
            int[] results = preparedStatement.executeBatch();
            System.out.println("Batch update completed. " + results.length + " statement(s) executed.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Summary of JDBC PreparedStatement Methods

Here’s a summary of the key methods used with PreparedStatement:

Method Description Used For Example
setInt() Sets an integer value for a parameter in the SQL query. Used for setting integer parameters. preparedStatement.setInt(1, 1);
setString() Sets a string value for a parameter in the SQL query. Used for setting string parameters. preparedStatement.setString(2, "John Doe");
executeUpdate() Executes an SQL statement that updates, inserts, or deletes data. Used for DML operations like INSERT, UPDATE, DELETE. preparedStatement.executeUpdate();
executeQuery() Executes an SQL query that returns a result set. Used for SELECT queries. resultSet = preparedStatement.executeQuery();
addBatch() Adds an SQL statement to the batch. Used for batch updates. preparedStatement.addBatch();
executeBatch() Executes all the SQL statements in the batch. Used for executing batch updates. preparedStatement.executeBatch();

Comparison Table: Statement vs PreparedStatement

Feature Statement PreparedStatement
SQL Query Direct SQL query with no parameters. Parameterized query with placeholders for parameters.
Security More vulnerable to SQL injection attacks. Prevents SQL injection attacks by using parameterized queries.
Performance Slower when executing the same query multiple times. Faster for repeated queries as it can be precompiled.
Code Complexity More code for dynamic parameters. Cleaner code with dynamic parameter setting using setXXX methods.
Use Case Suitable for simple, one-time queries. Ideal for repeated queries or queries with variable parameters.
Reusability Cannot be reused with different parameters. Can be reused with different parameters by setting new values.

Key Points: