Explain the Prepared Statement Object with an Example

9.A] Explain the Prepared Statement Object with an Example

Prepared Statement Object

In Java, the PreparedStatement object is an interface provided by the JDBC (Java Database Connectivity) API. It is used to execute SQL queries in a more efficient and secure manner compared to the traditional Statement object.

The key features of PreparedStatement include:

1. Precompiled SQL Queries:
   • SQL queries using PreparedStatement are precompiled on the database server.
   • This reduces the overhead of compiling the query each time it is executed,
   • leading to improved performance, especially when the same query is executed multiple times with different parameters.
2. Parameterized Queries:
   • Allows creation of parameterized SQL queries with placeholders (represented by ?).
   • Placeholders are set dynamically at runtime.
   • Helps avoid SQL injection attacks.
   • Makes the code cleaner and more manageable.
3. Improved Security:
   • By using parameterized queries, PreparedStatement helps prevent SQL injection attacks, as the query structure is defined before any user input is added.

Example of Using PreparedStatement

Suppose we have a database table named employees with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

Here is an example of how to use a PreparedStatement to insert a new employee record into this table:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementExample {
    public static void main(String[] args) {
        // Database connection URL, username, and password
        String url = "jdbc:mysql://localhost:3306/company_db";
        String username = "root";
        String password = "password";

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // Establishing a connection to the database
            connection = DriverManager.getConnection(url, username, password);

            // SQL query with placeholders (?)
            String sql = "INSERT INTO employees (id, name, age, department) VALUES (?, ?, ?, ?)";

            // Creating a PreparedStatement object
            preparedStatement = connection.prepareStatement(sql);

            // Setting the values for the placeholders
            preparedStatement.setInt(1, 101); // ID
            preparedStatement.setString(2, "John Doe"); // Name
            preparedStatement.setInt(3, 30); // Age
            preparedStatement.setString(4, "Engineering"); // Department

            // Executing the query
            int rowsAffected = preparedStatement.executeUpdate();

            // Checking if the insertion was successful
            if (rowsAffected > 0) {
                System.out.println("A new employee has been inserted successfully!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Explanation of the Example

• SQL Query: The SQL query contains placeholders (?) where the actual values will be substituted later.
• Setting Parameters: The method preparedStatement.setInt(1, 101) sets the first placeholder (?) to the integer value 101, representing the employee’s ID. Similarly, other placeholders are set using setString() and setInt() methods.
• Executing the Query: The executeUpdate() method is used to execute the SQL query. It returns the number of rows affected by the query, which helps determine if the operation was successful.
• Security: By using a PreparedStatement, the query is precompiled, and parameters are safely inserted into the query, reducing the risk of SQL injection attacks.

Advantages of PreparedStatement

1. Performance: Since the SQL statement is precompiled, it can be executed multiple times more efficiently.
2. Security: Helps in preventing SQL injection attacks.
3. Code Clarity: Parameterized queries make the code cleaner and easier to maintain.

Overall, PreparedStatement is a powerful and secure way to interact with a database in Java, especially when dealing with dynamic queries.