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


Accessing Parameters

It is necessary to access parameters in left-to-right order and with the method that matches their type. These methods are provided for this purpose:

CallableStatement

boolean wasNull();
String getString(int parameterIndex);
boolean getBoolean(int parameterIndex);
byte getByte(int parameterIndex);
short getShort(int parameterIndex);
int getInt(int parameterIndex);
long getLong(int parameterIndex);
float getFloat(int parameterIndex);
double getDouble(int parameterIndex);
java.math.BigDecimal getBigDecimal(int parameterIndex, int scale);
byte[] getBytes(int parameterIndex);
java.sql.Date getDate(int parameterIndex);
java.sql.Time getTime(int parameterIndex);
java.sql.Timestamp getTimestamp(int parameterIndex);
Object getObject(int parameterIndex);

boolean wasNull();

When an OUT parameter has a null value, this method returns true. Note that you must call the corresponding getXXX() method before calling wasNull().

Example

Listing 7-1 illustrates how to prepare a callable statement, how to set IN parameters, how to register OUT parameters, how to execute the statement, and how to access the OUT parameters. In this example, the first parameter is an IN parameter used to pass a value to the stored procedure. The second one is an OUT parameter used to retrieve a value after the execution of the stored procedure.

Listing 7-1: Dealing with stored procedure parameters.

...
...
Connection myConnection = DriverManager.getConnection(url,
         “javauser”, “hotjava”);
CallableStatement myStmt = myConnection.prepareCall(
   “{call my_stored_procedure ?, ?}”);
myStmt.setString(1, “Hotjava”);
myStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
int res = myStmt.executeUpdate();
String outParam = myStmt.getString(2);
myStmt.close();
myConnection.close();
...
...

Dynamic SQL

A prepared statement is a SQL statement that is sent to the database prior to its execution. Unlike stored procedures, prepared statements will not remain in the database after the resources associated with it are freed. They may be called a number of times with different parameter values. Figures 7-3 and 7-4 illustrate some differences between the execution of simple SQL statements and the execution of prepared statements.


Figure 7-3:  Sending a static statement.


Figure 7-4:  Sending parameters to a prepared statement.

In Figure 7-3, the SQL text is sent to the database along with specific values and literals. This example illustrates an INSERT.

When executing a prepared statement (Figure 7-4), the SQL statement is already at the database side. Only parameter values are passed.

As Figure 7-5 shows, five steps must be followed to use prepared statements with JDBC:

1.  Prepare the SQL statement
2.  Set IN parameters
3.  Execute the statement
4.  Get the results, if any
5.  If necessary, set new IN parameter values and reexecute the statement


Figure 7-5:  Overview of prepared statements.

The last step is optional, but in many cases prepared statements are used for this facility.

Connection

PreparedStatement prepareStatement(String sql);

PreparedStatement prepareStatement(String sql);

This method is used to get a PreparedStatement object for later execution. Parameters are symbolized by “?” characters.

Passing IN Parameters

As for callable statements, prepared statement IN parameters must be set one by one. These methods are available for this purpose:

PreparedStatement

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 setBigDecimal(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();

Note that the setObject() and clearParameters() methods have the same meaning as for callable statements.

Executing the Query and Retrieving Results

Once all IN parameters are set, the execution of a prepared statement is performed as for normal statements. A prepared statement may return a count value as well as a ResultSet. Listing 7-2 shows all these steps put together.

Listing 7-2: Dealing with prepared statement parameters.

...
...
Connection myConnection = DriverManager.getConnection(url,
“javauser”, “hotjava”);
PreparedStatement myStmt = myConnection.prepareStatement(
   “UPDATE employees SET salary = ? WHERE department = ?”);
myStmt.setInt(1, 100000);
myStmt.setString(2, “Systems and Networking”);
int res = myStmt.executeUpdate();
myStmt.setInt(1, 200000);
myStmt.setString(2, “Engineering”);
res = myStmt.executeUpdate();
myStmt.setInt(1, 300000);
myStmt.setString(2, “Management”);
res = myStmt.executeUpdate();
myStmt.close();
myConnection.close();
...
...

Dealing with BLOBs

There was no way to send Binary Large Objects (BLOBs) to a database using normal SQL statements. This feature is simply not supported within normal SQL statements. Because prepared statements support the setAsciiStream(), setUnicode-Stream(), and setBinaryStream() methods of setting IN parameters, it is possible to send multikilobyte and multimegabyte values to the database.


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.