import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
*
* @author 沙振华
*2008年9月26日
*/
public class Clobnono
{
//ORACLE驱动程序
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
//ORACLE连接用URL
private static final String URL = "jdbc:oracle:thin:@10.62.1.12:1521:oracle";
//用户名
private static final String USER = "wtdpf";
//密码
private static final String PASSWORD = "wtdpf";
//数据库连接
private static Connection conn = null;
//SQL语句对象
private static Statement stmt = null;
//空的构造方法
public Clobnono(){}
//建立测试用表格
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) {}
}
/**
* * 描述：查出所有大字段的值
* 沙振华
* 2008-9-26
* @param getclobpre 查询语句
* @param clob 大字段clob的列名
* @throws Exception
*/
public static String getClobPre(String getclobpre,String clob) throws Exception{
String clobbig="";
PreparedStatement pstm = null;//预处理语句，用于查询数据
ResultSet rs = null;
pstm = conn.prepareStatement(getclobpre);
rs = pstm.executeQuery();
while(rs.next()){
clobbig=getClobString(rs.getClob(clob));
}
return clobbig;
}
/*
* 往数据库中插入一个新的CLOB对象
* insertSQL 插入空clob占位
* updateSQL 把空值修改
* bigString 大字段clob数据
* updateColumn 大字段clob列名
* */
public static void clobInsert(String insertSQL,String updateSQL,String bigString,String updateColumn) throws Exception {
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate(insertSQL);
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery(updateSQL);
while (rs.next()) {
//* 取出此CLOB对象
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(updateColumn);//updateColumn是要处理的大字段名
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new StringReader (bigString));//bigString是要处理的大字段值
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
public static String getClobString(Clob c) {
try {
Reader reader=c.getCharacterStream();
if (reader == null) {
return null;
}
StringBuffer sb = new StringBuffer();
char[] charbuf = new char[4096];
for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) {
sb.append(charbuf, 0, i);
}
return sb.toString();
} catch (Exception e) {
return "";
}
}
//main方法
public static void main(String[] args) throws Exception{
/* 装载驱动,建立数据库连接 */
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = conn.createStatement();
/* 建立测试表格 */
createTables();
/* CLOB对象插入 */
clobInsert("insert into TEST_CLOB (id,CLOBCOL) values ('789', empty_clob())", "select * from TEST_CLOB where id='789' for update", "大发发方法多发发生", "CLOBCOL");
/* CLOB对象查询 */
String clobpre=getClobPre("select * from TEST_CLOB where id='789' ","CLOBCOL");
System.out.println("**2333332@@@*"+clobpre);
/* CLOB对象修改 */
clobUpdate("select * from TEST_CLOB where id='789' for update","小忒啊对方答复的沙发啊", "CLOBCOL" );
/* CLOB对象修改后查询 */
String clobuuuppp=getClobPre("select * from TEST_CLOB where id='789' ","CLOBCOL");
System.out.println("**233333######32@@@*"+clobuuuppp);
}
/**
*
* 描述：
* 沙振华
* 2008-9-26
* @param bigstring
* @param updatesql
* @param updatecolumn
* @throws Exception
*/
public static void clobUpdate( String updatesql,String bigstring, String updatecolumn) throws Exception {
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/*
* 查询CLOB对象并锁定 SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR
* UPDATE
*/
ResultSet rs = stmt.executeQuery(updatesql);
while (rs.next()) {
/*
* 获取此CLOB对象 CLOBCOL
*/
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(updatecolumn);
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new StringReader (bigstring));
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);
}
}