Statement vs PreparedStatement interface

The Statement vs PreparedStatement interfaces in JDBC are used for executing SQL queries, but they differ in terms of functionality, performance, and security. Here are the key differences between Statement vs PreparedStatement interface

1. SQL Query Execution

  • Statement: Used for executing static SQL queries. Each time a query is executed, it is compiled by the database.
  • PreparedStatement: Used for executing precompiled SQL queries with parameters. The query is compiled only once, and the compiled version is reused, improving performance.

2. Parameters

  • Statement: Does not support parameters. You need to concatenate values into the SQL query string.
  • PreparedStatement: Supports input parameters, allowing you to set values dynamically using setter methods (e.g., setInt, setString).

3. Performance

  • Statement: Slower performance due to repeated compilation of the SQL query.
  • PreparedStatement: Faster performance because the SQL query is compiled once and reused.

4. Security

  • Statement: Prone to SQL injection attacks because values are concatenated into the query string.
  • PreparedStatement: Provides protection against SQL injection attacks by using parameter placeholders.

5. Reusability

  • Statement: Not reusable for different queries or with varying parameters.
  • PreparedStatement: Reusable with different parameters for the same query.

Statement vs PreparedStatement interface

Example

Let’s illustrate the differences with a Java example where we execute queries using both Statement vs PreparedStatement interface

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

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


Java Program Using Statement vs PreparedStatement interface

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 StatementVsPreparedStatementExample {
    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");

            // Retrieve and display the data
            String selectSQL = "SELECT * FROM employees";
            ResultSet resultSet = statement.executeQuery(selectSQL);

            System.out.println("Employees in the database:");
            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();

            // 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 Statement vs PreparedStatement interface

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.

Retrieving Data

  • ResultSet resultSet = statement.executeQuery(selectSQL); executes the SQL query to retrieve all employees.
  • The ResultSet object is used to iterate over the results and print them to the console.

Homepage

Readmore