Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section has the following subsections:
This section describes Oracle extensions not related to datatypes in the JDBC 2.0 specification. This consists of additional datatype extensions as well as performance extensions.
Oracle JDBC drivers support these extensions that improve performance by reducing round trips to the database:
TABLE_REMARKS
columns avoids an expensive outer join operation.
Oracle supports several extensions to connection properties objects to support these performance extensions. The properties object extensions enable you to set the remarksReporting
flag and default values for prefetching and update-batching. For more information, see "Oracle Extensions for Connection Properties".
Oracle JDBC drivers allow you to set the number of rows to prefetch into the client while a result set is being populated during a query. This feature reduces the number of round trips to the server.
Standard JDBC receives the result set one row at a time, and each row requires a round trip to the database. The row prefetching feature associates an integer row-prefetch setting with a given statement object. JDBC fetches that number of rows at a time from the database during the query. That is, JDBC will fetch N rows that match the query criteria and bring them all back to the client at once, where N is the prefetch setting. Then, once your next()
calls have run through those N rows, JDBC will go back to fetch the next N rows that match the criteria.
You can set the number of rows to prefetch for a particular Oracle statement (any type of statement). You can also reset the default number of rows that will be prefetched for all statements in your connection. The default number of rows to prefetch to the client is 10.
Set the number of rows to prefetch for a particular statement as follows:
OracleStatement
, OraclePreparedStatement
, or OracleCallableStatement
object, as applicable, if it is not already one of these.
setRowPrefetch()
method of the statement object to specify the number of rows to prefetch, passing in the number as an integer. If you want to check the current prefetch number, use the getRowPrefetch()
method of the Statement object, which returns an integer.
Set the default number of rows to prefetch for all statements in a connection as follows:
Connection
object to an OracleConnection
object.
setDefaultRowPrefetch()
method of your OracleConnection
object to set the default number of rows to prefetch, passing in an integer that specifies the desired default. If you want to check the current setting of the default, then use the getDefaultRowPrefetch()
method of the OracleConnection
object. This method returns an integer.
There is no maximum prefetch setting, but empirical evidence suggests that 10 is effective. Oracle does not recommend exceeding this value in most situations. If you do not set the default row prefetch number for a connection, 10 is the default.
A statement object receives the default row prefetch setting from the associated connection at the time the statement object is created. Subsequent changes to the connection's default row prefetch setting have no effect on the statement's row prefetch setting.
If a column of a result set is of datatype LONG
or LONG RAW
(that is, the streaming types), JDBC changes the statement's row prefetch setting to 1, even if you never actually read a value of either of those types.
If you use the form of the DriverManager
class getConnection()
method that takes a Properties
object as an argument, then you can set the connection's default row prefetch value that way. See "Specifying a Database URL and Properties Object" and "Oracle Extensions for Connection Properties" for more information about the Properties object and connection properties.
The following example illustrates the row prefetching feature. It assumes you have imported the oracle.jdbc.driver.*
classes.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger"); //Set the default row prefetch setting for this connection ((OracleConnection)conn).setDefaultRowPrefetch(7); /* The following statement gets the default row prefetch value for the connection, that is, 7. */ Statement stmt = conn.createStatement(); /* Subsequent statements look the same, regardless of the row prefetch value. Only execution time changes. */ ResultSet rset = stmt.executeQuery("SELECT ename FROM emp"); System.out.println( rset.next () ); while( rset.next () ) System.out.println( rset.getString (1) ); //Override the default row prefetch setting for this statement ( (OracleStatement)stmt ).setRowPrefetch (2); ResultSet rset = stmt.executeQuery("SELECT ename FROM emp"); System.out.println( rset.next () ); while( rset.next() ) System.out.println( rset.getString (1) ); stmt.close();
Oracle JDBC drivers allow you to accumulate inserts and updates of prepared statements at the client and send them to the server in batches, reducing round trips to the server. You might want to do this when you are repeating the same statement with different bind variables.
Normally JDBC makes a round trip to the database to execute a prepared statement whenever the statement's executeUpdate()
method is called. The Oracle update-batching feature, however, associates a batch value with each prepared statement object. Oracle JDBC accumulates execution requests for the prepared statement, then automatically passes them all to the database for execution once the batch value is reached.
You can use update batching with CallableStatement
s except when the CallableStatement
has OUT
parameters. In this case, the driver automatically overrides any previous batch value and resets it to 1.
Do not use the addBatch()
and executeBatch()
methods of the JDBC 2.0 PreparedStatement
interface. These methods are not consistent with the functionality offered by the methods associated with the OraclePreparedStatement
.
Regardless of the batch value of an Oracle prepared statement, if any of the bind variables of the statement is (or becomes) a streaming type, then JDBC sets the batch value to 1 and sends any queued requests to the database for execution.
JDBC automatically executes the statement's sendBatch()
method whenever the connection receives a commit request, the statement receives a close request, or the connection receives a close request.
If you use the form of the DriverManager
.getConnection()
method that takes a Properties
object as an argument, then you can set the connection's default batch value in the object. See "Oracle Extensions for Connection Properties" for more information about Properties
objects.
The default batch update value is 1.
You can set the batch value for any individual Oracle prepared statement by applying it to the OraclePreparedStatement
object. The batch value that you set for an individual statement overrides the value set for the connection. You can also set a default batch value that will apply to any Oracle prepared statement in your Oracle connection by applying it to the OracleConnection
object.
Follow these steps to apply the Oracle batch value feature for a particular prepared statement:
PreparedStatement ps = conn.prepareStatement ("INSERT INTO dept VALUES (?,?,?)"); ps.setInt (1,12); ps.setString (2,"Oracle"); ps.setString (3,"USA");
OraclePreparedStatement
object and apply the setDefaultExecuteBatch()
method. In this example, the default batch size of the statement is set to 2.
((OraclePreparedStatement)ps).setDefaultExecuteBatch(2);
If you wish, insert the getExecuteBatch()
method at any point in the program to check the default batch value for the statement:
System.outPrintln (" Statement Execute Batch Value " +((OraclePreperedStatement)ps).getExecuteBatch());
executeUpdate()
will return 0.
// No data is sent to the database by this call to executeUpdate System.out.println ("Number of rows updated so far: "+ ps.executeUpdate ());
executeUpdate()
will be equal to the batch value of 2. The data will be sent to the database and both rows will be inserted in a single round trip.
ps.setInt (1, 11); ps.setString (2, "Applications"); ps.setString (3, "Indonesia"); int rows = ps.executeUpdate (); System.out.println ("Number of rows updated now: " + rows); ps.close ();
If you want to execute accumulated statements before the batch value is reached, then use the sendBatch()
method of the OraclePreparedStatement
object. For example:
OracleConnection
object and apply the setDefaultExecuteBatch()
method for the connection. This example sets the default batch for all statements in the connection to 50.
((OracleConnection)conn).setDefaultExecuteBatch (50);
PreparedStatement ps = conn.prepareStatement ("insert into dept values (?, ?, ?)"); ps.setInt (1, 32); ps.setString (2, "Oracle"); ps.setString (3, "USA"); System.out.println (ps.executeUpdate ());
The execute update does not happen at this point. The ps.executeUpdate()
method returns "0".
executeUpdate()
, the data will still not be sent to the database since the batch default value for the statement is the same as for the connection: 50.
ps.setInt (1, 33); ps.setString (2, "Applications"); ps.setString (3, "Indonesia"); // this execute does not actually happen at this point int rows = ps.executeUpdate (); System.out.println ("Number of rows updated before calling sendBatch: " + rows);
Note that the value of rows
in the println
statement is "0".
sendBatch()
method at this point, then the two previously batched executes will be sent to the database in a single round trip. The sendBatch()
method also returns the number of updated rows. This property of sendBatch()
is used by println
to print the number of updated rows.
// Execution of both previously batched executes will happen // at this point. The number of rows updated will be // returned by sendBatch. rows = ((OraclePreparedStatement)ps).sendBatch (); System.out.println ("Number of rows updated by calling sendBatch: " + rows); ps.close ();
You can specify a default batch value for any Oracle prepared statement in your Oracle connection. To do this, set the setDefaultExecute()
method on the OracleConnection
object. For example, the following statement sets the default batch value for all prepared statements belonging to the conn
connection object to 20:
((OracleConnection)conn).setDefaultExecuteBatch(20);
Even though this sets the default batch value for all of the prepared statements belonging to the connection, you can override it by calling setDefaultBatch()
on individual statements.
The getExecuteBatch()
method enables you to check the current setting of the default batch value for a specific Oracle prepared statement object or for all of the prepared statements that belong to the Oracle connection. For example:
Integer batch_val = ((OraclePreparedStatement)ps).getExecuteBatch();
OR
Integer batch_val = ((OracleConnection)conn).getDefaultExecuteBatch();
The following example illustrates how you use the Oracle update batching feature. It assumes you have imported the oracle.jdbc.driver.*
classes.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger"); PreparedStatement ps = conn.prepareStatement("insert into dept values (?, ?, ?)"); //Change batch size for this statement to 3 ((OraclePreparedStatement)ps).setExecuteBatch (3); ps.setInt(1, 23); ps.setString(2, "Sales"); ps.setString(3, "USA"); ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 24); ps.setString(2, "Blue Sky"); ps.setString(3, "Montana"); ps.executeUpdate(); //JDBC queues this for later execution ps.setInt(1, 25); ps.setString(2, "Applications"); ps.setString(3, "India"); ps.executeUpdate(); //The queue size equals the batch value of 3 //JDBC sends the requests to the database ps.setInt(1, 26); ps.setString(2, "HR"); ps.setString(3, "Mongolia"); ps.executeUpdate(); //JDBC queues this for later execution ((OraclePreparedStatement)ps).sendBatch(); //JDBC sends the queued request ps.close();
Oracle JDBC drivers enable you to inform the driver of the types of the columns in an upcoming query, saving a round trip to the database that would otherwise be necessary to describe the table.
When standard JDBC performs a query, it first uses a round trip to the database to determine the types that it should use for the columns of the result set. Then, when JDBC receives data from the query, it converts the data, as necessary, as it populates the result set.
When you specify column types for a query, you avoid the first round trip to the database. The server, which is optimized to do so, performs any necessary type conversions.
To use this feature, you must specify a datatype for each column of the expected result set. If the number of columns for which you specify types does not match the number of columns in the result set, the process fails with a SQLException
.
You cannot define column types for objects or object references.
Following these general steps to redefine column types for a query:
OracleStatement
, OraclePreparedStatement
, or OracleCallableStatement
object, as applicable, if it is not already one of these.
clearDefines()
method of your Statement
object to clear any previous column definitions for this Statement
object.
This is according to oracle.jdbc.driver.OracleTypes
for Oracle-specific types, and according to either java.sql.Types
or OracleTypes
for standard types (constants for standard types have the same value in Types
and OracleTypes
).
defineColumnType()
, method of your Statement
object, passing it these parameters:
Use the static constants of the java.sql.Types
class or, for Oracle-specific types, the static constants of the oracle.jdbc.driver.OracleTypes
class (such as Types.INTEGER
, Types.FLOAT
, Types.VARCHAR
, OracleTypes.VARCHAR
, and OracleTypes.ROWID
.).
For example, assuming stmt
is an Oracle statement, use this syntax:
stmt.defineColumnType(column_index, type);
OR
stmt.defineColumnType(column_index, type, max_size);
Set maximum field size if you do not want to receive the full default length of the data. Less data than this maximum size will be returned if the maximum field size is set to a smaller value using the setMaxFieldSize()
method of the standard JDBC Statement
class, or if the natural maximum size of the datatype is smaller. Specifically, the size of the data returned will be the minimum of:
Once you complete these steps, use the statement's executeQuery()
method to perform the query.
The following example illustrates the use of this feature. It assumes you have imported the oracle.jdbc.driver.*
classes.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger"); Statement stmt = conn.createStatement(); /*Ask for the column as a string: *Avoid a round trip to get the column type. *Convert from number to string on the server. */ ((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR); ResultSet rset = stmt.executeQuery("select empno from emp"); while (rset.next() ) System.out.println(rset.getString(1)); stmt.close();
As this example shows, you must cast the statement (stmt
) to type OracleStatement
in the invocation of the defineColumnType()
method. The connection's createStatement()
method returns an object of type java.sql.Statement
, which does not have the defineColumnType()
and clearDefines()
methods. These methods are provided only in the OracleStatement
implementation.
The define-extensions use JDBC types to specify the desired types. The allowed define types for columns depend on the internal Oracle type of the column.
All columns can be defined to their "natural" JDBC types; in most cases, they can be defined to Types.CHAR
or Types.VARCHAR
.
Table 4-6 lists the valid column definition arguments you can use in the defineColumnType()
method.
The getColumns()
, getProcedureColumns()
, getProcedures()
, and getTables()
methods of the database metadata classes are slow if they must report TABLE_REMARKS
columns, because this necessitates an expensive outer join. For this reason, the JDBC driver does not report TABLE_REMARKS
columns by default.
You can enable TABLE_REMARKS
reporting by passing a TRUE
argument to the setRemarksReporting()
method of an OracleConnection
object.
If you are using a standard java.sql.Connection
object, you must cast it to OracleConnection
to use setRemarksReporting()
.
Assuming conn
is the name of your standard Connection
object, the following statement enables TABLE_REMARKS
reporting.
( (oracle.jdbc.driver.OracleConnection)conn ).setRemarksReporting(true);
According to JDBC versions 1.1 and 1.2, the methods getProcedures()
and getProcedureColumns()
treat the catalog
, schemaPattern
, columnNamePattern
and procedureNamePattern
parameters in the same way. In the Oracle definition of these methods, the parameters are treated differently:
catalog
: Oracle does not have multiple catalogs, but it does have packages. Consequently, the catalog
parameter is treated as the package name. This applies both on input (the catalog
parameter) and output (the catalog
column in the returned ResultSet
). On input, the construct "
"
(the empty string) retrieves procedures and arguments without a package, that is, stand-alone objects. A null
value means to drop from the selection criteria, that is, return information about both stand-alone and packaged objects (same as passing in "%
"). Otherwise the catalog
parameter should be a package name pattern (with SQL wild cards, if desired).
schemaPattern
: All objects within Oracle must have a schema, so it does not make sense to return information for those objects without one. Thus, the construct "
"
(the empty string) is interpreted on input to mean the objects in the current schema (that is, the one to which you are currently connected). To be consistent with the behavior of the catalog
parameter, null
is interpreted to drop the schema from the selection criteria (same as passing in "%
"). It can also be used as a pattern with SQL wild cards.
procedureNamePattern
and columnNamePattern
: The empty string (" ") does not make sense for either parameter, because all procedures and arguments must have names. Thus, the construct "
"
will raise an exception. To be consistent with the behavior of other parameters, null
has the same effect as passing in "%
".
One of the forms of the DriverManager.getConnection()
method enables you to specify a URL and a properties object:
getConnection(String URL, Properties info);
where the URL is of the form:
jdbc:oracle:<drivertype
>:@<database
>
In addition to the URL, you use an object of the standard Java Properties
class as input. For example:
java.util.Properties info = new java.util.Properties(); info.put ("user", "scott"); info.put ("password"
,"tiger"
); getConnection ("jdbc:oracle:oci8:",info);
Table 4-7 lists the connection properties that Oracle JDBC drivers support, including the Oracle extensions for defaultRowPrefetch
, remarksReporting
, and defaultBatchValue
.
The following example shows how to use the java.util.Properties.put()
method to set performance extension options before connection to the database.
//import packages and register the driver import java.sql.*; import java.math.*; DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); //specify the properties object java.util.Properties info = new java.util.Properties(); info.put("user", "scott"); info.put ("password", "tiger"); info.put ("defaultRowProfetch","20"); info.put ("defaultBatchValue", 5); //specify the connection object Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@database",info);
Oracle JDBC drivers support the Oracle-specific datatypes ROWID
and REF
CURSOR
, which were introduced in Oracle7 and are not part of the standard JDBC specification.
ROWID
is supported as a Java string and REF
CURSOR
as a JDBC result set.
A ROWID
is an identification tag that is unique for each row of an Oracle database table. ROWID
can be thought of as a virtual column, containing the ID for each row.
The oracle.sql.ROWID
class is supplied as a wrapper for ROWID
SQL data.
ROWID
s provide functionality similar to the java.sql.ResultSet.getCursorName()
and java.sql.Statement.setCursorName()
JDBC methods, which are not supported by the Oracle implementation.
If you include the ROWID
pseudo-column in a query, then you can retrieve the ROWID
s with the ResultSet.getString()
method (passing in either the column index or the column name). You can also bind a ROWID
to a PreparedStatement
parameter with the setString()
method. This allows in-place updates, as in the example that immediately follows.
The following example shows how to access and manipulate ROWID
data.
Statement stmt = conn.createStatement(); // Query the employee names with "FOR UPDATE" to lock the rows. // Select the ROWID to identify the rows to be updated. ResultSet rset = stmt.executeQuery ("SELECT ename, rowid FROM emp FOR UPDATE"); // Prepare a statement to update the ENAME column at a given ROWID PreparedStatement pstmt = conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?"); // Loop through the results of the query while (rset.next ()) { String ename = rset.getString (1); oracle.sql.ROWID rowid = rset.getROWID (2); // Get the ROWID as a String pstmt.setString (1, ename.toLowerCase ()); pstmt.setROWID (2, rowid); // Pass ROWID to the update statement pstmt.executeUpdate (); // Do the update }
A cursor variable holds the memory location (address) of a query work area rather than the contents of the area. So, declaring a cursor variable creates a pointer. In SQL, a pointer has the datatype REF
x
where REF
is short for REFERENCE
and x
represents the entity that is being referenced. "REF
CURSOR
", then, identifies a reference to a cursor variable. Since many cursor variables might exist to point to many work areas, REF
CURSOR
can be thought of as a category or "datatype specifier" that identifies many different cursor variables.
To create a cursor variable, begin by identifying a user-defined type that belongs to the REF
CURSOR
category. For example:
DECLARE TYPE DeptCursorTyp IS REF CURSOR
Then create the cursor variable by declaring it to be of the user-defined type DeptCursorTyp
:
dept_cv DeptCursorTyp - - declare cursor variable ...
A REF
CURSOR
, then, is a category of datatype rather than a datatype.
Stored procedures can return user-defined types, or cursor variables, of the REF
CURSOR
category. This output is equivalent to a database cursor or a JDBC result set. A REF
CURSOR
essentially encapsulates the results of a query.
In JDBC, REF
CURSOR
s are materialized as ResultSet
objects and can be accessed like this:
REF
CURSOR
.
getCursor()
method of the OracleCallableStatement
class to materialize the REF
CURSOR
as a JDBC ResultSet
object.
This example shows how to access REF
CURSOR
data.
import oracle.jdbc.driver.*; ... CallableStatement cstmt; ResultSet cursor; // Use a PL/SQL block to open the cursor cstmt = conn.prepareCall ("begin open ? for select ename from emp; end;"); cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.execute(); cursor = ((OracleCallableStatement)cstmt).getCursor(1); // Use the cursor like a normal ResultSet while (cursor.next ()) {System.out.println (cursor.getString(1));}
In the preceding example:
CallableStatement
object is created by using the prepareCall()
method of the connection class.
REF
CURSOR
.
REF
CURSOR
is OracleTypes.CURSOR
.
REF
CURSOR
.
CallableStatement
object is cast to an OracleCallableStatement
object to use the getCursor()
method, which is an Oracle extension to the standard JDBC API, and returns the REF
CURSOR
into a ResultSet
object.
For a full sample application using a REF
CURSOR
, see "REF CURSOR Sample".