ORACLE数据库中插入大字段数据的解决方法

myhongkongzhen

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 blocke.printStackTrace();} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (NumberFormatException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {replace(mysqlRs, mysqlStmt, mysqlConn);replace(oracleRs, oracleStmt, oracleConn);}}