/**
* @author Elangovan
* @version 1.0
*
* Development Environment : Oracle9i JDeveloper
* Name of the Application : NewLOBAPISample.java
* Creation/Modification History :
*
* Elangovan 21-Sep-2001 Created
* Venky 17-Feb-2003 Certfied on Redhat Linux Advanced Server 2.1
*
* Overview of Application :
*
* This sample illustrates the access and manipulation of LOBs using the New
* APIs in Oracle9i JDBC Drivers. The use of new datatypes Timestamp with Time
* Zone and Timestamp with Local Time Zone are also illustrated.
*
* Some of the new LOB APIs are:
*
* i) open(int)/close() : The use of open(int mode) API, opens the LOB in
* one of two modes namely MODE_READONLY or MODE_READWRITE. The close() method
* is used to close an already opened LOB. By enclosing all the LOB operations
* inside the open(int)/close() methods prevent the triggers that are fired
* each time a LOB is modified. The triggers are fired only once after the
* close() method is called and there by increasing the performance.
*
* ii) trim(offset) : Trims the content of the LOB to the length specified
* by the offset.
*
* iii) getXXXStream(offset) : Returns a stream of the LOB at the position
* specified by the offset. Reading or Writing can be done directly at
* any position of the LOB by specifying the offset.
*
* iv) createTemporary(conn,boolean,int) : Temporary LOBs can be used to store
* transient data. The data is stored in temporary table space rather than
* regular table space. The temporary LOB must be freed after it is no
* longer needed, else the temporary table space will not be reclaimed.
* They can exist for DURATION_CALL or DURATION_SESSION and can also be
* cached.
*
* DateTime datatype support.
*
* Timestamp with TimeZone : Date is shown with the TimeZone and
* fractional seconds precision which can range from 0 to 9.
*
* Timestamp with Local TimeZone : Date is normalized to the database
* time zone when stored in the database. When the date is retrieved,
* user sees the date in the session time zone.
*
* This sample illustrates the above APIs in an Advertisement management
* application. User can either Add or Update an advertisement. The
* advertisement text and image are stored in LOB columns. 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. The timestamp also changes each time the LOB is updated.
*/
package oracle.otnsamples.oracle9ijdbc.newlobapi;
// Oracle extensions
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import oracle.sql.TIMESTAMPLTZ;
// New datetime datatypes
import oracle.sql.TIMESTAMPTZ;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
// IO operations
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
// JDBC imports
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
// Date Formatter
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Properties;
import java.util.ResourceBundle;
// Utils
import java.util.TimeZone;
public class NewLOBAPISample {
// Connection instance
Connection connection = null;
// GUI handling
NewLOBAPIFrame gui;
// Temporary LOBs
BLOB tempBlob = null;
CLOB tempClob = null;
/**
* Constructor. Initializes the GUI
**/
public NewLOBAPISample() {
try {
gui = new NewLOBAPIFrame(this);
} catch (Exception ex) { // Trap errors
System.out.println(" Error : Couldn't Initialize frame " +
ex.toString());
}
}
/**
* Main entry point for the class. Connects to the database and populates the
* Advertisement JTable with rows from the Printmedia table.
**/
public static void main(String[] arg) {
// Instantiate the class
NewLOBAPISample sample = new NewLOBAPISample();
// Connect to database
sample.dbConnect();
// If connected, display the advertisement details
if (sample.connection != null) {
sample.checkTables();
sample.displayAdvertisements();
}
}
/**
* Dispatches the events that are generated in the GUI to the appropriate methods.
* The methods do the required JDBC operations.
**/
public void dispatchEvent(String eventName) {
if (eventName.equals("ADD ADV")) {
// Add new advertisement details
addAdvDetails();
} else if (eventName.equals("SHOW DATA")) {
// Displays the details of the selected advertisement
showAdvDetails();
} else if (eventName.equals("UPDATE ADV")) {
// Update an existing advertisement details
updateAdvDetails();
} else if (eventName.equals("FREE LOB")) {
// Neither add nor update has happened, free the LOBs
freeTempLOB();
} else if (eventName.equals("EXIT")) {
// Perform clean up and closes the application
exitApplication();
}
}
/**
* This method reads a properties file which is passed as
* the parameter to it and loads it into a java Properties
* object and returns it.
*/
public static Properties loadParams(String file) throws IOException {
// Loads a ResourceBundle and creates Properties from it
Properties prop = new Properties();
ResourceBundle bundle = ResourceBundle.getBundle(file);
Enumeration enum = bundle.getKeys();
String key = null;
while (enum.hasMoreElements()) {
key = (String) enum.nextElement();
prop.put(key, bundle.getObject(key));
}
return prop;
}
/**
* This Method initializes the variable 'connection' with a valid Database Connection.
* To initialize a Database connection, this method makes use of DataSources which
* were introduced in JDBC2.0 specification.
**/
void dbConnect() {
gui.putStatus("Trying to connect to the Database ...");
OracleDataSource ods = null;
try {
// Load the properties file to get the connection information
Properties prop = this.loadParams("Connection");
// Lookup in the naming service for the Datasource using the logical name
ods = new OracleDataSource();
// Sets the driver type
ods.setDriverType("thin");
// Sets the database server name
ods.setServerName((String) prop.get("HostName"));
// Sets the database name
ods.setDatabaseName((String) prop.get("SID"));
// Sets the port number
ods.setPortNumber(new Integer((String) prop.get("Port")).intValue());
// Sets the user name
ods.setUser((String) prop.get("UserName"));
// Sets the password
ods.setPassword((String) prop.get("Password"));
// Create a connection object
connection = ods.getConnection();
// Sets the auto-commit property for the connection to be false.
connection.setAutoCommit(false);
// Session time zone has to be set while accessing Timestamp with
// Time Zone and Timestamp with Local TimeZone datatypes.
// OracleConnection has the APIs to set the session time zone.
// Sets the session time zone to the time zone specified in the java
// virtual machine.
((OracleConnection) connection).setSessionTimeZone(TimeZone.getDefault()
.getID());
// Set AutoCommit to false
// LOB operations are performed much faster when AutoCommit is set to false.
connection.setAutoCommit(false);
gui.putStatus(" Connected to Database ...");
} catch (SQLException sqlEx) { // Trap SQL Errors
gui.putStatus("SQL Errors = " + sqlEx.toString());
}catch (Exception ex) { // Trap Generic Errors
gui.putStatus("Generic Errors = " + ex.toString());
}
}
/**
* Populates the JTable with the rows from Printmedia table
**/
public void displayAdvertisements() {
Statement stmt = null;
ResultSet rset = null;
try {
int productId = 0;
int advId = 0;
String productName = null;
// Create a statement object
stmt = connection.createStatement();
// Retrieve all the advertisement details
rset = stmt.executeQuery("SELECT ProductId, AdId, ProductName " +
"FROM Printmedia " +
"ORDER BY Productid,Adid ");
// Loop until all the rows are retrieved
while (rset.next()) {
productId = rset.getInt(1);
advId = rset.getInt(2);
productName = rset.getString(3);
// Add a row to the JTable
gui.addToJTable(productId, advId, productName);
}
// Enable the GUI for Add or Update
gui.enableButton();
gui.putStatus(" Click ADD / UPDATE to add new or update an " +
"existing advertisement ");
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while populating table : " + ex.toString());
} finally {
try {
// Close the resultset and statement objects
if (rset != null) {
rset.close();
}
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
gui.putStatus(" Error : " + e.toString());
}
}
}
/**
* 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;
int productId = gui.getProductId();
int advId = gui.getAdvId();
String productName = gui.getProductName();
try {
// Create a PreparedStatement object and cast it to an
// OraclePreparedStatement
opstmt = (OraclePreparedStatement) connection.prepareStatement(" INSERT INTO " +
"Printmedia VALUES(?,?,?,?,?,?,?) ");
opstmt.setInt(1, productId);
opstmt.setString(2, productName);
opstmt.setInt(3, advId);
// 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();
// Commit the insert
connection.commit();
// Add the inserted data into the JTable
gui.addToJTable(productId, advId, productName);
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while inserting Advertisement details : " +
ex.toString());
} finally {
try {
// Close the PreparedStatement object
if (opstmt != null) {
opstmt.close();
}
} catch (Exception ex) {
gui.putStatus(" Error : " + ex.toString());
}
// Free the temporary LOBs
freeTempLOB();
}
}
/**
* Retrieves a new Advertisement Id generated by the sequence(seqAdvId).
**/
public String genAdvId() {
Statement stmt = null;
ResultSet rset = null;
String advId = null;
try {
stmt = connection.createStatement();
// Get the next id
rset = stmt.executeQuery(" SELECT seqAdvId.nextval FROM DUAL ");
if (rset.next()) {
// Retrieve the generated sequence
advId = rset.getString(1);
}
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while generating AdvId : " + ex.toString());
} finally {
try {
// Close the resultset and statement objects
if (rset != null) {
rset.close();
}
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
gui.putStatus(" Error : " + e.toString());
}
}
// Return the generated Advertisement Id
return (advId);
}
/**
* Queries the database for the Advertisement details of the selected
* advertisement in the JTable.
**/
public void showAdvDetails() {
// Get the selected advertisement from the JTable
ArrayList product = getSelectedAdvertisement();
PreparedStatement pstmt = null;
OracleResultSet orset = null;
CLOB clob = null;
BLOB blob = null;
try {
// Query to format the datetime while retrieving data
pstmt = connection.prepareStatement(" SELECT AdFinaltext,AdImage,TimeAdCreated," +
" TimeAdFinalized " +
" FROM Printmedia WHERE AdId= ? ");
// The selected advertisement id in the JTable
int advId = Integer.parseInt(product.get(1).toString());
pstmt.setInt(1, advId);
// Execute the query
orset = (OracleResultSet) pstmt.executeQuery();
if (orset.next()) {
// Get the CLOB locator
clob = orset.getCLOB(1);
if (clob.length() != 0) {
// Create a temporary CLOB with data pointed by CLOB locator
createTempCLOB(clob);
}
// Get the BLOB locator
blob = orset.getBLOB(2);
if (blob.length() != 0) {
// Create a temporary BLOB with data pointed by BLOB locator
createTempBLOB(blob);
}
// Date, the advertisement was created
String advCreated = TIMESTAMPTZ.toString(connection,
orset.getTIMESTAMPTZ(3)
.toBytes());
// Date, the advertisement was last updated
Date dtLastUpdated = TIMESTAMPLTZ.toDate(connection,
orset.getTIMESTAMPLTZ(4)
.toBytes());
// Format the date in DD-Mon-YYYY HH:MM:SS , eg '22-Feb-1980 13:10:45'
SimpleDateFormat localzoneFormatter = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss ");
String advLastUpdated = localzoneFormatter.format(dtLastUpdated);
// Display the retrieved details
gui.frmAdv.displayAdvDetails( // Product Id
product.get(0).toString(),
// Adv Id
product.get(1).toString(),
// Product name
product.get(2).toString(),
// Adv Image
tempBlob,
// Adv Text
tempClob,
// DateTime Adv created
advCreated,
// DateTime Adv was finalized
advLastUpdated);
}
} catch (Exception exc) { // Trap errors
gui.putStatus("Error while displaying Adv Details : " +
exc.toString());
// Since an error has been caught , free the temporary LOBs
freeTempLOB();
} finally {
try {
// Close the PreparedStatement and Resultset objects
if (pstmt != null) {
pstmt.close();
}
if (orset != null) {
orset.close();
}
} catch (Exception e) {
gui.putStatus(" Error : " + e.toString());
}
}
}
/**
* Updates the temporary LOBs to the database table and sets the
* time the advertisement was finalized, to the current Timestamp.
**/
public void updateAdvDetails() {
// To access the setTIMESTAMPLTZ() API
OraclePreparedStatement opstmt = null;
try {
// Create a preparedstatement object and cast it
opstmt = (OraclePreparedStatement) connection.prepareStatement(" UPDATE Printmedia" +
" SET AdFinalText=?," +
" AdImage=?," +
" TimeAdFinalized=?" +
" WHERE AdId=? ");
// Update the CLOB Data
opstmt.setCLOB(1, tempClob);
// Update the BLOB Data
opstmt.setBLOB(2, tempBlob);
// Set the datetime the advertisement was finalized to current Timestamp
opstmt.setTIMESTAMPLTZ(3, getCurrentTimeStampLTZ(connection));
// The selected Advertisement Id
int advId = gui.getAdvId();
opstmt.setInt(4, advId);
// Execute the update
int i = opstmt.executeUpdate();
// Commit the update
connection.commit();
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while updating Adv details : " +
ex.toString());
} finally {
try {
// Free the temporary LOBs
freeTempLOB();
// Close the PreparedStatement object
if (opstmt != null) {
opstmt.close();
}
} catch (Exception e) {
gui.putStatus(" Error : " + e.toString());
}
}
}
/**
* Replaces a part of the Advertisement text with the new text, at the
* specified offset.
**/
void replaceAdvText(String str, long offset) {
try {
// Open the CLOB in readwrite mode
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream from the position specified by the offset
Writer writer = tempClob.getCharacterOutputStream(offset);
// Write the new text
writer.write(str);
// Flush and close the stream
writer.flush();
writer.close();
// Close the CLOB
tempClob.close();
// Refresh the contents of the preview frame
gui.refreshPreview();
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while updating temp CLOB: " + ex.toString());
// Since an error has been caught, free the temporary LOBs
freeTempLOB();
}
}
/**
* Creates a temporary CLOB with data read from the given CLOB locator
**/
public void createTempCLOB(CLOB clob) {
if (clob != null) {
try {
// Create a temporary CLOB with duration session
tempClob = CLOB.createTemporary(connection, true,
CLOB.DURATION_SESSION);
// Open the CLOB in readonly mode
clob.open(CLOB.MODE_READONLY);
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// No of bytes read each trip to database
int bytesread = 0;
// Get the input stream for reading from the CLOB
Reader clobReader = clob.getCharacterStream();
// Get the output stream for writing into the CLOB
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Create a buffer to read data
// getBufferSize() returns the optimal buffer size
char[] charbuffer = new char[clob.getBufferSize()];
// Read from the CLOB and write into the temporary CLOB
while ((bytesread = clobReader.read(charbuffer)) != -1)
tempClobWriter.write(charbuffer, 0, bytesread);
// Flush and close the streams
tempClobWriter.flush();
tempClobWriter.close();
clobReader.close();
// Close the CLOBs
clob.close();
tempClob.close();
} catch (Exception ex) {
gui.putStatus(" Error while retrieving CLOB from database " +
ex.toString());
// Since an error has been caught, free the temporary LOBs
freeTempLOB();
}
}
}
/**
* Creates a temporary BLOB with data from the given BLOB.
**/
public void createTempBLOB(BLOB blob) {
if (blob != null) {
try {
// Create a temporary BLOB with duration session
tempBlob = BLOB.createTemporary(connection, true,
BLOB.DURATION_SESSION);
// Open the BLOB in readonly mode
blob.open(BLOB.MODE_READONLY);
// Open the temporary BLOB in readwrite mode to enable writing
tempBlob.open(BLOB.MODE_READWRITE);
// No of bytes read each trip to database
int bytesread = 0;
// Get the input stream for reading from the BLOB
InputStream blobIStream = blob.getBinaryStream();
// Get the output stream for writing into the BLOB
OutputStream tempBlobOStream = tempBlob.getBinaryOutputStream();
// Create a buffer to read data
// getBufferSize() returns the optimal buffer size
byte[] buffer = new byte[blob.getBufferSize()];
// Read from the BLOB and write into the temporary BLOB
while ((bytesread = blobIStream.read(buffer)) != -1)
tempBlobOStream.write(buffer, 0, bytesread);
// Flush and close the streams
tempBlobOStream.flush();
tempBlobOStream.close();
blobIStream.close();
// Close the BLOBs
blob.close();
tempBlob.close();
} catch (Exception ex) {
gui.putStatus(" Error while retrieving BLOB from database" +
ex.toString());
// Since an error has been caught, free the temporary LOBs
freeTempLOB();
}
}
}
/**
* Updates the content of the temporary CLOB with the new data in the string
**/
void updateTempCLOB(String clobData) {
if (clobData != null) {
try {
// If the temporary CLOB has not yet been created, create new
if (tempClob == null) {
tempClob = CLOB.createTemporary(connection, true,
CLOB.DURATION_SESSION);
}
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Clear the previous contents of the CLOB
tempClob.trim(0);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(clobData);
// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();
// Close the temporary CLOB
tempClob.close();
} catch (Exception ex) { // Trap errors
gui.putStatus("Error while updating CLOB with string:" +
ex.toString());
// Since an error has been caught, free the temporary LOBs
freeTempLOB();
}
}
}
/**
* Updates the content of the temporary BLOB with the new data in the file.
**/
void updateTempBLOB(File file) {
if (file != null) {
try {
// If temporary BLOB has not yet been created, create new
if (tempBlob == null) {
tempBlob = BLOB.createTemporary(connection, true,
BLOB.DURATION_SESSION);
}
// Open the temporary BLOB in readwrite mode to enable writing
tempBlob.open(BLOB.MODE_READWRITE);
// Clear the contents of the temporary BLOB
tempBlob.trim(0);
// Get the input stream to read from the file
FileInputStream fileIStream = new FileInputStream(file);
// Get the output stream to write into the temporary BLOB
OutputStream tempBlobOStream = tempBlob.getBinaryOutputStream();
// Get the optimal buffer size to read bytes
byte[] buffer = new byte[tempBlob.getBufferSize()];
// No of bytes read in each trip to database
int bytesread = 0;
// Read from the file and write to the temporary BLOB
while ((bytesread = fileIStream.read(buffer)) != -1)
tempBlobOStream.write(buffer, 0, bytesread);
// Flush and close the streams
tempBlobOStream.flush();
fileIStream.close();
tempBlobOStream.close();
// Close the temporary BLOB
tempBlob.close();
} catch (Exception ex) {
gui.putStatus(" Error while writing a file into BLOB : " +
ex.toString());
// Since an error has been caught, free the temporary LOBs
freeTempLOB();
}
}
}
/**
* Creates a new Timestamp with Local TimeZone object having the current
* datetime and the database timezone.
**/
public TIMESTAMPLTZ getCurrentTimeStampLTZ(Connection conn) {
TIMESTAMPLTZ localTime = null;
// Timestamp with current datetime
Timestamp currentTimeStamp = new Timestamp(System.currentTimeMillis());
try {
// Create a Timestamp with Local TimeZone object
localTime = new TIMESTAMPLTZ(conn, currentTimeStamp);
} catch (Exception ex) { // Trap errors
gui.putStatus("Couldn't create a TIMESTAMP WITH LOCAL TIME ZONE object" +
ex.toString());
}
return (localTime);
}
/**
* Creates a Timestamp with TimeZone object having the current datetime
**/
public TIMESTAMPTZ getCurrentTimeStampTZ(Connection conn) {
TIMESTAMPTZ timeStampZone = null;
// Timestamp with current datetime
Timestamp currentTimeStamp = new Timestamp(System.currentTimeMillis());
try {
// Create a Timestamp with TimeZone object
timeStampZone = new TIMESTAMPTZ(conn, currentTimeStamp);
} catch (Exception ex) {
gui.putStatus(" Couldn't create a TIMESTAMP WITH TIME ZONE object " +
ex.toString());
}
return (timeStampZone);
}
/**
* Returns the Advertisement details of the selected advertisement in the
* JTable.
**/
ArrayList getSelectedAdvertisement() {
// Get the selected row in the JTable
int selectedRow = gui.advTable.getSelectedRow();
ArrayList selectedAdv = null;
// If no rows are selected, show this message in the status bar
if (selectedRow == -1) {
gui.putStatus(" Select an Advertisement from the Table ...");
} else {
// Get the advertisement details from the JTable
selectedAdv = gui.tableModel.getRow(selectedRow);
}
return (selectedAdv);
}
/**
* Frees the temporary LOBs when an exception is raised in the application
* or when the LOBs are no longer needed. If the LOBs are not freed , the
* space used by these LOBs are not reclaimed.
**/
void freeTempLOB() {
try {
if (tempClob != null) {
// If the CLOB is open, close it
if (tempClob.isOpen()) {
tempClob.close();
}
// Free the memory used by this CLOB
tempClob.freeTemporary();
tempClob = null;
}
if (tempBlob != null) {
// If the BLOB is open, close it
if (tempBlob.isOpen()) {
tempBlob.close();
}
// Free the memory used by this BLOB
tempBlob.freeTemporary();
tempBlob = null;
}
} catch (Exception ex) { // Trap errors
gui.putStatus(" Error while Freeing LOBs : " + ex.toString());
}
}
/**
* Checks if the table ('OrintMedia') is present, else creates it.
* Look into PopulateTable.java for more details
*/
private void checkTables() {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = connection.createStatement();
// check from User_tables data dictionary table if the table is existing.
rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables " +
" WHERE Table_Name = 'Printmedia' ");
// if the resultset of the above query does not have any record, it means
// table is not existing. So the table is created.
if (!rset.next()) {
// call the class to create the table
PopulateTable popTable = new PopulateTable(connection, gui);
popTable.createTableAndSequence();
gui.putStatus("Printmedia Table and Sequence created.");
}
} catch (SQLException sqlEx) {
gui.putStatus("Could not create table Printmedia or Sequence : " +
sqlEx.toString());
} finally {
try {
if (rset != null) {
rset.close();
}
if (stmt != null) {
stmt.close();
}
} catch (SQLException ex) {
}
}
}
/**
* This method performs the clean up action and closes the application
**/
void exitApplication() {
// If connected to database
if (connection != null) {
try {
// Close the connection
connection.close();
} catch (Exception ex) { // Trap errors
gui.putStatus(" Error while Closing the connection: " +
ex.toString());
}
}
// Close the application
System.exit(0);
}
}
Bookmark
