Illustrating Use of Large OBjects (LOBs)

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This sample application illustrates insertion, retrieval and manipulation of LOB columns in a Oracle9i database using JDBC 2.0 API. The above operations are performed on both Binary Large OBjects (BLOBs) and Character Large OBjects (CLOBs)

Working of the Sample

• When the user invokes the application, it connects to the database. The status and errors, if any, will be displayed in the status bar.
• If the connection was successful, and if the tables (OTN_AIRPORTS and OTN_AIRPORT_LOB_DETAILS) does not exist, the tables are created. Then all airports are retrieved and displayed in a JTable.
• When the user chooses an airport, the OTN_AIRPORT_LOB_DETAILS table is accessed and the "airport map" (BLOB) and "suggestion book" (CLOB) locators are retrieved. If the LOB details exist, then the data is read and displayed in the GUI. If LOB details do not exist, the "Load Sample Files" button is enabled, so that the user can insert LOB details for the chosen airport, using the sample files provided.
• When the user presses the "Load Sample Files" button, a new row is created in the OTN_AIRPORT_LOB_DETAILS table, and the LOB columns are populated with the sample files provided. These are then retrieved and displayed.
• When the user presses the "Add Suggestions" button, a dialog box is popped up where the user can enter new suggestions. These suggestions are then appended to the CLOB column in the OTN_AIRPORT_LOB_DETAILS table.

Here is the code where the LOB columns are inserted, retrived and manipulated. You can find more details of the code in LOBSample.java file under src/oracle/otnsamples/jdbc/lobtype folder. Look into Description of Sample Files section for folder and file details.

Accessing BLOB and CLOB columns

private void airportSelected(String airportCode) { .............. .............. Statement stmt = connection.createStatement(); // Query OTN_AIRPORT_LOB_DETAILS for the selected AIRPORT ResultSet lobDetails = stmt.executeQuery( "SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+ "WHERE airport_code = '" + airportCode + "'"); // Check if LOB columns exist if( lobDetails.next() ) { // LOB details exist // Display airport map and suggestion book (LOB details) drawBlob(lobDetails.getBlob(1), airportCode); writeClob(lobDetails.getClob(2), airportCode); gui.putStatus("Done retrieving and displaying LOB details"); ..................... ......................

 

Reading BLOB column

private void drawBlob(Blob blob, String airPCode) { ............ ............ // Open a stream to read the Blob data InputStream blobStream = blob.getBinaryStream(); // Open a file stream to save the Blob data FileOutputStream fileOutStream = new FileOutputStream(fileName); // buffer holding bytes to be transferred byte[] buffer = new byte[10]; int nbytes = 0; // Number of bytes read // Read from the Blob data input stream, and write to the // file output stream while((nbytes = blobStream.read(buffer)) != -1) //Read from Blob stream fileOutStream.write(buffer, 0, nbytes); // Write to file stream ..................... ......................

 

Reading CLOB column

void writeClob(Clob clob, String airPCode) { ........... ........... // Open a stream to read Clob data Reader clobStream = clob.getCharacterStream(); // Holds the Clob data when the Clob stream is being read StringBuffer suggestions = new StringBuffer(); // Read from the Clob stream and write to the stringbuffer int nchars = 0; // Number of characters read //Buffer holding characters being transferred char[] buffer = new char[10]; while((nchars = clobStream.read(buffer)) != -1) // Read from Clob suggestions.append(buffer, 0, nchars); // Write to StringBuffer clobStream.close(); // Close the Clob input stream ..................... ......................

 

Inserting BLOB and CLOB column

private void loadSamples( String airportCode ) { ................. ................. // Insert a row into OTN_AIRPORT_LOB_DETAILS with // LOB column values are initialized to empty PreparedStatement pstmt = connection.prepareStatement( "INSERT INTO OTN_AIRPORT_LOB_DETAILS( airport_code, airport_map,"+ "airport_sug_book) VALUES(? , EMPTY_BLOB() , EMPTY_CLOB())"); pstmt.setString(1, airportCode); // Bind AIRPORT code pstmt.execute(); // Execute SQL statement pstmt.close(); // Close statement // Retrieve the row just inserted, and lock it for insertion // of the LOB columns. Statement stmt = connection.createStatement(); ResultSet lobDetails = stmt.executeQuery( "SELECT airport_map, airport_sug_book FROM OTN_AIRPORT_LOB_DETAILS "+ "WHERE airport_code = '" + airportCode + "' FOR UPDATE"); // Load the properties file to get the sample files information Properties prop = this.loadParams("Misc"); String mapFileName = (String)prop.get("map"); String sugBookFileName = (String)prop.get("suggestions"); // Retrieve Blob and Clob streams for AIRPORT_MAP and // AIRPORT_SUG_BOOK columns, and load the sample files if( lobDetails.next() ) { // Get the Blob locator and open output stream for the Blob Blob mapBlob = lobDetails.getBlob(1); OutputStream blobOutputStream = ((oracle.sql.BLOB)mapBlob).getBinaryOutputStream(); // Open the sample file as a stream for insertion // into the Blob column File mapFile = new File(mapFileName); InputStream sampleFileStream = new FileInputStream(mapFile); // Buffer to hold chunks of data to being written to the Blob. byte[] buffer = new byte[10* 1024]; // Read a chunk of data from the sample file input stream, // and write the chunk to the Blob column output stream. // Repeat till file has been fully read. int nread = 0; // Number of bytes read while( (nread= sampleFileStream.read(buffer)) != -1 ) blobOutputStream.write(buffer, 0, nread); // Write to Blob // Close both streams sampleFileStream.close(); blobOutputStream.close(); // Get the Clob locator and open an output stream for the Clob Clob sugBookClob = lobDetails.getClob(2); Writer clobWriter = ((oracle.sql.CLOB)sugBookClob).getCharacterOutputStream(); // Open the sample file as a stream for insertion // into the Clob column File sugbookFile = new File(sugBookFileName); FileReader sugFileReader = new FileReader(sugbookFile); // Buffer to hold chunks of data to being written to the Clob. char[] cbuffer = new char[10* 1024]; // Read a chunk of data from the sample file input stream, // and write the chunk into the Clob column output stream. // Repeat till file has been fully read. nread = 0; while( (nread= sugFileReader.read(cbuffer)) != -1 ) clobWriter.write( cbuffer, 0, nread); // Write to Clob ..................... ......................

 

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.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 JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here.

• Unjar the provided LOBSample.jar using the following command 

> jar xvf LOBSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder LOBSamplewith all the source files


• Edit LOBSample/Misc.properties file located in the directory LOBSample . Change the values of map and suggestions to point to the absolute path of the files map.gif and suggestionbook.txt respectively. Note that both these files are provided with the samples in the directory LOBSample/misc

• Edit LOBSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to required schema in your database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the LOBSample.jws from the LOBSample directory.
• Next, select Project/Make LOBSample.jpr from main menu.
• Now, select Run/Run LOBSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from LOBSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\LOBSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\LOBSample> set JAVA_HOME=d:\jdk1.3.1
D:\LOBSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar. 
• Also add LOBSample directory where Connection.properties exists and the current directory to the CLASSPATH
  Example: 
  D:\LOBSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\LOBSample;.
  
• From the directory LOBSample\src\oracle\otnsamples\jdbc\lobtype, compile all the java files using javac:
  Example: 
  D:\LOBSample\src\oracle\otnsamples\jdbc\lobtype>javac -d . *.java
  
• Run the class file using java from  LOBSample\src\oracle\otnsamples\jdbc\lobtype directory
  Example:
  D:\LOBSample\src\oracle\otnsamples\jdbc\lobtype>
  java oracle.otnsamples.jdbc.lobtype.LOBSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from LOBSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to LOBSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar.
• LOBSample  directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/LOBSample:.
• From the directory LOBSample/src/oracle/otnsamples/jdbc/lobtype, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same LOBSample/src/oracle/otnsamples/jdbc/lobtypedirectory.
  Example:
  $java oracle.otnsamples.jdbc.lobtype.LOBSample