Java - JDBC - PreparedStatement Interface

The PreparedStatement interface is a subinterface of the Statement interface in Java. It is used to execute parameterized SQL queries against a database.

The basic steps to use the PreparedStatement interface are as follows:

• Load the JDBC driver for the database you want to connect to using Class.forName() method.
• Create a Connection object by invoking the DriverManager.getConnection() method with the database URL, username, and password as arguments.
• Create a PreparedStatement object by calling the Connection.prepareStatement() method with the SQL query containing parameters as an argument.
• Set values for the parameters using the appropriate setX() method of the PreparedStatement interface, where X is the SQL data type of the parameter.
• Execute the query using the execute() method of the PreparedStatement interface. This returns a ResultSet object containing the results of the query.
• Iterate over the ResultSet object using its next() method to process the rows returned by the query.
• Close the ResultSet, PreparedStatement, and Connection objects using their close() method in reverse order of creation.

Here's an example code snippet that demonstrates the use of PreparedStatement:

import java.sql.*;
public class PreparedStatementExample {
   public static void main(String[] args) throws SQLException {
      String databaseUrl = "jdbc:mysql://localhost:3306/mydatabase";
      String username = "root";
      String password = "password";
      
      Connection connection = null;
      PreparedStatement preparedStatement = null;
      ResultSet resultSet = null;
      
      try {
         // Load the JDBC driver
         Class.forName("com.mysql.jdbc.Driver");
         
         // Create a connection to the database
         connection = DriverManager.getConnection(databaseUrl, username, password);
         
         // Create a PreparedStatement with parameterized SQL query
         preparedStatement = connection.prepareStatement("SELECT * FROM employee WHERE id = ?");
         
         // Set the value for the parameter
         preparedStatement.setInt(1, 101);
         
         // Execute the query and get the ResultSet
         resultSet = preparedStatement.executeQuery();
         
         // Process the rows in the ResultSet
         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 (ClassNotFoundException e) {
         e.printStackTrace();
      } finally {
         // Close the ResultSet, PreparedStatement, and Connection objects
         if (resultSet != null) {
            resultSet.close();
         }
         if (preparedStatement != null) {
            preparedStatement.close();
         }
         if (connection != null) {
            connection.close();
         }
      }
   }
}

In this example, we use a parameterized SQL query to retrieve data from the employee table in a MySQL database. We set the value for the parameter using the setInt() method and then execute the query using the executeQuery() method of the PreparedStatement interface. Finally, we process the rows returned by the query using the ResultSet object.

Example of using the PreparedStatement interface in Java to insert, update, and delete records in a MySQL database:

import java.sql.*;

public class ExamplePreparedStatement {
   static final String DB_URL = "jdbc:mysql://localhost/ExampleDB";
   static final String USER = "username";
   static final String PASS = "password";

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

      try {
         Class.forName("com.mysql.jdbc.Driver");

         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL, USER, PASS);

         // Insert example
         String insertQuery = "INSERT INTO ExampleTable (col1, col2, col3) VALUES (?, ?, ?)";
         stmt = conn.prepareStatement(insertQuery);
         stmt.setString(1, "value1");
         stmt.setString(2, "value2");
         stmt.setString(3, "value3");
         stmt.executeUpdate();
         System.out.println("Record inserted successfully");

         // Update example
         String updateQuery = "UPDATE ExampleTable SET col1=? WHERE col2=?";
         stmt = conn.prepareStatement(updateQuery);
         stmt.setString(1, "new value");
         stmt.setString(2, "value2");
         stmt.executeUpdate();
         System.out.println("Record updated successfully");


         // Delete example
         String deleteQuery = "DELETE FROM ExampleTable WHERE col3=?";
         stmt = conn.prepareStatement(deleteQuery);
         stmt.setString(1, "value3");
         stmt.executeUpdate();
         System.out.println("Record deleted successfully");

      } catch (SQLException se) {

        se.printStackTrace();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         try {
            if (stmt != null) stmt.close();
         } catch (SQLException se2) {
            // Nothing we can do
         }
         try {
            if (conn != null) conn.close();
         } catch (SQLException se) {
            se.printStackTrace();
         }
      }
      System.out.println("Finished");
  }
}

In this example, we first establish a connection to the MySQL database using the DriverManager class. We then use PreparedStatement to execute an INSERT, UPDATE, and DELETE query on a table called ExampleTable.

For each query, we create a String containing the SQL statement with placeholders for the values we want to insert, update, or delete. We then create a PreparedStatement object from the Connection object and set the values for the placeholders using the setString() method. Finally, we execute the query using the executeUpdate() method.

Note that we're using try-with-resources statement to automatically close the PreparedStatement and Connection objects when we're finished with them.