JDBC Review 3 accessing Oracle Big Data Clob BLOB

1 directory Structure remember guide packet mysql Oracle

2 Code, Dbutil tool class see the previous essay Blog

Package Dbex.mysql;import Java.io.bufferedreader;import Java.io.bufferedwriter;import java.io.File;import Java.io.filereader;import Java.io.filewriter;import Java.sql.connection;import Java.sql.PreparedStatement;import Java.sql.resultset;import Java.sql.sqlexception;import Dbex. dbutil;/** * * @ClassName: Orcclob * @Description: Oracle character CLOB Big Data exercise * @author Penny * @date November 29, 2017 10:30:37 *    */public class Orcclob {Connection conn=null;    PreparedStatement ppst = null;    ResultSet rs= null;    /** * @throws SQLException * @Title: Insertclob * @Description: Insert * @param * @throws * * void Insertclob () throws exception{conn = Dbutil.getconnection ();//Get to Connection object, Dbutil front post has Boolean D        Efaultcommit = Conn.getautocommit ();        Conn.setautocommit (false);//setting does not automatically commit String path =orcclob.class.getclassloader (). GetResource ("Big.txt"). GetPath ();      Path =path.replace ("%20", "");//Replace space with 20% try {      Ppst = Conn.preparestatement ("INSERT into CLOB values (?, Empty_clob ())");            Ppst.setint (1, 125);            Ppst.execute (); Ppst = Conn.preparestatement ("Select Clobcol from Clob where id=?            For update ");            Ppst.setint (1, 125);            Rs=ppst.executequery ();                if (Rs.next ()) {Oracle.sql.CLOB clob= (Oracle.sql.CLOB) Rs.getclob ("Clobcol");                BufferedWriter bw = new BufferedWriter (Clob.getcharacteroutputstream ());                BufferedReader br = new BufferedReader (new FileReader (path));                int Len;                    while ((Len=br.read ())!=-1) {System.out.println ("in writing");                Bw.write (len);                } br.close ();            Bw.close ();                    }//Real submit conn.commit ();            } catch (SQLException e) {e.printstacktrace ();        Conn.rollback (); }finally{Conn.setautocommiT (Defaultcommit);        Dbutil.closeall (conn, ppst, RS); }}/** * @throws Exception * * @Title: Readclob * @Description: Read Big Data * @param * @t        Hrows */void Readclob () throws exception{Connection conn = Dbutil.getconnection ();        PreparedStatement Ppst =null;        ResultSet rs= null;        Boolean defaultcommit = Conn.getautocommit ();            try {ppst = conn.preparestatement ("Select Clobcol from clob where id =?");            Ppst.setint (1, 125);            Rs=ppst.executequery ();                if (Rs.next ()) {Oracle.sql.CLOB clob= (Oracle.sql.CLOB) Rs.getclob ("Clobcol");                BufferedReader br = new BufferedReader (Clob.getcharacterstream ());                BufferedWriter bw = new BufferedWriter (New FileWriter ("2.txt"));                int Len;                    while ((Len=br.read ())!=-1) {System.out.println ("read in");                Bw.write (len);    }            Br.close ();            Bw.close ();        } conn.commit ();            } catch (Exception e) {conn.rollback ();        Throw (e);            }finally{Conn.setautocommit (Defaultcommit);        Dbutil.closeall (conn, ppst, RS);        }} public static void Main (string[] args) throws Exception {//New Orcclob (). Insertclob ();    New Orcclob (). Readclob (); }}

3 effects

4 Summary:
Using JDBC to manipulate the LOB fields of an Oracle database is no more than four ways to insert, modify, replace, and read. Observing the above program's access to the LOB Type field, we can see that there are several distinct features compared to other types of fields:

1. Automatic submission must be canceled.

The automatic commit must be canceled with Setautocommit (false) before the access operation begins. Other types of fields do not have this special requirement. This is because when you access the LOB Type field, you typically have to do more than one operation. If not, Oracle will throw a "read violation order" error.

2, the insertion method is different.

LOB data cannot be inserted directly (insert) Like other types of data. An empty LOB object must first be inserted before insertion, the empty object of type CLOB is Empty_clob (), and the empty object of the BLOB type is Empty_blob (). The previously inserted record is then queried with the Select command and locked, and then the empty object is modified to the LOB object to be inserted.

3. Different ways of modification.

When modifying other types of fields, use update ... SET ... command. While the LOB Type field, you can only use SELECT ... The FOR UPDATE command queries the record and locks it before it can be modified. And the modification also has two kinds of modification: first, the original data based on the modification (that is, overlay modification), the implementation of SELECT ... For update and then change the data, the second is to replace (clear the original data, and then modify), first execute the update command to set the value of the LOB field to an empty LOB object, and then make the first modification. It is recommended that you use the replacement method to achieve the same effect as the other field update operations.

4. Access should use the LOB operation classes provided by the database JDBC driver.

For Oracle databases, you should use Oracle.sql.CLOB and Oracle.sql.BLOB. When you do not use LOB classes provided by the database JDBC driver, the program is run with an "abstract method call" error, because the Java.sql.Clob defined by JDBC and Java.sql.Blob interface, some of which are not actually implemented in the driver provided by the database manufacturer.

5, access means and file operation similar.

For BLOB types, the Inputstream/outputstream class is applied, and this class does not encode conversions and accesses bytes-by-byte. The Oracle.sql.BLOB class provides the Getbinarystream () and Getbinaryoutputstream () two methods, and the previous method is used to read the Oracle's BLOB fields. The latter method is used to write data to the Oracle BLOB field.

For the CLOB type, the Reader/writer class is applied, and this class is encoded for conversion. The Oracle.sql.CLOB class provides the Getcharacterstream () and Getcharacteroutputstream () two methods, and the previous method is used to read the CLOB field of Oracle. The latter method is used to write data to the Oracle CLOB field.

It should be explained that in order to significantly improve the efficiency of the program execution, read and write operations on the Blob/clob field should use the buffer operation class (with the buffered prefix), namely: Bufferedinputstream,bufferedoutputstream, Bufferedreader,bufferedwriter. The buffer operation class is used in all routines.

JDBC Review 3 accessing Oracle Big Data Clob BLOB