将数据从MongoDB数据库洗到MySQL数据库

最近有个需求，要把MongoDB数据库里的数据，按照业务需求洗到MySQL中

需要的pom依赖

<dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongodb-driver</artifactId>
            <version>3.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>bson</artifactId>
            <version>3.8.2</version>
        </dependency>

        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongodb-driver-core</artifactId>
            <version>3.8.2</version>
        </dependency>

        <!-- Google Gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>

实体类

public class BookNode {
    private Integer id;
    private String name;
    private String path_ids;
    private String textbook_id;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPath_ids() {
        return path_ids;
    }

    public void setPath_ids(String path_ids) {
        this.path_ids = path_ids;
    }

    public String getTextbook_id() {
        return textbook_id;
    }

    public void setTextbook_id(String textbook_id) {
        this.textbook_id = textbook_id;
    }

    @Override
    public String toString() {
        return "BookNode{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", path_ids='" + path_ids + '\'' +
                ", textbook_id='" + textbook_id + '\'' +
                '}';
    }
}

对MySQL的操作采用JDBC，因为对mysql的需求只是插入数据，jdbc简单方便

import java.sql.*;

public class JDBC {
    public Connection conn;

    public Connection connXbzyk;

    public JDBC() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取数据库连接
        this.conn = DriverManager.getConnection( "jdbc:mysql://192.168.9.199:4706/test_db1?characterEncoding=utf-8", "root", "123456");
        this.connXbzyk = DriverManager.getConnection( "jdbc:mysql://192.168.9.199:4706/test_db2?characterEncoding=utf-8", "root", "123456");
    }

    //返回主键id
    public int getId(String sql) throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        int id = 0;
        while (resultSet.next()){
            id = resultSet.getInt(1);
        }
        statement.close();
        return id;
    }

    public int getXbzykId(String sql) throws SQLException {
        Statement statement = connXbzyk.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        int id = 0;
        while (resultSet.next()){
            id = resultSet.getInt(1);
        }
        statement.close();
        return id;
    }

    //返回主键id
    public int insert(String sql) throws SQLException {
        Statement statement = conn.createStatement();
        statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet resultSet = statement.getGeneratedKeys();
        int id = 0;
        while (resultSet.next()){
            id = resultSet.getInt(1);
        }
        statement.close();
        return id;
    }

    public BookNode getBookNode(String sql) throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        BookNode bookNode = new BookNode();
        while (resultSet.next()){
            bookNode.setId(resultSet.getInt(1));
            bookNode.setName(resultSet.getString("name"));
            bookNode.setPath_ids(resultSet.getString("path_ids"));
            bookNode.setTextbook_id(resultSet.getString("textbook_id"));
        }
        statement.close();
        return bookNode;
    }

    public BookNode getKnowledgePoint(String sql) throws SQLException {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        BookNode bookNode = new BookNode();
        while (resultSet.next()){
            bookNode.setId(resultSet.getInt(1));
            bookNode.setName(resultSet.getString("name"));
            bookNode.setPath_ids(resultSet.getString("path_ids"));
            bookNode.setTextbook_id(resultSet.getString("subject_id"));
        }
        statement.close();
        return bookNode;
    }

    public void close() throws SQLException {
        conn.close();
        connXbzyk.close();
    }
}

刷数据的Java脚本，标了点注释

import com.google.gson.Gson;
import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.MongoClientURI;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.Filters;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.bson.types.ObjectId;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class FlushMongoDB {
    //子母题的标志，true的时候，表示正在处理子母题
    public boolean flag = false;

    //母题的标志，true的时候，表示已经处理完了母题（已经添加了母题的数据到数据库中）
    public boolean parentFlag = false;

    public String parentContentAll = "";

    //子母题没被处理的计数
    public Integer parentCount = 0;

    //处理的总的题目量的计数
    public Integer countAll = 0;

    //表示子母题中，母题的id
    public Integer parentId = 0;

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        FlushMongoDB flushMongoDB = new FlushMongoDB();
        flushMongoDB.flush();
        System.out.println("countAll" + flushMongoDB.countAll);
        System.out.println("parentCount" + flushMongoDB.parentCount);
    }

    public void flush() throws SQLException, ClassNotFoundException {
        //账号:密码@数据库地址:端口
        MongoClientURI uri = new MongoClientURI("mongodb://admin:123456@192.168.9.199:27017/?authSource=question_bank");
        MongoClient mongoClient = new MongoClient(uri);
        //连接数据库，参数为数据库名称
        MongoDatabase mDatabase = mongoClient.getDatabase("mongo_test");
        System.out.println("MongoDatabase info is : "+mDatabase.getName());

        JDBC jdbc = new JDBC();

        //获取collection，参数为collection的名称
        MongoCollection collection = mDatabase.getCollection("collection_v1");
        MongoCollection collectionV2 = mDatabase.getCollection("collection_v2");
        //设置以id正序排序
        BasicDBObject sortObject = new BasicDBObject();
        sortObject.append("_id", 1);
        //ossProcess存在的，createby为0的，ParentSID为空的
        FindIterable findIterable = collection.find().
                filter(Filters.and(Filters.exists("ossProcess"), Filters.eq("CreatedBy",0), Filters.eq("ParentSID", null))).sort(sortObject).limit(10);

        process(findIterable, jdbc, collection, collectionV2);
    }

    public void process(FindIterable findIterable, JDBC jdbc, MongoCollection collection, MongoCollection collectionV2) throws SQLException {
        BasicDBObject sortObject = new BasicDBObject();
        sortObject.append("_id", 1);
        String objectId = null;
        while(null != findIterable){
            MongoCursor iterator = findIterable.iterator();
            while(iterator.hasNext()){
                Document next = (Document) iterator.next();
                objectId = next.getObjectId("_id").toString();
                System.out.println(objectId);

                String createdBy = next.get("CreatedBy").toString();
                //如果CreatedBy字段不为0，说明处理过了这道题（预防子母题的情况）
                if(flag && !"0".equals(createdBy)){
                    //处理字母题的时候，在查到createdBy不为0的时候，把flag置为false。因为此时子母题全部处理完成.
                    flag = false;
                    break;
                }

                Document process = (Document) next.get("args1");
                Document from = (Document) next.get("args2");
                Document jyeoo = (Document) from.get("args3");
                Document question = (Document) jyeoo.get("args4");

                String parentSID = question.getString("ParentSID");

                //flag为false才进这里面，表示当前没有处理子母题
                if(!flag && null != parentSID && parentSID.length() != 0){
                    //处理子母题
                    FindIterable parentQuestion = collection.find().filter(Filters.and(
                            Filters.eq("ParentSID", parentSID),
                            Filters.exists("args1"))).sort(sortObject);
                    flag = true;
                    MongoCursor parentIterator = parentQuestion.iterator();
                    while (parentIterator.hasNext()){
                        Document parentNext = (Document) parentIterator.next();
                        Document parentfrom = (Document) parentNext.get("args1");
                        String parentContent = parentfrom.getString("ParentContent");
                        if(null != parentContent && parentContent.length() > 1){
                            parentContentAll = parentContent;
                            break;
                        }
                    }
                    //如果parentContentAll的长度为0，说明没有母题的内容，直接不解析这道题
                    if(parentContentAll.length() == 0){
                        parentCount++;
                        break;
                    }
                    process(parentQuestion, jdbc, collection, collectionV2);
                    flag = false;
                    parentFlag =false;
                    parentContentAll = "";
                    parentId = 0;
                    break;//处理完了子母题就退出此次循环，子母题均在上面的递归中处理完
                }
                Document questionAttributes = (Document) jyeoo.get("args5");
                //todo 如果QuestionAttributes为空，则去查询new_total_v2数据库
                if(null == questionAttributes){
                    FindIterable findIterableV2 = collectionV2.find().
                            filter((Filters.eq("_id",new ObjectId(objectId))));
                    MongoCursor iteratorV2 = findIterableV2.iterator();
                    while(iteratorV2.hasNext()) {
                        Document nextV2 = (Document) iteratorV2.next();
                        Document fromV2 = (Document) nextV2.get("args2");
                        Document jyeooV2 = (Document) fromV2.get("args3");
                        questionAttributes = (Document) jyeooV2.get("args5");
                    }
                }
                int subjectId = getSubjectId(questionAttributes, jdbc);
                int difficultyId = getDifficultyId(questionAttributes, jdbc);
                int questionTypeId = getQuestionTypeId(questionAttributes, jdbc);
                String sid = question.getString("SID");
                String fromYear = next.get("FromYear").toString();

                //父题干需要单独增添一条数据
                //parentFlag为false的时候，才需要进来添加一条母题数据
                if(!parentFlag && parentContentAll.length() > 0){
                    String insertSql = String.format("insert into question (args1, args2, args3, args4, args5, args6, args7, args8) values (1, %s, %s, %s, %s, 2, '%s', %s)",
                            difficultyId, questionTypeId, 0, subjectId, parentSID, fromYear);
                    parentId = jdbc.insert(insertSql);//questionId，增加完一条question数据后返回的主键id
                    String insertContent = String.format("insert into question_content (args1, args2) values ('%s', %s)",
                            parentContentAll, parentId);
                    jdbc.insert(insertContent);//往question_content中插入一条数据
                    parentFlag = true;
                }
                String insertSql = String.format("insert into question (args1, args2, args3, args4, args5, args6, args7, args8) values (1, %s, %s, %s, %s, 2, '%s', %s)",
                        difficultyId, questionTypeId, parentId, subjectId, sid, fromYear);
                int insertId = jdbc.insert(insertSql);//questionId，增加完一条question数据后返回的主键id
                System.out.println("insertId=" + insertId);
                insertContent(process, questionAttributes, jdbc, insertId, parentContentAll);

                insertChapters(questionAttributes, jdbc, insertId);

                insertKnowledges(questionAttributes, jdbc, insertId);

                updateCreateBy(collection, objectId);
            }

            if(!flag){
                findIterable = collection.find().filter(Filters.and(Filters.exists("args1"), Filters.gt("_id",new ObjectId(objectId)),Filters.eq("ParentSID", null))).sort(sortObject).limit(10);
            }
        }
    }

    private void updateCreateBy(MongoCollection collection, String objectId){
        //根据objectid，把CreatedBy改为1，表示这条数据被修改过
        Bson filter = Filters.eq("_id", new ObjectId(objectId));
        Bson update =  new Document("$set", new Document().append("CreatedBy", 1));
        collection.updateOne(filter, update);
        countAll++;
    }

    public void insertContent(Document process, Document questionAttributes, JDBC jdbc, Integer insertId, String parentContent) throws SQLException {
        String analyse = process.getString("Analyse");
        List<String> answers = (List<String>) process.get("Answers");
        Document questionType = (Document) questionAttributes.get("QuestionType");
        String questionTypeName = questionType.getString("Name");
        if("选择题".equals(questionTypeName) && null != answers && answers.size() == 1){
            String s = answers.get(0);
            if("0".equals(s)){
                answers.set(0, "A");
            }else if("1".equals(s)){
                answers.set(0, "B");
            }else if("2".equals(s)){
                answers.set(0, "C");
            }else if("3".equals(s)){
                answers.set(0, "D");
            }else if("4".equals(s)){
                answers.set(0, "E");
            }else if("5".equals(s)){
                answers.set(0, "F");
            }
        }
        Gson gson = new Gson();
        String answer = null;
        if(null != answers && answers.size() > 1){
            Map<Integer, String> map = new HashMap<Integer, String>();
            int num = 0;
            for(String ss : answers){
                num++;
                map.put(num, ss);
            }
            answer = gson.toJson(map);
        }else {
            if(null != answers && answers.size() == 1){
                answer = answers.get(0);
            }
        }
        String discuss = process.getString("Discuss");
        List<String> options = (List<String>) process.get("Options");
        String option = null;
        if(null != options && options.size() > 0){
            Map<Character, String> map = new HashMap<Character, String>();
            int num = 65;
            for(String s : options){
                map.put((char) num, s);
                num++;
            }
            option = gson.toJson(map);
        }
        String content = process.getString("Content");
        int indexOf = -1;
        if(null != parentContentAll){
            indexOf = content.indexOf(parentContentAll);
        }
        String substring;
        if(indexOf > -1){
            substring = content.substring(indexOf + parentContentAll.length());
        }else{
            substring = content;
        }
        String method = process.getString("Method");
        if(null != analyse){
            analyse = analyse.replace("'", "\\'");
        }
        if(null != answer){
            answer = answer.replace("'", "\\'");
        }
        if(null != discuss){
            discuss = discuss.replace("'", "\\'");
        }
        if(null != option){
            option = option.replace("'", "\\'");
        }
        if(null != substring){
            substring = substring.replace("'", "\\'");
        }
        if(null != method){
            method = method.replace("'", "\\'");
        }
        String insertContent = String.format("insert into question_content (args1, args2, args3, args4, args5, args6, args7) values ('%s', '%s', '%s', '%s', '%s', %s, '%s')",
                analyse, answer, discuss, option, substring, insertId, method);
        System.out.println(insertContent);
        jdbc.insert(insertContent);//往question_content中插入一条数据
    }

    public void insertChapters(Document questionAttributes, JDBC jdbc, Integer insertId) throws SQLException {
        if(questionAttributes.containsKey("Chapters")){
            List<Document> chapters = (List<Document>) questionAttributes.get("Chapters");
            for(Document document : chapters){
                String code = document.getString("Code");
                String sql = String.format("SELECT args1,args2,args3,args4 FROM test_db where code = '%s'", code);
                BookNode bookNode = jdbc.getBookNode(sql);
                String insertSQL = String.format("insert into test_db (args1, args2, args3, args4, args5) values (%s, %s, '%s', '%s', %s)",
                        insertId, bookNode.getId(), bookNode.getName(), bookNode.getPath_ids(), bookNode.getTextbook_id());;
                jdbc.insert(insertSQL);
            }
        }
    }

    public void insertKnowledges(Document questionAttributes, JDBC jdbc, Integer insertId) throws SQLException {
        if(questionAttributes.containsKey("Knowledges")){
            List<Document> knowledges = (List<Document>) questionAttributes.get("Knowledges");
            for(Document document : knowledges){
                String code = document.getString("Code");
                String sql = String.format("SELECT args1,args2,args3,args4 FROM `test_db` where code = '%s'", code);
                BookNode bookNode = jdbc.getKnowledgePoint(sql);
                String insertSQL = String.format("insert into test_db (args1, args2, args3, args4, args5) values (%s, %s, '%s', '%s', %s)",
                        insertId, bookNode.getId(), bookNode.getName(), bookNode.getPath_ids(), bookNode.getTextbook_id());;
                jdbc.insert(insertSQL);
            }
        }
    }

    public int getSubjectId(Document questionAttributes, JDBC jdbc) throws SQLException {
        Document subject = (Document) questionAttributes.get("Subject");
        String subjectCodePath = subject.getString("CodePath");
        String sql = String.format("select id from test_db where code = '%s'", subjectCodePath);
        int subjectId = jdbc.getId(sql);
        return subjectId;
    }

    public int getDifficultyId(Document questionAttributes, JDBC jdbc) throws SQLException {
        Document difficulty = (Document) questionAttributes.get("Difficulty");
        String difficultyCode = difficulty.getString("Code");
        String sql1 = String.format("select id from test_db where code = '%s'", difficultyCode);
        int difficultyId = jdbc.getId(sql1);
        return difficultyId;
    }

    public int getQuestionTypeId(Document questionAttributes, JDBC jdbc) throws SQLException {
        Document questionType = (Document) questionAttributes.get("QuestionType");
        String questionTypeCode = questionType.getString("CodePath");
        String sql2 = String.format("select id from test_db where code_path = '%s'", questionTypeCode);
        int questionTypeId = jdbc.getId(sql2);
        return questionTypeId;
    }
}