How to Use PreparedStatement in Java?

In Java, PreparedStatement is an interface in the java.sql package used to execute precompiled SQL queries against a database. It provides an efficient and secure way to interact with the database by preventing SQL injection attacks and improving performance.

Here is a basic guide on how to use PreparedStatement in Java:

Steps to Use PreparedStatement in Java:

1. Import Necessary Classes: You need to import the following classes:

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

2. Establish a Database Connection: Use DriverManager.getConnection() to create a connection to the database.

   Connection conn = null;
   try {
       // Load the database driver (optional if using JDBC 4.0 or later)
       Class.forName("com.mysql.cj.jdbc.Driver"); 
   
       // Establish connection (replace URL, username, password with actual values)
       conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");
   } catch (SQLException | ClassNotFoundException e) {
       e.printStackTrace();
   }
   

3. Create a PreparedStatement Object: Prepare a SQL query using Connection.prepareStatement(). The query can include placeholders (?) for dynamic parameters.

   String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
   PreparedStatement stmt = conn.prepareStatement(sql);
   

4. Set Parameters for the PreparedStatement: Use the setXXX() methods to bind values to the placeholders in the query. For example:

   stmt.setString(1, "John Doe");  // First placeholder
   stmt.setInt(2, 25);             // Second placeholder
   

5. Execute the Query: Depending on the query type (e.g., SELECT, INSERT, UPDATE, DELETE), you can execute the query using one of the following methods:
   • For an INSERT, UPDATE, or DELETE query, use executeUpdate():

     int rowsAffected = stmt.executeUpdate();
     System.out.println("Rows affected: " + rowsAffected);
     

   • For a SELECT query, use executeQuery() and retrieve the ResultSet:

     ResultSet rs = stmt.executeQuery();
     while (rs.next()) {
         System.out.println("Name: " + rs.getString("name") + ", Age: " + rs.getInt("age"));
     }
     

6. Close Resources: Always close the PreparedStatement and Connection objects to release resources.

   stmt.close();
   conn.close();
   

Complete Example:

import java.sql.*;

public class PreparedStatementExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // Establish connection
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");

            // SQL query with placeholders
            String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
            stmt = conn.prepareStatement(sql);

            // Set parameters
            stmt.setString(1, "John Doe");
            stmt.setInt(2, 25);

            // Execute the query
            int rowsAffected = stmt.executeUpdate();
            System.out.println("Rows affected: " + rowsAffected);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Key Advantages of PreparedStatement:

• Prevents SQL Injection: The parameters are bound separately from the SQL query, so user input cannot alter the structure of the SQL statement.
• Improved Performance: The SQL query is compiled once and can be reused with different parameters, reducing overhead for repeated queries.
• Cleaner Code: It handles dynamic parameter substitution automatically, making your code easier to read and maintain.

Conclusion:

Using PreparedStatement in Java helps ensure your code is more secure and performs better by allowing you to safely execute SQL queries with dynamic parameters. Always make sure to close database resources properly to avoid memory leaks.