How to pass LOBs as parameters from Java to a Database
Stored Procedure.
Date: 12-May-2003
Objective
After reading this document, you should be able to:
- Create LOB(Large OBject) objects
in a Java program and pass it as a parameter to a database stored procedure.
This document illustrates the same by giving the code snippet and description.
- Run the sample using the instructions provided.
Software Requirements
- JDK1.3.x or above This can be downloaded
from here.
- Oracle9i
Database Release 2 or later. This can be downloaded from here.
- Oracle9i JDBC Driver. This can
be downloaded from here.
Note: Oracle JDBC Driver is available with Oracle Database/client
installation. If you have a database/client installation,
you need not download JDBC driver separately.
Description
LOBs or Large Objects, are designed to support the
unstructured data that cannot be decomposed into standard components i.e
they can't be broken down into any finer structure in terms of database
storage. LOBs can store unstructured data and is optimized for large amounts
of data such as text, graphic images, still video clips, full motion video,
and sound waveforms. They provide a uniform way of accessing large unstructured
data within the database or outside.
With Oracle 8 and above, there can be multiple LOB
columns in a database table, which can also be of different types. The
storage capacity of LOBs is upto 4GB.
Oracle supports the following two types of LOBs.
- BLOB, CLOB, and NCLOB: These are stored in the database either in-line
in the table or in a separate segment or tablespace.
- BLOBs can store large amounts of binary data typically graphic
images and pictures.
- CLOBs can store large amounts of character data and are useful
for storing unstructured XML documents.
- NCLOB: consists of character data that corresponds to the national
character set defined for the Oracle database.
- BFILEs: These are stored as operating system files.
- BFILE is a LOB whose value is composed of binary ("raw")
data, and is stored outside the database tablespaces in a server-side
operating system file.
Sample Scenario:
Lets assume a story table in the database having two columns, an id and
a CLOB. The story text is stored in the CLOB column. A stored procedure
updates the story table in the database. It accepts two input parameters:
id and CLOB. Below is an example SQL code snippet:
Database preparation:
CREATE TABLE stories (id NUMBER(4), story CLOB);
INSERT INTO stories VALUES(1, EMPTY_CLOB());
COMMIT;
-- The following procedure updates the CLOB in the table.
CREATE OR REPLACE PROCEDURE updateStory(id NUMBER, colValue IN CLOB) IS
BEGIN
UPDATE stories s SET s.story = colValue
WHERE s.id = id ;
COMMIT;
-- Exception handling.
-- EXCEPTION
-- WHEN OTHERS THEN
-- <process exception here>
END updateStory;
/
|
|
We shall now look into different ways of passing CLOB
to a database stored procedure. We can create and manipulate CLOB object
in two ways:
With and without streaming the data. Data streaming is always recommended
for large amount of data.
Below is the code snippet for both the ways.
Without Streaming the data and using oracle.sql.CLOB
class
.............
.............
// Using Oracle implementation of CLOB
import oracle.sql.CLOB;
// IO Imports
import java.io.Writer;
/*
* This method uses temporary clob to create the CLOB object.
*/
private static CLOB getCLOB( String clobData )
throws Exception {
CLOB tempClob = null;
try {
// create a new temporary CLOB
tempClob = CLOB.createTemporary( conn, true, CLOB.DURATION_SESSION );
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open( CLOB.MODE_READWRITE );
// 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 exp ) {
// Free CLOB object
tempClob.freeTemporary( );
// do something
}
return tempClob;
}
|
|
Calling the database procedure by using
the above getCLOB method:
..............................
..............................
CallableStatement cs = null;
oracle.sql.CLOB clob = null;
String clobData = null;
.......................
.......................
// read the file whose content has to be passed as CLOB.
String lineSep = System.getProperty("line.separator");
BufferedReader br = new BufferedReader(new FileReader(filename));
String nextLine = "";
StringBuffer sb = new StringBuffer();
while ((nextLine = br.readLine()) != null) {
sb.append(nextLine);
sb.append(lineSep);
}
// convert to string
clobData = sb.toString();
// call Stored DB procedure for updating clob column
cs = (CallableStatement)
conn.prepareCall( "begin updateStory(?,?); end;" );
// Create the CLOB object from the getCLOB method listed above.
clob = getCLOB(clobData);
// Bind the columns values
cs.setObject(1, "1" );
cs.setObject(2, clob );
............................
............................
|
|
Creating CLOB object by streaming the data
..............................
..............................
public void callUpdateUsingStream(String id, String filename )
throws SQLException, FileNotFoundException {
CallableStatement cs = null;
.....................
.....................
conn.setAutoCommit(false);
String fileName = filename;
// Open the sample file as a stream
File file = new File(fileName);
FileInputStream fin = new FileInputStream(file);
// Call Stored DB procedure for updating clob column
cs = (CallableStatement)
conn.prepareCall( "begin updateStory(?,?); end;" );
// set id.
cs.setObject(1, id );
// use setAsciiStream to set the clob parameter.
cs.setAsciiStream(2,fin,(int)file.length());
cs.execute();
conn.setAutoCommit(true);
............................
............................
|
|
Source Code:
Click here to see the
full runnable source code.
Running the Java class
- Copy the full source code to a directory and save it as TestCLob.java
- Run the SQL script listed above to create
the table and the stored procedure as SCOTT user in the database.
- Edit TestClob.java and change the getConnection()
method to use your own database parameters.
// Connect to the database.
conn = DriverManager.getConnection
("jdbc:oracle:thin:@<hostname>:<port>:<sid>",
"scott", "tiger");
- From a command prompt in the copied directory, set the classpath to
include the
Oracle JDBC driver class: classes12.zip or
classes12.jar
- Compile the TestClob class.
Example:
javac -d . TestClob.java
- Run the class by giving values to the parameters.
java TestClob <id> <filename> <flag>
- <id> String value that denotes the
id of the record to be updated.
- <filename> String value that denotes
the file to be uploaded as content in the clob column.
- <flag> String value, can be either
"true" or "False".
"false" will use non-streaming
way to update the CLOB using callUpdate()
method.
"true" will use streaming way
to update thr CLOB using callUpdateUsingStreaming()method.
Example:
java TestClob "1" "d:\text.txt" "false"
- Check the file size and the CLOB size in the database to verify the
updated data by running the following query:
SQL> SELECT DBMS_LOB.GETLENGTH(story) FROM stories
Resources
Sample Application References on OTN:
Summary
This document has tried to explain and illustrate how
to create CLOB objects from a Java program and pass it to a database stored
procedure.
Please enter your
comments about this sample in the
OTN Sample Code Discussion Forum.
|
