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.