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

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);
		}
	}