各位tx，有没有既能读写oracle8i的clob字段又能读写oracle9i的clob字段的java组件？
一、 CLOB对象的存取
1、往数据库中插入一个新的CLOB对象
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);
}
2、修改CLOB对象（是在原CLOB对象基础上进行覆盖式的修改）
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);
}
3、替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象）
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);
}
4、CLOB对象读取
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);
}
这段代码在8,9i都没问题。
用的weblogic连接池
package com.westerasoft.kdcerp.servlets.drapshap;
import java.net.*;
import java.sql.*;
import java.io.*;
import com.westerasoft.util.dbutil.dataaccess.DBconn;
/**
*
* <p>Title:处理blob数据类
* <p>Description: 其中包括两个方法：1。将文件写入数据库2。从数据库中读数据显示到客户端
* <p>Copyright: Copyright (c) 2003</p>
* <p>author: liuyi </p>
* @
* @version 1.0
*/
public class BlobData {
public Connection con=null;
public ResultSet rs=null;
public java.sql.PreparedStatement pst=null;
File myFile=null;
java.io.InputStream bais=null;
String sqlblob[]=new String[2];
public BlobData(){
init();
}
private void init(){
sqlblob[0]="select dcblob from p_dcblob where ";//p_dcblob
sqlblob[1]="select jkzzblob from p_jkzzblob where ";//p_jkzzblob
}
/**
* Get Blob
* @param index Array
* @param ifwhere condition
* @return ByteArrayOutputStream
*/
public java.io.ByteArrayOutputStream readFileFromDB(int index,String ifwhere){
InputStream is=null;
int bytesread=0;
byte []butter=new byte[8*1024];
java.io.ByteArrayOutputStream bos=null;
String sql="";
java.sql.Blob blob = null;
sql=sqlblob[index]+ifwhere;
System.out.println(sql);
System.out.println(sql);
try{
//Initialization ByteArrayOutputStream
bos = new java.io.ByteArrayOutputStream();
//Get Connection
con = new DBconn().getConnection();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
int size = 0;
while (rs.next()) {
//Get Blob
blob = rs.getBlob(1);
if(blob.length()!=0)
is = blob.getBinaryStream();
}
while(((bytesread=is.read())!=-1)){
bos.write(bytesread);
}
}catch (Exception e) {
System.out.println(e.toString());
}finally {
try {
if (rs != null)
rs.close();
if (pst != null)
pst.close();
if (con != null)
con.close();
}
catch (Exception e) {
System.out.println("数据库连接错误："+e.toString());
}
}
return bos;
}
/**
* 将文件写入数据库
* @param pathname：文件全路径
*/
public void writeDBFromFile(String pathname){
myFile=new File(pathname);
try{
//将文件转化为文件流
java.io.FileInputStream fis=new FileInputStream(myFile);
//得到一个数据库连接
con=new DBconn().getConnection();
pst=con.prepareStatement("update test set mblob=? where id=2");
//pst.setInt(1,2);
byte bb[];
String sql="ab";
bb=sql.getBytes();
pst.setBinaryStream(1,new java.io.ByteArrayInputStream(bb),bb.length);
//pst.setBinaryStream(1,fis,(int)myFile.length());
pst.executeUpdate();
}catch(Exception e){
System.out.println(e.toString());
}finally{
try{
if (rs!=null) rs.close();
if (pst!=null) pst.close();
if (con!=null) con.close();
}catch(Exception e){
System.out.println(e.toString());
}
}
}
public void ss(){
String sql="update H_ZGJJJSXX set enddate = to_date('2003-12-04','yyyy-mm-dd'),declarefundbase = 44.0,checkedfundbase = 66.0 where 1=1 and stardate = to_date('2003-10-02','yyyy-mm-dd') and rybm = '001'";
try{
con = new DBconn().getConnection();
java.sql.Statement st = con.createStatement();
st.executeUpdate(sql);
}catch(Exception e){
}
}
public static void main(String[] args){
new BlobData().readFileFromDB(1,"");
//new testBlob().readFileFromDB(0,"1");
}
}
