JDBC clob \ blob operation

There has been no time to solve the big field problem of data migration in the past few days. Today, we finally have time to solve this problem.

Scenario

When a table in the SQL Server database is migrated to the Oracle database, special field types were not considered in the early stage, but problems were found during real business application testing, the content of this field in Oracle is blank (ignored during migration ). We need to update the content in the SQL Server table to Oracle based on the primary key, which can be handled directly by using JDBC.

Summary of JDBC Processing

Oracle JDBC driverProgramThere are compatibility issues, including jdbc3 and jdbc4.

Clob operations in Oracle are in the form of a response stream. inserting data with clob before jdbc4 requires two steps.

1. When empty_clob () is used and inserted to the database, the field value is blank.

1. Update the clob field after the insertion is complete.

After jdbc4, oracle11g improved the processing of this item a lot.

1. Use the connection object to create a clob object and write the value to the clob object.
2. Directly add the parameter to the preparedstatement.

SQL Server database JDBC

1. Use resultset # getstring (INDEX) to obtain the value of the text/ntext field.

1. To retrieve binary data, you must use byte streams.

ExampleCode

Jdbc4.0 insert clob

/*** test insertion * 
 1. Note that the oracle JDBC driver is ojdbc6.jar * @ throws sqlexception */Public void. insertdatenew () throws sqlexception {string SQL = "insert ". concat (tablename ). concat ("(ID, content) values (?,?) "); Preparedstatement pstmt = NULL; try {This. conn. setautocommit (false); pstmt = This. conn. preparestatement (SQL); For (INT I = 1; I <50; I ++) {pstmt. setstring (1, String. valueof (I); clob = This. conn. createclob (); clob. setstring (1, "helloworld" + I); pstmt. setclob (2, clob); pstmt. addbatch ();} pstmt.exe cutebatch (); this. conn. commit ();} catch (sqlexception e) {This. conn. rollback (); E. printstacktrace ();} finally {This. conn. setautocommit (true); dbutils. close (pstmt) ;}}

 

Jdbc3.0 insert

/*** Test clob insertion */Public void insertdata () throws sqlexception {string SQL = "insert ". concat (tablename ). concat ("(ID, content) values (?, Empty_clob () "); preparedstatement pstmt = NULL; try {This. conn. setautocommit (false); pstmt = This. conn. preparestatement (SQL); For (INT I = 1; I <50; I ++) {pstmt. setstring (1, String. valueof (I); pstmt. addbatch ();} pstmt.exe cutebatch (); this. updateinsertclob (); this. conn. commit ();} catch (sqlexception e) {This. conn. rollback (); E. printstacktrace ();} finally {This. conn. setautocommit (true); dbutils. close (pstmt) ;}}/*** does not support jdbc4 * @ Param pstmt {@ link preparedstatement} * @ throws sqlexception */private void updateinsertclob () throws sqlexception {string SQL = "select ID, content from ". concat (tablename); preparedstatement pstmt = NULL; resultset = NULL; try {pstmt = This. conn. preparestatement (SQL); resultset = pstmt.exe cutequery (SQL); While (resultset. next () {clob clobinst = resultset. getclob (2); writer writerinst = clobinst. setcharacterstream (1); writerinst. write ("helloworld" + resultset. getstring (1); writerinst. close () ;}} catch (ioexception e) {Throw new sqlexception (E. getcause ();} finally {dbutils. close (pstmt );}}

 

Byte/batch stream type Processing

/*** Test query ** <p> * 1. You can directly getstring the text type. * <p> * 2. It is mainly Binary-type processing * @ throws sqlexception */Public void querydate () throws sqlexception {string qrysql = "select * From blob_table A where. id =? "; Preparedstatement pstmt = NULL; try {pstmt = This. conn. preparestatement (qrysql); pstmt. setstring (1, "1"); resultset rs = pstmt.exe cutequery (); While (RS. next () {inputstream ins = Rs. getbinarystream ("photo"); restoreinputstream (INS); ins. close () ;}} catch (sqlexception e) {e. printstacktrace ();} catch (ioexception e) {// todo auto-generated Catch Block E. printstacktrace ();} finally {dbui Ls. close (pstmt) ;}}/*** dump input stream * @ Param ins {@ link inputstream} * @ throws ioexception */private void restoreinputstream (inputstream INS) {bufferedoutputstream bosinst = NULL; byte [] buff = new byte [1024*1024]; try {bosinst = new bufferedoutputstream (New fileoutputstream ("C: \ ttt.zip ")); int flag =-1; while (flag = ins. read (buff ))! =-1) {bosinst. write (buff, 0, flag);} bosinst. flush ();} catch (filenotfoundexception e) {e. printstacktrace ();} catch (ioexception e) {e. printstacktrace ();} finally {If (bosinst! = NULL) {try {bosinst. Close ();} catch (ioexception e) {e. printstacktrace ();}}}}