Oracle CLOB字段处理方法

以下方法为 自己编写的OracleDAOUtil中的方法。

1. 插入记录
    private static void insert(String id, Connection connection) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";
        try {

            sql = "insert into Test (ID,Value) values(?,empty_clob())"; //先插入空的CLOB对象
            conn = connection;
            stmt = conn.prepareStatement(sql);

            int count = 0;
            stmt.setString(++count, id);

            stmt.execute();

            OracleDAOUtil.updateField(conn, "Test", "ID", id, "Value", "abcd"); //写入数据
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
            } catch (Exception ex) {

            }
            try {
                if (conn != null) {
                    conn = null;
                }
            } catch (Exception ex) {

            }
        }
    }

2. 向CLOB字段写入数据
    public static void updateField(Connection conn, String tableName, String keyName, String keyValue, String fieldName, String fieldValue) throws WorkflowException
    {
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
        ResultSet rset = null;
        try {
            String sql = "select " + fieldName + " from " + tableName + " where " + keyName + "=? for update";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, keyValue);
            rset = pstmt.executeQuery();
            Clob clob = null;
            if (rset.next())
                clob = (Clob) rset.getClob(1);

            String value = fieldValue;
            if (value == null)
                value = "";
            char[] valueChars = new char[value.length()];
            value.getChars(0,value.length(),valueChars,0);
            clob.truncate(0); //避免前一次的数据混在第二次的数据中（如果上次的数据比本次的数据多的话）
            Writer writer = clob.setCharacterStream(0);
            writer.write(valueChars);
            writer.close();

        } catch (Exception e) {
            throw e;
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                    rset = null;
                } catch (SQLException e) {
                    throw e;
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                    pstmt = null;
                } catch (SQLException e) {
                    throw e;
                }
            }
            if (pstmt2 != null) {
                try {
                    pstmt2.close();
                    pstmt2 = null;
                } catch (SQLException e) {
                    throw e;
                }
            }
        }
    }

3. 从CLOB字段读数据
    public static String readField(Connection conn, String tableName, String keyName, String keyValue, String fieldName)  throws WorkflowException
    {
        String value = "";
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try {
            String sql = "select " + fieldName + " from " + tableName + " where " + keyName + "=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, keyValue);
            rset = pstmt.executeQuery();
            Clob clob = null;
            if (rset.next())
                clob = (Clob) rset.getClob(1);
            if (clob != null) {
                int len = (int) clob.length();
                char[] buffer = new char[len];
                Reader reader = clob.getCharacterStream();
                reader.read(buffer);
                reader.close();
                value = new String(buffer);
            }
            return value;
        } catch (Exception e) {
            throw e;
        } finally {
            if (rset != null) {
                try {
                    rset.close();
                    rset = null;
                } catch (SQLException e) {
                    throw e;
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                    pstmt = null;
                } catch (SQLException e) {
                    throw e;
                }
            }
        }
    }

以上方法稍作改进就可用于处理BLOB字段。
如果使用Oracle提供的JDBC驱动来处理CLOB和BLOB字段，那么使用Oracle 10g的版本，Oracle 9i的版本对CLOB和BLOB的处理有问题。Oracle 10g JDBC的thin模式也可用于Oracle 9i。