yunkai-export/src/main/java/cn/com/qmth/export/SetRootId.java

package cn.com.qmth.export;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SetRootId {
	public static void main(String[] args) {
		System.out.println("开始处理");
		Connection connect = null;

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");

			String url = "jdbc:mysql://localhost:3306/"+Param.dbName+"?serverTimezone=GMT%2B8";

			String user = Param.dbUser;

			String password = Param.dbPass;
			connect = DriverManager.getConnection(url, user, password);
			List<Knowledge> ks=getKnowledge(connect);
			setRootId(ks);
			if (ks.size() <= 1000) {
				updateKnowledge(connect, ks);
				System.out.println("处理:1");
			} else {
				int size = ks.size();
				int len = 1000;
				int count = (size + len - 1) / len;

				for (int i = 0; i < count; i++) {
					List<Knowledge> subList = ks.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
					updateKnowledge(connect, subList);
					System.out.println("处理:"+(i+1)+"/"+count);
				}
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (connect != null) {
				try {
					connect.close();
				} catch (SQLException e) {
				}
			}
			System.out.println("结束处理");
		}
	}
	
	private static void updateKnowledge(Connection connect,List<Knowledge> ks) throws SQLException, IOException {
		PreparedStatement preState = null;
		try {
			String sql = "update wq_knowledge_system set root_id=? where id=?";
			preState = connect.prepareStatement(sql);

			for(Knowledge k:ks) {
				preState.setLong(1, k.getRootId());
				preState.setLong(2, k.getId());
				preState.addBatch();
			}
			preState.executeBatch();
			
		} finally {
			if (preState != null) {
				preState.close();
			}
		}
	}
	
	private static void setRootId(List<Knowledge> ks) {
		Map<Long,Knowledge> map=new HashMap<>();
		for(Knowledge k:ks) {
			map.put(k.getId(), k);
		}
		for(Knowledge k:ks) {
			getAndSetRootId(map, k);
		}
	}
	
	private static void getAndSetRootId(Map<Long,Knowledge> map,Knowledge k) {
		if(k.getParentId()==0) {
			k.setRootId(k.getId());
		}else {
			Knowledge pk=map.get(k.getParentId());
			getAndSetRootId(map, pk);
			k.setRootId(pk.getRootId());
		}
	}

	private static List<Knowledge> getKnowledge(Connection connect) throws SQLException, IOException {
		List<Knowledge> as = new ArrayList<>();
		PreparedStatement preState = null;
		ResultSet resultSet = null;
		try {
			String sql = "select id,parent_id,root_id from wq_knowledge_system order by parent_id,id";
			preState = connect.prepareStatement(sql);

			resultSet = preState.executeQuery();

			while (resultSet.next()) {
				Knowledge a = new Knowledge();
				a.setId(resultSet.getLong("id"));
				a.setParentId(resultSet.getLong("parent_id"));
				as.add(a);
			}
			return as;
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preState != null) {
				preState.close();
			}
		}
	}
}