JDBC operator oracle LOB column
JDBC operator oracle LOB column：对LOB类型字段存取(JDBC operator oracle LOB column：insert/modify/replace/read)
LOB(Large Object，大型对象)：容量大(最多能容纳4GB数据)，且一个表中可有多个这种类型字段；
LOB分为：BLOB和CLOB：BLOB：二进制大型对象(Binary Large Object)：适用于存贮非文本字节流数据，如程序、图象、影音等；
CLOB：字符型大型对象(Character Large Object)：与字符集相关，适于存贮文本型数据，如历史档案、大部头著作等；
与其它类型区别：
1：存取操作之前，必须取消自动提交：setAutoCommit(false)，其它类型字段无此特殊要求；
因存取LOB类型字段，通常要进行多次操作可完成，否则Oracle将抛出'读取违反顺序'错误；
2：插入方式不同：LOB数据不能像其它类型数据一样直接insert，插入之前必须先插入一个空LOB对象(empty_clob()或empty_blob())，
之后通过select命令查询得到之前插入的记录并锁定，继而将空对象修改为所要插入的LOB对象；
3：修改方式不同：其它类型字段修改时用update tb set column = '' 即可，而LOB类型字段只能用
select …… for update命令将记录查询出来并锁定，然后才能修改，修改有两种方法：
a：在原数据基础上修改(覆盖式修改)，执行select …… for update后再修改数据；
b：替换(先将原数据清除，再修改)，先执行update命令将LOB字段的值设为空的LOB对象，
然后进行第一种改法，建议使用替换改法，以实现与其它字段update操作后一样的效果；
4：存取时应使用由数据库JDBC驱动程序提供的LOB操作类：
对Oracle数据库应使用oracle.sql.CLOB和oracle.sql.BLOB，不使用有数据库JDBC驱动程序提供的LOB类，
否则程序运行则出现'抽象方法调用'错误，这是因为JDBC所定义的java.sql.Clob与java.sql.Blob接口，
其中的一些方法并未在数据库厂家提供的驱动程序中真正实现；
5：存取手段与文件操作相仿：
对BLOB类型：应用InputStream/OutputStream类，此类不进行编码转换，逐个字节存取，
oracle.sql.BLOB类提供相应的getBinaryStream()(读取oracle的BLOB字段)和getBinaryOutputStream()(将数据写入oracle的BLOB字段)两方法
对CLOB类型：应用Reader/Writer类，此类进行编码转换，oracle.sql.CLOB类提供相应的getCharacterStream()(读取oracle的CLOB字段)
和getCharacterOutputStream()(将数据写入oracle的CLOB字段)两方法
注：为大幅提高程序执行效率，对BLOB/CLOB字段的读写操作，应该使用缓冲操作类，即：BufferedInputStream/BufferedOutputStream/BufferedReader/BufferedWriter
create table test_clob(id number(3),clobcol clob);create table test_blob(id number(3),blobcol blob);package cn.com.axis.lob;import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.BufferedReader;import java.io.BufferedWriter;import java.io.FileInputStream;import java.io.FileReader;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;public class LobOperation {//insert a new clob into dbpublic static void clobInsert(String infile) throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//insert a null clob objectstmt.executeUpdate("insert into test_clob values (1,empty_clob())");//query this clob object and lock itResultSet rs = stmt.executeQuery("select clobcol from test_clob where id = 1 for update");while (rs.next()) {//take out this clob objectoracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//write data into clob objectBufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//modify clob object(overlay update based on origin clob object)public static void clobOverlay(String infile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//query this clob object and lock itResultSet rs = stmt.executeQuery("select clobcol from test_clob where id = 1 for update");while (rs.next()) {//take out this clob objectoracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//write data into clob objectBufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//replace clob object(clear origin clob object first,replace to a brand new clob object)public static void clobReplace(String infile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//clear origin clob objectstmt.executeUpdate("update test_clob set clobcol=empty_clob() where id = 1");//query this clob object and lock itResultSet rs = stmt.executeQuery("select clobcol from test_clob where id = 1 for update");while (rs.next()) {//take out this clob objectoracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//write data into clob objectBufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//read clob objectpublic static void clobRead(String outfile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//query this clob objectResultSet rs = stmt.executeQuery("select clobcol from test_clob where id = 1");while (rs.next()) {//take out this clob objectoracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");//output as character formBufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());BufferedReader in = new BufferedReader(new FileReader(outfile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//insert a new blob into dbpublic static void blobInsert(String infile) throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//insert a null blob objectstmt.executeUpdate("insert into test_blob values (1,empty_blob())");//query this blob object and lock itResultSet rs = stmt.executeQuery("select blobcol from test_blob where id = 1 for update");while (rs.next()) {//take out this blob objectoracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//write data into blob objectBufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//modify blob object(overlay update based on origin blob object)public static void blobOverlay(String infile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//query this blob object and lock itResultSet rs = stmt.executeQuery("select blobcol from test_blob where id = 1 for update");while (rs.next()) {//take out this blob objectoracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//write data into blob objectBufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//replace blob object(clear origin blob object first,replace to a brand new blob object)public static void blobReplace(String infile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//clear origin blob objectstmt.executeUpdate("update test_blob set blobcol=empty_blob() where id = 1");//query this blob object and lock itResultSet rs = stmt.executeQuery("select blobcol from test_blob where id = 1 for update");while (rs.next()) {//take out this blob objectoracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//write data into blob objectBufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}//read blob objectpublic static void blobRead(String outfile)throws Exception{Connection conn = null;conn = DBUtil.getConnection();boolean defaultCommit = conn.getAutoCommit(); try {//set do not auto commitconn.setAutoCommit(false);Statement stmt = conn.createStatement();//query this blob objectResultSet rs = stmt.executeQuery("select blobcol from test_blob where id = 1");while (rs.next()) {//take out this blob objectoracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL");//output as binary formBufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());BufferedInputStream in = new BufferedInputStream(new FileInputStream(outfile));int c;while ((c = in.read()) != -1) {out.write(c);}in.close();out.close();}conn.commit();} catch (Exception e) {conn.rollback();e.printStackTrace();}//recover origin commit stateconn.setAutoCommit(defaultCommit);}}
页:
[1]