Oracle® Database Lite Developer's Guide for Java
10g (10.0.0) Part No. B13811-01 |
|
![]() Previous |
![]() Next |
This chapter discusses the Oracle Database Lite support for JDBC programming. It includes the following topics:
JDBC is an application programmer's interface for accessing relational databases from Java programs. Oracle Database Lite supplies a native JDBC driver that allows Java applications to communicate directly with Oracle Database Lite's object relational database engine. Oracle Database Lite's implementation of JDBC complies with JDBC 1.22. In addition, Oracle Database Lite provides certain extensions specified by JDBC 2.0. Oracle Database Lite's extensions are compatible with the Oracle8i JDBC implementation. For a complete JDBC reference, see the Sun Microsystems web site.
If you are using the client/server model, include the olite40.jar in the 'system' classpath on the server machine. Include the 'user
' classpath on the client machine.
For more information on how to start the Multiuser Oracle Database Lite Database Service, see Section 2.2.1.2, "Starting a Multi-User Oracle Database Lite Database Service," in the Oracle Database Lite Developer’s Guide.
JDK 1.3.x or higher is required to connect to Oracle Database Lite.
There are three ways to connect to Oracle Database Lite.
Oracle Database Lite supports two types of drivers namely, Type 2 and Type 4. The Type 2 driver requires a native code on the client side. The Type 2 driver interfaces with the Oracle Database Lite ODBC driver through this native code.
Note: On the Windows platform, the Type 2 driver uses theoljdbc40.dll .
|
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. A Java applet can use the Type 4 JDBC driver.
Type 2 Driver Connection URL Syntax
DriverManager.getConnection("jdbc:polite@URL_Name:100:polite","system","admin");
This syntax is used to make a direct connection to a database on a client machine. Enter the URL definition as given below.
jdbc:polite@host:port:dsn
The following arguments can be made as part of the URL clause or as a separate key-value pair. There may be none or many occurrences of the key-value pair which provide additional information to the driver. All information that can be specified in the URL can be specified as a key-value pair. The information that is specified as a key-value pair always overrides the information that is specified in the URL.
The URL interpretation and key-value pair options for each argument are described in the following table.
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 user ID and password. If the database is encrypted, you must include the password in the key-value pair. |
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, refer the Oracle Database Lite Developer’s Guide. |
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 the Oracle Database Lite Developer’s Guide. |
Autocommit= | Commit behavior, either ON or OFF. |
CursorType= | Cursor behavior: DYNAMIC, FORWARD ONLY, KEYSET DRIVEN or STATIC. For more information on cursor types, see the Oracle Database Lite Developer’s Guide. |
UID= | User name |
PWD= | Password |
Example
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) { ... }
Type2 Client/Server Driver Connection URL Syntax
jdbc:polite[:uid / pwd]@[host]:[port]:dsn [;key=value]*
The URL can be used to connect to the Oracle Database Lite service using the Type 2 JDBC driver. For more information on how to install and start the Multiuser Oracle Database Lite Database Service, refer to Section 2.2.1.2, "Starting a Multi-User Oracle Database Lite Database Service," in the Oracle Database Lite Developer’s Guide.
Argument | Description |
---|---|
host | The name of the machine that hosts Oracle Database Lite 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 Oracle Database Lite service listens. The port number is optional and if omitted defaults to port "100". |
Example
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) {
You should enclose the getConnection
method in a try-catch block to intercept any SQL exception thrown during the connection attempt. You can insert an exception handling statement in the catch block.
Type4 (Pure Java) Driver Connection URL Syntax
The URL syntax for the type4 driver is given below.
jdbc:polite4[:uid/pwd]@[host]:[port]:dsn[;key=value]*
The parameter
4
indicates that the type4 driver is being used. For the rest of the parameters, see the definitions of those parameters for the type2 driver as described above.
Note: The URL works with the Oracle Database Lite service only. For more information on how to start and stop the Oracle Database Lite service, refer the Oracle Database Lite Developer’s Guide. |
After creating a Java stored procedure, you can execute the procedure from a JDBC application by performing the following steps.
By passing an SQL SELECT
string that executes the stored procedure to the Statement.executeQuery
method.
By using a JDBC CallableStatement
.
Note: For more information on creating stored procedures, see Chapter 4, "Java Stored Procedures and Triggers". |
The executeQuery
method executes table-level and row-level stored procedures. CallableStatement
currently only supports execution of table-level stored procedures.
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, by passing the SQL SELECT
string that invokes the Java stored procedure.
For example, suppose you want to execute a row-level procedure SHIP
on a table named 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, PreparedStatement
s 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 given below.
"SELECT SHIP(" + q + ")" + FROM INVENTORY WHERE PID = " + p + "FOR UPDATE");
To execute the stored procedure using a callable statement, create a CallableStatement
object and register its parameters as given below.
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 4, "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: When no longer needed, you should reclaim system resources by closing JDBC objects, such asResultset and Statement objects.
|
The Oracle Database 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 scrollable result sets. The Oracle Database Lite JDBC extensions are compatible with the Oracle8i JDBC implementation. However, Oracle Database Lite does not support the Oracle8i JDBC datatype extensions, Array
, Struct
, or REF
.
This section lists and describes the Oracle Database Lite datatype and data access extensions. For details regarding function syntax and call parameters, see the Sun Microsystems Java 2 specification at the Sun Javasoft website.
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 Database Lite BLOB class and their descriptions:
Table 3-1 Methods in the Oracle Database Lite BLOB Class
Table 3-2 lists the methods included in the Oracle Database Lite CLOB class and their descriptions.
Table 3-2 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 3-3 lists the data access functions included in the OracleResultSet
class.
Table 3-3 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.