Difference between Statement and PreparedStatement (with Examples)

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

Java Guides

Search This Blog