Jdbc oracle blob Processing

Jdbc oracle blob Processing
LOB (Large Objects) is a data type used to store a Large amount of binary and text data (a LOB field can store up to 4 GB of data ).
LOB can be divided into two types: Internal LOB and external LOB.
Internal LOB stores data in the database as byte streams. Therefore, many internal LOB operations can be involved in transactions,
You can also back up and restore common data. Oracle supports three types of internal LOB:
BLOB (binary data)
CLOB (single-byte character data)
NCLOB (Multi-byte character data ).
CLOB and NCLOB types are suitable for storing ultra-long text data, and BLOB fields are suitable for storing a large amount of binary data, such as, video, audio, and files.


Currently, only one type of external LOB is supported, that is, BFILE. In the database, this type only stores the location information of data in the operating system,
The external data objects are stored in the file system of the operating system. Therefore, the data of this type is read-only and does not participate in transactions.
This type helps users manage a large number of files accessed by external programs.


Use JDBC to write Blob data to Oracle:
The Blob field in Oracle has better performance than the long field and can be used to store binary data such as slices.
The BLOB field of Oracle consists of data (value) and pointer to data (Locator ). Despite
The table itself is stored together, but a BLOB column does not contain values. It only has its own positioning pointer. To use large objects, the program must declare local variables of the positioner type.
When 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 internal table of the database.
Because Blob itself has a cursor, when writing Blob fields, you must use a pointer (Locator) to operate Blob. Therefore, before writing Blob,
You must obtain a pointer (Locator) to write data.
How to obtain the Blob pointer (Locator): You need to insert an empty blob first, which will create a blob pointer and then put this empty
In this way, the blob pointer is obtained through two steps, and the blob data can be truly written.
Procedure:
1. insert an empty blob insert into javatest (name, content) values (?, Empty_blob ());


2. Obtain the blob's cursor select content from javatest where name =? For update;
Note: you must add for update to lock the row until the row is modified to avoid concurrent conflicts.
3. Write Data streams to the database using io and obtained cursor.

Case: insert an image and obtain the query result of an image;

/** File name: BlobTest. java * Copyright: Copyright by www.w.wei.com * Description: * modifier: Cuigaochong * modification time: * tracking Ticket No.: * modification Ticket No.: * modification 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. driverM Anager; 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;/*** <one-sentence function description> <function description> ** @ author name ID * @ version [version number,] * @ see [related classes/methods] * @ since [product/module version] */public class BlobTest {/*** <one-sentence function description> test method, insert a piece of data, one of which is Blob <function description> ** @ throws FileNotFoundE Xception * @ see [Class, Class # method, class # member] */@ Test public void test00 () throws FileNotFoundException {// Note: For empty_blob () values should be directly assigned in SQL statements, which cannot be achieved by assigning values using preset statements. string SQL = "insert into t_emp13 (first_name, salary, picture) values (?,?, Empty_BLOB () "; // query Blob pay attention to the for update String sqlQuery =" select picture from t_emp13 where first_name =? And salary =? For update "; updateBlob (SQL, sqlQuery," QQA122 ", 1233 );} /*** <one-sentence function description> Update database tables with blob <function description> ** @ param sqlInsert * @ param sqlQuery * @ param args * @ see [Class and Class # methods and classes # members] */public void updateBlob (String sqlInsert, string sqlQuery, Object... args) {Connection conn = null; PreparedStatement prepareStatement = null; ResultSet rs = null; OutputStream OS = null; FileInputStream FD = null; try {// current table Insert the empty Blob conn = getConn (); // before the transaction is processed, cancel the default commit action conn of the Connection. setAutoCommit (false); prepareStatement = conn. prepareStatement (sqlInsert); for (int I = 0; I <args. length; I ++) {prepareStatement. setObject (I + 1, args [I]);} prepareStatement.exe cuteUpdate (); 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.exe cuteQuery (); if (rs. next () {blob = (BLOB) rs. getBlob (1);} // get the database output stream OS = blob. getBinaryOutputStream (); // you can obtain the input stream of the file to be inserted. The input stream is: new FileInputStream ("Tulips.jpg"); byte [] B = new byte [1024]; int len; while (-1! = (Len = Fi. read (B) {OS. write (B, 0, len);} // clears the cache OS of the stream. flush (); // transaction processing: if the transaction is processed successfully, commit the transaction conn. commit ();} catch (Exception e) {e. printStackTrace (); try {// transaction processing: if an exception occurs, the catch Block rolls back the transaction conn. rollback ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {if (null! = Fiis) {try {fiis. close () ;}catch (IOException e) {e. printStackTrace () ;}} if (null! = OS) {try {OS. close ();} catch (IOException e) {e. printStackTrace () ;}} releaseSource (prepareStatement, conn, null) ;}}/*** <one-sentence function description> test method, query tables with blob <function description> ** @ see [Class, Class # method, class # member] */@ Test public void test01 () {String SQL = "select picture from t_emp13 where first_name =? "; QueryBlob (SQL," QQA122 ");} /*** <one-sentence function description> query of table methods with Blob <function description> ** @ 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 (); // before transaction processing, cancel the default conn commit action of Connection. setAutoCommit (false); prepareS Tatement = conn. prepareStatement (SQL); for (int I = 0; I <args. length; I ++) {prepareStatement. setObject (I + 1, args [I]);} rs = prepareStatement.exe cuteQuery (); 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 ();} // transaction processing: if the transaction is processed successfully, commit the transaction conn. commit ();} catch (Exception e) {e. printStackTrace (); try {// transaction processing: if an exception occurs, the catch Block rolls back the transaction conn. rollback ();} 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 );}} /*** <one-sentence function description> database connection <function description> ** @ 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 (); InputS Tream 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 connection Connection = DriverManager through getConnection of DriverManager. getConnection (jdb CUrl, user, password); return connection ;} /*** <one-sentence function description> release database resources <function description> ** @ param statement * @ param conn * @ param resultSet * @ see [Class, Class # method, class # member] */public void releaseSource (Statement statement, connection conn, ResultSet resultSet) {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 ();}}}}