数据库迁移之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的插入部分。肯定高手给与指教。

转载于:https://my.oschina.net/kingjin5/blog/351542