JDBC ORACLE BLOB processing

LOB, or large Objects (large object), is a data type used to store large amounts of binary and textual data (a LOB field can store up to 4GB of data).
There are two types of lobs: internal lob and external lob.
An internal LOB stores data in the form of a byte stream inside the database. As a result, many operations on internal lobs can participate in transactions,
You can also back up and restore operations as you would with normal data. Oracle supports three types of internal lobs:
BLOB (binary data)
CLOB (Single-byte character data)
NCLOB (multibyte character data).
The CLOB and NCLOB types are suitable for storing very long text data, and BLOB word MSA is used to store large amounts of binary data, such as video, audio, files, and so on.


Only one external LOB type, the bfile type, is currently supported. Within the database, this type stores only the location information of the data in the operating system,
The entity of the data exists in the file system of the operating system in the form of an external file. Thus, the data represented by the type is read-only and does not participate in transactions.
This type helps users to manage a large number of files accessed by external programs.


Use JDBC to write blob-type data to Oracle:
Oracle's BLOB fields perform better than long fields, and can be used to preserve binary data such as slices.
The Oracle BLOB field consists of two parts: the data (value) and the pointer to the data (the locator). Although the value and
The table itself is stored together, but a BLOB column does not contain a value, only its anchor pointer. In order to use large objects, the program must declare local variables of the locator type.
When an Oracle internal LOB is created, the locator is stored in the column, the value is stored in the LOB segment, and the LOB segment is part of the table inside the database.
Because the BLOB itself has a cursor, when writing to a BLOB field must use a pointer (locator) to manipulate the blob, and thus before writing to the Blob,
You must obtain a pointer (locator) to write
How to get a blob pointer (Locator): You need to insert an empty BLOB first, which will create a BLOB pointer and then put this empty
A pointer to the BLOB is queried, so that a blob pointer can be actually written to the BLOB data in two steps.
Specific steps:
1. Insert empty blob INSERT into javatest (name,content) VALUES (?, Empty_blob ());


2. Get the cursor for BLOB select content from Javatest where name=? for update;
Note: You must add a for update to lock the row until the row has been modified to ensure that no concurrency conflicts occur.
3, using IO, and get to the cursor to the database to write data flow

Case: The following case implementation inserts a picture, and gets the query result with a picture;

/* * File name: Blobtest.java * Copyright: Copyrights by Www.huawei.com * Description: * Modified by: Cuigaochong * Modified: 2015-8-25 * Tracking Number: * Change the number: * Modify the content: * /package com.jdbc.cgc.blob;import java.io.file;import Java.io.fileinputstream;import java.io.FileNotFoundException ; Import Java.io.fileoutputstream;import Java.io.ioexception;import java.io.inputstream;import Java.io.OutputStream ; Import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.statement;import Java.util.Properties;import Oracle.sql.blob;import org.junit.test;/** * < A sentence function description > < function details > * * @author name work number * @version [version number, 2015-8-25] * @see [Related class/method] * @since [Product/Module Version] */public class blobtest{/** * < A sentence function summary > test method, insert a data, the data has a column is blob < work        Can be described in detail > * * @throws filenotfoundexception * @see [Class, Class # method, Class # Member] */@Test public void test00 () Throws FileNotFoundException {//Note: for Empty_blob () should be placed directly in the SQL statementAssignment cannot be achieved by assigning a value using a preset statement.        String sql = "INSERT into T_EMP13 (first_name,salary,picture) VALUES (?,?, Empty_blob ())"; Query blob note for the for update String behind row Locking sqlquery = "Select Picture from t_emp13 where first_name =?" and salary =?        For update ";    Updateblob (SQL, SQLQuery, "QQA122", 1233); }/** * < A sentence function brief > update database table with BLOB < feature details > * * @param sqlinsert * @param sqlquery * @p    Aram args * @see [Class, Class # method, Class # Member] */public void Updateblob (string sqlinsert, String sqlquery, Object ... args)        {Connection conn = null;        PreparedStatement preparestatement = null;                ResultSet rs = null;        OutputStream OS = null;        FileInputStream FIS = null;            try {//Now insert empty blob conn = Getconn () in table;            Cancels the default commit behavior of connection before the thing is processed conn.setautocommit (false);                        Preparestatement = Conn.preparestatement (Sqlinsert); for (int i = 0; i < Args.length;            i++) {Preparestatement.setobject (i + 1, args[i]);                        } preparestatement.executeupdate ();                        Blob blob = null;                        Preparestatement = Conn.preparestatement (sqlquery);            for (int i = 0; i < args.length; i++) {Preparestatement.setobject (i + 1, args[i]);            }//Preparestatement.setstring (1, "Qqa");            rs = Preparestatement.executequery ();            if (Rs.next ()) {blob = (BLOB) Rs.getblob (1);            }//Get the output stream of the database OS = Blob.getbinaryoutputstream ();                        Get the input stream to insert file FIS = new FileInputStream ("tulips.jpg");            Byte[] B = new byte[1024];            int Len;            while ( -1! = (len = fis.read (b))) {Os.write (b, 0, Len);     }//Empty stream cache Os.flush ();       Thing handling: If the thing processing succeeds then submits the thing Conn.commit ();            } catch (Exception e) {e.printstacktrace ();            try {//Thing handling: If an exception occurs, the thing conn.rollback () is rolled back in the catch block;            } catch (SQLException E1) {e1.printstacktrace ();                    }} finally {if (null! = FIS) {try {                Fis.close ();                } catch (IOException e) {e.printstacktrace ();                }} if (null! = OS) {try {os.close ();                } catch (IOException e) {e.printstacktrace ();        }} releasesource (preparestatement, conn, null); }}/** * < A sentence function summary > test method, Query hasBlob table < function detail Description > * * @see [Class, Class # method, Class # Member] */@Test public void test01 () {String sql = "se        Lect picture from t_emp13 where first_name =? ";    Queryblob (SQL, "QQA122"); }/** * < A sentence function brief > query table method with BLOB < feature detail > * * @param SQL * @param args * @see [Class, Class # method        , class # Member] */public void Queryblob (String sql, Object ... args) {Connection conn = null;        PreparedStatement preparestatement = null;                ResultSet rs = null;        FileOutputStream fos = null;                InputStream is = null;            try {conn = Getconn ();            Cancels the default commit behavior of connection before the thing is processed conn.setautocommit (false);            preparestatement = conn.preparestatement (sql);            for (int i = 0; i < args.length; i++) {Preparestatement.setobject (i + 1, args[i]);            } rs = Preparestatement.executequery ();            if (Rs.next ()){Blob blob = (BLOB) Rs.getblob (1);                                is = Blob.getbinarystream ();                                FOS = new FileOutputStream (New File ("Test.png"));                Byte[] B = new byte[1024];                                int Len;                while ( -1! = (len = is.read (b))) {Fos.write (b, 0, Len);            } fos.flush ();                    }//Thing handling: If a thing is handled successfully then commits the thing Conn.commit ();            } catch (Exception e) {e.printstacktrace ();            try {//Thing handling: If an exception occurs, the thing conn.rollback () is rolled back in the catch block;            } catch (SQLException E1) {e1.printstacktrace ();                    }} finally {if (null! = is) {try {                Is.close ();       } catch (IOException e)         {E.printstacktrace ();                    }} if (null! = Fos) {try {                Fos.close ();                } catch (IOException e) {e.printstacktrace ();        }} releasesource (Preparestatement, Conn, RS); }}/** * < A sentence function brief > Connection database < feature details > * * @return * @throws Exception * @see [Class, Class #        method, Class # Member] */public Connection Getconn () throws Exception {String dirvername = null;        String jdbcurl = null;        String user = null;                String password = null;        Properties Propertoes = new properties ();        InputStream is = GetClass (). getClassLoader (). getResourceAsStream ("jdbc.properties");                Propertoes.load (IS);        Dirvername = Propertoes.getproperty ("Driver"); Jdbcurl = PropertoeS.getproperty ("Jdbcurl");        user = Propertoes.getproperty ("user");                Password = propertoes.getproperty ("password");                Class.forName (Dirvername); Get the database connection through DriverManager getconnection Connection Connection = drivermanager.getconnection (jdbcurl, user, password                );    return connection; }/** * < A sentence function summary > release database resources < feature details > * * @param statement * @param conn * @param resu Ltset * @see [Class, Class # method, Class # Member] */public void Releasesource (Statement Statement, Connection conn, ResultSet ResultS            ET) {if (null! = ResultSet) {try {resultset.close ();            } catch (SQLException e) {e.printstacktrace ();            }} if (null! = statement) {try {statement.close (); } catch (Exception e) {e.Printstacktrace ();            }} if (null! = conn) {try {conn.close ();            } catch (Exception e) {e.printstacktrace (); }        }    }}

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

JDBC ORACLE BLOB processing