oracle之CLOB处理完整版

作者:网络 来源:佚名 更新时间:2008-02-08 22:56:24 点击:
/**
*
*操作oracle数据库的clob字段,包括读和写
*作者:令少爷
* */

package com.nes.common.sql.lob;

import java.sql.*;
import java.io.*;
import oracle.jdbc.oracleresultset;
import oracle.sql.*;

public class jclob {

string tablename = null; //表名
string primarykey = null; //表的主键名
string primaryvalue = null; //表的主键值
string fieldname = null; //表的clob字段名
string clobvalue = null; //表的clob字段值

connection conn = null; //与oracle的连接

/**
*
*用于测试用
*
* */
public static void main(string[] args) {
try {
jclob jc = new jclob(getconnection(),"aa","a","aaaa","c","ccc");
jc.write();
jc.read();
}
catch (exception e) {
system.out.println(e);
e.printstacktrace();
}
}


/**
*
*构造方法
*
* */
public jclob(connection connection,string tablename,string primarykey,string primaryvalue,string fieldname,string clobvalue) {
this.conn = connection;
this.tablename = tablename;
this.primarykey = primarykey;
this.primaryvalue = primaryvalue;
this.fieldname = fieldname;
this.clobvalue = clobvalue;
}

/**
*
*构造方法,但不必传clobvalue值
*一般构造出的实例用来读clob字段
*
* */
public jclob(connection connection,string tablename,string primarykey,string primaryvalue,string fieldname) {
this.conn = connection;
this.tablename = tablename;
this.primarykey = primarykey;
this.primaryvalue = primaryvalue;
this.fieldname = fieldname;
}

/**
*
*用于测试
*
* */
public static connection getconnection() throws sqlexception,classnotfoundexception {
class.forname("oracle.jdbc.oracledriver");
connection conn = drivermanager.getconnection("jdbc:oracle:thin:@192.168.1.18:1521:portal","portal","portal");
return conn;
}

/**
*
*读数据库中clob字段的内容
*@return clob字段值
*
* */
public string read() throws sqlexception,ioexception {
string rtn = null;
try {
string sql = "select " + fieldname + " from " + tablename + " where " + primarykey + "=" + primaryvalue;
//connection conn = getconnection();
preparedstatement pstmt = conn.preparestatement(sql);
//int v = integer.parseint(primaryvalue);
//pstmt.setint(1,v);
resultset rs = pstmt.executequery();

java.sql.clob clob = null;
if (rs.next()) {
clob = rs.getclob(fieldname);
//clob = ((oracleresultset)rs).getclob(fieldname);
//clob = ((org.apache.commons.dbcp.delegatingresultset)rs).getclob(fieldname);
//reader in = clob.getcharacterstream();
inputstream input = clob.getasciistream();
int len = (int)clob.length();
byte[] by = new byte[len];
int i ;//= input.read(by,0,len);
while(-1 != (i = input.read(by, 0, by.length))) {
input.read(by, 0, i);
}
rtn = new string(by);
}
}
catch (sqlexception e){
throw e;
}
catch (exception ee) {
ee.printstacktrace();
}

return rtn;
}

/**
*
*葱数据库中clob字段的内容
*
* */
public void write() throws sqlexception,ioexception {
string sql = "update " + tablename + " set " + fieldname + "=empty_clob() where " + primarykey + "=" + primaryvalue;
//connection conn = getconnection();
conn.setautocommit(false);

preparedstatement pstmt = conn.preparestatement(sql);
pstmt.executeupdate();

sql = "select " + fieldname + " from " + tablename + " where " + primarykey + "=" + primaryvalue;
statement st = conn.createstatement();
resultset rs = st.executequery(sql);

java.sql.clob clob ;
if (rs.next()) {
clob = ((oracle.jdbc.oracleresultset)rs).getclob(fieldname);
//clob = ((org.apache.commons.dbcp.delegatingresultset)rs).getclob(fieldname);
oracle.sql.clob my_clob = (oracle.sql.clob)clob;
outputstream writer = my_clob.getasciioutputstream();
byte[] contentstr = this.getcontent().getbytes();
writer.write(contentstr);
writer.flush();
writer.close();
}

conn.commit();
rs.close();
st.close();
pstmt.close();
conn.setautocommit(true);
}

/**
*
*
* */
private string getcontent() {
return this.clobvalue;
}

/**
*
*
* */
public void setclobvalue(string clobvalue) {
this.clobvalue = clobvalue;
}
}