mongodb直接导入oracle,数据库迁移之Oracle to MongoDB

第一次接触非关系型数据库mongodb，经理让我研究将Oracle数据库中的数据迁移进mongodb。Oracle数据库中主要是地理信息字段，在处理时需要对特殊字段进行转换。简单了解之后，写了如下代码：

package convert;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.log4j.Logger;

import org.apache.log4j.PropertyConfigurator;

import com.mongodb.BasicDBObject;

import com.mongodb.DBCollection;

import db.DbUtil;

import convert.DBUtil;

public class MongoNew {

public static String table = "T_PATROLPOINTSTATUSMON";

private static final Logger logger = Logger.getLogger(MongoNew.class);

public static void main(String[] args) {

PropertyConfigurator.configure("config/log4j.properties ");

long beginTime = System.currentTimeMillis();

System.out.println(table + "。。。。。。。。。。导入开始。。。。。。。。。。。。");

converttableDate(table);

System.out.println(table + "。。。。。。。。。。导入结束。。。。。。。。。。。。");

long endTime = System.currentTimeMillis();

System.out.println(table + "。。。。。。。。。。导数据用时。。。。。。。。。。。。"

+ (endTime - beginTime) / 1000 + "秒");

}

/**

* @param table

*/

public static void converttableDate(String table) {

DbUtil db = new DbUtil();

Statement stat = null;

try {

Connection oracleConn = db.oracleGetConn();

DBCollection coll = db.getDBCollection("patrolstatus");

stat = oracleConn.createStatement();

int rowCount;

rowCount = db.getCount(stat, table);

if (stat != null) {

stat.close();

stat = null;

}

// 设置分页

int tableRowCount = rowCount;

int pageSize = 10000;

int maxPage = (tableRowCount % pageSize) == 0 ? tableRowCount

/ pageSize : (tableRowCount / pageSize) + 1;

String sqlTable1 = "select * from(select t.*, rownum rn from(select id from t_PatrolProperty )t  where rownum< ? ) where rn> ?";

String sqlTable2 = "select p.id,p.name,p.areaid,p.areaname,p.secareacode,p.specifity,p.category,p.categoryname,p.createmonth,sdo_util.to_wktgeometry(p.shape),p.patrolled,p.sde,t.name,t.value from t_PatrolProperty t,t_patrolpointstatusmon p where p.id = ? and p.id = t.ownerid(+)";

/*

* String insertSql = "insert into public." + table +

* " (id, name, regioncode, shape, areacode) values (?, ?, ?, ST_GeomFromText(?), ?)"

* ;

*/

/* PreparedStatement postsm = postConn.prepareStatement(insertSql); */

PreparedStatement oraclesm1 = oracleConn

.prepareStatement(sqlTable1);

PreparedStatement oraclesm2 = oracleConn

.prepareStatement(sqlTable2);

for (int currentPage = 1; currentPage < maxPage + 1; currentPage++) {

int beginRow = (currentPage - 1) * pageSize;

int endRow = currentPage * pageSize + 1;

oraclesm1.setInt(2, beginRow);

oraclesm1.setInt(1, endRow);

ResultSet rs1 = oraclesm1.executeQuery();

while (rs1.next()) {

oraclesm2.setInt(1, rs1.getInt(1));

ResultSet rs2 = oraclesm2.executeQuery();

ResultSetMetaData rsmd = rs2.getMetaData();

List datas = new ArrayList();

BasicDBObject bo = new BasicDBObject();

while(rs2.next()){

bo.put(rsmd.getColumnName(1), rs2.getInt(1));

bo.put(rsmd.getColumnName(2), rs2.getString(2));

bo.put(rsmd.getColumnName(3), rs2.getInt(3));

bo.put(rsmd.getColumnName(4), rs2.getString(4));

bo.put(rsmd.getColumnName(5), rs2.getString(5));

bo.put(rsmd.getColumnName(6), rs2.getInt(6));

bo.put(rsmd.getColumnName(7), rs2.getInt(7));

bo.put(rsmd.getColumnName(8), rs2.getString(8));

bo.put(rsmd.getColumnName(9), rs2.getInt(9));

bo.put("shape", rs2.getString(10));

bo.put(rsmd.getColumnName(11), rs2.getInt(11));

bo.put(rsmd.getColumnName(12), rs2.getInt(12));

//                        BasicDBObject tmpBo = new BasicDBObject();

//                        tmpBo.put(rs2.getString(13), rs2.getString(14));

bo.put(rs2.getString(13), rs2.getString(14));

datas.add(bo);

}

coll.insert(datas);

}

/*  List datas = new ArrayList();

BasicDBObject bo = new BasicDBObject();

while (rs.next()) {

bo.put("id", rs.getLong(1));

bo.put("name", rs.getString(2));

// String geo = geo1+rs.getString(3)+" ] }";

BasicDBObject tmpBo = new BasicDBObject();

tmpBo.put("type", "Point");

tmpBo.put("coordinates", rs.getString(3).split(","));

bo.put("geo", tmpBo);

datas.add(bo);

}*/

//                coll.insert(datas);

}

if (oraclesm1 != null) {

oraclesm1.close();

oraclesm1 = null;

}

if (oraclesm2 != null) {

oraclesm2.close();

oraclesm2 = null;

}

if (oracleConn != null) {

oracleConn.close();

oracleConn = null;

}

} catch (SQLException e) {

logger.error("SQL错误", e);

} catch (ClassNotFoundException e) {

logger.error("驱动加载错误", e);

} catch (IOException e) {

logger.error("读取property错误", e);

}

}

}

个人觉得执行效率不高，尤其在mongodb的插入部分。肯定高手给与指教。