Save LOB Field Type to Oracle DB

尽管网上有很多关于LOB类型的栏位读写方法,但由于本人才疏学浅的原故,还是摸索了很长时间才将该方法应用到我的项目中来.

我总结方法如下:

i.insert a null Blob buffer

 sql=" insert into "+ strTableName+"(ID, title, postdate, userid, contents, attachfile,hit)"
              +" values("+ID+","+Title+",sysdate,"+UserID+",empty_clob(),"+AttachFile+","+Hit+") ";
          conn=ConPool.getConnection();
          Statement stmt;
          stmt=conn.createStatement();
          stmt.executeUpdate(sql);
          conn.commit();

ii.update the record
       //select the BLOB object and locks it
this.updateClob(Contents,strTableName,"contents","ID",ID); //this use as below the update method

 

//updateCLOB Method
      public void updateClob(String inContent,String table,String clobColumn,String keyColumn,int keyValue) throws Exception,SQLException{
      {
        String sql = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        StringBuffer sqlBuffer = new StringBuffer();

        try {
          if (conn==null) conn=ConPool.getConnection();

          //将Commit设置为false很关键
          conn.setAutoCommit(false);

          sqlBuffer.append("select ");
          sqlBuffer.append(clobColumn);
          sqlBuffer.append(" from ");
          sqlBuffer.append(table);
          sqlBuffer.append(" where ");
          sqlBuffer.append(keyColumn);
          sqlBuffer.append("=");
          sqlBuffer.append(keyValue);
          //注意这里的”for update”
          sqlBuffer.append(" for update ");

          stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

 //有此人说要用Oracle.JDBC.Driver.OracleResult方法,但我一直没试成功,仍然使用java.sql.Result方法
//这里主要是取得刚才添加的Clob.Empty()的course

          rs = stmt.executeQuery(sqlBuffer.toString());
          //System.out.println(sqlBuffer.toString());
          try{
          if (rs.next()) {
           //向CLOB对象中写入数据,写大数据入DB了
             CLOB clob= (CLOB)rs.getClob(1);
             clob.putString(1, inContent);
             System.out.println(inContent);
             sql = "update "+table+" set "+clobColumn+"=? where "+keyColumn+"="+keyValue; //将大文本更新进去
             System.out.println(sql);
             pstmt = conn.prepareStatement(sql);
             pstmt.setClob(1,(Clob)clob);
             pstmt.executeUpdate();
         }
         conn.commit();
        }catch(SQLException e){
          conn.rollback();
          throw e;
        }finally{
          rs.close();
          stmt.close();
          pstmt.close();
        }//end try

      }catch (SQLException e) {
        throw e;
      }finally {
        conn.setAutoCommit(true); //前面关闭了Commit,这里打开
      }
    }
    }
 

还是比较简单的.