Oracle大文本clob数据类型的增删改查(二)
clob类型 empty_clob()，然后再单独更新clob字段
// String sql = "insert into
// User_CourseWare(User_Id,Courseware_Id,Progress,Report,Id)values(
// , , ,empty_clob(), )";
String sql = "insert into User_CourseWare(User_Id,Courseware_Id,Progress,Report ,id)values( , , ,empty_clob(), user_courseware_sq.nextval )";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userid);
pstmt.setInt(2, courseware_Id);
pstmt.setInt(3, Progress);
// System.out.println("sql insert=" + sql);
// pstmt.setInt(4, testid);
int i1 = pstmt.executeUpdate();
conn.commit(); www.2cto.com
pstmt = null;
if (i1 > 0) {
// System.out.println("用户ID" + userid + "插入" + courseware_Id+
// "课件成功");
}
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, testid); pstmt.setInt(2, userid); pstmt.setInt(3,
* courseware_Id);
*/
// System.out.println("sql1 select=" + 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";
}
/**
* 获得大字段XML
* 获得大字符串格式
*
* @param user_id
* 用户ID
* @param courseware_id
* 课件ID
* @return 大字符串
*
*/
public String getCourseClob(int user_id, int courseware_id) {// 根据课件ID和人ID查询课程ID www.2cto.com
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();
www.2cto.com
} 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 { www.2cto.com
int length = 0;
Reader inStream = clob.getCharacterStream(); // 取得大字侧段