近日,公司项目要求将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();
}