使用java操作oracle的blob字段

 一、增删改示例
package page;
import java.sql.*;
import java.io.*;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
import java.lang.Exception;

public class aaa implements Serializable {
public static void main(String[] args) throws Exception{
System.out.println("-------------------insert 插入CLOB字段（先插入空值，再更新） -----------------");
Connection con=null;
PreparedStatement pstm = null;
ResultSet rs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(
"jdbc:oracle:thin:@fangm:1521:LICSFC", "SFC", "SFC");
// 方法一  
String sql = "insert into test values(1,empty_clob())";  
pstm = con.prepareStatement(sql);
pstm.executeUpdate();
long id = 2;
String sqll = "select content from test where id=？ for update";
pstm = con.prepareStatement(sqll);
pstm.setLong(1, id);
rs = pstm.executeQuery();
if (rs.next()) {
CLOB clob = ((OracleResultSet) rs).getCLOB(1);
//java.sql.Clob clob = rss.getClob(1);
clob.setString(1, "ddddddddddddddddddddddddddddddddddd");
sql = "update test set content=? where id=1";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
pstmt.close();
}
// 方法二
String sql2 = "insert into test values(?,?)";
pstm = con.prepareStatement(sql2);
pstm.setInt(1, 2);
pstm.setClob(2, oracle.sql.CLOB.empty_lob());
pstm.executeUpdate();

String sqll2 = "select content from test where id=2 for update";
pstm = con.prepareStatement(sqll2);
rs = pstm.executeQuery();
if (rs.next()) {
java.sql.Clob clob = rs.getClob(1);
clob.setString(1,"affffffffffdfdfdfdddddddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdddfff");
String sql1 = "update test set content=? where id=2";
pstm = con.prepareStatement(sql1);
pstm.setClob(1, clob);
pstm.executeUpdate();
pstm.close();
}  
System.out.println("-------------insert ok-------------");
} catch (SQLException e) {
e.printStackTrace();
throw new Exception(
"aaa===============insert has error!");
} finally {
if(rs!=null){
rs.close();
rs=null;
}
if (pstm != null) {
pstm.close();
pstm = null;
}
if (con != null) {
con.close();
con = null;
}
}
System.out.println("-------------------query 查询-----------------");
try {
String content = "";
Class.forName("oracle.jdbc.driver.OracleDriver");
//   con = DriverManager.getConnection(
//   "jdbc:oracle:thin:@fangm:1521:LICSFC", "SFC", "SFC");

String sql = "select content from test where id=1";
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
//   用来读文字
String cont = "";
java.sql.Clob clob = rs.getClob(1);
if (clob != null) {
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
try {
String s = br.readLine();
while (s != null) {
  cont += s;
  s = br.readLine();
}
} catch (java.io.IOException ex) {
ex.printStackTrace();
throw new Exception(
  "aaa===============读CLOB字段 has error!");
}
}

//   用来读图片
clob = rs.getClob(1);
InputStream input = clob.getAsciiStream();
int len = (int)clob.length();
String rtn = null;
if(clob != null){
byte[] by = new byte[len];  
        int i;
        try {
while(-1 != (i = input.read(by,0,by.length))){  
    input.read(by,0,i);
  }
rtn = new String(by);
} catch (IOException e) {
e.printStackTrace();
}
//   knowlege.setFtext(rtn);
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception(
"aaa===============insert has error!");
} finally {
if(rs!=null){
rs.close();
rs=null;
}
if (pstm != null) {
pstm.close();
pstm = null;
}
if (con != null) {
con.close();
con = null;
}
}
}
}
************************************************************************************************************
二、对于内容中有{}的情况
/**
* @author 迈兴志 用于修改Template纪录 创建于2007-04-06
*/
public static void updateTemplate(Connection con, Template temp,
String cssCont) throws Exception {
PreparedStatement pstm = null;
ResultSet rs = null;
String sql = "update tab_template set Css_name=?,Back=? where Css_id=?";
try {
//先更新其它字段
pstm = con.prepareStatement(sql);
pstm.setString(1, temp.getCssName());
pstm.setString(2, temp.getBack());
pstm.setLong(3, temp.getCssId());
pstm.executeUpdate();

//清空CLOB字段（对于｛｝内容CLOB字段不能更改掉以前的内容，所以需要将其设为null。如果肯定没有{}的内容插入，不用这么做）
String sqll2 = "select Css_cont from tab_template where Css_id=? for update";
pstm = con.prepareStatement(sqll2);
pstm.setLong(1, temp.getCssId());
rs = pstm.executeQuery();

if (rs.next()) {
java.sql.Clob clob = rs.getClob(1);
clob.setString(1, cssCont);
String sql3="update tab_template set Css_cont=null where Css_id=?";
pstm = con.prepareStatement(sql3);  
pstm.setLong(1, temp.getCssId());
pstm.executeUpdate();
}

//重新初始化这个字段
pstm = con.prepareStatement(sqll2);
pstm.setLong(1, temp.getCssId());
rs = pstm.executeQuery();
String sql5 = "update tab_template set Css_cont=? where Css_id=?";  
pstm = con.prepareStatement(sql5);
pstm.setClob(1, oracle.sql.CLOB.empty_lob());// 对于CLOB字段的插入
pstm.setLong(2,temp.getCssId());
pstm.executeUpdate();

//再插入字段值
String sqll3 = "select Css_cont from tab_template where Css_id=? for update";  
pstm = con.prepareStatement(sqll3);
pstm.setLong(1, temp.getCssId());
rs = pstm.executeQuery();
if (rs.next()) {
java.sql.Clob clob = rs.getClob(1);
clob.setString(1, cssCont);
String sql1 = "update tab_template set Css_cont=? where Css_id=?";
pstm = con.prepareStatement(sql1);
pstm.setClob(1, clob);
pstm.setLong(2, temp.getCssId());
pstm.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
throw new Exception(
"修改纪录时出错TemplateTable=============================updateTemplate");
} finally {
if(rs!=null){
rs.close();
rs=null;
}
if (pstm != null) {
pstm.close();
pstm = null;
}
}
}
**************************************************************************************
三、用流的方式：
/**
* 修改clob对象（是在原clob对象基础上进行覆盖式的修改）
*
* @param infile -
*       数据文件
* @throws java.lang.exception
* @roseuid 3eda04b60367
*/
/* 设定不自动提交 */
boolean defaultcommit = con.getAutoCommit();
con.setAutoCommit(false);
try {
/* 查询clob对象并锁定 */
String sql3="select cont from tab_article where article_id=? for update";
pstm = con.prepareStatement(sql3);
pstm.setLong(1,art.getArticleID());
rs = pstm.executeQuery();
while (rs.next()) {
/* 获取此clob对象 */
CLOB clob = (oracle.sql.CLOB) rs
  .getClob("cont");
/* 进行覆盖式修改 */
java.io.BufferedWriter out = new java.io.BufferedWriter(clob.getCharacterOutputStream());  
  BufferedReader in = new BufferedReader(new StringReader(art.getCont()));
String str="";
String c="";
while ((c = in.readLine()) != null) {
  str+=c;  
}
out.write(str);
in.close();
out.close();
}
/* 正式提交 */
con.commit();
} catch (Exception ex) {
/* 出错回滚 */
ex.printStackTrace();
con.rollback();
throw ex;
}
/* 恢复原提交状态 */
con.setAutoCommit(defaultcommit);
*********************************************************************************
四、流的示例
jdbc存取oracle大型数据对象lob几种情况的示范类：来自http://blog.tiibii.com/a/happy/view14771.aspx
/**
*
*/
/**
* @author mxz
*
*/
import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
class lobpros {
/**
* oracle驱动程序
*/
private static final String driver = "oracle.jdbc.driver.oracledriver";
/**
* oracle连接用url
*/
private static final String url = "jdbc:oracle:thin:@test2000:1521:orac";
/**
* 用户名
*/
private static final String user = "user";
/**
* 密码
*/
private static final String password = "pswd";
/**
* 数据库连接
*/
private static Connection conn = null;
/**
* sql语句对象
*/
private static Statement stmt = null;
/**
* @roseuid 3eda089e02bc
*/
public lobpros() {
}
/**
* 往数据库中插入一个新的clob对象
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda04a902bc
*/
public static void clobinsert(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的clob对象 */
stmt.executeUpdate("insert into test_clob values ('111', empty_clob())");
/* 查询此clob对象并锁定 */
ResultSet rs = stmt.executeQuery("select clobcol from test_clob where id='111' for update");
while (rs.next()) {
/* 取出此clob对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("clobcol");
/* 向clob对象中写入数据 */
BufferedWriter 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 修改clob对象（是在原clob对象基础上进行覆盖式的修改）
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda04b60367
*/
public static void clobmodify(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询clob对象并锁定 */
ResultSet rs = stmt.executeQuery("select clobcol from test_clob where id='111' for update");
while (rs.next()) {
/* 获取此clob对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("clobcol");

/* 进行覆盖式修改 */
BufferedWriter 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 替换clob对象（将原clob对象清除，换成一个全新的clob对象）
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda04bf01e1
*/
public static void clobreplace(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原clob对象 */
stmt
.executeUpdate("update test_clob set clobcol=empty_clob() where id='111'");
/* 查询clob对象并锁定 */
ResultSet rs = stmt.executeQuery(
"select clobcol from test_clob where id='111' for update");
while (rs.next()) {
/* 获取此clob对象 */
oracle.sql.clob clob = (oracle.sql.clob) rs.getClob("clobcol");
/* 更新数据 */
BufferedWriter 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* clob对象读取
*
* @param outfile -
*       输出文件名
* @throws java.lang.Exception
* @roseuid 3eda04d80116
*/
public static void clobread(String outfile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询clob对象 */
ResultSet rs = stmt
.executeQuery("select * from test_clob where id='111'");
while (rs.next()) {
/* 获取clob对象 */
oracle.sql.clob clob = (oracle.sql.clob) rs.getClob("clobcol");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob
  .getcharacterstream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c = in.read()) != -1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 向数据库中插入一个新的blob对象
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda04e300f6
*/
public static void blobinsert(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的blob对象 */
stmt
.executeUpdate("insert into test_blob values ('222', empty_blob())");
/* 查询此blob对象并锁定 */
ResultSet rs = stmt
.executeQuery("select blobcol from test_blob where id='222' for update");
while (rs.next()) {
/* 取出此blob对象 */
oracle.sql.blob blob = (oracle.sql.blob) rs.getBlob("blobcol");
/* 向blob对象中写入数据 */
BufferedOutputStream 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 修改blob对象（是在原blob对象基础上进行覆盖式的修改）
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda04e90106
*/
public static void blobmodify(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询blob对象并锁定 */
ResultSet rs = stmt
.executeQuery("select blobcol from test_blob where id='222' for update");
while (rs.next()) {
/* 取出此blob对象 */
oracle.sql.blob blob = (oracle.sql.blob) rs.getBlob("blobcol");
/* 向blob对象中写入数据 */
BufferedOutputStream 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 替换blob对象（将原blob对象清除，换成一个全新的blob对象）
*
* @param infile -
*       数据文件
* @throws java.lang.Exception
* @roseuid 3eda0505000c
*/
public static void blobreplace(String infile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原blob对象 */
stmt
.executeUpdate("update test_blob set blobcol=empty_blob() where id='222'");
/* 查询此blob对象并锁定 */
ResultSet rs = stmt
.executeQuery("select blobcol from test_blob where id='222' for update");
while (rs.next()) {
/* 取出此blob对象 */
oracle.sql.blob blob = (oracle.sql.blob) rs.getBlob("blobcol");
/* 向blob对象中写入数据 */
BufferedOutputStream 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 ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* blob对象读取
*
* @param outfile -
*       输出文件名
* @throws java.lang.Exception
* @roseuid 3eda050b003b
*/
public static void blobread(String outfile) throws Exception {
/* 设定不自动提交 */
boolean defaultcommit = conn.getAutoCommit();
conn.setAutoCommit(false);

try {
/* 查询blob对象 */
ResultSet rs = stmt
.executeQuery("select blobcol from test_blob where id='222'");
while (rs.next()) {
/* 取出此blob对象 */
oracle.sql.blob blob = (oracle.sql.blob) rs.getBlob("blobcol");
/* 以二进制形式输出 */
BufferedOutputStream out = new BufferedOutputStream(
  new FileOutputStream(outfile));
BufferedInputStream in = new BufferedInputStream(blob
  .getbinarystream());
int c;
while ((c = in.read()) != -1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultcommit);
}
/**
* 建立测试用表格
*
* @throws Exception
*/
public static void createtables() throws Exception {
try {
stmt.executeUpdate(
"create table test_clob ( id number(3), clobcol clob)");
stmt
.executeUpdate("create table test_blob ( id number(3), blobcol blob)");
} catch (Exception ex) {
}
}
/**
* @param args -
*       命令行参数
* @throws java.lang.Exception
* @roseuid 3eda052002ac
*/
public static void main(String[] args) throws Exception {
/* 装载驱动,建立数据库连接 */
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
/* 建立测试表格 */
createtables();
/* clob对象插入测试 */
clobinsert("c:/clobinsert.txt");
clobread("c:/clobinsert.out");
/* clob对象修改测试 */
clobmodify("c:/clobmodify.txt");
clobread("c:/clobmodify.out");
/* clob对象替换测试 */
clobreplace("c:/clobreplace.txt");
clobread("c:/clobreplace.out");
/* blob对象插入测试 */
blobinsert("c:/blobinsert.doc");
blobread("c:/blobinsert.out");
/* blob对象修改测试 */
blobmodify("c:/blobmodify.doc");
blobread("c:/blobmodify.out");
/* blob对象替换测试 */
blobreplace("c:/blobreplace.doc");
blobread("c:/bolbreplace.out");
/* 关闭资源退出 */
conn.close();
System.exit(0);
}
}