Difference between PreparedStatement and CallableStatement

PreparedStatement and CallableStatement are both interfaces in the Java Database Connectivity (JDBC) API that are used to execute SQL statements. They have some similarities but are designed for different purposes.

Purpose

PreparedStatement

It is used to execute parameterized SQL statements. It allows you to precompile a SQL statement with placeholders for parameters, which can be set later with specific values. This is useful when you need to execute the same SQL statement multiple times with different parameter values.

CallableStatement

It is used to execute stored procedures or database functions. In addition to parameterized SQL statements, CallableStatement provides features to work with stored procedures, including input and output parameters.

Syntax

PreparedStatement

SQL statements executed with a PreparedStatement are typically regular SQL queries with placeholders for parameters. For example:

String sql = "SELECT * FROM customers WHERE age > ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 18);
ResultSet resultSet = statement.executeQuery();

CallableStatement

SQL statements executed with a CallableStatement are typically stored procedure calls or function calls using the appropriate database syntax. For example:

String sql = "{CALL procedureName(param1, param2)}";
CallableStatement statement = connection.prepareCall(sql);
statement.setInt(param1, value1);
//we can add as many parameter as we can.
statement.execute();
//Now we can print values from tatement

There are three types of parameters for CallableStatement: IN, OUT, and INOUT.

• IN parameters are used to pass values to the stored procedure. The values of IN parameters are set before the stored procedure is executed.
• OUT parameters are used to return values from the stored procedure. The values of OUT parameters are not set until the stored procedure has been executed.
• INOUT parameters are a combination of IN and OUT parameters. The values of INOUT parameters are set before the stored procedure is executed, and they can also be updated by the stored procedure.

Example of how to use IN parameters with CallableStatement:

CallableStatement cs = connection.prepareCall("{call getEmployeeName(?)}");
cs.setString(1, "123456789");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
    System.out.println(rs.getString(1));
}

In this example, the getEmployeeName() stored procedure takes one parameter, which is the employee ID. The setString() method is used to set the value of the parameter before the stored procedure is executed. The executeQuery() method is then used to execute the stored procedure and retrieve the results.

Example of how to use OUT parameters with CallableStatement:

CallableStatement cs = connection.prepareCall("{call getEmployeeNameOut(?)}");
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
String employeeName = cs.getString(1);
System.out.println(employeeName);

In this example, the getEmployeeNameOut() stored procedure takes one parameter, which is an OUT parameter. The registerOutParameter() method is used to register the parameter before the stored procedure is executed. The execute() method is then used to execute the stored procedure. The value of the OUT parameter can be retrieved using the getString() method.

Execution

PreparedStatement

A PreparedStatement is executed using the executeQuery(), executeUpdate(), or execute() methods depending on the type of SQL statement. It is typically used for queries that retrieve data or perform data manipulation.

CallableStatement

A CallableStatement is executed using the execute() or executeUpdate() methods. It is used to call stored procedures or functions that can have both input and output parameters.

Parameter handling

PreparedStatement

You can set the values of parameters in a PreparedStatement using methods like setInt(), setString(), etc. The parameter values are bound to their corresponding placeholders in the SQL statement.

CallableStatement

In addition to setting input parameter values similar to PreparedStatement, CallableStatement allows you to register output parameters using methods like registerOutParameter(). These output parameters can be retrieved after the execution of the stored procedure or function.

The following table summarizes the key differences between PreparedStatement and CallableStatement: