Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
This chapter describes data access in oracle.sql.*
formats, as opposed to standard Java formats. As described in the previous chapter, the oracle.sql.*
formats are a key factor of the Oracle JDBC extensions, offering significant advantages in efficiency and precision in manipulating SQL data.
Using oracle.sql.*
formats involves casting your result sets and statements to OracleResultSet
, OracleStatement
, OraclePreparedStatement
, and OracleCallableStatement
objects, as appropriate, and using the getOracleObject()
, setOracleObject()
, getXXX()
, and setXXX()
methods of these classes (where XXX
corresponds to the types in the oracle.sql
package).
This chapter covers the following topics:
When JDBC programs retrieve SQL data into Java, you can use standard Java types, or you can use types of the oracle.sql
package. The classes in this package simply wrap the raw SQL data.
In processing speed and effort, the oracle.sql.*
classes provide the most efficient way of representing SQL data. These classes store the usual representations of SQL data as byte arrays. They do not reformat the data or perform any character-set conversions (aside from the usual network conversions) on it. The data remains in SQL format, and therefore no information is lost. For SQL primitive types (such as NUMBER
, and CHAR
), the oracle.sql.*
classes simply wrap the SQL data. For SQL structured types (such as objects and arrays), the classes provide additional information such as conversion methods and structure details.
If you are moving data within the database, then you will probably want to keep your data in oracle.sql.*
format. If you are displaying the data or performing calculations on it in a Java application running outside the database, then you will probably want to materialize the data as instances of standard types such as java.sql.*
or java.lang.*
types. Similarly, if you are using a parser that expects the data to be in a standard Java format, then you must use one of the standard formats instead of oracle.sql.*
format.
Java represents a SQL NULL
datum by the Java value null
. Java datatypes fall into two categories: primitive types (such as byte
, int
, float
) and object types (class instances). The primitive types cannot represent null
. Instead, they store the null as the value zero (as defined by the JDBC specification). This can lead to ambiguity when you try to interpret your results.
In contrast, Java object types can represent null
. The Java language defines an object wrapper type corresponding to every primitive type (for example, Integer
for int
, Float
for float
) that can represent null
. The object wrapper types must be used as the targets for SQL data to detect SQL NULL
without ambiguity.
The JDBC Statement
object returns an OracleResultSet
object, typed as a java.sql.ResultSet
. If you want to apply only standard JDBC methods to the object, keep it as a ResultSet
type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleResultSet
type. Although the type by which the Java compiler will identify the object is changed, the object itself is unchanged.
For example, assuming you have a standard Statement
object stmt
, do the following if you want to use only standard JDBC ResultSet
methods:
ResultSet rs = stmt.executeQuery("SELECT * FROM emp");
If you need the extended functionality provided by the Oracle extensions to JDBC, you can select the results into a standard ResultSet
object, as above, and then cast that object into an OracleResultSet
object later.
Similarly, when you want to execute a stored procedure using a callable statement, the JDBC drivers will return an OracleCallableStatement
object typed as a java.sql.CallableStatement
. If you want to apply only standard JDBC methods to the object, then keep it as a CallableStatement
type. However, if you want to use the Oracle extensions on the object, you must cast it to an OracleCallableStatement
type. Although the type by which the Java compiler will identify the object is changed, the object itself is unchanged.
You use the standard JDBC java.sql.Connection.prepareStatement()
method to create a PreparedStatement
object. If you want to apply only standard JDBC methods to the object, keep it as a PreparedStatement
type. However, if you want to use the Oracle extensions on the object, you must cast it to an OraclePreparedStatement
type. While the type by which the Java compiler will identify the object is changed, the object itself is unchanged.
Key extensions to the result set and statement classes include getOracleObject()
and setOracleObject()
methods that you can use to access and manipulate data in oracle.sql.*
formats, instead of standard Java formats. For more information, see the next section: "Comparison of Oracle get and set Methods to Standard JDBC".
This section describes get
and set
methods, particularly the JDBC standard getObject()
and setObject()
methods and the Oracle-specific getOracleObject()
and setOracleObject()
methods, and how to access data in oracle.sql.*
format compared with Java format.
Although there are specific getXXX()
methods for all the Oracle SQL types (as described in "Other getXXX() Methods"), you can use the general get
methods for convenience or simplicity, or if you are not certain in advance what type of data you will receive.
The standard JDBC getObject()
method of a result set or callable statement returns data into a java.lang.Object
object. The format of the data returned is based on its original type, as follows:
getObject()
returns the default Java type corresponding to the column's SQL type, following the mapping specified in the JDBC specification.
ROWID
, discussed in "Oracle ROWID Type"), getObject()
returns an object of the appropriate oracle.sql.*
class (such as oracle.sql.ROWID
).
getObject()
returns an object of the Java class specified in your type map. (Type maps specify the correlation between Java classes and database SQL types and are discussed in "Understanding Type Maps for SQLData Implementations".) The getObject(
parameter_index
)
method uses the connection's default type map. The getObject(
parameter_index
,
map
)
enables you to pass in a type map. If the type map does not provide a mapping for a particular Oracle object, then getObject()
returns an oracle.sql.STRUCT
object.
For more information on getObject()
return types, see Table 6-1, "Summary of getObject() and getOracleObject() Return Types".
If you want to retrieve data from a result set or callable statement into an oracle.sql.*
object, then cast your result set to an OracleResultSet
type or your callable statement to an OracleCallableStatement
type, and use the getOracleObject()
method.
When you use getOracleObject()
, the data will be of the appropriate oracle.sql.*
type and is returned into an oracle.sql.Datum
object (the oracle.sql
type classes extend Datum
). The signature for the method is:
public oracle.sql.Datum getOracleObject(int parameter_index
)
When you have retrieved data into a Datum
object, you can use the standard Java instanceof
operator to determine which oracle.sql.*
type it really is.
For more information on getOracleObject()
return types, see Table 6-1, "Summary of getObject() and getOracleObject() Return Types".
The following example creates a table that contains a column of character data (in this case, a row number) and a column containing a BFILE
locator. A SELECT
statement retrieves the contents of the table into a result set. The getOracleObject()
then retrieves the CHAR
data into the char_datum
variable and the BFILE
locator into the bfile_datum
variable. Note that because getOracleObject()
returns a Datum
object, the results must be cast to CHAR
and BFILE
, respectively.
stmt.execute ("CREATE TABLE bfile_table (x varchar2 (30), b bfile)"); stmt.execute ("INSERT INTO bfile_table VALUES ('one', bfilename ('TEST_DIR', 'file1'))"); ResultSet rset = stmt.executeQuery ("SELECT * FROM bfile_table"); while (rset.next ()) { CHAR char_datum = (CHAR) ((OracleResultSet)rset).getOracleObject (1); BFILE bfile_datum = (BFILE) ((OracleResultSet)rset).getOracleObject (2); ... }
The following example prepares a call to the procedure myGetDate()
, which associates a character string (in this case a name) with a date. The program passes the string SCOTT
to the prepared call and registers the DATE
type as an output parameter. After the call is executed, getOracleObject()
retrieves the date associated with the name SCOTT
. Note that because getOracleObject()
returns a Datum
object, the results are cast to a DATE
object.
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ("begin myGetDate (?, ?); end;"); cstmt.setString (1, "SCOTT"); cstmt.registerOutParameter (2, Types.DATE); cstmt.execute (); DATE date = (DATE) ((OracleCallableStatement)cstmt).getOracleObject (2); ...
Table 6-1 summarizes the information in the preceding sections, "Standard getObject() Method" and "Oracle getOracleObject() Method".
This table lists the underlying return types for each method for each Oracle SQL type, but keep in mind the signatures of the methods when you write your code:
getObject()
: Always returns data into a java.lang.Object
instance.
getOracleObject()
: Always returns data into an oracle.sql.Datum
instance.
You must cast the returned object to use any special functionality (see "Casting Your get Method Return Values").
For information on type compatibility between all SQL and Java types, see Table 21-1, "Valid SQL Datatype-Java Class Mappings".
Standard JDBC provides a getXXX()
for each standard Java type, such as getByte()
, getInt()
, getFloat()
, and so on. Each of these returns exactly what the method name implies (a byte
, an int
, a float
, and so on).
In addition, the OracleResultSet
and OracleCallableStatement
classes provide a full complement of getXXX()
methods corresponding to all the oracle.sql.*
types. Each getXXX()
method returns an oracle.sql.XXX
object. For example, getROWID()
returns an oracle.sql.ROWID
object.
Some of these extensions are taken from the JDBC 2.0 specification. They return objects of type java.sql.*
(or oracle.jdbc2.*
under JDK 1.1.x), instead of oracle.sql.*
. For example, compare the following method names and return types:
java.sql.Blob getBlob(int
parameter_index)
oracle.sql.BLOB getBLOB(int
parameter_index)
Although there is no particular performance advantage in using the specific getXXX()
methods, they can save you the trouble of casting, because they return specific object types.
Table 6-2 summarizes the underlying return types and the input parameter types for each getXXX()
method, and notes which are Oracle extensions under JDK 1.2.x and JDK 1.1.x. You must cast to an OracleResultSet
or OracleCallableStatement
to use methods that are Oracle extensions.
This section provides additional details about some of the getXXX()
methods.
JDBC 2.0 supports a simplified method signature for the getBigDecimal()
method. The previous input signature was:
(int columnIndex, int scale)
or (String columnName, int scale)
The new input signature is simply:
(int columnIndex)
or (String columnName)
The scale
parameter, used to specify the number of digits to the right of the decimal, is no longer necessary. The Oracle JDBC drivers retrieve numeric values with full precision.
In JDBC 2.0, the getDate()
, getTime()
, and getTimestamp()
methods have the following input signatures:
(int columnIndex, Calendar cal)
or:
(String columnName, Calendar cal)
The Oracle JDBC drivers ignore the Calendar
object input, because it is not currently feasible to support java.sql.Date
timezone information together with the data. You should continue to use previous input signatures that take only the column index or column name. Calendar input will be supported in a future Oracle JDBC release.
As described in "Standard getObject() Method", Oracle's implementation of getObject()
always returns a java.lang.Object
instance, and getOracleObject()
always returns an oracle.sql.Datum
instance. Usually, you would cast the returned object to the appropriate class so that you could use particular methods and functionality of that class.
In addition, you have the option of using a specific getXXX()
method instead of the generic getObject()
or getOracleObject()
methods. The getXXX()
methods enable you to avoid casting, because the return type of getXXX()
corresponds to the type of object returned. For example, getCLOB()
returns an oracle.sql.CLOB
instance, as opposed to a java.lang.Object
instance.
This example assumes that you have fetched data of type CHAR
into a result set (where it is in column 1). Because you want to manipulate the CHAR
data without losing precision, cast your result set to an OracleResultSet
, and use getOracleObject()
to return the CHAR
data in oracle.sql.*
format. If you do not cast your result set, you have to use getObject()
, which returns your character data into a Java String
and loses some of the precision of your SQL data.
The getOracleObject()
method returns an oracle.sql.CHAR
object into an oracle.sql.Datum
return variable unless you cast the output. Cast the getOracleObject()
output to oracle.sql.CHAR
if you want to use a CHAR
return variable and any of the special functionality of that class (such as the getCharacterSet()
method that returns the character set used to represent the characters).
CHAR char = (CHAR)ors.getOracleObject(1); CharacterSet cs = char.getCharacterSet();
Alternatively, you can return the object into a generic oracle.sql.Datum
return variable and cast it later when you must use the CHAR
getCharacterSet()
method.
Datum rawdatum = ors.getOracleObject(1); ... CharacterSet cs = ((CHAR)rawdatum).getCharacterSet();
This uses the getCharacterSet()
method of oracle.sql.CHAR
. The getCharacterSet()
method is not defined on oracle.sql.Datum
and would not be reachable without the cast.
Just as there is a standard getObject()
and Oracle-specific getOracleObject()
in result sets and callable statements for retrieving data, there is also a standard setObject()
and an Oracle-specific setOracleObject()
in Oracle prepared statements and callable statements for updating data. The setOracleObject()
methods take oracle.sql.*
input parameters.
To bind standard Java types to a prepared statement or callable statement, use the setObject()
method, which takes a java.lang.Object
as input. The setObject()
method does support a few of the oracle.sql.*
types--it has been implemented so that you can also input instances of the oracle.sql.*
classes that correspond to JDBC 2.0-compliant Oracle extensions: BLOB
, CLOB
, BFILE
, STRUCT
, REF
, and ARRAY
.
To bind oracle.sql.*
types to a prepared statement or callable statement, use the setOracleObject()
method, which takes an oracle.sql.Datum
(or any subclass) as input. To use setOracleObject()
, you must cast your prepared statement or callable statement to an OraclePreparedStatement
or OracleCallableStatement
object.
This example assumes that you have fetched character data into a standard result set (where it is in column 1), and you want to cast the results to an OracleResultSet
so that you can use Oracle-specific formats and methods. Because you want to use the data as oracle.sql.CHAR
format, cast the results of the getOracleObject()
(which returns type oracle.sql.Datum
) to CHAR
. Similarly, because you want to manipulate the data in column 2 as strings, cast the data to a Java String
type (because getObject()
returns data of type Object
). In this example, rs
represents the result set, charVal
represents the data from column 1 in oracle.sql.CHAR
format, and strVal
represents the data from column 2 in Java String
format.
CHAR charVal=(CHAR)((OracleResultSet)rs).getOracleObject(1); String strVal=(String)rs.getObject(2); ...
For a prepared statement object ps
, the setOracleObject()
method binds the oracle.sql.CHAR
data represented by the charVal
variable to the prepared statement. To bind the oracle.sql.*
data, the prepared statement must be cast to an OraclePreparedStatement
. Similarly, the setObject()
method binds the Java String
data represented by the variable strVal
.
PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement
");
((OraclePreparedStatement)ps).setOracleObject(1,charVal);
ps.setObject(2,strVal);
As with getXXX()
methods, there are several specific setXXX()
methods. Standard setXXX()
methods are provided for binding standard Java types, and Oracle-specific setXXX()
methods are provided for binding Oracle-specific types.
Similarly, there are two forms of the setNull()
method:
void setNull(int
parameterIndex, int
sqlType)
This is specified in the standard java.sql.PreparedStatement
interface. This signature takes a parameter index and a SQL typecode defined by the java.sql.Types
or oracle.jdbc.OracleTypes
class. Use this signature to set an object other than a REF
, ARRAY
, or STRUCT
to NULL
.
void setNull(int
parameterIndex, int
sqlType, String
sql_type_name)
With JDBC 2.0, this signature is also specified in the standard java.sql.PreparedStatement
interface. Under JDK 1.1.x, it is available as an Oracle extension. It takes a SQL type name in addition to a parameter index and a SQL type code. Use this method when the SQL typecode is java.sql.Types.REF
, ARRAY
, or STRUCT
. (If the typecode is other than REF
, ARRAY
, or STRUCT
, then the given SQL type name is ignored.)
Similarly, the registerOutParameter()
method has a signature for use with REF
, ARRAY
, or STRUCT
data:
void registerOutParameter
(int
parameterIndex, intsqlType
, Stringsql_type_name
)
For binding Oracle-specific types, using the appropriate specific setXXX()
methods instead of methods for binding standard Java types may offer some performance advantage.
Table 6-3 summarizes the input types for all the setXXX()
methods and notes which are Oracle extensions under JDK 1.2.x and JDK 1.1.x. To use methods that are Oracle extensions, you must cast your statement to an OraclePreparedStatement
or OracleCallableStatement
.
For information on all supported type mappings between SQL and Java, see Table 21-1, "Valid SQL Datatype-Java Class Mappings".
Table 6-4 lists size limitations for the setBytes()
and setString()
methods for SQL binds to Oracle8 and Oracle7 databases. (These limitations do not apply to PL/SQL binds.) For information about how to work around these limits using the stream API, see "Using Streams to Avoid Limits on setBytes() and setString()".
Oracle8 | Oracle7 | |
---|---|---|
setBytes() size limitation |
2000 bytes |
255 bytes |
setString() size limitation |
4000 bytes |
2000 bytes |
The following setXXX()
methods take an additional input parameter other than the parameter index and the data item itself:
setAsciiStream(int paramIndex, InputStream istream,
int length)
Takes the length of the stream, in bytes.
setBinaryStream(int paramIndex, InputStream istream,
int length)
Takes the length of the stream, in bytes.
setCharacterStream(int paramIndex, Reader reader,
int length)
Takes the length of the stream, in characters.
setUnicodeStream(int paramIndex, InputStream istream,
int length)
Takes the length of the stream, in bytes.
The particular usefulness of the setCharacterStream()
method is that when a very large Unicode
value is input to a LONGVARCHAR
parameter, it can be more practical to send it through a java.io.Reader
object. JDBC will read the data from the stream as needed, until it reaches the end-of-file mark. The JDBC driver will do any necessary conversion from Unicode
to the database character format.
Important: The preceding stream methods can also be used for LOBs. See "Reading and Writing BLOB and CLOB Data" for more information. |
setDate(int paramIndex, Date x, Calendar cal)
setTime(int paramIndex, Time x, Calendar cal)
setTimestamp(int paramIndex, Timestamp x, Calendar cal)
The JDBC 2.0 signatures for setDate()
, setTime()
, and setTimestamp()
include a Calendar
object, but the Oracle JDBC drivers ignore this input because it is not yet feasible to support java.sql.Date
timezone information together with the data. You should continue to use the previous signatures that take only the parameter index and data item. Calendar input will be supported in a future release.
CHAR
data in the database is padded to the column width. This leads to a limitation in using the setCHAR()
method to bind character data into the WHERE
clause of a SELECT
statement--the character data in the WHERE
clause must also be padded to the column width to produce a match in the SELECT
statement. This is especially troublesome if you do not know the column width.
To remedy this, Oracle has added the setFixedCHAR()
method to the OraclePreparedStatement
class. This method executes a non-padded comparison.
The following example demonstrates the difference between the setCHAR()
and setFixedCHAR()
methods.
/* Schema is : create table my_table (col1 char(10)); insert into my_table values ('JDBC'); */ PreparedStatement pstmt = conn.prepareStatement ("select count(*) from my_table where col1 = ?"); pstmt.setString (1, "JDBC"); // Set the Bind Value runQuery (pstmt); // This will print " No of rows are 0" CHAR ch = new CHAR("JDBC ", null); ((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytes runQuery (pstmt); // This will print "No of rows are 1" ((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC"); runQuery (pstmt); // This will print "No of rows are 1" void runQuery (PreparedStatement ps) { // Run the Query ResultSet rs = pstmt.executeQuery (); while (rs.next()) System.out.println("No of rows are " + rs.getInt(1)); rs.close(); rs = null; }
The Oracle 8.0.x JDBC drivers use the same protocol as the Oracle 7.3.x JDBC drivers. In both cases, Oracle datatypes are as defined for an Oracle 7.3.x database, and data items longer than 2K bytes must be LONG
.
As with any LONG
data, use the stream APIs to read and write data between your application and the database. Essentially, this means that you cannot use the normal getString()
and setString()
methods to read or write data longer than 2K bytes when using the 8.0.x and 7.3.x drivers.
The stream APIs include methods such as getBinaryStream()
, setBinaryStream()
, getAsciiStream()
, and setAsciiStream()
. These methods are discussed under "Java Streams in JDBC".
The oracle.jdbc.OracleResultSetMetaData
interface is JDBC 2.0-compliant but does not implement the getSchemaName()
and getTableName()
methods because underlying protocol does not make this feasible. Oracle does implement many methods to retrieve information about an Oracle result set, however.
Key methods include the following:
int getColumnCount()
: Returns the number of columns in an Oracle result set.
String getColumnName(int column)
: Returns the name of a specified column in an Oracle result set.
int getColumnType(int column)
: Returns the SQL type of a specified column in an Oracle result set. If the column stores an Oracle object or collection, then this method returns OracleTypes.STRUCT
or OracleTypes.ARRAY
respectively.
String getColumnTypeName(int column)
: Returns the SQL type name for a specified column of type REF
, STRUCT
, or ARRAY
. If the column stores an array or collection, then this method returns its SQL type name. If the column stores REF
data, then this method returns the SQL type name of the objects to which the object reference points.
The following example uses several of the methods in the OracleResultSetMetadata
interface to retrieve the number of columns from the EMP
table, and each column's numerical type and SQL type name.
DatabaseMetaData dbmd = conn.getMetaData(); ResultSet rset = dbmd.getTables("", "SCOTT", "EMP", null); while (rset.next()) { OracleResultSetMetaData orsmd = ((OracleResultSet)rset).getMetaData(); int numColumns = orsmd.getColumnCount(); System.out.println("Num of columns = " + numColumns); for (int i=0; i<numColumns; i++) { System.out.print ("Column Name=" + orsmd.getColumnName (i+1)); System.out.print (" Type=" + orsmd.getColumnType (i + 1) ); System.out.println (" Type Name=" + orsmd.getColumnTypeName (i + 1)); } }
The program returns the following output:
Num of columns = 5 Column Name=TABLE_CAT Type=12 Type Name=VARCHAR2 Column Name=TABLE_SCHEM Type=12 Type Name=VARCHAR2 Column Name=TABLE_NAME Type=12 Type Name=VARCHAR2 Column Name=TABLE_TYPE Type=12 Type Name=VARCHAR2 Column Name=TABLE_REMARKS Type=12 Type Name=VARCHAR2
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|