2009年7月2日 天气晴 周四
解决向ORACLE数据库表中大字段(BLOB类型)插入字符数据的方法:(完整代码下载在附件 )
// 对应文章导入
public static void MysqlarchiveToOracleAracle() {
// log.debug("文章导入中...");
String sql = "select * from archive where 1=1";
try {
mysqlStmt = mysqlConn.prepareStatement(sql);
mysqlRs = mysqlStmt.executeQuery();
// List<Archive> archiveList = new ArrayList<Archive>();
Set<String> archiveBodyList = new HashSet<String>();
oracleConn.setAutoCommit(false);
while (mysqlRs.next()) {
archive = new Archive();
// 文章导入
String osql = "insert into archive values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
oracleStmt = oracleConn.prepareStatement(osql);
oracleStmt.setInt(1, mysqlRs.getInt("id"));
oracleStmt.setInt(2, mysqlRs.getInt("typeid"));
oracleStmt.setString(3, mysqlRs.getString("typeid2"));
oracleStmt.setString(4, mysqlRs.getString("title"));
oracleStmt.setString(5, mysqlRs.getString("shorttitle"));
oracleStmt.setString(6, mysqlRs.getString("color"));
oracleStmt.setInt(7, mysqlRs.getInt("sortrank"));
oracleStmt.setInt(8, mysqlRs.getInt("commend"));
oracleStmt.setInt(9, mysqlRs.getInt("ishtml"));
oracleStmt.setString(10, mysqlRs.getString("html"));
oracleStmt.setString(11, mysqlRs.getString("litpic"));
oracleStmt.setString(12, mysqlRs.getString("summary"));
oracleStmt.setString(13, mysqlRs.getString("keyword"));
oracleStmt.setString(14, mysqlRs.getString("template"));
oracleStmt.setString(15, mysqlRs.getString("sender"));
oracleStmt.setTimestamp(16, mysqlRs.getTimestamp("sendtime"));
oracleStmt.setString(17, mysqlRs.getString("source"));
oracleStmt.setString(18, mysqlRs.getString("author"));
oracleStmt.setTimestamp(19, mysqlRs.getTimestamp("publictime"));
oracleStmt.setTimestamp(20, mysqlRs.getTimestamp("visittime"));
oracleStmt.setInt(21, mysqlRs.getInt("click"));
// 处理大字段
// archiveBodyList.add(mysqlRs.getString("body"));
oracleStmt.setBlob(22, oracle.sql.BLOB.getEmptyBLOB());
// java.sql.Blob body = g.sql.SQLHelper.createBlob(mysqlRs
// .getString("body").getBytes("UTF-8"));
// oracleStmt.setBlob(22, body);
oracleStmt.setInt(23, mysqlRs.getInt("ischecked"));
oracleStmt.executeQuery();
// 更新大字段数据
PreparedStatement pstmt = null;
ResultSet rs;
String query = "select body from archive where id=? for update";
pstmt = oracleConn.prepareStatement(query);
pstmt.setInt(1, mysqlRs.getInt("id"));
rs = pstmt.executeQuery();
oracle.sql.BLOB blobtt = null;
if (rs.next()) {
blobtt = (oracle.sql.BLOB) rs.getBlob(1);
}
OutputStream out = blobtt.setBinaryStream(1); // 建立输出流
java.sql.Blob body = g.sql.SQLHelper.createBlob(mysqlRs
.getString("body").getBytes("UTF-8"));
out.write(body
.getBytes(1, Integer.parseInt(body.length() + "")));
out.close();
rs.close();
pstmt.close();
// 重新更新大字段数据
pstmt = null;
pstmt = oracleConn
.prepareStatement(" update archive set body=? where id=? ");
pstmt.setBlob(1, blobtt);
pstmt.setInt(2, mysqlRs.getInt("id"));
pstmt.executeUpdate();
pstmt.close();
log.debug("插入完成ID : " + mysqlRs.getInt("id"));
}
oracleConn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
replace(mysqlRs, mysqlStmt, mysqlConn);
replace(oracleRs, oracleStmt, oracleConn);
}
}