Statement,PreparedStatement and CallableStatement in java JDBC with examples

In JDBC, statements use to execute the SQL queries in databases.

We can send the simple SQL, complex SQL, and PL/SQL queries using these statements.

There are three types of Statements.

• Statement
• PreparedStatement
• CallableStatement

a Connection object is required to create any statement object.

Here is java code to create connection object

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
Connection con= DriverManager.getConnection("jdbc:odbc:orc", "scott", "tiger");  

Statement in JDBC

It is a simple Statement to execute SQL queries like insert, update, and delete operations.

We can use this for executing static queries, input values that are not accepted and passed to statement via fixed string

For each query, compilation and execution are done at the database level.

try {  
Statement stmt = con.createStatement();  
ResultSet rs = stmt.executeQuery("select \* from employee where employee_id=11");  
......  
} catch (SQLException e) {  
} finally {  
stmt.close();  
}  

Whenever this query is passed from the JDBC application to the database for getting the result, At the Database level, the query is compiled first, and execute the query and return the result. if we want to execute the multiple same queries with different employee_id, multiple compilation and executions have happened.

To avoid multiple compilations, we have to use PreparedStatement.

PreparedStatement in jdbc

This Statement uses to execute multiple SQL queries like insert, update, and delete operations multiple times.

It can also pass multiple different input values for the same query.

if there are multiple queries with different input values, the query is compiled (parsed and create a plan) for the first time and execution will be happened for different values multiple times by using the same plan at the database level.This improves the performances as compared to Statement .

we can use this for dynamic queries and dynamic input values are accepted. The same query takes the values as placeholder(?,?,?).

PreparedStatement pstmt = null;  
try {  
String SQL = "Update Employees SET name= ? WHERE employee\_id = ?";  
pstmt = con.prepareStatement(SQL);  
...  
} catch (SQLException e) {  
} finally {  
stmt.close();  
}  

CallableStatement in jdbc

This CallableStatement use to execute database stored procedures that reside in the database.

stored procedures are a set of SQL statements stored at the database level under a single name, thus improving the performance.

CallableStatement cstmt = null;  
try {  
String SQL = "{call procedureName (?, ?)}";  
cstmt = conn.prepareCall (SQL);  
. . .  
}  
catch (SQLException e) {  
   . . .  
}  
finally {  
. . .  
}  

SQL statement has the procedure name with placeholders.
Here the placeholders are of type IN, OUT, INOUT parameters.
IN: parameters mean input parameters, we can set using setXXX methods. OUT or INOUT parameters should be used by having one more method registerOutParameters that is mapped the java datatypes to database column data types.