Programming |
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)
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)
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)
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 |