用JDBC操縱BLOB和CLOB資料

在訪問Oracle 資料庫，對Oracle 的BLOB 和CLOB 進行操作的時候，當通過Oracle JDBC Driver 來呼叫的時，如下所例：

Driver myDriver = (Driver)
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
conn = myDriver.connect("jdbc:oracle:thin:" , props);
Statement stmt = conn.createStatement();
stmt.execute("select blob_content from lobtest where id=1");
ResultSet rs = stmt.getResultSet();
System.out.println("ResultSet result:"+rs);
while ( rs.next() ) ...{
System.out.println("BLOB:"+rs.getBlob("blob_content"));
myBlob = (oracle.sql.BLOB)rs.getBlob("blob_content");
}

 

返回的資料物件正確，是oracle.sql.BLOB 物件，然後可以使用該物件進行方法訪問。
但當使用Oracle JDBC Driver 配置連線池後，使用DataSource 取得資料庫連線之後，查詢CLOB 資料，賦值給oracle.sql.CLOB 的物件，這是就會報型別轉換錯誤。

在網路上有相應的解決方案，但都是依賴於某一種應用伺服器所提供的oracle連線環境。
下面是一個比較好的解決方案：
1。必須有支援jdbc3.0以上規範的oracle驅動。
2。更新blob欄位資料

public void updateBlob(String tableName, String blobFeild, String pryKey, String pryKeyValue, byte[] blob)
            throws IOException, SQLException ...{
        Statement stmt = null;
        ResultSet rs = null;
        try ...{
            stmt = conn.createStatement();
            String sql = "UPDATE " + tableName + " SET " + blobFeild + "=EMPTY_BLOB() WHERE " + pryKey + "='"
                    + pryKeyValue + "'";
            // 如果引數blob為null，清空blob值；否則先清空blob值，然後插入新的blob值。
            if (blob == null) ...{
                stmt.executeUpdate(sql);
            } else ...{
                stmt.executeUpdate(sql);
                sql = "SELECT " + blobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue
                        + "' FOR UPDATE";
                rs = stmt.executeQuery(sql);
                if (rs.next()) ...{
                    Blob inBlob = rs.getBlob(blobFeild);
                    int len = inBlob.setBytes(1, blob);
                    log.debug("已成功儲存BLOB大物件：" + len / 1024 + "KB");
                }
            }
        } finally ...{
            DbUtils.close(rs);
            DbUtils.close(stmt);
        }
    }

 

3。獲取blob欄位資料
   

public byte[] findBlob(String tableName, String blobFeild, String pryKey, String pryKeyValue) throws SQLException ...{
        byte[] blob = null;
        Statement stmt = null;
        String sql = "SELECT " + blobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "'";
        ResultSet rs = null;
        try ...{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) ...{
                Blob outBlob = rs.getBlob(blobFeild);
                if (outBlob != null && outBlob.length() > 0) ...{
                    blob = outBlob.getBytes(1, (int) outBlob.length());
                }
            }
        } finally ...{
            DbUtils.close(rs);
            DbUtils.close(stmt);
        }
        return blob;
    }

 

4。更新clob欄位資料
   

public void updateClob(String tableName, String clobFeild, String pryKey, String pryKeyValue, String clob)
            throws IOException, SQLException ...{
        Statement stmt = null;
        ResultSet rs = null;
        try ...{
            stmt = conn.createStatement();
            String sql = "UPDATE " + tableName + " SET " + clobFeild + "=EMPTY_CLOB() WHERE " + pryKey + "='"
                    + pryKeyValue + "'";
            // 如果引數clob為null，清空clob值；否則先清空clob值，然後插入新的clob值。
            if (clob == null) ...{
                stmt.executeUpdate(sql);
            } else ...{
                stmt.executeUpdate(sql);
                sql = "SELECT " + clobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue
                        + "' FOR UPDATE";
                rs = stmt.executeQuery(sql);
                if (rs.next()) ...{
                    Clob inClob = rs.getClob(clobFeild);
                    int len = inClob.setString(1, clob);
                    log.debug("已成功儲存CLOB大物件：" + len / 1024 + "KB");
                }
            }
        } finally ...{
            DbUtils.close(rs);
            DbUtils.close(stmt);
        }
    }

 

5。獲取clob欄位資料
   

 public String findClob(String tableName, String clobFeild, String pryKey, String pryKeyValue) throws SQLException ...{
        String clob = "";
        Statement stmt = null;
        String sql = "SELECT " + clobFeild + " FROM " + tableName + " WHERE " + pryKey + "='" + pryKeyValue + "'";
        ResultSet rs = null;
        try ...{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) ...{
                Clob outClob = rs.getClob(clobFeild);
                if (outClob != null && outClob.length() > 0) ...{
                    clob = outClob.getSubString(1, pryKeyValue.length());
                }
            }
        } finally ...{
            DbUtils.close(rs);
            DbUtils.close(stmt);
        }
        return clob;
    }

 

採用以上這種方式可以避免對連線池提供方的依賴。