Artikel Terbaru

Sunday, 4 December 2011

Statement,PreparedStatement and CallableStatement

In JDBC, There are different statements to execute the sql queries in databases. We can send the simple SQL,complex SQL and PL/SQL queries using this statements There are three types of Statements. The following statements are returned from the connection object.before creating any statement object, connection should be presented. The connection object can be returned from the following lines of code
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con= DriverManager.getConnection("jdbc:odbc:orc", "scott", "tiger");
1.Statement :-

This is simple Statement to execute SQL queries like insert,update and delete operations. for this we can not know the input values. For each query, compilation and execution is done at database level.we can use this for static queries and input values are not accepted

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 jdbc application to database for getting the result,At Database level,query is complied 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 multiple executions are happened. To avoid the multiple compilations, we have to use PreparedStatement.

2.PreparedStatement :-

This Statement is to be used to execute multiple SQL queries like insert,update and delete operations multiple times. for this, we will pass multiple different input values for the same query. if there are multiple queries with different input values, query is complied (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 database level.This improves the performances as compared to Statement .we can use this for dynamic queries and dynamic input values are accepted.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();
}
3.CallableStatement :-

This Statement is to be used to execute database stored procedures which resides at database.stored procedures are set of sql statements stored at database level under single name, thus improves 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 place holders.

Here the placeholders are of type IN,OUT,INOUT parameters.

IN parameters means input parameters, we can set using setXXX methods. OUT or INOUT parameters should be used by having one more method registerOutParameters which are mapped the java datatypes to database column data types.

Please leave a comment if you have any questions.