oracle之CLOB处理完整版

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

上一个：weblogic下写clob的方法
下一个：一个连接池的例子（来自JIVE）（5）