Several cases of manipulating Oracle Database LOB fields through JDBC During the Conditioning Center, Yizhen 2003-6-10 15:14:19
In Oracle, the field of LOB (Large Object, large object) is now more and more. Because of this type of field, capacity is large (up to 4GB data), and a table can have multiple fields, which is flexible, suitable for business areas of data volume (such as image, file) Wait). LONG, long RAW, etc. field, although the storage capacity is not small (up to 2GB), but now there is only one such type of field limit, it is rarely used. The LOB type is divided into two types of BLOB and Clob: BINARY LARGE Object, which is suitable for storage non-text byte stream data (such as programs, images, audio, etc.). CLOB, ie character largon Object, is related to character set, suitable for storage text type data (such as historical files, most works, etc.). Below the program instance illustrate several cases of manipulating the Oracle database LOB type field through the JDBC. First establish a database table for the following two tests, the Power Designer PD model is as follows:
Construction table SQL statement is: CREATE TABLE TEST_CLOB (ID Number (3), Clobcol Clob) Create Table Test_blob (Id Number (3), Blobcol Blob), access to the Clob object 1, insert a new Clob object into the database Public Static Void Clobinsert (String Infile) Throws Exception {/ * Settings Not Auto Submit * / Boolean DefaultCommit = Conn.getautocommit (); conn.setautocommit (false); try {/ * Insert an empty Clob object * / STMT. executeUpdate ( "INSERT INTO TEST_CLOB VALUES ( '111', EMPTY_CLOB ())"); / * this query and lock CLOB object * / ResultSet rs = stmt.executeQuery ( "SELECT CLOBCOL FROM TEST_CLOB WHERE ID = '111' FOR UPDATE" ); while (rs.next ()) {/ * Remove this Clob object * / oracle.sql.clob Clob = (ORACLE.SQL.CLOB) Rs.getClob ("Clobcol"); / * Writes to the Clob object data * / BufferedWriter out = new BufferedWriter (clob.getCharacterOutputStream ()); BufferedReader in = new BufferedReader (new FileReader (infile)); int c; while (! (c = in.read ()) = - 1) {out .write (c);} in.close (); out.close ();} / * official submission * / conn.commit ();} catch (exception ex) {/ * error Roll * / conn.rollback ); Throw EX;} / * Restore the original submission status * / conn.setautocommit (defaultCommit);} 2, modify CLO B object (is a modified modification based on the original CLOB object) Public Static Void ClobModify (String Infile) Throws Exception {/ * Settings Not Auto Submit * / Boolean DefaultCommit = Conn.getAutoCommit (); conn.setautocommit (False) Try {/ * Query the Clob object and lock * / resultSet = stmt.executeQuery ("SELECT Clobcol from test_clob where id = '111' for update"; while (rs.next ()) {/ * Get this CLOB object * / oracle.sql.clob clob = (ORACLE.SQL.CLOB) rs.getClob ("Clobcol"); / * Overlay modification * / bufferedWriter out = new bufferedWriter (Clob.getCharacteroutputStream ()); bufferedReader in = New BufferedReader (InfileReader (Infile); INT C; While ((c = in.read ())! = - 1) {Out.write (c);
} in.close (); out.close ();} / * official submission * / conn.commit ();} catch (exception ex) {/ * error back * / conn.rollback (); throw ex;} / * Restore the original submission status * / conn.setautocommit (defaultCommit);} 3, replace the CLOB object (cleared the original CLOB object, replace it with a brand new Clob object) public static void clobreplace (string infile) throws exception {/ * set not automatically given to submit * / boolean defaultCommit = conn.getAutoCommit (); conn.setAutoCommit (false); try {/ * empty original CLOB object * / stmt.executeUpdate ( "UPDATE TEST_CLOB sET CLOBCOL = EMPTY_CLOB () WHERE ID = '111 '"); / * Query the Clob object and lock * / resultSet RS = stmt.executeQuery (" SELECT Clobcol from test_clob where id = 111' for update "); while (rs.next ()) {/ * Get this CLOB Object * / oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ( "CLOBCOL"); / * update data * / BufferedWriter out = new BufferedWriter (clob.getCharacterOutputStream ()); BufferedReader in = new BufferedReader (New fileReader); int C; while ((c = in.read ())! = - 1) {Out.write (); out.close ();} / * Officially submit * / conn.commit ();} catch (exception ex) {/ * error back * / conn.rollback (); throw ex;} / * recovery Rehabilitation Submit status * / conn.setautocommit (defaultCommit);} 4, Clob object read public static void clobread (String outfile) throws exception {/ * setting does not automatically submit * / boolean defaultCommit = conn.getautocommit (); conn. SetAutocommit (false); try {/ * query clob object * / resulttSet = stmt.executeQuery ("SELECT * version); while (rs.next ()) {/ * Get Clob object * / oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob ( "CLOBCOL"); / * output in the form of characters * / BufferedReader in = new BufferedReader (clob.getCharacterStream ()); BufferedWriter out = new BufferedWriter (New FileWriter); INT C; While ((c = in.read ())! =
-1) {OUT.WRITE (C);} out.close (); in .close ();}} catch (exception ex) {conn.rollback (); throw ex;} / * Restore original submission status * / Conn.SetAutoCommit (DefaultCommit);} II, Blob object Access 1, insert a new blob object into the database PUBLIC VOID BLOBINSERT (STRING Infile) Throws Exception {/ * Settings Not Auto Submit * / Boolean DefaultCommit = Conn .Getautocommit (); conn.setautocommit (false); try {/ * Insert an empty blob object * / stmt.executeUpdate ("INSERT INTO TEST_BLOB VALUES (" 222 ', EMPTY_BLOB () ")"); / * Query this BLOB Object and lock * / resultset = stmt.executeQuery ("SELECT BLOBCOL from Test_blob where id = '222' for update); while (rs.next ()) {/ * Remove this BLOB object * / Oracle.sql.blob blob = (oracle.sql.BLOB) rs.getBlob ( "BLOBCOL"); (new FileInputStream BufferedInputStream in = new BufferedInputStream; / * write to the BLOB data in the object * / BufferedOutputStream out = new BufferedOutputStream (blob.getBinaryOutputStream ()) (Infile); int C; while ((c = in.read ()))! = - 1) {Out.write (); out.close (); out.close ();} / * official submission * / conn.commit ();} catch (exception ex) {/ * error back * / conn.rollback (); throw EX } / * Restore the original submission status * / conn.setAutocommit (defaultCommit);} 2, modify the BLOB object (which is a modification of the over-blob object) public static void blobmodify (String Infile) throws exception {/ * not automatically given to submit * / boolean defaultCommit = conn.getAutoCommit (); conn.setAutoCommit (false); try {/ * query object and lock BLOB * / ResultSet rs = stmt.executeQuery ( "SELECT BLOBCOL FROM TEST_BLOB WHERE ID = '222 'For update "); while (rs.next ()) {/ * Remove this blob object * / oracle.sql.blob blob = (oracle.sql.blob) rgetblob (" blobcol "); / * To blob Write data in object * / bufferedoutputstream out = new buffremeTputStream (blob.getbinaryoutputstream ()); bufferedinputstream in = BUFFEREDINPUTSTREAM IN =
New BufferedInputStream (Infile); INT C; While ((c = in.read ())! = - 1) {Out.write (); out.close (); } / * Official submission * / conn.commit ();} catch (exception ex) {/ * error back * / conn.rollback (); throw ex;} / * Restore original submission status * / conn.setautocommit (DefaultCommit) );} 3, replace the BLOB object (cleared the original blob object, replace it with a brand new blob object) Public static void blobreplace (String Infile) throws exception {/ * setting not Auto Submit * / boolean defaultCommit = conn.getautocommit ( ); conn.setautocommit (false); try {/ * clever original BLOB object * / stmt.executeUpdate ("Update test_blob set blobcol = Empty_blob () where id = '222'); / * Query this BLOB object and lock * / ResultSet RS = Stmt.executeQuery ("SELECT Blobcol from test_blob where id = '222' for update"); while (rs.next ()) {/ * Remove this BLOB object * / Oracle.sql.blob blob = (Oracle .sql.BLOB) rs.getBlob ( "BLOBCOL"); / * write to the BLOB data in the object * / BufferedOutputStream out = new BufferedOutputStream (blob.getBinaryOutputStream ()); BufferedInputStream in = new BufferedInputStream (new FileInputStream (infile)) ; int C; while ((c = in.read ())! = - 1) {OUT. Write (c);} in.close (); out.close ();} / * official submission * / conn.commit ();} catch (Exception EX) {/ * error back * / conn.rollback () Throw EX;} / * Restore the original submission status * / conn.setautocommit (DefaultCommit);} 4, blob object read public static void blobread (String outfile) throws exception {/ * setting not Auto Submit * / boolean defaultCommit = Conn.getautocommit (); conn.setautocommit (false); try {/ * query blob object * / resultset = stmt.executeQuery ("SELECT Blobcol from test_blob where id = '222'); while (rs.next () ) {/ * Remove this BLOB object * / oracle.sql.blob Blob = (Oracle.Sql.blob) rgetblob ("blobcol"); / * Output * / bufferedoutputstream out =
new BufferedOutputStream (new FileOutputStream (outfile)); BufferedInputStream in = new BufferedInputStream (blob.getBinaryStream ()); int c; while ((c = in.read ()) = - 1!) {out.write (c); } in.close (); out.close ();} / * official submission * / conn.commit ();} catch (exception ex) {/ * error back * / conn.rollback (); throw ex;} / * Restore the original submission status * / conn.setAutocommit (defaultCommit);} Observe the above program to access the LOB type field, we can see that there are several significant differences compared to other types of fields: one must Cancel is automatically submitted. Before the start of the access operation, you must cancel the automatic submission with SetAutocommit (FALSE). Other types of fields have no special requirements. This is because when accessing the LOB type field, you can usually perform multiple operations. In this case, Oracle will throw a "read violation order" error. Second, the insert is different. LOB data cannot be directly inserted as other types of data (INSERT). An empty LOB object must be inserted before insertion, and the CLOB type empty object is EMPTY_CLOB (), the blob type empty object is EMPTY_BLOB (). Then the select command query gets the previously inserted record and locks, followed by modifying the empty object to the LOB object to be inserted. Third, the modification is different. Other types of fields are modified, use the Update ... set ... command. The Lob type field can only be used to query and lock it with the select ... for update command, and then you can modify it. And there are two modifications: First, the modification (ie, overwriting modification) on the basis of the original data, perform the select ... for update and then change the data; the other is the replacement (first data clear, then modify), first execute The UPDATE command sets the value of the LOB field to an empty LOB object and then performs the first change. Replacement methods are recommended to achieve the same effect as follows with other fields. Fourth, the LOB operation class provided by the Database JDBC driver is used when accessing. For Oracle Database, Oracle.Sql.Clob and Oracle.Sql.blob should be used. When not using the LOB class provided by the Database JDBC driver, the program is easy to appear "Abstract Method Call" error, because JDBC defined by Java.sql.clob and java.sql.blob interface, some of them Not truly implemented in the drivers provided by the database manufacturers. The fifth is that the access method is similar to the file operation. For the BLOB type, apply the InputStream / OutputStream class, which is not encoded and converted one byte by one byte. Oracle.sql.blob class provides both getBinaryStream () and getBinaryOutputStream () two methods, the former method is used to read the Oracle's BLOB field, and the latter method is used to write data to the Oracle's BLOB field. For Clob type, apply the Reader / Writer class, this class performs encoding conversion. The Oracle.SQL.Clob class provides both a getcharacterStream () and GetCharacterOutputStream (), the former method is used to read the Oracle's Clob field, and the latter method is used to write data to the CLOB field of Oracle.
It should be noted that in order to significantly improve program execution efficiency, read and write to the blob / clob field should use buffer operation classes (with buffered prefix), ie: bufferedInputstream, BufferedReader, BufferedWriter. The buffer operation class is all used in the routine. Summary: Manipulate the LOB field of the Oracle database through the JDBC, not more inserted, modified, replaced, and read four ways, and it is not difficult to master. Pay attention to the few points mentioned above, combine reading routine source procedures, users will soon understand the use of LOB type fields, will also understand this type of field! Download Source Download >> (Page Editor: Programming Raw)
