Difference between JDBC’s Statement, PreparedStatement, and CallableStatement

In JDBC (Java Database Connectivity) there are 3-types of statements that we can use to interact with the DBMS (Database Management System). These statements are: Statement, PreparedStatement, and CallableStatement. As I introduce each of these types of statements, I will elaborate on their advantages and constraints in respect to each other:


• Statement represents the base statements interface. In terms of efficiency, it is suitable to use Statement only when we know that we will not need to execute the SQL query multiple times. In contrast to PreparedStatement the Statement doesn't offer support for the parameterized SQL queries, which is an important protection from SQL injection attacks. With that said, Statement would be suitable for the execution of the DDL (Data Definition Language) statements, such as CREATE, ALTER, DROP. For example:

Statement stmt = con.createStatement();
stmt.executeUpdate("DROP TABLE PRODUCTS IF EXISTS");

• PreparedStatment extends the Statement interface. In most cases it is more efficient (in the context of multiple executions) to use the PreparedStatement because the SQL statement that is sent gets pre-compiled (i.e. a query plan is prepared) in the DBMS. Furthermore, we can use PreparedStatement to safely provide values to the SQL parameters, through a range of setter methods (i.e. setInt(int,int), setString(int,String), etc.). With that said, here's an example of a PreparedStatement:

PreparedStatement pstmt = con.prepareStatement("UPDATE PRODUCTS SET PRICE = ? WHERE ID = ?");
pstmt.setFloat(1, 546.00f);
pstmt.setInt(2, 7889);

• CallableStatement extends the PreparedStatement interface. This interface is used for executing the SQL stored procedures. One particular advantage of using CallableStatement is that it adds a level of abstraction, so the execution of stored procedures does not have to be DBMS-specific. However, it should be noted that the output parameters need to be explicitly defined through the corresponding registerOutParameter() methods; whereas the input parameters are provided in the same manner as with the PreparedStatement. With that said, here's the example of how the CallableStatement interacts with a MySQL Stored Procedure that does basic multiplication of integers:

CREATE PROCEDURE MULTIPLY(OUT RESULT INT, IN a INT, IN b INT) SET RESULT = a * b;

CallableStatement cstmt = con.prepareCall("{call MULTIPLY(?, ?, ?)}");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setInt(2, 4);
cstmt.setInt(3, 8);
cstmt.execute();
int result = cstmt.getInt(1);