The
Statement Objects:
Creating
Statement Object:
Before you can use a Statement object to execute a SQL
statement, you need to create one using the Connection object's
createStatement( ) method, as in the following example:
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
Once you've created a Statement object, you can then
use it to execute a SQL statement with one of its three execute methods.
·
boolean execute(String SQL) :
Returns a boolean value of true if a ResultSet object can be retrieved;
otherwise, it returns false. Use this method to execute SQL DDL statements or
when you need to use truly dynamic SQL.
·
int executeUpdate(String SQL) :
Returns the numbers of rows affected by the execution of the SQL statement. Use
this method to execute SQL statements for which you expect to get a number of
rows affected - for example, an INSERT, UPDATE, or DELETE statement.
·
ResultSet executeQuery(String SQL) :
Returns a ResultSet object. Use this method when you expect to get a result
set, as you would with a SELECT statement.
Closing
Statement Obeject:
Just as you close a Connection object to save database
resources, for the same reason you should also close the Statement object.
A simple call to the close() method will do the job. If
you close the Connection object first it will close the Statement object as
well. However, you should always explicitly close the Statement object to
ensure proper cleanup.
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}
catch (SQLException e) {
. . .
}
finally {
stmt.close();
}
For a better understanding, I would suggest to study Statement.
The
PreparedStatement Objects:
The PreparedStatement interface extends the
Statement interface which gives you added functionality with a couple of
advantages over a generic Statement object.
This statement gives you the flexibility of supplying
arguments dynamically.
Creating
PreparedStatement Object:
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
All parameters in JDBC are represented by the ?
symbol, which is known as the parameter marker. You must supply values for
every parameter before executing the SQL statement.
The setXXX() methods bind values to the
parameters, where XXX represents the Java data type of the value you
wish to bind to the input parameter. If you forget to supply the values, you
will receive an SQLException.
Each parameter marker is referred to by its ordinal
position. The first marker represents position 1, the next position 2, and so
forth. This method differs from that of Java array indices, which start at 0.
All of the Statement object's methods for
interacting with the database (a) execute(), (b) executeQuery(), and (c)
executeUpdate() also work with the PreparedStatement object. However, the
methods are modified to use SQL statements that can take input the parameters.
Closing
PreparedStatement Obeject:
Just as you close a Statement object, for the same
reason you should also close the PreparedStatement object.
A simple call to the close() method will do the job. If
you close the Connection object first it will close the PreparedStatement
object as well. However, you should always explicitly close the
PreparedStatement object to ensure proper cleanup.
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
pstmt.close();
}
For a better understanding, I would suggest to study.
The
CallableStatement Objects:
Just as a Connection object creates the Statement and
PreparedStatement objects, it also creates the CallableStatement object which
would be used to execute a call to a database stored procedure.
Creating
CallableStatement Object:
Suppose, you need to execute the following Oracle
stored procedure:
CREATE OR REPLACE PROCEDURE getEmpName
(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
SELECT first INTO EMP_FIRST
FROM Employees
WHERE ID = EMP_ID;
END;
Tags:
Advanced Java