Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click Here!
Click Here!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Chapter 7:
Advanced Techniques

In This Chapter

This chapter introduces and shows how to deal with advanced techniques of Structured Query Language (SQL) and Java Database Connectivity (JDBC) that add a professional touch to applications:

  Handling stored procedures from JDBC
  Dynamic SQL
  Fetching database metadata
  Dynamic data access

This chapter discusses advanced techniques supported by JDBC. These techniques may prove to be very powerful when developing professional applications or applets. Some database management system (DBMS) and JDBC drivers may not support all these techniques.

Callable Statements

Callable statements are SQL statements that invoke stored procedures, which are also called database Remote Procedure Calls (RPCs). The RPC is popular in the Unix system programming world. It is used here in the sense of invoking remote code that is stored in the database — stored procedures. Such procedures, when supported by the DBMS, allow storing of user statements containing SQL text in the database. These procedures are usually stored for reuse from user session to user session. They are useful for embedding application logic at the database side. Figure 7-1 illustrates the invocation mechanism for stored procedures.


Figure 7-1:  Invoking a stored procedure.

Statements that invoke stored procedures should use the JDBC CallableStatement class. A dedicated method must be called to prepare the callable statement. The usual methods are then used to execute the statement.

Connection

CallableStatement prepareCall(String sql);

where the argument is of the form:

“{? =call stored_procedure_name ?, ?, ....}”

CallableStatement prepareCall(String sql);

This method prepares a callable statement. It returns a CallableStatement object. Why something more elaborate than a simple Statement object is needed is discussed later.

Stored procedures may return multiple result types because they may be composed of SQL statements that return diverse result types: result sets and update counts. The usual methods are used to retrieve these results. However, when a procedure returns both multiple results and OUT parameter values, the OUT parameters should be retrieved last. Stored procedures may be called with parameters. They provide maximum flexibility by allowing values to be passed from and to the user’s application. There are two types of such parameters: IN and OUT. IN parameters pass data to the stored procedure, and OUT parameters are values returned by the procedure code. Special JDBC methods exist to set and access these parameters. As Figure 7-2 shows, setting IN values and registering OUT parameters must be done before the callable statement is executed. Figure 7-2 illustrates how to handle the parameters of callable statements.


Figure 7-2:  Overview of dealing with parameters.

Once the statement has been executed, all its OUT parameters may be explored, one by one, in left-to-right order. Note that Figure 7-2 illustrates the processing that must be done for a stored procedure that does not return ResultSets. If it returns a ResultSet, we would simply add a loop to fetch the result set before accessing the OUT parameters.

Setting Parameters

IN and OUT parameters must be set or registered prior to executing a callable statement.

IN Parameters

IN parameters receive a value from the user’s application. They are set via setXXX() methods that take two arguments: the parameter index, beginning at 1, and the value to set. The following methods are used to set values corresponding to their parameter’s specific type.

CallableStatement

void setNull(int parameterIndex, int sqlType);
void setBoolean(int parameterIndex, boolean x);
void setByte(int parameterIndex, byte x);
void setShort(int parameterIndex, short x);
void setInt(int parameterIndex, int x);
void setLong(int parameterIndex, long x);
void setFloat(int parameterIndex, float x);
void setDouble(int parameterIndex, double x);
void set BigDecimal(int parameterIndex, java.math.BigDecimal x);
void setString(int parameterIndex, String x);
void setBytes(int parameterIndex, byte x[]);
void setDate(int parameterIndex, java.sql.Date x);
void setTime(int parameterIndex, java.sql.Time x);
void setTimestamp(int parameterIndex, java.sql.Timestamp x);
void setAsciiStream(int parameterIndex, java.io.InputStream x, int length);
void setUnicodeStream(int parameterIndex, java.io.InputStream x, int length);
void setBinaryStream(int parameterIndex, java.io.InputStream x, int length);
void setObject(int parameterIndex, Object x);
void setObject(int parameterIndex, Object x, int targetSqlType);
void setObject(int parameterIndex, Object x, int targetSqlType, int scale);
void clearParameters();

void setObject(...);

The setObject() methods belong to advanced JDBC features. They allow given Java objects to be stored in the database. However, they are converted to the database target SQL data type before they are actually sent to the database. Note that it is possible to pass database specific abstract data types by using a driver specific Java type and using a targetSqlType of java.sql.types.OTHER with the setObject(int parameterIndex, Object x, int targetSqlType) and the setObject(int parameterIndex, Object x, int targetSqlType, int scale) methods.

void clearParameters();

Normally, parameter values remain unaffected for repeated use of a statement. When invoked, this method immediately releases the resources used by the current parameters, and their values are cleared.

OUT Parameters

OUT parameters must be registered prior to executing the callable statement. This registration is the way to specify their type. The following methods are available to register OUT parameters:

CallableStatement

void registerOutParameter(int parameterIndex, int sqlType);
void registerOutParameter(int parameterIndex, int sqlType, int scale);

void registerOutParameter(int parameterIndex, int sqlType);

The first argument is the parameter index, beginning at 1. The type argument must be defined in java.sql.Types.

void registerOutParameter(int parameterIndex, int sqlType, int scale);

This method is used to register OUT parameters of type SQL numeric or decimal. The scale argument represents the desired number of digits to the right of the decimal point.


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.