Difference Statement PreparedStatement CallableStatement

Statement vs PreparedStatement vs CallableStatement

We know earlier, Statement interface works as a courier service between a Java (JDBC) program and database. There comes 3 types Statement interfaces in JDBC API doing the same job but used for different purposes.

Let us see how they differ in Statement vs PreparedStatement vs CallableStatement and use them programmatically.

Following table gives the list of 3 interfaces and their functionality.

Interface Name	What for used
Statement	Used for executing simple SQL statements like CRUD (create, retrieve, update and delete)
PreparedStatement	Used for executing dynamic SQL statements
CallableStatement	Used for executing stored procedures

Hierarchy of Statement vs PreparedStatement vs CallableStatement

Let us go in detail on Statement vs PreparedStatement vs CallableStatement

.

1. Statement interface

Following is the interface signature as defined in JDBC API.

public interface Statement extends Wrapper, AutoCloseable

We have seen earlier many programs using Statement interface like creating a new table, inserting records and retrieving records etc. Statement interface is a general-purpose carrier used for static SQL statements. This interface does not accept runtime parameters; that is, values known at runtime cannot be passed. All the values of the SQL statement should be known at compile time only. It is preferred when SQL statement is executed only once; that is, when to insert only one record or update one record etc. When compared to other two interfaces it is very slow in performance.

2. PreparedStatement

Contrary to Statement interface, the PreparedStatement accepts runtime values and these values can be passed as parameters. For example, the values of EMPID, EMPNAME and EMPSAL can be taken from GUI and passed as parameter values to PreparedStatement dynamically (this facility does not exist with Statement interface). This interface is useful to use the same SQL command multiple times, like inserting number of records. This gives a better performance. Let us see how it is possible shortly.

Following is the interface signature

public interface PreparedStatement extends Statement

As the signature indicates, the PreparedStatement interface is derived from Statement interface.

Let us see a snippet of code of using PreparedStatement.

PreparedStatement pst = con.prepareStatement(“insert into Employee (empid, empname, empsal) values (?, ?, ?)”);

where con is an object of Connection interface.

The questions marks in the above statement are called as parameters. The values are represented as question marks as their values are not known at compile time but known as runtime only. As values can be inserted dynamically, PreparedStatement is used for dynamic SQL statements executed repeatedly like inserting number of records one-by-one. The above statement is used in the program Dynamic SQL with JDBC PreparedStatement Interface.

Each question mark represents one value and is called as parameter marker. Values for markers should be supplied before the SQL statement is executed.

The values for the markers can be assigned with setXXX() methods defined in PreparedStatement interface as follows.

pst.setInt(1, id);
pst.setString(2, name);
pst.setDouble(3, salary);

where the values for the variables id, name and salary are known dynamically (at runtime most probably from GUI). The numbers 1, 2 and 3 represent each parameter marker; the 1 represents first question mark, the 2 represents the second question mark and 3 for third question mark. Moreover, the 1st question mark represents EMPID, the 2nd question mark represents EMPNAME and the 3rd represents EMPSAL. The number of database table fields written in the query and question marks should be of equal number. The 1st question mark is binded with the value of the variable id. That is, if the 1st question mark is given some value (through variable id) at runtime with setInt() method, the value is inserted in EMPID field and so on. Many setXXX() methods like setShort(), setFloat() etc also exist.

PreparedStatement query (but for parameters) is compiled and placed in database cache. For this reason, PreparedStatement is known as "partially compiled statement". At runtime, only the parameter values are compiled and inserted into the partially compiled query and executed and thereby PreparedStatement gives higher performance than Statement interface.

The dynamic SQL statements partially compiled can be placed on a server. The client application can call them in the required format while giving the values for marker variables.

Programming Traps

1. prepareStatement() method of Connection interface returns an object of PreparedStatement. Observe, the method is prepareStatement() and not preparedStatement().


2. Without setting values for question marks, executing PreparedStatement results in SQLException.


3. Parameter marker starts from 1 and not 0 position; that is, the first question mark is represented 1 and not 0.

3. CallableStatement

It is exclusively used for executing stored procedures – SQL procedures and functions. The CallableStatement also accepts runtime input parameters like PreparedStatement.

Following is the inteface signature

public interface CallableStatement extends PreparedStatement

As the signature indicates, the CallableStatement interface is derived from PreparedStatement interface. It can make use of all the methods of PreparedStatement also like setXXX() methods.

There comes three types of parameters for stored procedures – IN, OUT and INOUT. PreparedStatement uses only IN parameter where as CallableStatement can use all the three. Let us see how they differ.

IN	Parameter value not known at compile time (unknown when SQL statement is created) but known at runtime. The values for IN are binded with setXXX() methods.
OUT	This parameter value is known after executing the CallableStatement. The OUT value can be accessed with getXXX() methods from any part of the program.
INOUT	This parameter has a double advantage used for both purposes of input and output. The values for this parameter can be set with setXXX() and retrieved with getXXX() methods.

CallableStatement gives higher performance.

Snippet of code executing a function calculate() using CallableStatement.

CallableStatement cst=con.prepareCall(“{ ? = call calculate(?,?) }”);

In the above statement, first question mark is the return value of the function calculate(), the second question mark is IN value and the third one is OUT parameter. This is explained in the Executing SQL Function with CallableStatement Interface.