将Oracle中BLOB字段同步到SQLSERVER的IMAGE字段中

近日，公司項目要求将Oracle資料庫中的大字段BLOB資料同步到SQLSERVER中IMAGE大字段中。

網上找了些列子，再結合自己項目實際情況進行編寫，代碼如下：

String hql2 = "from Datp t where t.status='" + Constant.YWZT_0 + "'";
            List<Datp> list2 = this.getSession().createQuery(hql2).list(); // ORACLE 連接配接
            if (list2.size() > 0) {
                for (int i = 0; i < list2.size(); i++) {
                    Datp datp = list2.get(i);
                    String lsh = datp.getLsh();
                    conn = DbUtil.getConnection(DRIVERNAME, URL, USERNAME, PASSWORD);
                    conn.setAutoCommit(false);
                    PreparedStatement stmt = conn.prepareStatement("INSERT INTO DATP(LSH,TPSJ) VALUES (?,?)");
                    stmt.setString(1, lsh);
                    // 通過流水号讀取Oracle BLOB字段
                    Datp datp1 = (Datp) this.getSession().createQuery("from Datp t where t.lsh='" + lsh + "'").uniqueResult();
                    byte[] tpsj = datp1.getTpsj(); // 擷取Oracle BLOB字段
                    InputStream in = new ByteArrayInputStream(tpsj); // byte[]轉換為InputStream
                    stmt.setBinaryStream(2, in, in.available()); // 存入圖檔，SQLSERVER連接配接
                    stmt.execute();
                    conn.commit();
                    stmt.close();
                    conn.close();
                    // 更新記錄同步狀态
                    String sql = "UPDATE datp t SET t.status = '" + Constant.YWZT_1 + "' WHERE t.id = '" + datp.getId() + "'";
                    this.getSession().createSQLQuery(sql).executeUpdate();
                }
            }
           

    檢視插入SQLSERVER中的大字段是否成功，代碼如下：

try {
            String DRIVERNAME = Factory.getSysPara().getProperty("snapshot_sqlserver_driver");
            String URL = Factory.getSysPara().getProperty("snapshot_sqlserver_url");
            String USERNAME = Factory.getSysPara().getProperty("snapshot_sqlserver_user");
            String PASSWORD = Factory.getSysPara().getProperty("snapshot_sqlserver_password");
            conn = DbUtil.getConnection(DRIVERNAME, URL, USERNAME, PASSWORD);
            PreparedStatement ps = conn.prepareStatement("select * from datp where lsh = ?");
            ps.setString(1, "51010074000001");
            ResultSet rs = ps.executeQuery();
            rs.next();
            InputStream in = rs.getBinaryStream("tpsj");
            System.out.println(in.available());
            FileOutputStream out = new FileOutputStream("c:/test.png");
            byte[] b = new byte[1024];
            int len = 0;
            while ((len = in.read(b)) != -1) {
                out.write(b, 0, len);
                out.flush();
            }
            out.close();
            in.close();
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }