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);
}
}