JDBC CS creating ResultSet

Creating ResultSet with CallableStatement object methods?

All the prepareCall() methods in
the java.sql.Connection interface
can be used as a start to create a ResutSet, but only two of them
gives you the opportunity to update the DB table through the ResultSet.

When you use this method, the returned CallableStatement
object can be used to query for a ResultSet (This require that
the database-stored procedure must return a DB table)
that can be updateable and can be scrollable.

This will create a CallableStatement object as the previous one.
Use this method when you also want to decide whether
you want the resultset to stay open (resultSetHoldability) after a transaction is committed.
(Only available in JDBC 3.0)

Before you creates a ResultSet with the executeQuery() method
in the CallableStatement object you have to decide what kind of
connection you want. That is done by the parameters of the
prepareCall() method.

The first parameter is about whether the changes made by others
in the database table should be reflected
in your ResultSet and about which type of ResultSet scrolling you want.

Optional constant for the first parameter of the
prepareCall() method is:

Constants in ResultSet interface

Description

java.sql.ResultSet.TYPE_SCROLL_SENSITIVE

This constant indicates that the ResultSet
can be scrolled both forward and backward and
that the ResultSet is sensitive to changes made by others.

java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE

This constant indicates that the ResultSet
can be scrolled both forward and backward and
that the ResultSet is NOT sensitive to changes made by others.

java.sql.ResultSet.TYPE_FORWARD_ONLY

This constant indicates that the ResultSet
can be scrolled only forward.

The second parameter is about the type of access the
ResultSet should have to the underlying database table.

Optional constant for the second parameter of the
prepareCall() method is:

Constants in ResultSet interface

Description

java.sql.ResultSet.CONCUR_READ_ONLY

The constant indicating the concurrency
mode for a ResultSet object that may NOT be updated.

java.sql.ResultSet.CONCUR_UPDATABLE

The constant indicating the concurrency
mode for a ResultSet object that may be updated.

The third parameter is about keeping the ResultSet open after
committing.

There is two resultSetHoldability option to use:

Constants in ResultSet interface

Description

ResultSet.HOLD_CURSORS_OVER_COMMIT

The constant indicating that open ResultSet
objects with this holdability will remain
open when the current transaction is commited.

ResultSet.CLOSE_CURSORS_AT_COMMIT

The constant indicating that open ResultSet
objects with this holdability will be closed
when the current transaction is commited.

After you have deside which type of ResultSet you want to create you
can create the CallableStatement object
with one of the prepareCall() method
using your SQL SELECT statement that may also contains parametric placeholders for
values.

If you have any parametric placeholders for
values in your SQL statement, then it is time to
set these using the CallableStatement
setXXX() methods.

The last ting to do is to retrieve the ResultSet with the method:

Method in CallableStatement interface

Description

ResultSet executeQuery()

Executes the SQL procedure call statement
in this CallableStatement object and returns the ResultSet object
returned by the query contained in the database procedure.

Example using CallableStatement and ResultSet with a DriverManager connection: