引用:
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();