Difference Between Statement, PreparedStatement, and CallableStatement in JDBC

In JDBC (Java Database Connectivity), there are three commonly used interfaces for executing SQL queries: Statement, PreparedStatement, and CallableStatement. Each of these serves a different purpose and has unique features.

1. Statement:

• Use Case: It is used for executing static SQL queries that are fixed at runtime.
• Dynamic Parameters: Cannot accept parameters at runtime.
• Execution Type: Typically used for simple queries where the SQL statement is known in advance and doesn't change.
• Performance: Since Statement does not pre-compile queries, it can be less efficient, especially if the same query is executed multiple times.
• Example:

  Statement stmt = null;
  try {
      stmt = conn.createStatement();
      String sql = "SELECT * FROM Employee WHERE id=1";
      ResultSet rs = stmt.executeQuery(sql);
  } catch (SQLException e) {
      e.printStackTrace();
  } finally {
      try {
          if (stmt != null) stmt.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  

2. PreparedStatement:

• Use Case: Used for executing dynamic SQL queries where parameters are provided at runtime. It helps in preventing SQL injection by binding variables to placeholders (?).
• Dynamic Parameters: Allows parameters to be passed in at runtime.
• Execution Type: Ideal for repeated execution of the same query with different parameter values, as the SQL statement is precompiled by the database.
• Performance: More efficient than Statement for repeated queries because the SQL query is precompiled and can be reused.
• Example:

  PreparedStatement pstmt = null;
  try {
      String sql = "SELECT * FROM Employee WHERE id=? AND age=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, 1);   // Set the first parameter
      pstmt.setInt(2, 30);  // Set the second parameter
      ResultSet rs = pstmt.executeQuery();
  } catch (SQLException e) {
      e.printStackTrace();
  } finally {
      try {
          if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  

3. CallableStatement:

• Use Case: It is used to execute stored procedures in the database. Stored procedures are precompiled SQL statements that are stored in the database.
• Dynamic Parameters: Can handle IN, OUT, and INOUT parameters for stored procedures, which allows both sending and receiving values.
• Execution Type: Ideal for calling stored procedures that may involve complex operations like data manipulations or calculations on the server side.
• Performance: As with PreparedStatement, stored procedures can also improve performance by executing on the database server, avoiding some of the overhead of transferring data back and forth.
• Example:

  CallableStatement cstmt = null;
  try {
      String sql = "{call selectEmployee(?, ?)}";  // Call a stored procedure with parameters
      cstmt = conn.prepareCall(sql);
      cstmt.setInt(1, 1);   // Set the first input parameter
      cstmt.setInt(2, 30);  // Set the second input parameter
      ResultSet rs = cstmt.executeQuery();  // Execute the stored procedure
  } catch (SQLException e) {
      e.printStackTrace();
  } finally {
      try {
          if (cstmt != null) cstmt.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
  

Key Differences Summary:

Feature	Statement	PreparedStatement	CallableStatement
Use Case	Static SQL queries	Dynamic SQL queries with parameters	Calling stored procedures with input/output params
Parameterization	No	Yes, supports dynamic parameters	Yes, supports IN, OUT, INOUT parameters
Performance	Not optimized for repeated queries	Optimized for repeated queries	Optimized for executing stored procedures
SQL Compilation	No pre-compilation	Precompiled SQL for reuse	Precompiled SQL for stored procedures
SQL Injection Protection	No	Yes, protects against SQL injection	Yes, protects against SQL injection
Example Use	Simple queries without variables	Queries with parameters (e.g., SELECT * FROM WHERE id=?)	Stored procedures (e.g., {call procedure(?, ?)})

Conclusion:

• Statement is ideal for static queries that don't require parameters.
• PreparedStatement is better for dynamic queries that involve parameters, and is more efficient for repetitive execution of the same query.
• CallableStatement is used for executing stored procedures, and it can handle input/output parameters, making it suitable for more complex database operations.

When choosing which one to use, consider the nature of the SQL operation (static vs. dynamic vs. stored procedure) and whether the query needs to handle parameters. For most scenarios involving parameterized queries, PreparedStatement is the preferred choice, whereas CallableStatement is essential when working with stored procedures.