This chapter discusses the Oracle Database Lite support for JDBC programming. It includes the following topics:
Oracle Database Lite provides a native JDBC driver that allows Java applications to communicate directly with the Oracle Database Lite object relational database engine. The Oracle Database Lite implementation of JDBC complies with JDBC 1.2. In addition, Oracle Database Lite provides certain extensions specified by JDBC 2.0, which are compatible with the Oracle database JDBC implementation. For a complete JDBC reference, see the Sun Microsystems Web site.
For your Java applications using the client/server model, include the olite40.jar
, which is located in OLITE_HOME
/bin
, in the system CLASSPATH
on the server machine and in the user CLASSPATH
on the client machine.
When using the Oracle Lite JDBC driver in your OC4J application, use the default classloader instead of a per-application classloader, which many J2EE containers use. Ensure that the olite40.jar
is in the OC4J CLASSPATH
when OC4J initiates and not in the /lib
subdirectory of your application WAR file.
Note: For more information on how to start the Multiuser Oracle Database Lite Database Service, see Section 2.5, "Oracle Database Lite Multi-User Service". |
Note: JDK 1.4.2 or higher is required to connect to Oracle Database Lite. |
Oracle Database Lite supports Type 2 and Type 4 drivers.
The Type 2 driver uses native code on the client side through which it interfaces with the Oracle Database Lite ODBC driver.
The Type 4 JDBC driver is a pure Java driver and uses the Oracle Database Lite network protocol to communicate with the Oracle Database Lite service. Before using this driver, ensure that you start Oracle Database Lite. Any Java applet can use the Type 4 JDBC driver.
The supported Type 2 and Type 4 drivers are described in the following sections:
For most applications, use the type 2 driver for connecting to the database. You can use the type 2 driver to connect either to the local Oracle Lite database or to the server where a Multi-User Service is managing the Oracle Lite databases.
To connect to the local Oracle Lite database, use the following URL syntax:
jdbc:polite[:uid / pwd]:localDSN[;key=value]*
where the localDSN is the DSN name for the local Oracle Lite database (the ODB file on the local machine) and the optional key=value pairs are listed in Table 10-1.
The following example retrieves a connection to the local Oracle Lite database, where the DSN name is polite
:
DriverManager.getConnection("jdbc:polite:polite","system","admin");
To access the Oracle Lite database on a remote host where a Multi-User Service or Branch Office is located, use the following URL syntax:
jdbc:polite[:uid / pwd]@[host]:[port]:serverDSN [;key=value]*
where the host, port, and serverDSN identify the host, port and DSN of the remote host where the Oracle Lite database (the ODB file on the local machine) and the multi-user service is located. The optional key=value pairs are listed in Table 10-1.
For more information on how to install and start the Multiuser Oracle Database Lite Service, refer to Section 2.5, "Oracle Database Lite Multi-User Service".
You can provide additional configuration information in the JDBC driver URL within key-value pairs, as specified in Table 10-1, each of which are separated by a semi-colon. The information specified within the key-value pairs always overrides the information that is specified in the URL.
Table 10-1 Key/Value Pairs for JDBC Connect URL
Argument | Description |
---|---|
jdbc |
Identifies the protocol as JDBC. |
polite |
Identifies the subprotocol as polite . |
uid / pwd | The optional user ID and password for Oracle Database Lite. If specified, this overrides the specification of a username and password defined in the UID and PWD arguments. If the database is encrypted, you must include the password in the key-value pair. |
host | The name of the machine that hosts the Multi-User Service or Branch Office and on which the Oracle Database Lite service olsv2040.exe runs. This host name is optional. If omitted, it defaults to the local machine on which the JDBC application runs. |
port | The port number at which the Multi-User or Branch Office service listens. The port number is optional and if omitted defaults to port "1531". |
dsn | Identifies the data source name (DSN) entry in the odbc.ini file. This entry contains all the necessary information to complete the connection to the server.
Note: For a JDBC program, you need not create a DSN if you have supplied all the necessary values for the data directory and database through key=value pairs. On the windows platform, you can use the ODBC administrator to create a DSN. For more information, see Section 2.5.1.6, "Creating DSNs". |
DataDirectory= | Directory in which the .odb file resides. |
Database= | Name of database as given during its creation. |
IsolationLevel= | Transaction isolation level: READ COMMITTED, REPEATABLE READ, SERIALIZABLE or SINGLE USER. For more information on isolation levels, see Section 15.2, "What Are the Transaction Isolation Levels?". |
Autocommit= | Commit behavior, either ON or OFF . |
CursorType= | Cursor behavior: DYNAMIC, FORWARD ONLY, KEYSET DRIVEN or STATIC. For more information on cursor types, see Section 15.4, "Supported Combinations of Isolation Levels and Cursor Types". |
UID= | User name |
PWD= | Password |
Example of Using JDBC Type 2 Connection for Local Oracle Lite Database
String ConnectMe=("jdbc:polite:SCOTT/tiger:polite; DataDirectory=<ORACLE_HOME>;Database=polite;IsolationLevel=SINGLE USER; Autocommit=ON;CursorType=DYNAMIC") try {Class.forName("oracle.lite.poljdbc.POLJDBCDriver") Connection conn = DriverManager.getConnection(ConnectMe) } catch (SQLException e) { ... }
Example of Using Type 2 in Multi-User Service Situation
An example of this type of connection is given below.
try { Connection conn = DriverManager.getConnection( "jdbc:polite@yourhostname ;DataDirectory=<ORACLE_HOME> ;Database=polite ;IsolationLevel=SINGLE USER ;Autocommit=ON ;CursorType=DYNAMIC", "Scott", "tiger") } catch (SQLException e) { }
Use the JDBC Type 4 driver for any pure Java application that uses the Multi-User or Branch office services. The URL syntax for the type 4 driver as follows:
jdbc:polite4[:uid/pwd]@[host]:[port]:serverDSN[;key=value]*
The parameter polite4
indicates that the JDBC type 4 driver is being used. For the rest of the parameters, see the definitions of those parameters for the type 2 driver, as described in Table 10-1.
Note: The URL works with the Oracle Database Lite service only. |
In JDBC 2.0, the DataSource
object is an alternative to the DriverManager
facility. The DataSource
object is the preferred method for retrieving a connection and is typically registered with a naming service based on the JNDI API. A driver that is accessed through a DataSource
object does not register itself with the DriverManager
.
The Oracle Database Lite JDBC driver contains the basic DataSource
implementation to produce a standard Connection
object. The retrieved connection is identical to a connection obtained through the DriverManager
.
Oracle Database Lite implements javax.sql.DataSource
interface with the POLJDBCDataSource
class in the oracle.lite.poljdbc
package.
As with any class that implements the DataSource
interface, the POLJDBCDataSource
object defines properties for connecting to a specific database. In addition to the standard DataSource properties, the POLJDBCDataSource
class has one additional property, which is a String
to define the URL of the database connection string, as described in Section 10.3.1, "Type 2 Driver" and Section 10.3.2, "Type4 (Pure Java) Driver Connection URL Syntax".
The URL can include username and password, which then overrides any previous individual property settings. The following URL specifies a username and password of system/manager:
jdbc:polite4:system/manager@::polite
You must have a username and password defined either in the URL or in the getConnection method. If defined in both places, then the username and password in the getConnection takes precedence over the URL definitions.
See the JDBC example—JDBCEXJSR169—on the CD for how to use the POLJDBCDataSource
object.
The Oracle Database Lite JDBC driver supports JDBC 1.2 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 scrollable result sets. The Oracle Database Lite JDBC extensions are compatible with the Oracle database JDBC implementation. However, Oracle Database Lite does not support the following Oracle database JDBC datatype extensions: Array
, Struct
, or REF
.
The following sections list and describe the Oracle Database Lite datatypes and data access extensions. For details regarding function syntax and call parameters, see the Sun Microsystems Java 2 specification at the Sun Javasoft website.
Oracle Database Lite performs type conversions between Java and Oracle datatypes as indicated by the following table. Table 10-2 lists the Java datatypes and the corresponding SQL datatypes that result from the type conversion.
Table 10-2 Datatype Conversions
Java Datatype | SQL Datatype |
---|---|
byte[], byte[][], Byte[] |
BINARY, RAW, VARBINARY, BLOB |
boolean, Boolean |
BIT |
String, String[] |
CHAR, VARCHAR, VARCHAR2, CLOB |
short, short[], short[][], Short, Short[] |
SMALLINT |
int,int[], int[][], Integer, Integer[] |
INT |
float, float[], float[][], Float, Float[] |
REAL |
double, double[], double[][], Double, Double[] |
DOUBLE, NUMBER (without precision) |
BigDecimal, BigDecimal[] |
NUMBER(n) |
java.sql.Date, java.sql.Date[] |
DATE |
java.sql.Time, java.sql.Time[] |
TIME |
java.sql.Timestamp, java.sql.Timestamp[ ] |
TIMESTAMP |
java.sql.Connection |
Default JDBC connection to database |
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 10-3 lists the methods included in the Oracle Database Lite BLOB class and their descriptions:
Table 10-3 Methods in the Oracle Database Lite BLOB Class
Table 10-4 lists the methods included in the Oracle Database Lite CLOB class and their descriptions.
Table 10-4 Methods in the Oracle Database Lite CLOB Class
Oracle Database 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 10-5 lists the data access functions included in the OracleResultSet
class.
Table 10-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.
The POLClobReader
class extends the class java.io.reader
. It includes these data access functions.
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. |
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); ...
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; 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(); ...
If data truncation occurs during a write, a SQL data truncation exception is thrown. A SQL data truncation warning results if data truncation occurs during a read.
The Oracle Database 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:
Import the class explicitly (for example, import oracle.sql.Blob
).
Class files always contain fully qualified class names, so the overlapping datatype names do not cause conflicts at runtime.
This section describes JDBC 2.0 methods or interfaces that are supported by the Oracle Database Lite JDBC driver. Topics include:
This section describes the JDBC 2.0 Interface methods that are implemented by the Oracle Database Lite JDBC driver.
Statement
createStatement(int resultSetType, int resultSetConcurrency)
Creates a statement object that generates ResultSet objects with the given type and concurrency.
Map
getTypeMap()
Gets the TypeMap object associated with this connection.
CallableStatement
prepareCall(String sql, int resultSetType, int resultSetConcurrency)
Creates a CallableStatement object that generates ResultSet objects with the given type and concurrency.
PreparedStatement
prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
Creates a PreparedStatement object that generates ResultSet objects with the given type and concurrency.
void
setTypeMap(Map map)
Installs the given type map as the type map for this connection.
This section describes the JDBC 2.0 Interface Statement methods that are implemented by the Oracle Database Lite JDBC driver.
Connection
getConnection()
Returns the Connection object that produced this Statement object.
int
getFetchDirection()
Retrieves the direction for fetching rows from database tables that is the default for result sets generated from this Statement object. Only FETCH_FORWARD is supported for now.
int
getFetchSize()
Retrieves the number of result set rows that is the default fetch size for result sets generated from this Statement object. Only fetch size = 1 is supported for now.
int
getResultSetConcurrency()
Retrieves the result set concurrency. Only CONCUR_READ_ONLY is supported for now.
int
getResultSetType()
Determine the result set type. Only TYPE_FORWARD_ONLY and TYPE_SCROLL_INSENSITIVE are supported for now.
void
setFetchDirection(int direction)
Gives the driver a hint as to the direction in which the rows in a result set will be processed.
void
setFetchSize(int rows)
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.
This section describes the JDBC 2.0 Interface ResultSet methods that are implemented by the Oracle Database Lite JDBC driver.
The following fields can be used to implement the Interface ResultSet feature.
static int
CONCUR_READ_ONLY
The concurrency mode for a ResultSet object that may NOT be updated.
static int
CONCUR_UPDATABLE
The concurrency mode for a ResultSet object that may be updated. Not supported for now.
static int
FETCH_FORWARD
The rows in a result set will be processed in a forward direction; first-to-last.
static int
FETCH_REVERSE
The rows in a result set will be processed in a reverse direction; last-to-first. Not supported for now.
static int
FETCH_UNKNOWN
The order in which rows in a result set will be processed is unknown.
static int
TYPE_FORWARD_ONLY
The type for a ResultSet object whose cursor may move only forward.
static int
TYPE_SCROLL_INSENSITIVE
The type for a ResultSet object that is scrollable but generally not sensitive to changes made by others.
static int
TYPE_SCROLL_SENSITIVE
The type for a ResultSet object that is scrollable and generally sensitive to changes made by others. Not supported for now.
This section describes the JDBC 2.0 ResultSet method implemented by the Oracle Database Lite JDBC driver.
boolean
absolute(int row)
Moves the cursor to the given row number in the result set.
void
afterLast()
Moves the cursor to the end of the result set, just after the last row.
void
beforeFirst()
Moves the cursor to the front of the result set, just before the first row.
boolean
first()
Moves the cursor to the first row in the result set.
Array
getArray(String colName)
Gets an SQL ARRAY value in the current row of this ResultSet object.
BigDecimal
getBigDecimal(int columnIndex)
Gets the value of a column in the current row as a java.math.BigDecimal object with full precision.
BigDecimal
getBigDecimal(String columnName)
Gets the value of a column in the current row as a java.math.BigDecimal object with full precision.
int
getConcurrency()
Returns the concurrency mode of this result set.
Date
getDate(int columnIndex, Calendar cal)
Gets the value of a column in the current row as a java.sql.Date object.
int
getFetchDirection()
Returns the fetch direction for this result set.
int
getFetchSize()
Returns the fetch size for this result set.
int
getRow()
Retrieves the current row number.
Statement
getStatement()
Returns the Statement that produced this ResultSet object.
int
getType()
Returns the type of this result set.
boolean
isAfterLast()
boolean
isBeforeFirst()
boolean
isFirst()
boolean
isLast()
boolean
last()
Moves the cursor to the last row in the result set.
boolean
previous()
Moves the cursor to the previous row in the result set.
void
refreshRow()
Refreshes the current row with its most recent value in the database. Currently does nothing.
boolean
relative(int rows)
Moves the cursor a relative number of rows, either positive or negative.
The following three methods always return false because this release does not support deletes, inserts, or updates.
boolean
rowDeleted()
Indicates whether a row has been deleted.
boolean
rowInserted()
Indicates whether the current row has had an insertion.
boolean
rowUpdated()
Indicates whether the current row has been updated.
void
setFetchDirection(int direction)
Gives a hint as to the direction in which the rows in this result set will be processed.
void
setFetchSize(int rows)
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this result set.
This section describes the JDBC 2.0 Interface Database MetaData methods that are implemented by the Oracle Database Lite JDBC driver.
The following methods can be used to implement the Interface Database MetaData feature.
Connection
getConnection()
Retrieves the connection that produced this metadata object.
boolean
supportsResultSetConcurrecny(int type, int concurrency)
Supports the concurrency type in combination with the given result set type.
boolean
supportsResultSetType(int Type)
Supports the given result set type.
The following methods return false, because this release does not support deletes or updates.
boolean
deletesAreDetected(int Type)
Indicates whether or not a visible row delete can be detected by calling ResultSet.rowDeleted().
boolean
insertsAreDetected(int Type)
Indicates whether or not a visible row insert can be detected by calling ResultSet.rowInserted().
boolean
othersDeletesAreVisible(int Type)
Indicates whether deletes made by others are visible.
boolean
othersInsertsAreVisible(int Type)
Indicates whether inserts made by others are visible.
boolean
othersUpdatesAreVisible(int Type)
Indicates whether updates made by others are visible.
boolean
ownDeletesAreVisible(int Type)
Indicates whether a result set's own deletes are visible.
boolean
ownInsertsAreVisible(int Type)
Indicates whether a result set's own inserts are visible.
boolean
ownUpdatesAreVisisble(int Type)
Indicates whether a result set's own updates are visible.
boolean
updatesAreDetected(int Type)
Indicates whether or not a visible row update can be detected by calling the method ResultSet.rowUpdated.
This section lists methods that can be implemented using the Interface ResultMetaData feature.
The following method can be used to implement the Interface ResultMetaData feature.
String
getColumnClassName(int column)
Returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.
This section describes methods that can be implemented using the Interface PreparedStatement feature.
The following methods can be used to implement the Interface PreparedStatement feature.
Result
SetMetaDatagetMetaData()
Gets the number, types and properties of a ResultSet's columns.
void
setDate(int parameter Index, Date x, Calendar cal)
Sets the designated parameter to a java.sql.Date value, using the given Calendar object.
void
setTime(int parameterIndex, Time x, Calendar cal)
Sets the designated parameter to a java.sql.Time value, using the given Calendar object.
void
setTimestamp(int parameter Index, Timestamp x, Calendar cal)
Sets the designated parameter to a java.sql.Timestamp value, using the given Calendar object.