oraclebloboperation.java

以前做的一个j2ee的项目

            stmt = conn.createStatement();

            //defaultCommit = conn.getAutoCommit();
            //conn.setAutoCommit(false);

            BufferedOutputStream out=null;
            StringReader sReader = new StringReader(wd.getM_strWdNr());
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(colName);
                OutputStream outstream = blob.getBinaryOutputStream();
                out = new BufferedOutputStream(outstream);
                int c;
                while ((c = sReader.read()) != -1) {
                    out.write(c);
                }
                outstream.flush();
                out.flush();
                outstream.close();
                out.close();

                sReader.close();
            }
            rs.close();
            stmt.close();
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw new Exception(e.getMessage());
        } finally {
            conn.setAutoCommit(defaultCommit);
            conn.close();
        }
    }

    public static void updateOracleBlob(Connection conn, String nr, String tablename, String colName, String key) throws
            SQLException, FileNotFoundException, IOException, Exception {
        //SqlMapClient sqlMap = m_sqlMap;
        Statement stmt = null;
        boolean defaultCommit = false;
        try {
            //sqlMap.startTransaction();

            String sql = "select " + colName;
            sql = sql + " FROM " + tablename + " WHERE " + key + " FOR UPDATE ";
            defaultCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            defaultCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);

            BufferedOutputStream out=null;
            StringReader sReader = new StringReader(nr);
            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob(colName);
                OutputStream outstream = blob.getBinaryOutputStream();
                out = new BufferedOutputStream(outstream);
                int c;
                while ((c = sReader.read()) != -1) {
                    out.write(c);
                }
                outstream.flush();
                out.flush();
                outstream.close();
                out.close();

                sReader.close();
            }
            rs.close();
            stmt.close();
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw new Exception(e.getMessage());
        } finally {
            conn.setAutoCommit(defaultCommit);
            stmt.close();
        }
    }


    public static void updateOracleBlob(WDSCwdmbDAO wdmb, String tablename, String colName, String key) throws
            SQLException, FileNotFoundException, IOException, Exception {
        SqlMapClient sqlMap = wdmb.getSqlMap();

        boolean defaultCommit = false;
        Connection conn = null;
        Statement stmt = null;
        String sql = "";
        try {
            conn = JDBCLocator.getInstance().getJDBCConnection();

            defaultCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);

            stmt = conn.createStatement();


            sql = "update " + tablename + " set " + colName + " = EMPTY_BLOB() "
                + " WHERE " + key ;
            stmt.executeUpdate(sql);

            sql = "select " + colName;
            sql = sql + " FROM " + tablename + " WHERE " + key + " FOR UPDATE ";

            BufferedOutputStream out = null;
            StringBuffer buffer = wdmb.getMB_NR_BUFFER();
            StringReader sReader = new StringReader(StringUtils.gbkToISO(buffer.toString()));
            //StringReader sReader = new StringReader((buffer.toString()));
            ResultSet rs = stmt.executeQuery(sql);

            java.sql.Blob blob = null;
            if (rs.next()) {
                blob = (java.sql.Blob) rs.getBlob(colName);
            }
            rs.close();
            stmt.close();
            OutputStream outstream = null;

            if (blob instanceof oracle.sql.BLOB){
                outstream = ((oracle.sql.BLOB)blob).getBinaryOutputStream();
            } else if (blob instanceof weblogic.jdbc.vendor.oracle.
                       OracleThinBlob) {
                outstream = ((weblogic.jdbc.vendor.oracle.OracleThinBlob)blob).getBinaryOutputStream();
            }
            out = new BufferedOutputStream(outstream);
            int c;

            while ((c = sReader.read()) != -1) {
                out.write(c);
            }
            outstream.flush();
            out.flush();
            outstream.close();
            out.close();

            sReader.close();

            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            conn.setAutoCommit(defaultCommit);
            conn.close();
        }
    }







    //将指定的文件转化成blob
    //参数说明
    //sqlMap:SqlMapClient
    //wdmb:WDSCwdmbDAO
    //tablename:需要写入的大字段所在的表
    //colName:表中的字段名
    //key:查询条件
    //返回值:
    public static void insertOracleBlob(WDSCwdmbDAO wdmb, String tablename, String colName, String key) throws
            SQLException, FileNotFoundException, IOException, Exception {

        SqlMapClient sqlMap = null;
        Connection conn = null;
        boolean defaultCommit = false;
        try {

            //sqlMap = wdmb.getSqlMap();
            //sqlMap.startTransaction();
            //conn = sqlMap.getCurrentConnection();
            conn = JDBCLocator.getInstance().getJDBCConnection();
            defaultCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);

            String sql = "INSERT INTO T_WDSC_WDMB ( " +
                         " MB_DM, MB_MC, MB_NR, MB_LB, MB_ENABLE, SCSJ, MB_CZY, MB_SWJG " +
                         " ) VALUES ( ?, ?, EMPTY_BLOB(), ?, ?, sysdate, ?, ?) ";

            PreparedStatement pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, wdmb.getMB_DM());
            pStmt.setString(2, wdmb.getMB_MC());
            pStmt.setString(3, wdmb.getMB_LB());
            pStmt.setString(4, wdmb.getMB_ENABLE());
            pStmt.setString(5, wdmb.getMB_CZY());
            pStmt.setString(6, wdmb.getMB_SWJG());
            pStmt.executeUpdate();

            pStmt.close();
            conn.commit();
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            conn.setAutoCommit(defaultCommit);
            conn.close();
        }
    }

}