当前页面: 开发资料首页 → JSP 专题 → 如何向oracle中插入clob记录?

如何向oracle中插入clob记录?

摘要: 如何向oracle中插入clob记录?

可以读取了.但是插入时候
oracle.sql.CLOB clob = (oracle.sql.CLOB)(rs.getObject(1));
报错,说类型不匹配,我用tomcat.
还有,问一下java.sql.Clob有什么用?

---

1.先插入一个EMPTY_CLOB()
2.sql = "select cn_detail from T_CONT_SAMPLE where cms_id="+cmsid +" for update";

myDbTrans.setAutoCommit(false);
ResultSet rs1 = myDbTrans.executeQuery(sql);
oracle.sql.CLOB clob = null;
if(rs1.next())
{
clob = (oracle.sql.CLOB)rs1.getClob("cn_detail");
}
Writer wr = clob.getCharacterOutputStream();
wr.write(leirong);
wr.flush();
wr.close();
rs1.close();
myDbTrans.commit();
应该可以

---

把你的要插入CLOB的数据先转换成流.这个ByteArrayInputStream是我这里需要的,你可以按你自己的需要转换成对应的流.
ByteArrayInputStream fin=new ByteArrayInputStream(this.fileInfo.getFileContent());
下面对那个CLOB设置值:
pstmt.setBinaryStream(2, fin,(new Integer(fileInfo.getFileSize().toString())).intValue());

---

package com.trsoft.lob;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import oracle.sql.*;
import java.sql.SQLException;

public class OracleClob {

private String DBUrl="";
private String DBUsername="";
private String DBPassword="";

/*
* gong c w 20061030 by update
* 功能：插入clob的值
*
* 参数:
* DBUrl:数据库地址
* DBUsername:数据库名称
* DBPassword:数据库密码
* TableName:表名
* idRow:主键列名
* id:主键值
* LobRow:Lob列名
* Log:Lob值
*
* 流程:
* 1.根据DBUrl、DBUsername、DBPassword建立连接
* 2.拼出sql语句，更新clob字段，把clob字段置这空
* 3.拼出sql语句，查询出要更新有那条记录
* 4.循环记录。把要写的数据读入缓存
* 5.用文字流写进操作
* 6.关闭流
*/
public boolean updateLob(String DBUrl,String DBUsername,
String DBPassword,String TableName,
String idRow,String id,
String LobRow,String Log){

boolean retbool=true;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
this.DBUrl=DBUrl;
this.DBUsername=DBUsername;
this.DBPassword=DBPassword;
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn=DriverManager.getConnection(this.DBUrl,this.DBUsername,this.DBPassword);
stmt=conn.createStatement();
conn.setAutoCommit(false);

stmt.executeUpdate("update "+TableName+" set "+LobRow+"=EMPTY_CLOB() where "+idRow+"='"+id+"'");
rs = stmt.executeQuery("SELECT "+LobRow+" FROM "+TableName+" WHERE "+idRow+"='"+id+"' FOR UPDATE");
if(rs.next()){
oracle.sql.CLOB clob = (oracle.sql.CLOB)(rs.getClob(LobRow));
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
StringReader sr=new StringReader(Log);
BufferedReader in = new BufferedReader(sr);
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
/* 正式提交 */
conn.commit();
}else retbool=false;

} catch (Exception ex) {
try {
if(conn!=null){
conn.rollback();
conn.setAutoCommit(true);
}
}catch (SQLException ex2){
//throw new DatabaseException(" rollback error.", ex2);
retbool=false;
}
}finally{
try{
rs.close();
stmt.close();
if (conn != null) {
try {
conn.close();
conn = null;

}catch (SQLException ex3) {
//throw new DatabaseException("Error occured in close con.", ex3);
}
//return retbool
}
}catch (Exception e) {
// throw new DatabaseException("Error occured in close con.", ex3);
}
}
return retbool;
}

}
详细请参考我的blog:
http://dxadnwfn.cublog.cn/
里面有读与写.

---

只能UPDATE不能INSERT

---

楼上几位,我就是oracle.sql.CLOB clob = (oracle.sql.CLOB)(rs.getObject(1));
这里过不去,类型匹配错误.
oracle.sql.CLOB clob = (oracle.sql.CLOB)(rs.getClob(1));也是一样的.

还有要转换成流,那form的提交方式也要改了.有没有更好的方法?

---

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 工具类
* @author liuguangshuai
*
*/
public class ClobUtil{
/**
* 把colb类型的内容转换为string
* @param o
* @return
* @throws Exception
*/
public static String getStringFromCLOB(Object o) throws Exception{
String result = "";
int clobPerReadLen = 10 * 2048;
StringBuffer tmpstr = new StringBuffer("");
oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
if (clob != null) {
long cloblen = (long) clob.length();
if (cloblen > 0) {
int times = (int) (cloblen / clobPerReadLen);
int len = (int) (cloblen % clobPerReadLen);
if (len != 0) {
tmpstr.append( clob.getSubString(1, len) );
}
for (int j = 0; j < times; j++) {
tmpstr.append( clob.getSubString(len + j * clobPerReadLen + 1,clobPerReadLen) );
}
}

}
result = tmpstr.toString();

return result;
}
/**
* 说明:update CLOB 类型的字段
*
* @param inpConnection
* 连接
* @param tableName
* 表名
* @param colName
* 列名
* @param sWhere
* 条件语句，应包含 'where'
* @param content
* 更新的数据
* @return boolean 更新成功返回ture,更新成功返回flase
*/
public static boolean dbUpdateClob(Connection inpConn, String tableName,String colName, String sWhere,String content){
Statement stmt = null;
String str = null;
ResultSet rs = null;
oracle.sql.CLOB clob = null;
java.io.Writer outstream = null;

if (inpConn == null)
{
return false;
}

if ( (tableName.equals("")) || (tableName == null))
{
return false;
}

try
{
inpConn.setAutoCommit(false);
inpConn.setTransactionIsolation(java.sql.Connection.
TRANSACTION_SERIALIZABLE);
stmt =inpConn.createStatement();

str = "update " + tableName + " set " + colName + "=' ' " + sWhere;
stmt.executeUpdate(str);

str = "select " + colName + " from " + tableName + " " + sWhere +
" for update";
rs =stmt.executeQuery(str);
while (rs.next())
{
clob = (oracle.sql.CLOB) rs.getObject(1);
outstream = clob.getCharacterOutputStream();
if (content != null)
{
outstream.write(content);
outstream.close();
}
}

rs.close();
stmt.close();
inpConn.commit();
inpConn.setAutoCommit(true);
inpConn.setTransactionIsolation(java.sql.Connection.
TRANSACTION_READ_COMMITTED);
return true;
}
catch (Exception e)
{
try
{
if (stmt != null)
{
stmt.close();
}
if (inpConn != null)
{
inpConn.rollback();
inpConn.setAutoCommit(true);
inpConn.setTransactionIsolation(java.sql.Connection.
TRANSACTION_READ_COMMITTED);
}
}
catch (SQLException e1)
{
}
return false; //返回结果操作类实例
}
}
}

---

终于解决了,不能用连接池的连接,一定要自己建连接,这样才不会报错.

---