Difference between Statement, PreparedStatement and CallableStatement in Java

---

These are the 3 interfaces provided by JDBC API for making database calls. Based on the requirement the user can select between the three.

Statement(I)

1. Statement cannot take parameters
2. It is used for DML queries like create, alter, delete to maintain the database structure
3. It is slower because it is not stored in the memory and compiled every time the execute() method is called.

Usage

// Creating a Statement
Statement statement = connection.createStatement();  

// commiting Statement  to the databse
statement.executeUpdate("CREATE TABLE customer(id SERIAL PRIMARY KEY, name character varying(50))");

// closing the statement
statement.close();

 PreparedStatement(I) extends Statement(I)

• Can take parameters
• Used for DDL queries like insert, update, delete to manipulate the data instead of table structure.
• Prevents SQL injection by taking data from bean instead of hard coding it in the query itself.
• It is faster because it is pre-compiled.

Usage

// creating a PreparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO customer(id, name)VALUES (?, ?) ");

// setting the values by index. 1 for id and 2 for name
preparedStatement.setInt(1, 100);
preparedStatement.setString(2, "John");          

// commiting PreparedStatement to the database
preparedStatement.executeUpdate(); 

// Closing the preparedStatment
preparedStatment.close();

 CallableStatement(I) extends PreparedStatement(I)

• Can take parameters
• Used for stored procedures(batch queries)
• Takes 3 params IN, OUT, IN OUT

Usage

First, we will need to define the stored procedure

CREATE PROCEDURE InsertCustomer(_id integer, _firstname character varying(50))
LANGUAGE SQL
AS $BODY$
    INSERT INTO customer(id, first_name)
    VALUES(_id, _firstname);  
$BODY$;

Code to create and execute a procedure using CallableStatement

//Creating CallableStatement object 
CallableStatement callableStatement = connection.prepareCall("{call InsertCustomer (?, ?)}");
 
// setting the values by index. 1 for id and 2 for name
callableStatement.setInt(1, 101);
callableStatement.setString(2, "Mary");          

// executing the CallableStatement 
callableStatement.execute(); 

// Closing the callableStatement
callableStatement.close();

Revision

Statement(I)	PreparedStatement(I)	CallableStatement(I)
It is used to execute DDL queries that manipulate the structure of the database like, create, alter, etc.	It is used to execute parameterized DML queries which actually manipulate the data inside the databases like insert, update, etc.	It is used to execute stored procedures or functions that are already defined in the database.
It is slower because it is compiled every time execute() is called.	It is faster because pre-compiled and stored in the cache. Hence, it is preferred while doing bulk operations.	It is also precompiled and stored in the cache.
Cannot take parameters	Can take parameters to store the data dynamically to prevent SQL injection.	Can take parameters like IN, OUT, INOUT

---

Comments:


There are no comments.

Enter a new Comment:

Save Comment