Sample Illustrating
New LOB APIs and DateTime Support in Oracle9i
JDBC Drivers
Table Of Contents
This sample illustrates the access and manipulation
of LOBs using the New APIs in Oracle9i
JDBC Drivers. The use of new datatype Timestamp with Time
Zone and Timestamp with Local Time Zone are also illustrated.
Sample Application Scenario
With the help of a simple Advertisement Management application,
this sample illustrates new LOB APIs to support temporary LOBs,
trim, open/close APIs and Datetime support in Oracle9i
JDBC Driver. This sample application uses a database table - Printmedia
and a sequence - seqAdvId. When the application is invoked, the
table is created if it does not exist. Also it creates a sequence
in the database. The status of the connection is shown in the status
bar. Errors if any, are shown in the status bar. Using this application,
user can carry out the following operations:
- Create New Advertisement
- Update Existing Advertisement
- In this user can change the text of the advertisement.
- User can change the advertisement image/photo
The advertisement text and image are stored
in CLOB and BLOB columns respectively. User can make changes and
preview the advertisement. All the changes are saved in temporary
LOBs, when finalized, the temporary LOBs are updated to the database
and the memory is freed. Each time the LOB column gets updated,
the associated timestamp is also updated.
Here is the code usage for accessing LOB, Timestamp
with Time Zone and Timestamp with Local Time Zone columns. You can
find more details of the code in NewLOBAPISample.java
file under src/oracle/otnsamples/oracle9ijdbc/newlobapi
folder. Look into Description of Sample
Files section for folder and file details.
|
public class NewLOBAPISample
{
.......................
/**
* Inserts the Advertisement details into the database
table. Generates
* the required Timestamp objects.
**/
public void addAdvDetails() {
// Use OraclePreparedStatement object to use the
setXXX methods for setting
// Timestamp objects
OraclePreparedStatement opstmt = null;
// Create a PreparedStatement object and cast it to
an
// OraclePreparedStatement
opstmt =(OraclePreparedStatement)connection.prepareStatement("
INSERT INTO "+
"Printmedia VALUES(?,?,?,?,?,?,?) ");
...........................................
// CLOB data
of the temporary CLOB
opstmt.setCLOB(4, tempClob);
// BLOB data of the
temporary BLOB
opstmt.setBLOB(5, tempBlob);
// Set the current
Timestamp with TimeZone
opstmt.setTIMESTAMPTZ(6, getCurrentTimeStampTZ(connection));
// Set the current
Timestamp with Local TimeZone
opstmt.setTIMESTAMPLTZ(7, getCurrentTimeStampLTZ(connection));
// Execute the insert
opstmt.executeUpdate();
...........................................
}
.......................
|
|
This following notations are used through out
this document
|
Notation
|
Description
|
|
<SAMPLE_HOME>
|
Folder where the NewLOBAPISample will be unzipped.
|
|
<JAVA_HOME>
|
Folder where JAVA is installed.
|
|
<JDBC_HOME>
|
Folder where the Oracle JDBC driver is installed.
|
|
<ORACLE_HOME>
|
Folder where the ORACLE installed.
|
- Oracle9i JDeveloper (
Note: Oracle9i JDeveloper
is Oracle's Visual Java Development Tool and can be downloaded
from here )
or JDK1.3.x or above This can be downloaded from here .
- Oracle9i Database or higher
running SQL*Net TCP/IP listener. This can be downloaded from here .
- Oracle9i v9.0.1 JDBC Drivers
for use with JDK 1.3.x , downloadable from OTN
site.
If Oracle9i client is already installed on your system then this
driver need not be downloaded separately.
-
Edit NewLOBAPISample/Connection.properties file in your favorite editor. Change the HostName,
Port, SID, UserName and Password to connect to your own database.
|
HostName
|
= |
localhost
|
|
SID
|
= |
ORCL
|
|
Port
|
= |
1521
|
|
UserName
|
= |
scott
|
|
Password
|
= |
tiger
|
Running the application
using Oracle9i JDeveloper
|
Back To
Top |
This section describes the steps required in
running this application using Oracle9i
JDeveloper.
- Open Oracle9iJDeveloper
and use File/Open option to select the NewLOBAPISample.jws
from the NewLOBAPISample directory.
- Now, select Run/Run
NewLOBAPISample.jpr from main menu which opens up the browser and runs the
NewLOBAPISample.
|
Bookmark