最近有个需求,要把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;
}
}