PreparedStatement and CallableStatement in Java

Introduction

In Java, the PreparedStatement and CallableStatement are two key classes provided by JDBC to interact with a database. They are used to execute SQL queries and stored procedures with better performance, security, and maintainability.

PreparedStatement

The PreparedStatement interface is used to execute precompiled SQL queries with or without parameters. Using a prepared statement is more efficient and secure than using a Statement because:

• It helps prevent SQL injection attacks.
• It improves performance because the SQL query is precompiled and can be reused multiple times with different parameters.

Step 1: Creating a PreparedStatement

A PreparedStatement is created by passing the SQL query to the Connection.prepareStatement() method.

          import java.sql.*;
      
          public class PreparedStatementExample {
              public static void main(String[] args) {
                  try {
                      // Load the MySQL JDBC driver
                      Class.forName("com.mysql.cj.jdbc.Driver");
      
                      // Establish the connection
                      Connection connection = DriverManager.getConnection(
                          "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
                      // Create the PreparedStatement
                      String insertQuery = "INSERT INTO employees (id, name, department) VALUES (?, ?, ?)";
                      PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
      
                      // Set the values for the parameters
                      preparedStatement.setInt(1, 4);
                      preparedStatement.setString(2, "Mark Lee");
                      preparedStatement.setString(3, "Marketing");
      
                      // Execute the query
                      int rowsAffected = preparedStatement.executeUpdate();
                      System.out.println("Rows inserted: " + rowsAffected);
      
                      // Close the statement and connection
                      preparedStatement.close();
                      connection.close();
                  } catch (ClassNotFoundException | SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
        

In this example:

• We use a PreparedStatement to insert a new employee into the employees table.
• The placeholders ? are replaced with values using the setXXX() methods of PreparedStatement.
• The executeUpdate() method is used to execute the INSERT query.

Step 2: Using PreparedStatement for SELECT Query

You can also use PreparedStatement to execute SELECT queries with parameters.

          import java.sql.*;
      
          public class SelectPreparedStatementExample {
              public static void main(String[] args) {
                  try {
                      // Load the MySQL JDBC driver
                      Class.forName("com.mysql.cj.jdbc.Driver");
      
                      // Establish the connection
                      Connection connection = DriverManager.getConnection(
                          "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
                      // Create the SELECT query
                      String selectQuery = "SELECT * FROM employees WHERE department = ?";
                      PreparedStatement preparedStatement = connection.prepareStatement(selectQuery);
      
                      // Set the parameter for the department
                      preparedStatement.setString(1, "Marketing");
      
                      // Execute the query and get the result
                      ResultSet resultSet = preparedStatement.executeQuery();
      
                      // Process the result
                      while (resultSet.next()) {
                          int id = resultSet.getInt("id");
                          String name = resultSet.getString("name");
                          String department = resultSet.getString("department");
      
                          System.out.println("ID: " + id + ", Name: " + name + ", Department: " + department);
                      }
      
                      // Close the result set, statement, and connection
                      resultSet.close();
                      preparedStatement.close();
                      connection.close();
                  } catch (ClassNotFoundException | SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
        

In this example:

• We use a PreparedStatement to select employees from the employees table where the department is "Marketing".
• The result is processed using a ResultSet.

CallableStatement

The CallableStatement interface is used to execute stored procedures in the database. A stored procedure is a precompiled SQL block that can be executed on the database. It can accept input parameters, return output parameters, and execute SQL queries.

Step 1: Creating a CallableStatement

A CallableStatement is created by passing the SQL call to the Connection.prepareCall() method.

          import java.sql.*;
      
          public class CallableStatementExample {
              public static void main(String[] args) {
                  try {
                      // Load the MySQL JDBC driver
                      Class.forName("com.mysql.cj.jdbc.Driver");
      
                      // Establish the connection
                      Connection connection = DriverManager.getConnection(
                          "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
                      // Create the stored procedure
                      String storedProc = "{CALL addEmployee(?, ?, ?)}";
                      CallableStatement callableStatement = connection.prepareCall(storedProc);
      
                      // Set the input parameters
                      callableStatement.setInt(1, 5);
                      callableStatement.setString(2, "Alice Walker");
                      callableStatement.setString(3, "Sales");
      
                      // Execute the stored procedure
                      callableStatement.executeUpdate();
                      System.out.println("Employee added successfully!");
      
                      // Close the statement and connection
                      callableStatement.close();
                      connection.close();
                  } catch (ClassNotFoundException | SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
        

In this example:

• We call a stored procedure named addEmployee, which takes three parameters: employee ID, name, and department.
• The stored procedure is executed using the executeUpdate() method.

Step 2: Using CallableStatement with Output Parameters

You can also use CallableStatement to retrieve output parameters from a stored procedure.

          import java.sql.*;
      
          public class CallableStatementWithOutputExample {
              public static void main(String[] args) {
                  try {
                      // Load the MySQL JDBC driver
                      Class.forName("com.mysql.cj.jdbc.Driver");
      
                      // Establish the connection
                      Connection connection = DriverManager.getConnection(
                          "jdbc:mysql://localhost:3306/testdb", "root", "password");
      
                      // Create the stored procedure with an output parameter
                      String storedProc = "{CALL getEmployeeName(?, ?)}";
                      CallableStatement callableStatement = connection.prepareCall(storedProc);
      
                      // Set the input parameter for employee ID
                      callableStatement.setInt(1, 5);
      
                      // Register the output parameter for employee name
                      callableStatement.registerOutParameter(2, Types.VARCHAR);
      
                      // Execute the stored procedure
                      callableStatement.executeUpdate();
      
                      // Retrieve the output parameter
                      String employeeName = callableStatement.getString(2);
                      System.out.println("Employee Name: " + employeeName);
      
                      // Close the statement and connection
                      callableStatement.close();
                      connection.close();
                  } catch (ClassNotFoundException | SQLException e) {
                      e.printStackTrace();
                  }
              }
          }
        

In this example:

• We call a stored procedure named getEmployeeName that retrieves the employee name based on the employee ID.
• We use registerOutParameter() to register the output parameter for employee name.
• The result is retrieved using getString().

Conclusion

In this tutorial, we have covered how to use PreparedStatement and CallableStatement in Java:

• PreparedStatement is used for executing SQL queries with or without parameters, and it improves performance and security by preventing SQL injection attacks.
• CallableStatement is used for executing stored procedures, which are precompiled SQL blocks that can accept input parameters and return output parameters.