What is a Prepared Statement?

A Prepared Statement is a feature of JDBC that allows you to execute SQL queries with parameters. It helps prevent SQL injection attacks and improves performance by allowing the database to optimize and cache the query execution plan.

Creating a Prepared Statement

  1. Establish a Database Connection:

      Connection connection = DriverManager.getConnection("jdbc:database_url", "username", "password");

  2. Create a Prepared Statement:

    • Use the prepareStatement method of the Connection object.

    • Example:

      String sql = "SELECT * FROM users WHERE age > ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

Setting Parameters

  • Set parameters using setter methods on the PreparedStatement object.
  • Example:
```java
preparedStatement.setInt(1, 25); // Sets the first parameter to 25
```

Executing the Query

  • Execute the query using methods like executeQuery (for SELECT) or executeUpdate (for INSERT, UPDATE, DELETE).

  • Example (for SELECT):

  ResultSet resultSet = preparedStatement.executeQuery();
  • Example (for INSERT):
  int rowsAffected = preparedStatement.executeUpdate();

Closing Resources

  • Close the ResultSet, PreparedStatement, and Connection objects to release resources.
  • Example:
  resultSet.close();
preparedStatement.close();
connection.close();

Example Code

Here’s a complete example of using a prepared statement to query a database:

  import java.sql.*;

public class PreparedStatementExample {
    public static void main(String[] args) {
        String url = "jdbc:your_database_url";
        String username = "your_username";
        String password = "your_password";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // Establish a connection
            connection = DriverManager.getConnection(url, username, password);

            // Create a SQL query with parameters
            String sql = "SELECT * FROM users WHERE age > ?";
            preparedStatement = connection.prepareStatement(sql);

            // Set the parameter value
            preparedStatement.setInt(1, 25);

            // Execute the query
            resultSet = preparedStatement.executeQuery();

            // Process the result set
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Benefits of Using Prepared Statements

  • Prevents SQL Injection: By using parameterized queries, prepared statements help avoid SQL injection attacks.
  • Improves Performance: The database can optimize and cache the execution plan for prepared statements, making them faster for repeated execution.
  • Enhanced Readability: Separates SQL code from data, making the code cleaner and easier to maintain.