Learning Objectives
After completing this session, you will be able to:
‰ Identify java.sql.PreparedStatement and java.sql.CallableStatement
Prepared and Callable Statements
‰ PreparedStatement:SQL is sent to the database and compiled or prepared
beforehand
‰ CallableStatement:Executes SQL Stored Procedures
PreparedStatement
‰ The contained SQL is sent to the database and compiled or prepared beforehand.
‰ From this point on, the prepared SQL is sent and this step is bypassed. The more
dynamic statement requires thisstep on every execution.
‰ Depending on the DB engine, the SQL may be cached and reused even for a different
PreparedStatement and most of the work isdone by the DB engine rather than the
driver.
‰ Instances of PreparedStatement contain an SQL statement that has already been
compiled. This is what makes a statement "prepared.“
‰ The SQL statement contained in a PreparedStatement object may have one or more
IN parameters.
‰ An IN parameter is a parameter whose value is not specified when the SQL statement
is created.
‰ Instead, the statement has a question mark (?) as a placeholder for each IN
parameter.
‰ The ? is also known as a parameter marker or parameter placeholder.
‰ An application must set a value for each parameter marker in a prepared statement
before executing the prepared statement.
‰ Because PreparedStatement objects are precompiled, their execution can be faster
than that of Statement objects.
‰ Consequently, an SQL statement that is executed many times is often created as a
PreparedStatement object to increase efficiency.
‰ Being a subclass of Statement, PreparedStatement inherits all the functionality of
Statement.
‰ In addition, it adds a set of methods that are needed for setting the values to be sent
to the database in place of the placeholders for IN parameters.
‰ Also, the three methods execute, executeQuery, and executeUpdate are modified so
that they take no argument.
‰ The Statement forms of these methods (the forms that take an SQL statement
parameter) cannot be used with a PreparedStatement object.
PreparedStatement Steps
The steps of PreparedStatement object are:
1.You register the drive and create the db connection in the usual manner.
2.Once you have a db connection, create the prepared statement object.
Example:
PreparedStatement updateSales =
con.prepareStatement(“UPDATE OFFER_TBL SET
QUANTITY = ? WHERE ORDER_NUM = ? ");
// “?” are referred to as Parameter Markers
// Parameter Markers are referred to by number,
// starting from 1, in left to right order.
// PreparedStatement's setXXX() methods are used to set
// the IN parameters, which remain set until changed.
Bind in your variables. The binding in of variables is based on position.
updateSales.setInt(1, 75);
updateSales.setInt(2, 10398001);
Once all the variables have been bound, then you execute the PreparedStatement.
int iUpdatedRecords = updateSales.executeUpdate();
If AutoCommit is set to true, then once the statement is executed, the changes are committed.
From this point forth, you can just re-use the PreparedStatement object.
updateSales.setInt(1, 150);
updateSales.setInt(2,10398002);
If the PreparedStatement object is a select statement, then you execute it, and loop through the
ResultSet object in the same way as in the basic JDBC example:
PreparedStatement itemsSold =
con.prepareStatement("select o.order_num,
o.customer_num, c.name, o.quantity from order_tbl o,
customer_tbl c where o.customer_num =
c.customer_num and o.customer_num = ?;");
itemsSold.setInt(1,10398001);
ResultSet rsItemsSold = itemsSold.executeQuery();
while (rsItemsSold.next()){
System.out.println( rsItemsSold.getString(“NAME") +
"sold "+ rsItemsSold.getString(“QUANTITY") + " unit(s)");
}
CallableStatement
‰ CallableStatement is the interface used to execute SQL stored procedures.
‰ A stored procedure is a group of SQL statements that form a logical unit and perform a
particular task.
‰ Stored procedures are used to encapsulate a set of operations or queries to execute
on a database server.
‰ A CallableStatement object contains a call to a stored procedure. It does not contain
the stored procedure itself.
‰ The following first line of code creates a call to the stored procedure
SHOW_SUPPLIERS using the connection con .
‰ The part that is enclosed in curly braces is the escape syntax for stored procedures.
CallableStatement cs = con.prepareCall("{call
SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
CallableStatement Example
Here is an example using IN, OUT, and INOUT parameters.
// set int IN parameter
cstmt.setInt( 1, 333 );
// register int OUT parameter
cstmt.registerOutParameter( 2, Types.INTEGER );
// set int INOUT parameter
cstmt.setInt( 3, 666 );
// register int INOUT parameter
cstmt.registerOutParameter( 3, Types.INTEGER );
//You then execute the statement with no return value
cstmt.execute(); // could use executeUpdate()
// get int OUT and INOUT
int iOUT = cstmt.getInt( 2 );
int iINOUT = cstmt.getInt( 3 );
Stored Procedure Example
FUNCTION event_list ( appl_id_in VARCHAR2,
dow_in VARCHAR2,
event_type_in VARCHAR2 OUT,
status_in VARCHAR2 INOUT)
RETURN ref_cur;
Summary
‰ The SQL statement contained in a PreparedStatement object may have one or more
IN parameters.
‰ An application must set a value for each parameter marker in a prepared statement
before executing the prepared statement.
‰ Because PreparedStatement objects are precompiled, their execution can be faster
than that of Statement objects.
‰ Consequently, an SQL statement that is executed many times is often created as a
PreparedStatement object to increase efficiency.
‰ A CallableStatement object provides a way to call stored procedures in a standard
way for all RDBMSs.
‰ A stored procedure is stored in a database; the call to the stored procedure is what a
CallableStatement object contains.
‰ This call is written in an escape syntax that may take one of two forms: one form with
a result parameter and the otherwithout a result parameter.
‰ A result parameter, a kind of OUT parameter, is the return value for the stored
procedure.
Test Your Understanding
1.Differentiate between PreparedStatement and CallableStatement.
2.What is a significance of writing a stored procedure?
After completing this session, you will be able to:
‰ Identify java.sql.PreparedStatement and java.sql.CallableStatement
Prepared and Callable Statements
‰ PreparedStatement:SQL is sent to the database and compiled or prepared
beforehand
‰ CallableStatement:Executes SQL Stored Procedures
PreparedStatement
‰ The contained SQL is sent to the database and compiled or prepared beforehand.
‰ From this point on, the prepared SQL is sent and this step is bypassed. The more
dynamic statement requires thisstep on every execution.
‰ Depending on the DB engine, the SQL may be cached and reused even for a different
PreparedStatement and most of the work isdone by the DB engine rather than the
driver.
‰ Instances of PreparedStatement contain an SQL statement that has already been
compiled. This is what makes a statement "prepared.“
‰ The SQL statement contained in a PreparedStatement object may have one or more
IN parameters.
‰ An IN parameter is a parameter whose value is not specified when the SQL statement
is created.
‰ Instead, the statement has a question mark (?) as a placeholder for each IN
parameter.
‰ The ? is also known as a parameter marker or parameter placeholder.
‰ An application must set a value for each parameter marker in a prepared statement
before executing the prepared statement.
‰ Because PreparedStatement objects are precompiled, their execution can be faster
than that of Statement objects.
‰ Consequently, an SQL statement that is executed many times is often created as a
PreparedStatement object to increase efficiency.
‰ Being a subclass of Statement, PreparedStatement inherits all the functionality of
Statement.
‰ In addition, it adds a set of methods that are needed for setting the values to be sent
to the database in place of the placeholders for IN parameters.
‰ Also, the three methods execute, executeQuery, and executeUpdate are modified so
that they take no argument.
‰ The Statement forms of these methods (the forms that take an SQL statement
parameter) cannot be used with a PreparedStatement object.
PreparedStatement Steps
The steps of PreparedStatement object are:
1.You register the drive and create the db connection in the usual manner.
2.Once you have a db connection, create the prepared statement object.
Example:
PreparedStatement updateSales =
con.prepareStatement(“UPDATE OFFER_TBL SET
QUANTITY = ? WHERE ORDER_NUM = ? ");
// “?” are referred to as Parameter Markers
// Parameter Markers are referred to by number,
// starting from 1, in left to right order.
// PreparedStatement's setXXX() methods are used to set
// the IN parameters, which remain set until changed.
Bind in your variables. The binding in of variables is based on position.
updateSales.setInt(1, 75);
updateSales.setInt(2, 10398001);
Once all the variables have been bound, then you execute the PreparedStatement.
int iUpdatedRecords = updateSales.executeUpdate();
If AutoCommit is set to true, then once the statement is executed, the changes are committed.
From this point forth, you can just re-use the PreparedStatement object.
updateSales.setInt(1, 150);
updateSales.setInt(2,10398002);
If the PreparedStatement object is a select statement, then you execute it, and loop through the
ResultSet object in the same way as in the basic JDBC example:
PreparedStatement itemsSold =
con.prepareStatement("select o.order_num,
o.customer_num, c.name, o.quantity from order_tbl o,
customer_tbl c where o.customer_num =
c.customer_num and o.customer_num = ?;");
itemsSold.setInt(1,10398001);
ResultSet rsItemsSold = itemsSold.executeQuery();
while (rsItemsSold.next()){
System.out.println( rsItemsSold.getString(“NAME") +
"sold "+ rsItemsSold.getString(“QUANTITY") + " unit(s)");
}
CallableStatement
‰ CallableStatement is the interface used to execute SQL stored procedures.
‰ A stored procedure is a group of SQL statements that form a logical unit and perform a
particular task.
‰ Stored procedures are used to encapsulate a set of operations or queries to execute
on a database server.
‰ A CallableStatement object contains a call to a stored procedure. It does not contain
the stored procedure itself.
‰ The following first line of code creates a call to the stored procedure
SHOW_SUPPLIERS using the connection con .
‰ The part that is enclosed in curly braces is the escape syntax for stored procedures.
CallableStatement cs = con.prepareCall("{call
SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
CallableStatement Example
Here is an example using IN, OUT, and INOUT parameters.
// set int IN parameter
cstmt.setInt( 1, 333 );
// register int OUT parameter
cstmt.registerOutParameter( 2, Types.INTEGER );
// set int INOUT parameter
cstmt.setInt( 3, 666 );
// register int INOUT parameter
cstmt.registerOutParameter( 3, Types.INTEGER );
//You then execute the statement with no return value
cstmt.execute(); // could use executeUpdate()
// get int OUT and INOUT
int iOUT = cstmt.getInt( 2 );
int iINOUT = cstmt.getInt( 3 );
Stored Procedure Example
FUNCTION event_list ( appl_id_in VARCHAR2,
dow_in VARCHAR2,
event_type_in VARCHAR2 OUT,
status_in VARCHAR2 INOUT)
RETURN ref_cur;
Summary
‰ The SQL statement contained in a PreparedStatement object may have one or more
IN parameters.
‰ An application must set a value for each parameter marker in a prepared statement
before executing the prepared statement.
‰ Because PreparedStatement objects are precompiled, their execution can be faster
than that of Statement objects.
‰ Consequently, an SQL statement that is executed many times is often created as a
PreparedStatement object to increase efficiency.
‰ A CallableStatement object provides a way to call stored procedures in a standard
way for all RDBMSs.
‰ A stored procedure is stored in a database; the call to the stored procedure is what a
CallableStatement object contains.
‰ This call is written in an escape syntax that may take one of two forms: one form with
a result parameter and the otherwithout a result parameter.
‰ A result parameter, a kind of OUT parameter, is the return value for the stored
procedure.
Test Your Understanding
1.Differentiate between PreparedStatement and CallableStatement.
2.What is a significance of writing a stored procedure?
No comments:
Post a Comment