oracle的blob和clob字段的處理

package com.thomas.blog_clob;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.ByteArrayInputStream;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.IOException;

import java.io.OutputStream;

import java.io.Reader;

import java.io.Writer;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class BlobAndClobTest {

public static void main(String[] args){

//insertStringIntoBlob();

//getStringFromBlob();

//insertByteArrayIntoBlob();

//insertFileIntoBlob();

//getFileFromBlob();

//modifyFileIntoBlob();

//modifyAFileIntoBlobClearly();

//insertStringIntoClob();

//getStringFromClob();

//insertFileIntoClob();

//getFileFromClob();

//modifyFileArrayIntoClob();

modifyFileArrayIntoClobClearly();

}

public static void insertStringIntoBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = true;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

stmt.executeUpdate("insert into testblob (id, name, blobattr) values (1, 'blobname1', empty_blob())");

//一定要帶上"for update"

rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");

if (rs.next()) {

//convert java.sql.Blob into oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

OutputStream outStream = blob.getBinaryOutputStream();

byte[] data = "我是一個字元串，我将被用來檢驗oracle的blob對象的插入，讀取和修改功能的實作".getBytes();

ByteArrayInputStream is = new ByteArrayInputStream(data);

int size = blob.getBufferSize();

byte[] buffer = new byte[size];

int length = -1;

try {

while ((length = is.read(buffer)) != -1) {

outStream.write(buffer, 0, length);

}

} catch (IOException e) {

e.printStackTrace();

}finally{

try {outStream.close(); } catch (IOException e) {e.printStackTrace();}

try {is.close(); } catch (IOException e) {e.printStackTrace();}

}

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void getStringFromBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = true;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

rs = stmt.executeQuery("select BLOBATTR from TESTBLOB where ID=1");

if (rs.next()){

java.sql.Blob blob = rs.getBlob("BLOBATTR");

byte[] data = blob.getBytes(1, (int) blob.length());

String text = new String(data);

System.out.println(text);

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void insertByteArrayIntoBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = true;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

con.setAutoCommit(false);

defaultCommit = con.getAutoCommit();

stmt = con.createStatement();

stmt.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, 'blobname1', empty_blob())");

rs = stmt.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");

if (rs.next()){

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

OutputStream outStream = blob.getBinaryOutputStream();

byte[] data = "我是一個字元串，我将被用來檢驗oracle的blob對象的插入，讀取和修改功能的實作".getBytes();

outStream.write(data, 0, data.length);

outStream.flush();

outStream.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void insertFileIntoBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

stmt.executeUpdate("insert into testblob (id, name, blobattr) values (1, 'blobname1', empty_blob())");

//一定要帶上"for update"

rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");

if (rs.next()) {

//convert java.sql.Blob into oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

BufferedInputStream in = new BufferedInputStream(new FileInputStream("e:\\Bocaloco_licence.doc"));

int c;

while ((c=in.read())!=-1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void getFileFromBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

rs = stmt.executeQuery("select blobattr from testblob where ID=1");

if (rs.next()) {

//convert java.sql.Blob into oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream("e:\\abcdefg.doc"));

BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());

int c;

while ((c=in.read())!=-1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void modifyFileIntoBlob() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

//一定要帶上"for update"

rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");

if (rs.next()) {

//convert java.sql.Blob into oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

BufferedInputStream in = new BufferedInputStream(new FileInputStream("e:\\log4j.properties"));

int c;

while ((c=in.read())!=-1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void modifyAFileIntoBlobClearly() {

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

System.out.println(11);

stmt.executeUpdate("update testblob set blobattr=empty_blob()");

System.out.println(22);

//一定要帶上"for update"

rs = stmt.executeQuery("select blobattr from testblob where ID=1 for update");

if (rs.next()) {

//convert java.sql.Blob into oracle.sql.BLOB

oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");

BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());

BufferedInputStream in = new BufferedInputStream(new FileInputStream("e:\\log4j.properties"));

int c;

while ((c=in.read())!=-1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void insertStringIntoClob(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = true;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

stmt.executeUpdate("insert into testclob (id, name, clobattr) values (1, 'clobname1', empty_clob())");

rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");

if (rs.next()) {

oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");

Writer outStream = clob.getCharacterOutputStream();

char[] data = "我是一個字元串，我将被用來檢驗oracle的clob對象的插入，讀取和修改功能的實作".toCharArray();

outStream.write(data,0,data.length);

outStream.flush();

outStream.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void getStringFromClob(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = true;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

rs = stmt.executeQuery("select clobattr from testclob where ID=1");

if (rs.next()) {

java.sql.Clob clob = rs.getClob("CLOBATTR");

Reader inStream = clob.getCharacterStream();

char[] c = new char[(int) clob.length()];

inStream.read(c);

String data = new String(c);

System.out.println(data);

inStream.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void insertFileIntoClob(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

stmt.executeUpdate("insert into testclob (id, name, clobattr) values (1, 'clobname1', empty_clob())");

rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");

if (rs.next()) {

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());

BufferedReader in = new BufferedReader(new FileReader("e:\\log4j.properties"));

int c;

while ((c = in.read()) != -1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void getFileFromClob(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

rs = stmt.executeQuery("select clobattr from testclob where ID=1");

if (rs.next()) {

//注意：在這裡java.sql.Clob和oracle.sql.CLOB也都能達到相同的目的

java.sql.Clob clob = (java.sql.Clob)rs.getClob(1);

//oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

//注意：兩種路徑表達方式都可以達到想要的要求

BufferedReader in = new BufferedReader(clob.getCharacterStream());

BufferedWriter out = new BufferedWriter(new FileWriter("e:\\log4j1.properties"));

//BufferedWriter out = new BufferedWriter(new FileWriter("e:/bbb.txt"));

int c;

while ((c=in.read())!=-1) {

out.write(c);

}

out.close();

in.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void modifyFileArrayIntoClob(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");

if (rs.next()) {

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());

BufferedReader in = new BufferedReader(new FileReader("e:\\StringUtil.java"));

int c;

while ((c = in.read()) != -1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void modifyFileArrayIntoClobClearly(){

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

boolean defaultCommit = false;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.254:1521:oracle", "jiaoxue","jiaoxue");

defaultCommit = con.getAutoCommit();

con.setAutoCommit(false);

stmt = con.createStatement();

stmt.executeUpdate("update testclob set clobattr = empty_clob() where ID=1 for update");

rs = stmt.executeQuery("select clobattr from testclob where ID=1 for update");

if (rs.next()) {

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());

BufferedReader in = new BufferedReader(new FileReader("e:\\StringUtil.java"));

int c;

while ((c = in.read()) != -1) {

out.write(c);

}

in.close();

out.close();

}

con.commit();

} catch (Exception e) {

e.printStackTrace();

try {if(con!=null){con.rollback();}} catch (SQLException e1) {e1.printStackTrace();}

} finally {

releaseResource(con,stmt,rs,defaultCommit);

}

}

public static void releaseResource(Connection con,Statement stmt,ResultSet rs,boolean autoCommit){

if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}

if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}

if (con != null) {try {con.setAutoCommit(autoCommit);con.close();} catch (SQLException e) {e.printStackTrace();}}

}

public static void releaseResource(Connection con,PreparedStatement ps,ResultSet rs,boolean autoCommit){

if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}

if (ps != null) {try {ps.close();} catch (SQLException e) {e.printStackTrace();}}

if (con != null) {try {con.setAutoCommit(autoCommit);con.close();} catch (SQLException e) {e.printStackTrace();}}

}

}