Oracle中BLOB字段CRUD

项目中遇到Orcle的Blob字段：找了很多示例资料饶了不少路，终于调通了。贴出来分享一下！


[code="java"]
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Service;

import weblogic.jdbc.vendor.oracle.OracleThinBlob;

import com.comtop.top.iwfc.client.synchronousdata.model.SynchronousDataVO;

/**
* 同步数据DAO
*/
@Service
@Scope(value = "prototype")
public class SynchronousDataDAO {
   
    /** FIXME */
    @Resource
    private SqlSessionTemplate sqlSessionTemplate;
   
    /** 日志对象 */
    protected final Logger logger = LoggerFactory.getLogger(getClass());
   
    /**
     * 新增待同步数据对象
     *
     * @param objSynchronousDataVO 同步数据对象
     */
    public void insertSynchronousData(SynchronousDataVO objSynchronousDataVO) {
        StringBuffer sbSQL = new StringBuffer(128);
        sbSQL.append("INSERT INTO CWF_SYNCHRONOUSDATA ");
        sbSQL.append("(ID,SERVICE_ADDRESS,SYNCHRONOUS_DATA,SEND_COUNT)");
        sbSQL.append(" VALUES(?,?,EMPTY_BLOB(),?)");
       
        StringBuffer sbSQLUpdate = new StringBuffer(128);
        sbSQLUpdate.append("SELECT SYNCHRONOUS_DATA FROM CWF_SYNCHRONOUSDATA ");
        sbSQLUpdate.append(" WHERE  ID = ? FOR UPDATE");
       
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 设置数据库连接
            conn = getConnection();
            // 设置不自动提交事务
            // conn.setAutoCommit(false);
            pstmt =
                conn.prepareStatement(sbSQL.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String strKey = objSynchronousDataVO.getId();
            // 设置查询参数
            pstmt.setString(1, strKey);
            pstmt.setString(2, objSynchronousDataVO.getServiceAddress());
            pstmt.setInt(3, objSynchronousDataVO.getSendCount());
            // 执行新增
            pstmt.executeUpdate();
            // 清空pstmt的SQL语句
            pstmt.clearBatch();
            closeConnection(pstmt);
            // 装载插入SYNCHRONOUS_DATA大字段的SQL语句
            pstmt =
                conn.prepareStatement(sbSQLUpdate.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, strKey);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                OracleThinBlob blob = (OracleThinBlob) rs.getBlob("SYNCHRONOUS_DATA");
                OutputStream outStream = blob.getBinaryOutputStream();
                String strSynData = objSynchronousDataVO.getSynchronousData();
                byte byteSynData[] = strSynData.getBytes();
                outStream.write(byteSynData, 0, byteSynData.length);
                outStream.flush();
                outStream.close();
            }
        } catch (SQLException ex) {
            logger.error("SQL语句执行错误:" + ",SQL:" + sbSQL, ex);
        } catch (IOException ei) {
            logger.error("新增大字段文件时发生异常!" + sbSQLUpdate, ei);
        } finally {
            this.closeConnection(rs, pstmt, conn);
        }
    }
   
    /**
     * 查询所有待同步数据对象
     *
     * @param objSynchronousDataVO 同步数据对象
     * @return 满足条件的待同步数据对象集合
     */
    public List queryAllSynchronousData(SynchronousDataVO objSynchronousDataVO) {
        // 初始化查询条件
        StringBuffer sbSQL = new StringBuffer(128);
        sbSQL.append("SELECT * FROM CWF_SYNCHRONOUSDATA ");
       
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        List lstSynchronousDataVO = null;
        SynchronousDataVO objSynData;
        try {
            // 设置数据库连接
            conn = getConnection();
            pstmt =
                conn.prepareStatement(sbSQL.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            // 执行查询
            rs = pstmt.executeQuery();
            // 确定要查询记录的起始条数
            if (rs != null) {
                lstSynchronousDataVO = new ArrayList();
                while (rs.next()) {
                    objSynData = new SynchronousDataVO();
                    objSynData.setId(rs.getString("ID"));
                    objSynData.setServiceAddress(rs.getString("SERVICE_ADDRESS"));
                    objSynData.setSendCount(rs.getInt("SEND_COUNT"));
                   
                    Blob blob = rs.getBlob("SYNCHRONOUS_DATA");
                    InputStream inStream = blob.getBinaryStream();
                    if (inStream != null) {
                        byte[] buff = new byte[1024];
                        OutputStream outStream = new ByteArrayOutputStream();
                        int i = 0;
                        while ((i = inStream.read(buff)) != -1) {
                            outStream.write(buff, 0, i);
                        }
                        objSynData.setSynchronousData(outStream.toString());
                        inStream.close();
                        outStream.close();
                    }
                   
                    lstSynchronousDataVO.add(objSynData);
                }
            }
        } catch (SQLException ex) {
            logger.error("SQL语句执行错误 :" + ",SQL:" + sbSQL, ex);
        } catch (IOException ex) {
            logger.error("读取SYNCHRONOUS_DATA大字段时发生IO异常", ex);
        } finally {
            this.closeConnection(rs, pstmt, conn);
        }
        return lstSynchronousDataVO;
    }
   
    /**
     * 根据数据编号删除数据
     *
     * @param objSynchronousDataVO 数据ID
     */
    public void deleteSynchronousData(SynchronousDataVO objSynchronousDataVO) {
        StringBuffer sbSQL = new StringBuffer(128);
        sbSQL.append("DELETE FROM CWF_SYNCHRONOUSDATA ");
        sbSQL.append("WHERE ID=?");
       
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 设置数据库连接
            conn = getConnection();
            pstmt =
                conn.prepareStatement(sbSQL.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String strKey = objSynchronousDataVO.getId();
            // 设置查询参数
            pstmt.setString(1, strKey);
            // 执行新增
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            logger.error("SQL语句执行错误:" + ",SQL:" + sbSQL, ex);
        } finally {
            this.closeConnection(rs, pstmt, conn);
        }
    }
   
    /**
     * 跟新待同步数据
     *
     * @param objSynchronousDataVO 同步数据对象
     */
    public void updateSynchronousData(SynchronousDataVO objSynchronousDataVO) {
        StringBuffer sbSQL = new StringBuffer(128);
        sbSQL.append("UPDATE CWF_SYNCHRONOUSDATA SET SEND_COUNT=? ");
        sbSQL.append("WHERE ID=?");
       
        ResultSet rs = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 设置数据库连接
            conn = getConnection();
            pstmt =
                conn.prepareStatement(sbSQL.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String strKey = objSynchronousDataVO.getId();
            // 设置查询参数
            pstmt.setInt(1, objSynchronousDataVO.getSendCount());
            pstmt.setString(2, strKey);
            // 执行新增
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            logger.error("SQL语句执行错误:" + ",SQL:" + sbSQL, ex);
        } finally {
            this.closeConnection(rs, pstmt, conn);
        }
    }
   
    /**
     * 获取连接
     *
     * @return conn
     */
    private Connection getConnection() {
        Connection conn = null;
        try {
            conn = sqlSessionTemplate.getDataSource().getConnection();
        } catch (SQLException e) {
            logger.error("获取数据库连接失败！", e);
            e.printStackTrace();
        }
        return conn;
    }
   
    /**
     * 释放DB资源
     *
     * @param rs ResultSet 结果集
     * @param stat Statement 语句对象
     * @param conn Connection DB联接
     */
    private final void closeConnection(ResultSet rs, PreparedStatement stat, Connection conn) {
        this.closeConnection(rs);
        this.closeConnection(stat);
        this.closeConnection(conn);
    }
   
    /**
     * 释放DB资源
     *
     * @param rs ResultSet 结果集
     */
    private final void closeConnection(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.warn("数据库ResultSet关闭异常.", ex);
            }
        }
    }
   
    /**
     * 释放DB资源
     *
     * @param stat Statement 语句对象
     */
    private final void closeConnection(PreparedStatement stat) {
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException ex) {
                logger.warn("数据库Statement关闭异常.", ex);
            }
        }
    }
   
    /**
     * 释放DB资源
     *
     * @param conn Connection DB联接
     */
    private final void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                logger.error("数据库Connection关闭异常.", ex);
            }
        }
    }
}