Oracle CLOB字段处理方法

最新推荐文章于 2021-04-15 19:20:48 发布

原创最新推荐文章于 2021-04-15 19:20:48 发布 · 3.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #string #null #exception #sql #buffer

数据库专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中处理CLOB字段的方法，包括插入记录、更新CLOB字段的内容及从CLOB字段读取数据的过程。这些方法适用于Oracle10g及以上版本，并指出Oracle9i版本在处理CLOB和BLOB时存在问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

以下方法为自己编写的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。