操作Oracle的clob类型字段

引用:

https://www.cnblogs.com/johnason/archive/2013/04/17/3026529.html

1.插入和更新

插入一条数据，注意CLOB字段，需要先插入一个空的clob类型 empty_clob()，然后再单独更新clob字段

在插入到更新之间一定要将自动提交设为false，否则，再次查找时就不能正确更新，查找时一定要用select XXX from table where ... for update   
如果不加for   update会报：“row containing the LOB value is not locked”；
如果在插入前没有将自动提交设为false会报  “fetch out of sequence”。

String sql = "insert into User_CourseWare(User_Id,Courseware_Id,Progress,Report ,id)values( ?,?,?,empty_clob(),  user_courseware_sq.nextval  )";
　　conn.setAutoCommit(false);//设置不自动提交  
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, userid);
   pstmt.setInt(2, courseware_Id);
   pstmt.setInt(3, Progress);
   pstmt.executeUpdate();
   conn.commit();
   pstmt = null;
   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);
   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();
   conn.commit();

2.查询

Clob clob = rs.getClob("MODULE_INFO");
方法一：
byte[] info = clob!=null?clob.getSubString((long)1,(int)clob.length()).getBytes():null;
方法二：
String line = "";
String value = "";
Reader reader = clob.getCharacterStream();
BufferedReader br = new BufferedReader(reader);
while ((line = br.readLine()) != null) {
    value += line + "\r\n";
}
    System.out.println(value);
byte[] info = value.getBytes();