java读写oracle Clob数据

代码

package com.jstrd.common;

import java.io.BufferedReader;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.sql.CLOB;

import com.exp.dao.DBConnection;
import com.exp.dao.ormapping.BizDataAdapter;
import com.exp.fcl.vo.CommonVO;

/**
* 
* Description: Oracle CBlob读取
* 
* 
* Project: 江苏供应链系统
* 
* 
* Copyright: Copyright (c) jstrd 2004-2009
* 
* 
* Company: 中博信息技术研究院有限公司
* 
* 
* DateTime: 2010.02.03 16:25
* 
*
* @author dingzh
* @version 1.0.0.0
*/
public class ReadClobCommon {

 /**
 * 获取Clob
 * @param dbConn
 * @param sql
 * @param fieldName
 * @return
 */
 public String getClob(DBConnection dbConn, String sql, String fieldName) {
 String ret = "";
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 //java.sql.*
 Connection conn = dbConn.getConnection();
 pstmt = conn.prepareStatement(sql);
 rs = pstmt.executeQuery();
 Clob clob = null;
 if (rs.next()) {
 clob = rs.getClob(fieldName);
 BufferedReader in = new BufferedReader(clob.getCharacterStream());
 StringWriter out = new StringWriter();
 int c;
 while((c=in.read()) !=-1){
 out.write(c);
 }
 ret = out.toString().trim();
 out.close();
 in.close();
 }
 } catch(SQLException es) {
 es.printStackTrace();
 ret = "";
 } catch(Exception e) {
 e.printStackTrace();
 ret = "";
 } finally {
 try {
 if(null != rs) {
 rs.close();
 }
 if(null != pstmt) {
 pstmt.close();
 }
 } catch (SQLException e) {}
 }
 return ret;
 }

 /**
 * 插入Clob
 * @param dbConn
 * @param table
 * @param idField
 * @param fieldName
 * @param id
 * @param content
 * @param bApeand
 * @return
 */
 public int setClob(DBConnection dbConn, String table, String idField, String fieldName,
 String id, String content, boolean bApeand) {
 int ret = -1;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 //java.sql.*
 Connection conn = dbConn.getConnection();
 String sql = "select "+fieldName+" from "+table+" where "+idField+"="+id+" for update";
 pstmt = conn.prepareStatement(sql);
 rs = pstmt.executeQuery();
 CLOB clob = null;
 if (rs.next()) {
 //获取clob对象，此处的clob是oracle.sql.Clob
 clob = (CLOB)rs.getClob(fieldName);
 //执行更新操作
 long pos = clob.length();
 Writer wr = clob.setCharacterStream(pos); //clob.getCharacterOutputStream();
 if(bApeand) {
 wr.append(content);
 } else {
 wr.write(content);
 }
 wr.flush();
 wr.close();
// //
 ret = 1;
 }
 } catch(SQLException es) {
 es.printStackTrace();
 ret = -1;
 } catch(Exception e) {
 e.printStackTrace();
 ret = -1;
 } finally {
 try {
 if(null != rs) {
 rs.close();
 }
 if(null != pstmt) {
 pstmt.close();
 }
 } catch (SQLException e) {}
 }
 return ret;
 }

 /**
 * 获取IntfXml
 * @param dbConn
 * @param ifId
 * @return
 */
 public String getIntfXml(DBConnection dbConn, int ifId) {
 String ret = "";
 BizDataAdapter dataAdapter = null;
 try {
 CommonVO paramsVO = new CommonVO();
 paramsVO.setInteger("if_id", ifId);
 dataAdapter = new BizDataAdapter(dbConn);
 String sql = dataAdapter.getSql("[gyl.interface.log.getXmlInfo]", paramsVO);
 ret = this.getClob(dbConn, sql, "IF_XML");
 } catch(Exception e) {
 ret = "";
 }
 return ret;
 }
}

代码

<?xml version="1.0" encoding="GB2312"?>
<sqls>
 <sql id="gyl.interface.log.insert">
 <statement>
 <![CDATA[
 insert into GYL_INTEFACE_LOG(IF_ID, IF_MODULE, IF_SYSTEM, IF_CODE, IF_XML, IF_RESULT, IF_TYPE, IF_CREATETIME, IF_UPDATETIME, IF_STATE, IF_SEND_CNT)
 values({n:if_id}, {if_module}, {if_system}, {if_code}, empty_clob(), empty_clob(), {n:if_type}, sysdate, sysdate, 0, 0)
 ]]>
 </statement>
 </sql>
 <sql id="gyl.interface.log.update">
 <statement>
 <![CDATA[
 update GYL_INTEFACE_LOG
 set IF_UPDATETIME=sysdate, IF_STATE={if_state}, IF_SEND_CNT=IF_SEND_CNT+1
 where IF_ID={n:if_id}
 ]]>
 </statement>
 </sql>
 <sql id="gyl.interface.log.query4send">
 <statement>
 <![CDATA[
 select * from (
 select row_number() over (order by '1') as seq,TEMP_EXPPAGEQUERY.*
 from (
 select IF_ID, IF_MODULE, IF_SYSTEM, IF_CODE from GYL_INTEFACE_LOG
 where (IF_STATE=0 or (IF_SEND_CNT<3 and IF_STATE=2))
 and IF_MODULE={if_module} and IF_TYPE={n:if_type}
 order by IF_ID
 ) TEMP_EXPPAGEQUERY
 )
 where seq>0 and seq<=100
 ]]>
 </statement>
 </sql>
 <sql id="gyl.interface.log.getXmlInfo">
 <statement>
 <![CDATA[
 select IF_XML from GYL_INTEFACE_LOG where IF_ID={n:if_id}
 ]]>
 </statement>
 </sql>
 <sql id="gyl.interface.log.getXmlResult">
 <statement>
 <![CDATA[
 select IF_RESULT from GYL_INTEFACE_LOG where IF_ID={n:if_id}
 ]]>
 </statement>
 </sql>
</sqls>