Oracle中对CLOB的操作(by quqi99)

Oracle中对CLOB的操作(by quqi99)

作者：张华 发表于：2007-12-05 ( http://blog.csdn.net/quqi99 )

版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处和作者信息及本版权声明。

在更新CLOB时的字段时，如果直接将值写在SQL语句中，如querySql = "update yijian set content= '"+ content+"' where id="+id;会报错的。所以得采取下列方式：

public boolean updateReply(String reply,int id){
boolean result = false;
DataSource dataSource = null;
try {
Context ctx = new InitialContext();
dataSource = (DataSource) ctx.lookup("java:comp/env/" + dtsrc);
}
catch (Exception ex) {
ex.getMessage();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;

try{
conn = dataSource.getConnection();

//如果没有下面五句，在更新长文本内容时，会出现新内容始终在旧内容之前的问题，我们需要先把旧内容去掉

//对于短文本，则没有这个问题
String updateSql0 = "update yijian set huifu=empty_clob() WHERE id=?";
PreparedStatement updateStmt0 = conn.prepareStatement(updateSql0);
updateStmt0.setInt(1, id);
updateStmt0.executeUpdate();
updateStmt0.close();

String sql = "SELECT huifu FROM yijian WHERE id=? for update";
PreparedStatement selectStmt = conn.prepareStatement(sql);
selectStmt.setInt(1, id);
rs = selectStmt.executeQuery();
while (rs.next()) {
CLOB clob = (CLOB) rs.getClob(1);
//String aaa = readClob(clob);
clob.putString(1, reply);
String updateSql = "update yijian set huifu=? WHERE id=?";
PreparedStatement updateStmt = conn.prepareStatement(updateSql);
updateStmt.setClob(1, clob);
updateStmt.setInt(2, id);
updateStmt.executeUpdate();
updateStmt.close();
}
if (selectStmt != null)
selectStmt.close();
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();

conn.close();
conn=null;
result = true;
}catch(Exception e){
e.printStackTrace();
}
return result;
}

以下是插入数据库：

DataSource dataSource = null;
try {
Context ctx = new InitialContext();
dataSource = (DataSource) ctx.lookup("java:comp/env/" + dtsrc);
}
catch (NamingException ex) {
throw new SQLException(ex.getMessage());
}
Connection conn = null;

PreparedStatement pstmt = null;
ResultSet rs = null;

//生成校验码
if (i==1) {
i=0;
j = (new java.util.Random()).nextInt(900000)+100000;
//插入留言
conn = dataSource.getConnection();
//querySql = "insert into yijian (riqi,xingming,bumen,gongkai,lianxi,leixing,yijian,huifu,zhuangtai,manyi,pingjia,jiaoyan,district,tel)"
// + " values(sysdate(),'"+xingming+"',"+bmid+","+gongkai+",'"+lianxi+"',"+leixing+",'"+yijian+"','',0,0,'',"+j+",'"+district+"','"+tel+"')";
querySql = "insert into yijian (id,riqi,xingming,bumen,gongkai,lianxi,leixing,yijian,huifu,zhuangtai,manyi,pingjia,jiaoyan,district,tel,local)"
+ " values(?,SYSDATE,'"+xingming+"',"+bmid+","+gongkai+",'"+lianxi+"',"+leixing+",empty_clob(),empty_clob(),-1,0,'',"+j+",'"+district+"','"+tel+"','"+localStr+"')";
try {
System.out.println("将要执行： "+querySql);
int id=getNextPublicId("yijian");
pstmt = conn.prepareStatement(querySql);
pstmt.setInt(1,id); //设置主键
i = pstmt.executeUpdate();
//插入大字段
ResultSet rsClob = null;
//用这种容易发生错误ORA-01006: 赋值变量不存在，改成下句带？的形式
//ResultSet rsClob = pstmt.executeQuery("select yijian from yijian where id="+id+" for update");
PreparedStatement pstmttemp = conn.prepareStatement("select yijian from yijian where id=? for update");
pstmttemp.setInt(1,id);
rsClob = pstmttemp.executeQuery();
if(rsClob.next()){
//避免发生错误java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet，改用下句
//CLOB yijianClob = ((OracleResultSet)rsClob).getCLOB(1);
CLOB yijianClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rsClob).getClob(1);
//CLOB huifuClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rsClob).getClob(2);
yijianClob.putString(1,yijian);
//huifuClob.putString(1,"");
PreparedStatement pstmtClob=conn.prepareStatement("update yijian set yijian=? where id='"+id+"' ");
pstmtClob.setClob(1,yijianClob);
//pstmtClob.setClob(2,huifuClob);
pstmtClob.executeUpdate();
pstmtClob.close();
}
rsClob.close();
rsClob = null;
pstmttemp.close();
}
catch (SQLException ex) {
System.err.println("插入留言失败! "+ex.getMessage());
throw ex;
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException ex1) {
}
try {
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
} catch (SQLException ex2) {
}
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex3) {
}
}

//检查插入是否成功
conn = dataSource.getConnection();
querySql = "select id from yijian where xingming='"+xingming+"' and bumen = "+bmid+" and jiaoyan = "+j+" order by id desc";
try {
pstmt = conn.prepareStatement(querySql);
rs = pstmt.executeQuery();
i = 0;
while(rs.next()){
i = rs.getInt(1);
}
}
catch (SQLException ex) {
throw ex;
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
} catch (SQLException ex1) {
}
try {
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
} catch (SQLException ex2) {
}
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex3) {
}
}
}

查看CLOB

conn = dataSource.getConnection();
// if(! bmid.equals("0"))
//System.out.println("bmid in guanli_list.jsp: "+bmid);
// querySql = "SELECT y.id,y.bumen,b.bumen,xingming,date_format(riqi,'%Y-%m-%d'),yijian,zhuangtai,huifu,manyi,pingjia FROM yijian y,bumen b where y.bumen=b.id and "+qStr+" order by y.id desc " + lStr;
//querySql = "SELECT y.id,y.bumen,b.bumen,xingming,TO_CHAR(riqi,'YYYY-MM-DD'),yijian,zhuangtai,huifu,manyi,pingjia FROM yijian y,bumen b where y.bumen=b.id and "+qStr+" order by y.id desc ";
//querySql = "SELECT y.id,y.bumen,b.bumen,xingming,TO_CHAR(riqi,'YYYY-MM-DD'),yijian,zhuangtai,huifu,manyi,pingjia FROM yijian y,bumen b where "+qStr+" order by y.id desc ";
querySql = "SELECT y.id,y.bumen,y.bumen,xingming,TO_CHAR(riqi,'YYYY-MM-DD'),yijian,zhuangtai,huifu,manyi,pingjia,category,leixing FROM yijian y where "+qStr+" ";
// else
// querySql = "SELECT id,bumen, id, xingming,date_format(riqi,'%Y-%m-%d'),yijian,zhuangtai,huifu,manyi,pingjia FROM yijian, where bumen=0 and "+qStr+" order by y.id desc " + lStr;

try {
//pstmt = conn.prepareStatement(querySql);
pstmt = conn.prepareStatement(querySql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = pstmt.executeQuery();
if(pg>1)
rs.absolute((pg-1)*pgsz); //分页，将记录指针指向相应位置
int num = 0;
Map map = new HashMap();
while(rs.next()){
num++;
if(num > pgsz)
break;

//以前要求前台提交留言时同时提交分类，现在不要求有分类了，若仍按以上的按内连接的方式会显示不了数据，那么需用外连接
//用外连接，数据会有重复，这里需过滤掉
String bumenname = getBumenById(rs.getString(2));
String categoryName = "";
if(rs.getString(11)!=null && !"".equals(rs.getString(11)))
categoryName = getCategoryById(rs.getString(11));

//读取clob字段
//CLOB yijianClob = ((OracleResultSet)rs).getCLOB(6);
//报java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.DelegatingResultSet错误
//应该将数据源配置中加上属性：accessToUnderlyingConnectionAllowed="true"，
//如果是在Postgresql中，类似就应该是：PGConnection pgCon =(PGConnection)((DelegatingConnection)con).getInnermostDelegate();
//Oracle驱动放到Tomcat的common/lib目录下，如果直接放在应用的lib目录下也会报此错
CLOB yijianClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rs).getClob(6);
String yijian="";
if(yijianClob!=null){
Reader is=yijianClob.getCharacterStream();
BufferedReader br=new BufferedReader(is);
String s=br.readLine();
while(s!=null){
yijian+=s+"<br>"; //把从数据库中读取的/r/n转换成HTML中的<br>
s=br.readLine();
}
}
//CLOB huifuClob = ((OracleResultSet)rs).getCLOB(8);
CLOB huifuClob = (oracle.sql.CLOB)((org.apache.tomcat.dbcp.dbcp.DelegatingResultSet)rs).getClob(8);
String huifu="";
if(huifuClob!=null){
Reader is=huifuClob.getCharacterStream();
BufferedReader br=new BufferedReader(is);
String s=br.readLine();
while(s!=null){
huifu+=s+"<br>";
s=br.readLine();
}
}

String rengming = rs.getString(4);
if(rengming!=null){
rengming = rengming.replaceAll("<","&lt;");
rengming = rengming.replaceAll(">","&gt;");
}
if(yijian!=null){
yijian = yijian.replaceAll("<","&lt;");
yijian = yijian.replaceAll(">","&gt;");
}
if(huifu!=null){
huifu = huifu.replaceAll("<","&lt;");
huifu = huifu.replaceAll(">","&gt;");
}

读取CLOB内容：

public String readClob(Clob cBlob)throws java.sql.SQLException, java.io.IOException {
Reader r = cBlob.getCharacterStream();
char[] b = new char[1024 * 3];
int i = 0;
CharArrayWriter caw = new CharArrayWriter();
while ((i = r.read(b)) > 0) {
caw.write(b, 0, i);
}

b = caw.toCharArray();
String result = new String(b);
return result;
}