oracle的blob和clob字段的处理
来源:互联网 发布:数据字典最基本的功能 编辑:程序博客网 时间:2024/04/26 08:42
/**
* first of all,login the oracle's sqlplus with your userName and password
* in this sample
* SID = oracle
* userName = jiaoxue
* password = jiaoxue
*
* create the two test tables for use with the following statements
* CREATE TABLE TESTBLOB (id NUMBWE(20), name VARCHER2(20), blobattr BLOB);
* CREATE TABLE TESTCLOB (id NUMBWE(20), name Varchar2(20), blobattr BLOB);
*
*/
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();
}
/********************************************************************************
* 字符串和blob之间的转换 *
********************************************************************************/
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);
}
}
/********************************************************************************
* 字节数组和blob之间的转换 *
********************************************************************************/
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);
}
}
/********************************************************************************
* 文件流和blob之间的转换 *
********************************************************************************/
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);
}
}
/**************************************************************************************
* 该方法是覆盖式的修改blob字段,如果原来的数据过长的话将把新的内容与过长的部分进行衔接合并成一个*
**************************************************************************************/
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);
}
}
/********************************************************************************
* 字符串和clob之间的转换 *
********************************************************************************/
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);
}
}
/********************************************************************************
* 文件流和clob之间的转换 *
********************************************************************************/
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);
}
}
/**
* release the connection resource and set the autoCommit true
* @param con
* @param stmt
* @param rs
*/
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();}}
}
/**
* release the connection resource and set the autoCommit true
* @param con
* @param ps
* @param rs
*/
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();}}
}
}
- oracle的blob和clob字段的处理
- oracle的blob和clob字段的处理
- Oracle的clob,blob字段处理
- ibatis操作oracle的clob字段和blob字段
- mysql和Oracle在对clob和blob字段的处理
- Oracle中BLOB和CLOB字段的操作
- Java操纵oracle的CLOB和BLOB字段
- Oracle中的BLOB和CLOB字段类型的区别
- Oracle中的BLOB和CLOB字段类型的区别
- Oracle中的BLOB和CLOB字段类型的区别
- 关于 Oracle Clob Blob 字段的读写
- ibatis处理blob字段和clob字段
- Blob、Clob字段的映射
- 处理blob和clob的通用类
- oracle clob 和blob问题的解决
- Oracle中Blob和Clob的作用
- Oracle中Blob和Clob的作用
- Oracle中Blob和Clob的作用?
- rsync 配置
- jquery 240 个插件
- 九个教程让你不再是菜鸟
- sqlserver常用函数/存储过程/数据库角色
- Python文件去除注释
- oracle的blob和clob字段的处理
- #define与typedef用法
- Java中的堆和栈
- 使用VC2005express,时,MFC遇到的问题
- 程序员
- Linux: 3322.org+lynx+crontab 实现ddns的自动刷新
- Linux下GCC编程四个过程(转)
- 值类型和引用类型的区别
- C#继承、覆盖和多态、抽象类