CS276 Advanced Oracle Using Java

1. CS276 Advanced Oracle Using Java Large Objects Chapter 8

2. 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

3. 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

4. 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.

5. 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:

6. 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.

7. 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

8. 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.

9. 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

10. 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

11. 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

12. 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

13. The Put, Get, and Stream Methods(continue) NOTE: source ( destination): • an array of bytes • file • CLOB • BLOB • others

14. 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

15. 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

16. 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

17. 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

18. 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()

19. 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”;

20. 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())" );

21. 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

22. 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();

23. 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();

24. 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

25. 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.

26. 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.

27. 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.

28. 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)

29. 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

30. 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

31. 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

32. 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

33. 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();

34. 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

35. Using the Streams to Write to CLOB and BLOB Columns Step 10: : close the stream objects myFileInputStream.close(); myOutputStream.close(); Complete program: LobExample3.java

36. 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 ( /)

37. 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.

38. 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 + “ ’))”;

39. 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:

40. 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.

41. 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

42. 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

43. 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.

44. 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);

45. 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.

46. 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();

47. 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

48. 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

49. 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();

50. 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