Statement,PreparedStatement and CallableStatement in java JDBC with examples

In JDBC, statements are used to execute SQL queries in databases, handling simple, complex SQL, and PL/SQL queries.

Types of JDBC Statements

• Statement
• PreparedStatement
• CallableStatement

A Connection object is required to create any statement object.

Here is the Java code to create a Connection object

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

#Statement in JDBC

A simple Statement is used to execute SQL queries like insert, update, and delete operations. It is suitable for executing static queries where input values are not accepted and passed to the statement via a fixed string.

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

Each query’s compilation and execution occur at the database level. Multiple executions of the same query with different employee_id values result in multiple compilations.

To avoid this, use PreparedStatement.

#PreparedStatement in JDBC

PreparedStatement is used to execute multiple SQL queries like insert, update, and delete operations multiple times.

It allows passing different input values for the same query.

For example, 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

CallableStatement is used to execute database stored procedures residing in the database, enhancing performance.

Stored procedures are sets of SQL statements stored under a single name at the database level.

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

SQL statements for CallableStatement include the procedure name with placeholders for IN, OUT, or INOUT parameters.

IN parameters are set using setXXX methods, while OUT or INOUT parameters are registered with registerOutParameters method, mapping Java datatypes to database column data types.