Skip Headers

Oracle9i Lite Developer's Guide for Java
Release 5.0.1
Part No. A95261-01
Go To Table Of Contents
Contents
Go To Index
Index

Previous Next

3
Java Database Connectivity (JDBC)

This document discusses the Oracle Lite support for Java Database Connectivity (JDBC). It includes the following topics:

3.1 New Features in Oracle Lite

This section describes the enhancements made to JDBC support in this release of Oracle Lite.

The JDBC driver now supports large objects, including the BLOB and CLOB datatypes. This support conforms to features in the JDBC 2.0 specification, and is compatible with Oracle8i's JDBC implementation.

Oracle Lite also includes new functions for accessing large objects. For more information, see "Oracle Lite Extensions".

3.2 What is JDBC?

JDBC is an application programmer's interface for accessing relational databases from Java programs. Oracle Lite supplies a native JDBC driver that allows Java applications to communicate directly with Oracle Lite's object-relational database engine. Oracle Lite's implementation of JDBC complies with JDBC 1.22. In addition, Oracle Lite provides certain extensions specified by JDBC 2.0. Oracle Lite's extensions are compatible with the Oracle8i JDBC implementation. For a complete JDBC reference, see the Sun Microsystems Web site.

3.3 JDBC Environment Setup

To access an Oracle Lite database, your Java program must first load the Oracle Lite JDBC driver and then establish a connection to the database.

3.3.1 Loading the Oracle Lite JDBC Driver

To load the Oracle Lite JDBC driver, pass the fully qualified name of the Oracle Lite JDBC driver class to the Class.forName method:

Class.forName("oracle.lite.poljdbc.POLJDBCDriver");

The Class.forName method finds, loads, and links the referenced Oracle Lite JDBC driver class. You do not need to explicitly create an instance of the POLJDBCDriver class. An instance of the POLJDBCDriver class is automatically created and registered with the JDBC driver manager when you call Class.forName.


Note:

Since the Oracle Lite JDBC driver and the JDK 1.2 JDBC driver use the same names for certain datatypes (such as CLOB and BLOB), calling these datatypes from a program that imports both java.sql.* and oracle.sql.* may result in compiler errors. For information on avoiding these errors, see "Limitations".

3.3.2 Connecting to an Oracle Lite Database

The JDBC connect string for the Oracle Lite database is:

jdbc:polite:data_sourcename

The data_sourcename is the name of the data source defined in odbc.ini. The JDBC driver looks up the physical location of the database identified by data_sourcename in odbc.ini.

The following statement uses the JDBC connect string to connect to an Oracle Lite database with the data source name POLITE:

Connection conn = DriverManager.getConnection("jdbc:polite:POLITE",

   "system", "passwd");

When connecting as the user system, you can provide any combination of characters for the password. However, the password cannot be blank. If the database is encrypted, you must supply the correct password.

You use the Connection object returned by getConnection to access and manipulate data in the Oracle Lite database.

For more information on how to make connections with JDBC drivers, see Appendix B, " JDBC Application Tutorial".

3.4 Executing Java Stored Procedures from JDBC

After creating a Java stored procedure, you can execute the procedure from a JDBC application by doing the following:

The executeQuery method executes table-level and row-level stored procedures. CallableStatement currently only supports execution of table-level stored procedures.

3.4.1 Using the ExecuteQuery Method

To call a stored procedure using the executeQuery method, first create a Statement object, which you assign the value returned by the createStatement method of the current connection object. You then execute the Statement.executeQuery method, passing it the SQL SELECT string that invokes the Java stored procedure.

For example, suppose you want to execute a row-level procedure SHIP on table INVENTORY with the argument value stored in the variable q. The variable p contains the product ID for the product (row) for which you want to execute the stored procedure.

int res = 0;

Statement s = conn.createStatement();

ResultSet r = s.executeQuery("SELECT SHIP(" + q + ")" + 

   "FROM INVENTORY WHERE PID = " + p);

if(r.next()) res = r.getInt(1);

r.close();

s.close();

return res;

If you need to execute a procedure repeatedly with varying parameters, use PreparedStatement instead of Statement. Because the SQL statements in a PreparedStatement are pre-compiled, PreparedStatements execute more efficiently. Additionally, a PreparedStatement can accept IN parameters, represented in the statement with a question mark (?). However, if the PreparedStatement takes a "long" type parameter, such as LONG or LONG RAW, you must bind the parameter using the setAsciiStream, setUnicodeStream, or setBinaryStream methods.

In the preceding example, if the procedure SHIP updates the database and the isolation of the transaction that issues the above query is READ COMMITTED, you must append the FOR UPDATE clause to the SELECT statement, as follows:

"SELECT SHIP(" + q + ")" +

   FROM INVENTORY WHERE PID = " + 

   p + "FOR UPDATE");

3.4.2 Using a Callable Statement

To execute the stored procedure using a callable statement, create a CallableStatement object and register its parameters as follows:

CallableStatement cstmt = conn.prepareCall(

   "{?=call tablename.methodname() }");

cstmt.registerOutParameter(1, ...);

cstmt.executeUpdate();

cstmt.get..(1);

cstmt.close();

The following restrictions apply to JDBC callable statements:

  • JDBC callable statements can only execute table-level stored procedures.

  • Both IN and OUT parameters are supported. However, not all Java datatypes can be used as OUT parameters. For more information, see Chapter 2, " Java Stored Procedures and Triggers".

  • Procedure names correspond to the Java method names, and are case-sensitive.

  • As with prepared statements, if the callable statement has a "long" type, such as: LONG, LONG VARBINARY, LONG VARCHAR, LONG VARCHAR2, or LONG RAW, you must bind the parameter using the setAsciiStream, setUnicodeStream, or setBinaryStream methods.


    Note:

    You should reclaim system resources by closing JDBC objects, such as Resultset and Statement objects, when no longer needed.

3.5 Oracle Lite Extensions

The Oracle Lite JDBC driver supports JDBC 1.22 and provides extensions that support certain features defined in JDBC 2.0. The extensions include support for BLOB (large binary object) and CLOB (large character object) datatypes and data access functions. The Oracle Lite JDBC extensions are compatible with the Oracle8i JDBC implementation. However, Oracle Lite does not support the Oracle8i JDBC datatype extensions, Array, Struct, or REF.

This section lists and describes the Oracle Lite datatype and data access extensions. For details regarding function syntax and call parameters, see the Sun Microsystems Java 2 specification at:

http://www.javasoft.com/products/jdk/1.2/docs/api/index.html

3.5.1 Datatype Extensions

BLOBs and CLOBs store data items that are too large to store directly in a database table. Rather than storing the data, the database table stores a locator that points to the location of the actual data. BLOBs contain a large amount of unstructured binary data items and CLOBs contain a large amount of fixed-width character data items (characters that require a fixed number of bytes per character).

You can select a BLOB or CLOB locator from the database using a standard SELECT statement. When you select a BLOB or CLOB locator using SELECT, you acquire only the locator for the large object, not the data itself. Once you have the locator, however, you can read data from or write data to the large object using access functions.

Table 3-1 lists the methods included in the Oracle Lite BLOB class and their descriptions:

Table 3-1 The Methods in the Oracle Lite BLOB Class

Function Description
length Returns the length of a BLOB in bytes.
getBinaryOutputStream Returns BLOB data.
getBinaryStream Returns a BLOB instance as a stream of bytes.
getBytes Reads BLOB data, starting at a specified point, into a buffer.
getConnection Returns the current connection.
isConvertibleTo Determines if a BLOB can be converted to a particular class.
putBytes Writes bytes to a specified point in the BLOB data.
makeJdbcArray Returns the JDBC array representation of a BLOB.
toJdbc Converts a BLOB to a JDBC class.

Table 3-2 lists the methods included in the Oracle Lite CLOB class and their descriptions.

Table 3-2 The Methods in the Oracle Lite CLOB Class

Function Description
length Returns the length of a CLOB in bytes.
getSubString Retrieves a substring from a specified point in the CLOB data.
getCharacterStream Returns CLOB data as a stream of Unicode characters.
getAsciiStream Returns a CLOB instance as an ASCII stream.
getChars Retrieves characters from a specified point in the CLOB data into a character array.
getCharacterOutputStream Writes CLOB data from a Unicode stream.
getAsciiOutputStream Writes CLOB data from an ASCII stream.
getConnection Returns the current connection.
putChars Writes characters from a character array to a specified point in the CLOB data.
putString Writes a string to a specified point in the CLOB data.
toJdbc Converts a CLOB to a JDBC class.
isConvertibleTo Determines if a CLOB can be converted to a particular class.
makeJdbcArray Returns a JDBC array representation of a CLOB.

3.5.2 Data Access Extensions

Oracle Lite provides access functions to set and return values of the CLOB and BLOB datatypes. In addition, stream classes provide functions that enable stream-format access to large objects.

The large object access functions are located in the OraclePreparedStatement, the OracleCallableStatement, and the OracleResultSet class.

Table 3-3 lists the data access functions included in the OraclePreparedStatement class.

Table 3-3 Data Access Functions in the OraclePreparedStatement

Function Description
setBLOB Sets BLOB data.
setCLOB Sets CLOB data.

Table 3-4 lists the data access functions included in the OracleCallableStatement class.

Table 3-4 Data Access Functions in the OracleCallableStatement

Function Description
getBLOB Returns a locator to BLOB data.
getCLOB Returns a locator to CLOB data.

Table 3-5 lists the data access functions included in the OracleResultSet class.

Table 3-5 Data Access Functions in the OracleResultSet Class

Function Description
getBLOB Returns a locator to BLOB data.
getCLOB Returns a locator to CLOB data.

The stream format access classes are POLLobInputStream, POLLobOutputStream, POLClobReader, and POLClobWriter.

The POLLobInputStream class includes the following data access function:

Function Description
read Reads from a large object into an array.

The POLLobOutputStream class includes this data access function:

Function Description
write Writes from an output stream into a large object.

The POLClobReader class extends the class java.io.reader. It includes these data access functions:

Function Description
read Reads characters from a CLOB into a portion of an array.
ready Indicates whether a stream is ready to read.
close Closes a stream.
markSupported Indicates whether the stream supports the mark operation.
mark Marks the current position in the stream. Subsequent calls to the reset function reposition the stream to the marked location.
reset Resets the current position in the stream to the marked location. If the stream has not been marked, this function attempts to reset the stream in a way appropriate to the particular stream, such as by repositioning it at its starting point.
skip Skips characters in the stream.

The POLClobWriter class extends the class java.io.writer. It includes these data access functions:

Function Description
write Writes an array of characters to the output stream.
flush Writes any characters in a buffer to their intended destination.
close Flushes and closes the stream.

3.5.2.1 Reading from a BLOB Sample Program

The following sample uses the getBinaryStream method to read BLOB data into a byte stream. It then reads the byte stream into a byte array, and returns the number of bytes read.

// Read BLOB data from BLOB locator.

InputStream byte_stream = my_blob.getBinaryStream();

byte [] byte_array = new byte [10];

int bytes_read = byte_stream.read(byte_array);

...

3.5.2.2 Writing to a CLOB Sample Program

The following sample reads data into a character array, then uses the getCharacterOutputStream method to write the array of characters to a CLOB.

java.io.Writer writer;

// read data into a character array

char[] data = {'0','1','2','3','4','5','6','7','8','9'};


// write the array of character data to a CLOB 

writer = ((CLOB)my_clob).getCharacterOutputStream();

writer.write(data);

writer.flush();

writer.close();

...

3.6 Limitations

If data truncation occurs during a write, a SQL data truncation exception is thrown. A SQL data truncation warning is armed if data truncation occurs during a read. However, the data truncation object does not contain meaningful information.

The Oracle Lite JDBC classes and the JDBC 2.0 classes use the same name for certain datatypes (for example, oracle.sql.Blob and java.sql.Blob). If your program imports both oracle.sql.* and java.sql.*, attempts to access the overlapping classes without fully qualifying their names may result in compiler errors. To avoid this problem, use one of the following steps:

  1. Use fully qualified names for BLOB, CLOB, and data classes.

  2. Import the class explicitly (for example, import oracle.sql.Blob).

  3. Compile with the JDK 1.1 compiler without oljdk11.jar in the compile classpath.

Class files always contain fully qualified class names, so the overlapping datatype names do not cause conflicts at runtime.


Previous Next
Oracle Logo
Copyright © 2002 Oracle Corporation

All rights reserved
Go To Table Of Contents
Contents
Go To Index
Index