Difference between Statement and PreparedStatement (with Examples)

Java JDBC provides two key interfaces for executing SQL queries: Statement and PreparedStatement. Both are used to execute SQL queries against the database, but they have significant differences in terms of performance, security, and usage.

1. Introduction to Statement

Statement Overview

• Statement is used for executing a simple SQL query without parameters.
• It is suitable for executing static SQL statements.
• Vulnerable to SQL injection attacks.
• Each execution of the query is parsed and compiled by the database, which can be inefficient.

Example of Statement

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementExample {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:mysql://localhost:3306/library";
        String username = "root";
        String password = "root";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
             Statement statement = connection.createStatement()) {

            String sql = "SELECT * FROM books";
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                BigDecimal price = resultSet.getBigDecimal("price");

                System.out.println(id + ", " + title + ", " + author + ", " + price);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

• Connection: Establish a connection to the database.
• Statement: Create a Statement object to execute the SQL query.
• Execute Query: Execute the query using executeQuery() and process the ResultSet.

2. Introduction to PreparedStatement

PreparedStatement Overview

• PreparedStatement is used for executing precompiled SQL statements with or without parameters.
• It is suitable for executing dynamic SQL statements.
• Provides better performance due to precompilation and reuse of the SQL statement.
• Protects against SQL injection attacks by using parameterized queries.

Example of PreparedStatement

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.math.BigDecimal;

public class PreparedStatementExample {
    public static void main(String[] args) {
        String jdbcURL = "jdbc:mysql://localhost:3306/library";
        String username = "root";
        String password = "root";

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
            String sql = "SELECT * FROM books WHERE author = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "Joshua Bloch");

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                BigDecimal price = resultSet.getBigDecimal("price");

                System.out.println(id + ", " + title + ", " + price);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

• Connection: Establish a connection to the database.
• PreparedStatement: Create a PreparedStatement object with a parameterized SQL query.
• Set Parameter: Set the value of the parameter using setString() method.
• Execute Query: Execute the query using executeQuery() and process the ResultSet.

3. Key Differences

Performance

• Statement: Each execution is parsed and compiled by the database, which can be inefficient for repeated executions.
• PreparedStatement: The SQL statement is precompiled, and the database can reuse the precompiled statement, improving performance for repeated executions.

Security

• Statement: Vulnerable to SQL injection attacks as it does not support parameterized queries.
• PreparedStatement: Protects against SQL injection attacks by using parameterized queries, where the input values are treated as parameters rather than executable code.

Usage

• Statement: Suitable for executing simple SQL queries without parameters.
• PreparedStatement: Suitable for executing dynamic SQL queries with parameters, offering better performance and security.

4. Conclusion

In summary, Statement and PreparedStatement are both used to execute SQL queries in Java JDBC, but they serve different purposes and offer different benefits. Statement is suitable for simple, static queries, while PreparedStatement is ideal for dynamic queries with parameters, providing better performance and security against SQL injection attacks. Understanding the differences and appropriate usage scenarios of these two interfaces is crucial for effective and secure database operations in Java.