Java ResultSet 对Oracle Clob（大字符串）格式的操作 增改查

/*   获得大字段XML

    获得大字符串格式

    @param user_id

            用户ID

    @param courseware_id

              课件ID

   @return 大字符串

   

   */

  public  String getCourseClob( int  user_id, int  courseware_id) { // 根据课件ID和人ID查询课程ID

   String content = "null" ;

   try  {

    Class.forName( this .sDBDriver);

    Connection conn = DriverManager.getConnection( this .url, this .user,

      this .pwd);

    conn.setAutoCommit( false );

    ResultSet rs = null ;

    CLOB clob = null ;

    String sql = "" ;

    sql = "select Report from User_CourseWare  where user_id=? and courseware_id=?  " ;

 

    PreparedStatement pstmt = conn.prepareStatement(sql);

    pstmt.setInt( 1 , user_id);

    pstmt.setInt( 2 , courseware_id);

    rs = pstmt.executeQuery();

    if  (rs.next()) {

     clob = (CLOB) rs.getClob( 1 );

 

     if  (clob != null  && clob.length() != 0 ) {

      content = clob.getSubString(( long ) 1 , ( int ) clob.length());

      content = this .Clob2String(clob);

     }

 

    }

 

    rs.close();

    conn.commit();

    pstmt.close();

    conn.close();

 

   } catch  (ClassNotFoundException e) {

 

    e.printStackTrace();

    // return "null";

    content = "error" ;

   } catch  (SQLException e) {

    e.printStackTrace();

    // return "null";

    content = "error" ;

   }

   return  content;

  }

 

/*

    clob to string

    大字符串格式转换STRING

    @param clob

   @return 大字符串

    

   */

  public  String Clob2String(CLOB clob) { // Clob转换成String 的方法

   String content = null ;

   StringBuffer stringBuf = new  StringBuffer();

   try  {

    int  length = 0 ;

    Reader inStream = clob.getCharacterStream(); // 取得大字侧段对象数据输出流

    char [] buffer = new  char [ 10 ];

    while  ((length = inStream.read(buffer)) != - 1 ) // 读取数据库 //每10个10个读取

    {

     for  ( int  i = 0 ; i < length; i++) {

      stringBuf.append(buffer[i]);

     }

    }

 

    inStream.close();

    content = stringBuf.toString();

   } catch  (Exception ex) {

    System.out.println( "ClobUtil.Clob2String:"  + ex.getMessage());

   }

   return  content;

  }

 

  /*

    

    更新Clob(大字符串格式)内容

   */

 

  public  String updateClob( int  userid, int  courseware_Id, int  Progress,

    String CourseClob) {

   this .updateUser_Course(userid, courseware_Id, Progress); // 调用更新进度

   try  {

    Class.forName( this .sDBDriver);

    Connection conn = DriverManager.getConnection( this .url, this .user,

      this .pwd);

    String sql = "update User_CourseWare set  Report=empty_clob() , Progress=? where User_Id = ? and  Courseware_Id =? " ;

    PreparedStatement pstmt = conn.prepareStatement(sql);

    pstmt.setInt( 1 , Progress);

    pstmt.setInt( 2 , userid);

    pstmt.setInt( 3 , courseware_Id);

    int  i1 = pstmt.executeUpdate();

    conn.commit();

    // System.out.println("update sql="+sql);

    pstmt = null ;

    if  (i1 > 0 ) {

 

    }

 

    ResultSet rs = null ;

    CLOB clob = null ;

    String sql1 = "select Report from User_CourseWare where User_id=? and Courseware_id=?  for update" ;

    pstmt = conn.prepareStatement(sql1);

    // System.out.println("select sql="+sql1);

    pstmt.setInt( 1 , userid);

    pstmt.setInt( 2 , courseware_Id);

    rs = pstmt.executeQuery();

    if  (rs.next()) {

     clob = (CLOB) rs.getClob( 1 );

    }

 

    Writer writer = clob.getCharacterOutputStream();

    writer.write(CourseClob);

    writer.flush();

    writer.close();

    rs.close();

    conn.commit();

    pstmt.close();

    conn.close();

 

   } catch  (Exception e) {

    e.printStackTrace();

    return  "error" ;

   }

   return  "success" ;

  }