oracle之CLOB處理完整版__oracle

  /**
*
*操作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;
}
}