ResultSet Processing Retrieving Result

The ResultSet object is actually a tubular data set; that is, it consists of rows of data organized in uniform columns. In JDBC, the Java program can see only one row of data at one time. The program uses the next() method to go to the next row. JDBC does not provide any methods to move backwards along the ResultSet or to remember the row positions (called bookmarks in ODBC). After the program has a row, it can use the positional index (1 for the first column, 2 for the second column, and so on) or the column name to get the field value by using the getxxx() methods.

CallableStatement Object

For a secure, consistent and manageable multi-tier Client/Server systems, the data access should be allow the use of stores procedures. Stored procedures centralize the business logic in terms of manageability and also in terms of running the query. Java applets running on clients with limited resources cannot be expected to run huge queries. But the results are important to those clients. JDBC allows the use of stored procedures by the CallableStatement class and with the eascape clause string.

            A CallableStatement object is created by the prepareCall() method in the Connection object. The prepareCall() method takes a sting as the parameter. This string, called as escape clause, is of the form:

{[?=] call <stored procedure name> [<parameters>, <parameters>,..]}

The CallableStatement class supports parameters. These parameters are of the OUT kind from a stored procedures or the IN kind to pass values into a stored procedure. The parameters marker (question mark ?) must be used for the return value (if any) and any output arguments because the parameter marker is bound to a program variable in the stored procedure. Input arguments can be either literals or parameters. For a dynamic parameterized statement, the escape clause string takes the from:

{[?=] call <strored procedure name> [<?>,<?>,…]}

            The OUT parameters should be registerd using the registerOutparameter() method before the call to the executeQuery(), executeUpdate() or execute() methods.

            After the stored procedure is executed, the DBMS returns the result value to the JDBC driver. This return value is accessed by the Java program using the methods.

As you can see, JDBC has minimized the complexities of getting results from a stored procedure.

PreparedStatement Object

In the case of a PreparedStatement object, as the name implies the application program prepares a SQL statement using the java.sql.Connection.preapreStatement() method. The prepareStatement() method takes an SQL string, which is passed to the underlying DBMS. The DBMS goes through the syntax run, query plan optimization and the execution plan generation stages, but does not execute the SQL statement. Possibly, it returns the handle to the optimized execution plan that the JDBC driver stores internally in the PreparedStatement object.

            The method of the PreparedStatement object are executeQuery(), executeUpdate(), and execute(). Notice that the methods do not take any parameters. They are just calls to the underlaying DBMS to perform the already optimized SQL statement.

            One of the major features of a PreparedStatement is that it can handle IN types of parameters. The parameters are indicated in an SQL statement by placing the ‘?’ (question mark) as the parameter marker instead of actual values.

            In the case of the PreparedStatement, the driver actually sends only the execution plan ID and the parameters to the DBMS. This results in less network traffic and is well-suited for Java application on the Internet. The PrepatedStatement should be used when you need to execute the SQL statement many times in a Java application.

            But remember, even though the optimized execution plan is available during the execution of a Java program, the DBMS discards the execution plan at the end of the program. So, the DBMS must go through all of the steps of creating an execution plan every time the program runs. The PreparedStatement object achieves faster SQL execution performance than the simple Statement object, as the DBMS does not have to run through the steps of creating the execution plan.

Statement Object

A Statement object is created using the createStatement() method in the Connection object.

The most important methods are executeQuery(), executeUpdate(), and execute(). As you create a Statement object with a SQL statement, the executeQuery() method takes an SQL string. It passes the SQL string to the underlying data source through the driver manager and gets the ResultSet back to the application program. The executeQuery() method returns only one RecordSet(). For those cases that returns more than one ResultSet, the execute() method should be used.

For SQL statements that do not retun a ResultSet like the UPDATE, DELETE, and DDL, statements, the Statement object ha the executeUpdate() method that takes a SQL string and returns an integer. This integer indicates the number of rows that are affected by the SQL statement.

            The Statement object is best suited for ad-hoc SQL statements or SQL statements or SQL statements that are executed once.

JDBC Statements

         A Java application program first builds the SQL statement in a string buffer and passes this to the underlying DBMS through some API calls. An SQL statement needs to be verified syntactically, optimized, and converted to an executable form before execution. In the Call Level Interface (CLI) Application Program Interface (API) model, the application program through the driver passes the SQL statement to the underlying DBMS, which prepares and executes the SQL statement.

After the DBMS recieves the SQL string buffer, it parses the statement and does a syntax check run. If the statement is not syntactically correct, the system returns an error condition to the driver, which generates an SQLException. If the statement is syntactically correct, depending  on the DBMS, many query plans are usually generated that are run through an optinizer. Then the optimum plan is translated into a binary execution plan. After the execution plan is prepared, the DBMS usually returns a handler or identifier to this optimized binary version of the SQL statement to the application program.

      The three JDBC statement types (Statement, PreparedStatement and CallableStatement) differ in the timing of the SQL statement preparation and thie statement execution. In the case of the simple Statement object, the SQL ie prepared and executed in one step at least from the application program point of view, (Internally, the driver might get the identifier, command the DBMS to execute the query, and then discard the handle).

In the case ofa PreparedStatement object , the driver stores the execution plan handle for later use. In the case of the CallableStatement object, the SQL statement is actually making a call to a stored procedure that is usually already optimized.