Benefits PreparedStatement Over Statement

Benefits of PreparedStatement over Statement

PreparedStatement provides several advantages over Statement in JDBC. These benefits include enhanced performance, improved security, and better readability and maintainability of code.

Key Benefits of PreparedStatement over Statement

1. Performance

Precompilation: PreparedStatement queries are precompiled and stored in a prepared statement object. This means that the SQL query is compiled once and can be executed multiple times with different parameters without needing to be recompiled each time. This can lead to significant performance improvements, especially for queries executed multiple times.

2. Security

Protection Against SQL Injection: PreparedStatement uses placeholders for parameters, which helps prevent SQL injection attacks. When you set parameters using methods like setString(), the driver ensures that the parameter values are properly escaped and handled, eliminating the risk of SQL injection that can occur with dynamic query construction using Statement.

3. Maintainability

Parameterization: PreparedStatement allows you to set parameters using setter methods (e.g., setInt(), setString()), which makes the code cleaner and easier to maintain. This also makes it easier to handle dynamic input values.

4. Readability

Clear Separation of SQL Logic and Parameter Values: The use of placeholders for parameters in PreparedStatement makes the SQL query more readable and separates the SQL logic from the parameter values, enhancing the clarity of the code.

Example

Let’s illustrate the benefits of PreparedStatement over Statement with a Java example where we execute queries using both PreparedStatement over Statement.

Database Table Creation

Before running the Java code, create a table in your database.

sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DOUBLE
);


Java Program Using Statement and PreparedStatement
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatementBenefitsExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // 1. Using Statement to execute a static SQL query
            Statement statement = connection.createStatement();
            String name = "John";
            double salary = 50000.0;
            String insertSQL = "INSERT INTO employees (name, salary) VALUES ('" + name + "', " + salary + ")";
            statement.executeUpdate(insertSQL);
            System.out.println("Inserted an employee using Statement");

            // 2. Using PreparedStatement to execute a precompiled SQL query with parameters
            String insertSQLPrepared = "INSERT INTO employees (name, salary) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(insertSQLPrepared);
            preparedStatement.setString(1, "Jane");
            preparedStatement.setDouble(2, 60000.0);
            preparedStatement.executeUpdate();
            System.out.println("Inserted an employee using PreparedStatement");

            // 3. Using PreparedStatement to execute the same query multiple times with different parameters
            preparedStatement.setString(1, "Alice");
            preparedStatement.setDouble(2, 55000.0);
            preparedStatement.executeUpdate();

            preparedStatement.setString(1, "Bob");
            preparedStatement.setDouble(2, 52000.0);
            preparedStatement.executeUpdate();
            System.out.println("Inserted multiple employees using PreparedStatement");

            // 4. Using PreparedStatement to retrieve data with a parameterized query
            String selectSQL = "SELECT * FROM employees WHERE salary > ?";
            PreparedStatement selectPreparedStatement = connection.prepareStatement(selectSQL);
            selectPreparedStatement.setDouble(1, 50000.0);
            ResultSet resultSet = selectPreparedStatement.executeQuery();

            System.out.println("Employees with salary greater than 50000:");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String employeeName = resultSet.getString("name");
                double employeeSalary = resultSet.getDouble("salary");

                System.out.println("ID: " + id + ", Name: " + employeeName + ", Salary: " + employeeSalary);
            }
            resultSet.close();
            selectPreparedStatement.close();

            // Close the resources
            statement.close();
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

Explanation of the Example

Using Statement:

Statement statement = connection.createStatement(); creates a Statement object.
String insertSQL = “INSERT INTO employees (name, salary) VALUES (‘” + name + “‘, ” + salary + “)”; constructs the SQL query by concatenating values.
statement.executeUpdate(insertSQL); executes the static SQL query to insert an employee into the employees table.

Using PreparedStatement:

PreparedStatement preparedStatement = connection.prepareStatement(insertSQLPrepared); creates a PreparedStatement object for a SQL query with parameters.
preparedStatement.setString(1, “Jane”); sets the first parameter value.
preparedStatement.setDouble(2, 60000.0); sets the second parameter value.
preparedStatement.executeUpdate(); executes the precompiled SQL query to insert an employee into the employees table.
Reusability: The same PreparedStatement is used to insert multiple employees with different parameter values.

Retrieving Data with PreparedStatement:

PreparedStatement selectPreparedStatement = connection.prepareStatement(selectSQL); creates a PreparedStatement object for a parameterized query.
selectPreparedStatement.setDouble(1, 50000.0); sets the parameter value.
ResultSet resultSet = selectPreparedStatement.executeQuery(); executes the query and returns a ResultSet object containing the results

Homepage

TEST

Benefits PreparedStatement over Statement