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。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

kingspider-sh

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值