Cs276 advanced oracle using java
This presentation is the property of its rightful owner.
Sponsored Links
1 / 70

CS276 Advanced Oracle Using Java PowerPoint PPT Presentation


  • 86 Views
  • Uploaded on
  • Presentation posted in: General

CS276 Advanced Oracle Using Java. Large Objects Chapter 8. The Example Files.

Download Presentation

CS276 Advanced Oracle Using Java

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cs276 advanced oracle using java

CS276 Advanced Oracle Using Java

Large Objects

Chapter 8


The example files

The Example Files

Some of the examples read files and store their contents in the database, but the contents of a LOB doesn’t have to come from a file – the content can come from any valid source that your Java program can read as a string of characters or bytes.

Files used in the examples:

  • textContent.txt - A text file

  • binaryContent.doc A Word 2000 file


The example files continue

The Example Files ( continue)

NOTES:

  • The SQL Plus* script lob_user.sql contains the SQL DDL statements to create the database tables and other items required by examples.

  • The examples expect the path to the directory c:\sample_files. You can either copy the sample_files directory to that location and create a subdirectory retrieved, or modify the path in the programs to point to your own directory


Large objects lobs

Large Objects(LOBs)

JDBC supports three large object types:

  • CLOB The Character LOB type

  • BLOB The Binary LOB type

  • BFILE The Binary FILE type, which is used to store pointers to files located in the file system. You can read the contents of the actual file via a BFILE pointer in a Java program. These external files can be on a hard disk, a CD, a DVD and etc.


Large objects lobs continue

Large Objects(LOBs)(continue)

Columns created using CLOB and BLOB types have three advantages over those created using the older LONG and LONG RAW types:


Large objects lobs continue1

Large Objects(LOBs)(continue)

  • LOB columns can store up to four gigabytes of data

  • A table can have multiple LOB columns, but a table can only have one LONG or LONG RAW column

  • LOB data can be accessed in random order; LONG and LONG RAW data can only be accessed in sequential order.


Large objects lobs1

Large Objects(LOBs)

LOBs consist of two parts:

  • The LOB locator

    • A pointer that specifies the location of the LOB content

  • The LOB content

    • The actual character or byte data stored in the LOB


Large objects lobs continue2

Large Objects(LOBs) continue

NOTES:

Depending on the size of LOB content , the actual data will be stored in the table or out of the table.

If LOB is less than 4 kilobytes – the content is stored in the table in LOB column.

If LOB is bigger than 4 kilobytes – the content is stored outside the table

BFILE – only the locater is stored in the database. The locator points to the external file.


Large objects lobs2

Large Objects(LOBs)

The Example Tables:

  • clob_content

    • clob_column is used to store the character data contained in the textContent.txt

  • blob_content

    • blob_column is used to store the binary data contained in the binaryContent.doc

  • bfile_content

    • is used to store pointer to the two exetrnal files


Large objects lobs continue3

Large Objects(LOBs) continue

  • -- create the tables

  • CREATE TABLE clob_content (

  • file_name VARCHAR2(40) NOT NULL,

  • clob_column CLOB NOT NULL

  • );

  • CREATE TABLE blob_content (

  • file_name VARCHAR2(40) NOT NULL,

  • blob_column BLOB NOT NULL

  • );

  • CREATE TABLE bfile_content (

  • file_name VARCHAR2(40) NOT NULL,

  • bfile_column BFILE NOT NULL

  • );

  • NOTE: all columns are NOT NULL


The put get and stream methods

The Put, Get, and Stream Methods

To Write content to CLOB and BLOB columns using JDBC :

  • Use the put methods

  • Use stream

    NOTE: put(get) method is slightly faster than streams if content is less than 10 Kilobytes in size.

    Streams are faster if content is more than 10 Kilobytes


The put get and stream methods continue

The Put, Get, and Stream Methods(continue)

Package java.io

Classes used to manipulate data in the form of stream

  • InputStream – to read a stream of bytes from the source

  • OutputStream – to write a stream of bytes to the source

    NOTE: source ( destination) – an array of bytes, a file


The put get and stream methods continue1

The Put, Get, and Stream Methods(continue)

NOTE: source ( destination):

  • an array of bytes

  • file

  • CLOB

  • BLOB

  • others


The put get and stream methods1

The Put, Get, and Stream Methods

When working with LOBs using JDBC, you need to create a LOB object that is used to access a LOB column.

The Oracle.sql package contains:

oracle.sql.CLOB

oracle.sql.BLOB

oracle.sql.BFILE


Using the put methods to write to clob and blob columns

Using the Put Methods to Write to CLOB and BLOB Columns

putChars() method writes character content to a CLOB column

putBytes() – writes binary content to a BLOB column


Using the put methods to write to clob and blob columns1

Using the Put Methods to Write to CLOB and BLOB Columns

Steps to read content from the file and write it to a CLOB or BLOB column( see detailed explanation in the next slides):

  • Initialize the LOB column to set the LOB locator

  • Retrieve the row containing the LOB locator into a result set.

  • Create a LOB object in the Java program and read the LOB locator form the result set

  • Get the chunk size of the LOB from the LOB object


Using the put methods to write to clob and blob columns2

Using the Put Methods to Write to CLOB and BLOB Columns

5.Create a buffer to hold a block of data from the file

6. Create a file object

7. Create input stream objects to read the file contents.

8. Read the contents of the file and write it to the LOB using the following loop. While the end fo the file has not been reached

  • Read a block of data form the file into the buffer created in Step 5

  • Write the bugger contents to the OB host object

    9. Perform a commit to permanently save the changes

    10. Close the object used to read the file


Using the put methods to write to clob and blob columns3

Using the Put Methods to Write to CLOB and BLOB Columns

LobExample1.java

Example code reads the characters from the textContent.txt file and writes them to the clob_content table.

It contains the following methods:

writeCLOB()writeBLOB()

writeBFILE()


Cs276 advanced oracle using java

Using the Put Methods to Write to CLOB and BLOB Columns (continue)Steps to read content from the file and write it to a CLOB or BLOB column:

Step 1: Initialize the LOB Column to Set the LOB Locator

Store locator points to the LOB contents.

EMPTY_CLOB() initializes a CLOB column

EMPTY_BLOB() - initializes a BLOB column

String sourceDirectory = “c:\\sample_files”;

String fileName = sourceDirectory + “textContent.txt”;


Using the put methods to write to clob and blob columns continue

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

NOTE: two backslashes (\\) are used in the sourceDirectory string. The first backslash is used to escape the second one, so the final string is set to “c:\sample_files\”.

myStatement.executeUpdate(

"INSERT INTO clob_content(file_name, clob_column) " +"VALUES ('" + fileName + "', EMPTY_CLOB())" );


Using the put methods to write to clob and blob columns continue1

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

// step 1: initialize the LOB column to set the LOB locator

myStatement.executeUpdate(

"INSERT INTO blob_content(file_name, blob_column) " +

"VALUES ('" + fileName + "', EMPTY_BLOB())"

);

It uses the EMPTY_BLOB() function to initialize blob_column


Using the put methods to write to clob and blob columns continue2

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 2: retrieve the row containing the LOB locator

The result set is used to retrieve the locator set for clob_column in the row previously added to the clob_content table.

ResultSet clobResultSet = myStatement.executeQuery(

"SELECT clob_column " +

"FROM clob_content " +

"WHERE file_name = '" + fileName + "' " +

"FOR UPDATE"

);

clobResultSet.next();


Using the put methods to write to clob and blob columns continue3

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

Creates a ResultSet object named blobResultSet and retrieves the locator set for blob_column

ResultSet blobResultSet = myStatement.executeQuery(

"SELECT blob_column " +

"FROM blob_content " +

"WHERE file_name = '" + fileName + "' " +

"FOR UPDATE"

);

blobResultSet.next();


Using the put methods to write to clob and blob columns continue4

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 3: create a LOB object and read the LOB locator

CLOB myClob = ((OracleResultSet) clobResultSet).getCLOB("clob_column");

NOTE: import oracle.sql.CLOB or import .sql.* to use this class.

step 4: get the chunk size of the LOB from the LOB object

int chunkSize = myClob.getChunkSize();

getChunkSize() returns the optimal size of a LOB chunk in bytes and it’s determined by the Oracle database. The return value is 8,132 bytes for both CLOB and BLOB

step 5: create a buffer to hold a block of data from the file

char [] textBuffer = new char[chunkSize];

The text from the file is going to be stored in clob_column, and the binary data ( binaryContent.doc) is going to be stored in blob_column.

Because content is sent to LOB columns in chunks, you can read it into Java array


Using the put methods to write to clob and blob columns continue5

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 6: create a file object

File object acts as a “handler” to the file. Using it you can read the file using a Java input stream object ( see Step 7 and 8)

File myFile = new File(fileName);

step 7: create input stream objects to read the file contents

FileInputStream , InputStream , BufferedReader – read the contents of the textContent.txt file as ASCII text using buffer.


Using the put methods to write to clob and blob columns continue6

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 7: create input stream objects to read the file contents ( continue)

FileInputStream myFileInputStream = new FileInputStream(myFile);

InputStreamReader myReader =

new InputStreamReader(myFileInputStream);

BufferedReader myBufferedReader = new BufferedReader(myReader);

myFileInputStream object provides the low-level functionality to read the contents of a file as a stream bytes.

myReader – wrapper around myFileInputStream. Interprets the bytes as a stream of ASCII characters.

myBUfferedReader – read the stream of characters as blocks of multiple characters. Each block will be stored in the textBuffer array.

FileInputStream – reads the contents of the binary file. There is no need to convert bytes to ASCII.


Using the put methods to write to clob and blob columns continue7

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

  • step 8: read the file contents and write it to the LOB

  • While the end of the file has not been reached:

  • Read a block of data from the file into the buffer created in step 5

  • Copy the buffer contents to the LOB object

  • putChars() – writes characters to a specified position in the CLOB object.

  • Position is specified by passing a long value as a parameter, and the first character of the CLOB is considered to be a position 1.

  • putBytes() works similarly.

  • By using putChars() and putBytes() you can jump to any position in the LOB object.


Using the put methods to write to clob and blob columns continue8

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 8: read the file contents and write it to the LOB ( continue)

While loop reads characters from the text file and write them to myClob.

myBufferedReader.read() reads each block of characters.

read() returns -1 when the end of the file has been reached.

Each block is temporary stored in textBuffer, the contents of which are written to the end of myClob using putChars().

long position = 1;

int charsRead;

while ((charsRead = myBufferedReader.read(textBuffer)) != -1) {

// write the buffer contents to myClob using the putChars() method

myClob.putChars(position, textBuffer);

// increment the end position

position += charsRead;

}

NOTE: similarly it works for BLOB ( see LobExample1.java method writeBLOB)


Using the put methods to write to clob and blob columns continue9

Using the Put Methods to Write to CLOB and BLOB Columns(continue)

step 9: perform a commit

If auto-commit disabled, you must perform a commit to permanently record the contents of the LOB objects in the database

myStatement.execute("COMMIT");

step 10: close the objects used to read the file

myBufferedReader.close();

myReader.close();

myFileInputStream.close();

NOTE: BLOB – close myFileInputStream() , since that was the only object created


Using the streams to write to clob and blob columns

Using the Streams to Write to CLOB and BLOB Columns

  • LobExample3.java uses Java Streams to write the same file content in the clob_content and blob_content tables

    Steps to read content from the file and write it to a CLOB or BLOB column( see detailed explanation in the next slides):

  • Initialize the LOB column to set the LOB locator

  • Retrieve the row containing the LOB locator into a result set.

  • Create a LOB object in the Java program and read the LOB locator form the result set

  • Get the buffer size of the LOB from the LOB object


Using the streams to write to clob and blob columns1

Using the Streams to Write to CLOB and BLOB Columns

5. Create a byte buffer to hold a block of data from the file.

6. Create a file object.

7. Create a file input stream object to read the file contents.

8. Create an output stream object

9 . Read the contents of the file and write it to the LOB using the following steps:

  • Read a block of data from the file into the buffer created in Step 5)

  • Write the buffer contents to the output stream object, which sends the buffer contents to the LOB

    10. Close the file object


Using the streams to write to clob and blob columns2

Using the Streams to Write to CLOB and BLOB Columns

Steps 1 – 3, 6 and 7 are the same as used in the process using the put methods.

Difference: there is not commit step because content streamed to the LOB column is sent directly to the database and immediately made permanent.

step 4: get the buffer size of the LOB from the LOB object

Returns a default buffer size in bytes – 24, 396 bytes

int bufferSize = myClob.getBufferSize();

step 5: create a buffer to hold a block of data from the file

In Step 8 the content is streamed to a CLOB/BLOB column as bytes using OutputStream object. It requires a temporary byte array to store the content

byte [] byteBuffer = new byte[bufferSize];

NOTE: you can use byte arrays even when rading content from an ASCII text file that will be sent to a CLOB


Using the streams to write to clob and blob columns3

Using the Streams to Write to CLOB and BLOB Columns

Step 8 : create an input stream object and call the appropriate

myClob.getAsciiOutputStream – opens an output stream to the CLOB object. It causes the CLOB to interpret the stream of bytes sent to it as ASCII text.

getCharacterOutputStream() – causes the CLOB to interpret the stream of bytes as Unicode text.

NOTE: Unicode uses a two-byte character set and can represent over 65,000 characters; it may also be used to represent non-English characters.

CAUTION: when the stream to a LOB is opened using one of the methods above, you must not perform any other database operation until the stram has been close, otherwise data from the stream might be lost.

// LOB object output stream function

OutputStream myOutputStream = myClob.getAsciiOutputStream();


Using the streams to write to clob and blob columns4

Using the Streams to Write to CLOB and BLOB Columns

Step 9: Read the File Contents and Write It to the LOB

While the end of the file has not been reached:

  • Read a block of data from the file into the buffer created in Step 5)

  • Write the buffer contents to the output stream object, which sends the buffer contents to the LOB

    int bytesRead;

    while ((bytesRead = myFileInputStream.read(byteBuffer)) != -1) {

    // write the buffer contents to the output stream

    // using the write() method

    myOutputStream.write(byteBuffer);

    } // end of while


Using the streams to write to clob and blob columns5

Using the Streams to Write to CLOB and BLOB Columns

Step 10: : close the stream objects

myFileInputStream.close();

myOutputStream.close();

Complete program: LobExample3.java


Storing pointers to external files using bfile column

Storing Pointers to External Files Using BFILE Column

BFILE contains a pointer to an external file. The actual file itself is not stored in the database, only a pointer to that file is stored. The file must be accessible throught the file system.BFILE columns can point to files located on any media:

  • Hard disk

  • CD

  • DVD and etc

    Before storing a pointer to the file create a Directory Object.

    You must have the CREATE ANY DIRECTORY database privilege.

    CREATE DIRECTORY SAMPLE_FILES_DIR_AS ‘c:\sample_files’;

    NOTE: Windows uses the backslash character (\) in directories, while Linux and UNIX use the forward slash character ( /)


Storing pointers to external files using bfile column1

Storing Pointers to External Files Using BFILE Column

When you create a directory object you must ensure that:

  • The actual directory exists in the file system.

  • The user account in the operating system that was used to install the Oracle software has read permission on the directory and on any files that are to be pointed to.

    Populating a BFILE Column with a Pointer to a File

    BFILENAME() – populates the BFILE column with a pointer to your external file. Excepts 2 parameters: the database directory object’s name and the name of your file.


Storing pointers to external files using bfile column2

Storing Pointers to External Files Using BFILE Column

Populating a BFILE Column with a Pointer to a File

BFILENAME() – populates the BFILE column with a pointer to your external file. Excepts 2 parameters: the database directory object’s name and the name of your file.

The following examples creates two strings.

String directory = “SAMPLE_FILES_DIR”;

String fileName = “textContent.txt”;

The next example adds a row to the bfile_content table using the BFILENAME() function;

myStatement.executeUpdate(

“INSERT INTO bfile_content ( file_name, bfile_column) “ +

“VALUES (‘ “ + fileName+ “’, “+

“BFILENAME(‘ ” + directory +” ’ , ’ ” + fileName + “ ’))”;


Storing pointers to external files using bfile column3

Storing Pointers to External Files Using BFILE Column

  • LobExample1.java

  • Example code reads the characters from the textContent.txt file and writes them to the clob_content table.

  • It contains the following methods:


Storing pointers to external files using bfile column4

Storing Pointers to External Files Using BFILE Column

  • writeCLOB() reads the characters from an ASCII text file and writes them to clob_column in the clob_content table using putChars().

  • writeBLOB() Reads the bytes from a binary file and writes them to blob_column in the blob_conent table using the putBytes() methos

  • addBFILE() –add a pointer , which is stored in bfile_column of the bfile_content table, to a specified file.


Using the getmethods to read from clob and blob columns

Using the GetMethods to Read from CLOB and BLOB Columns

Steps to read content from a CLOB or BLOB and write it to a file

  • step 1: retrieve the row containing the LOB locator

  • step 2: create a LOB object and read the LOB locator

  • step 3: get the chunk size of the LOB from the LOB object

  • step 4: create a buffer to hold a chunk of data retrieved from the LOB object


Using the getmethods to read from clob and blob columns1

Using the GetMethods to Read from CLOB and BLOB Columns

step 5: create a file object

step 6: create output stream objects to write the LOB contents to the new file

step 7: get the length of the LOB contents from the LOB object

step 8: while the end of the LOB contents has not been reached,read a chunk of data from the LOB into the buffer, and write the buffer contents to the file

step 9: close the stream objects


Using the getmethods to read from clob and blob columns2

Using the GetMethods to Read from CLOB and BLOB Columns

Steps 6,7 and 8 are new.

Assume that steps 1 – 5 have donw and the LOB locator for clob_column has been read into a LOB object named myClob. The content will be rad from the database using this object. You can also assume that the new file, to which the content will be written, is accessing using a File object named myFile.


Using the getmethods to read from clob and blob columns3

Using the GetMethods to Read from CLOB and BLOB Columns

step 6: create output stream objects to write the LOB contents to the new file

FileOutputStream myFileOutputStream =

new FileOutputStream(myFile);

OutputStreamWriter myWriter =

new OutputStreamWriter(myFileOutputStream);

BufferedWriter myBufferedWriter = new BufferedWriter(myWriter);


Using the getmethods to read from clob and blob columns4

Using the GetMethods to Read from CLOB and BLOB Columns

myFileOutputStream – provides the low-level functionality to write the cotnents of a file as a stream of bytes

myWriter object – wrapper around myFileOutputStream and allows those bytes to be interpreted as a stream of ASCII characters.

myBufferedWriter – writes the stream of characters as blocks of multiple characters

NOTE: to write bynary content you only need FileOutputStream object because the bytes can be written to the file without the need for conversion.


Using the getmethods to read from clob and blob columns5

Using the GetMethods to Read from CLOB and BLOB Columns

step 7: get the length of the LOB contents from the LOB object

long clobLength = myClob.length();


Using the getmethods to read from clob and blob columns6

Using the GetMethods to Read from CLOB and BLOB Columns

step 8: Read the LOB Contents and Write It to the File

while the end of the LOB contents has not been reached,read a chunk of data from the LOB into the buffer,

and write the buffer contents to the file

  • for (

  • long position = 1;

  • position <= clobLength;

  • position += chunkSize

  • ) {

  • // read a chunk of data from myClob using the getChars() method

  • // and store it in the buffer

  • int charsRead =

  • myClob.getChars(position, chunkSize, textBuffer);

  • // write the buffer contents to the file

  • myBufferedWriter.write(textBuffer);

  • } // end of for


Using streams to read from clob and blob columns

Using Streams to Read from CLOB and BLOB Columns

  • Retrieve the row containing the LOB locator into a result set

  • Create a LOB object in your java program and read the LOB locator

  • Create an input stream object and call the appropriate LOB object input stream function

  • Read the LOB contents using the input stream object and write it to a file

  • Close the input stream object


Using streams to read from clob and blob columns1

Using Streams to Read from CLOB and BLOB Columns

Step 3 Create an input stream object and call the appropriate LOB object input stream function

NOTE: once a stream has been opened , you must not perform any other database operation until that stream has been closed. Data from the stream might be lost if you don’t follow this rule.

InputStream myInputStream = myClob.getAsciiStream();


Using streams to read from clob and blob columns2

Using Streams to Read from CLOB and BLOB Columns

Step 4 Read the LOB Contents Using the InputStream Object and Write It to a File

This steps is to read the LOB contents form the InputStream object, which was created in the Step 3, and save the contents to a new file


Using streams to read from clob and blob columns3

Using Streams to Read from CLOB and BLOB Columns

private static void saveFile(

InputStream myInputStream,

String fileName

) throws IOException {

// create a file object

File myFile = new File(fileName);

// create a file output stream

FileOutputStream myFileOutputStream =

new FileOutputStream(myFile);

// read the contents from the input stream until

// the end has been reached (the read() method

// returns -1 at the end)

byte [] byteBuffer = new byte[8132];

int bytesRead;

while ((bytesRead = myInputStream.read(byteBuffer)) != -1) {

// write the input to the file

myFileOutputStream.write(byteBuffer);

} // end of while

// close the file output stream

myFileOutputStream.close();

} // end of saveFile()


Using streams to read from clob and blob columns4

Using Streams to Read from CLOB and BLOB Columns

Notice the use of an array 8,132 bytes named byteBuffer – this buffer has the same size as the default chunk size for a CLOB or BLOB. It’s based on the total length of LOB contents.

String saveFileName = “c:\\sample_files\\retrieved\\”+fileName;

saveFile(myInputStream, saveFileName);


Using streams to read from clob and blob columns5

Using Streams to Read from CLOB and BLOB Columns

Step 5 Close the InputStream Object

myInputStream.close();


Reading external files using bfile pointers

Reading External Files Using BFILE Pointers

BFILE column only stores a pointer to the external file, you can actually read the file via that pointer.

  • Retrieve the row containing the BFILE column into a result set

  • Create a BFILE object and read the BIFLE locator from the result set

  • Get the external file name from the BFILE object

  • Check that the external file pointed to by the BFIEL object exists

  • Open the external file via the BFILE object

  • Create an input stream object to read the external file contents via the BFILE object.

  • Save the file contents read from the input stream to a new file

  • Close the input stream object

  • Close the external file via the BFILE object


Reading external files using bfile pointers1

Reading External Files Using BFILE Pointers

step 1: retrieve the row containing the BFILE locator

ResultSet bfileResultSet = myStatement.executeQuery(

"SELECT bfile_column " +

"FROM bfile_content " +

"WHERE file_name = '" + fileName + "'"

);

bfileResultSet.next();


Reading external files using bfile pointers2

Reading External Files Using BFILE Pointers

step 2: create a BFILE object and read the locator

BFILE myBfile =

((OracleResultSet) bfileResultSet).getBFILE("bfile_column");

step 3: get the file name from the BFILE object

String bfileName = myBfile.getName();

step 4: check that the external file pointed toby the BFILE object exists

myBfile.fileExists();

step 5: open the external file via the BFILE object

myBfile.openFile();

step 6: create an input stream object to read the external

file contents via the BFILE object

InputStream myInputStream = myBfile.getBinaryStream();


Reading external files using bfile pointers3

Reading External Files Using BFILE Pointers

step 7: save the file contents read from the input stream to a new file

String saveFileName = "C:\\sample_files\\retrieved\\"+ "retrievedBFILE" +

bfileName;

saveFile(myInputStream, saveFileName);

step 8: close the input stream

myInputStream.close();

step 9: close the external file via the BFILE object

myBfile.closeFile();


Using the getmethods to read from clob and blob columns7

Using the GetMethods to Read from CLOB and BLOB Columns

LobExample2.java

it shows how to read from CLOB and BLOB columns and how to read external files pointed to by BFILE columns

This program contains the following methods:

readCLOB()readBLOB()

retrieveBFILE()


Long and long raw columns

LONG and LONG RAW Columns

We use two new tables:

  • long_content

    CREATE TABLE long_content (

    file_name VARCHAR2(40) NOT NULL,

    long_column LONG NOT NULL );

  • long_raw_content

    CREATE TABLE long_raw_content (

    file_name VARCHAR2(40) NOT NULL,

    long_raw_column LONG RAW NOT NULL

    );


Writing to long and long raw columns

Writing to LONG and LONG RAW Columns

Example for this section is LongExample1.java

You can use eight steps to use a stream to write to a LONG or LONG RAW column, assuming that the content is read from a file ( see next a couple of slides for steps):


Writing to long and long raw columns1

Writing to LONG and LONG RAW Columns

step 1: create a file object

File myFile = new File(sourceDirectory + fileName);

step 2: get the file length

int fileLength = (int) myFile.length();

step 3: create an input stream object to read the file contents

InputStream myInputStream = new FileInputStream(myFile);

step 4: create a prepared statetment object to add a row to the long_content table

PreparedStatement myPrepStatement = myConnection.prepareStatement(

"INSERT INTO long_content(file_name, long_column) " +

"VALUES (?, ?)"

);

This statement will add a row to the long_content table , with the values for the file_name and long_column columns being supplied via the two placeholders.

The placeholders are indicated by the question marks.


Writing to long and long raw columns2

Writing to LONG and LONG RAW Columns

step 5: bind the file name and input stream to the prepared statement object

setString() – binds the fileName string

setAsciiStrem – binds the myInputStream object. It causes the characters in myInputStream to be interpreted as ASCII text.

setBinaryStream() methos causes myInputStream to be interpreted as bytes.

myPrepStatement.setString(1, fileName);

myPrepStatement.setAsciiStream(2, myInputStream, fileLength);

fileLength is defined in step 2

NOTE: if you are using a Unicode file, you use the setUnicodeStream() method to bind the InputStream object to the PreparedStatement


Writing to long and long raw columns3

Writing to LONG and LONG RAW Columns

step 6: run the SQL statement contained in the

prepared statement object

It runs the INSERT statement , which adds a row to the long_content table.

myPrepStatement.execute();

step 7: perform a commit

To permanently record the results of the INSERT statement.

myConnection.commit();

step 8: close the input stream and prepared statement objects

myInputStream.close();

myPrepStatement.close();


Writing to long and long raw columns4

Writing to LONG and LONG RAW Columns

Complete program named LongExample1.java

illustrates how to write content to LONG and LONG RAW columns

writeLong() – reads the characters from an ASCII text file and writes those characters to long_column in the long_content table

writeLONGRAW() – reads the bytes from a binary file and writes those bytes to long_raw_column in the long_raw_content table


Reading from long and long raw columns

Reading from LONG and LONG RAW Columns

To read content previously stored in a LONG or LONG RAW column, you can use strams.

For example, to get at the text previously stored in long_column of the long_content table , you can use the getAsciiStream() method; to get at the bytes previously stored in long_raw_column of the long_raw_content tables, you use the getBinaryStream() method.

LongExample2.java – illustrates how to read content from LONG and LONG RAW columns using streams.


Reading from long and long raw columns1

Reading from LONG and LONG RAW Columns

NOTE:

Prior to running this program , the long_content and long_raw_content tabes should already contain a row. You can add a row to these tables by running the LongExamples1 program shown earlier.


Reading from long and long raw columns2

Reading from LONG and LONG RAW Columns

This program contains the following methods that do the work:

readLONG() – reads the text previously stored in long_column and writes that text to a new file. This method is invoked once by the program and writes the text from long_column to a new file named readLONGTextContent.txt in the directory C:\sample_files\retrieved


Reading from long and long raw columns3

Reading from LONG and LONG RAW Columns

readLONGRAW() – reads the bytes previously stored in long_raw_column and writes those bytes to a new file. This method is invoked once by the program and writes the bytes from long_raw_column to a new file named readLONGRAWbinaryContent.doc in the directory c:\sample_files\retrieved


Reading from long and long raw columns4

Reading from LONG and LONG RAW Columns

// step 1: retrieve the rows into a result set object

ResultSet longResultSet = myStatement.executeQuery(

"SELECT file_name, long_column " +

"FROM long_content"

);

// step 2: create an input stream object

InputStream myInputStream = null;

// step 3: read the rows from the result set object

while (longResultSet.next()) {

String fileName = longResultSet.getString("file_name");

String saveFileName =

targetDirectory + "readLONG" + fileName;


Reading from long and long raw columns5

Reading from LONG and LONG RAW Columns

// step 3a: get the ASCII stream from the

// the result set, and store it in the

// input stream object

myInputStream = longResultSet.getAsciiStream("long_column");

// step 3b: save the input stream object to a new file

saveFile(myInputStream, saveFileName);

System.out.println("Read LONG and saved file " +

saveFileName);

} // end of while

// step 4: close the input stream object

myInputStream.close();


  • Login