guangkai-export/src/main/java/cn/com/qmth/export/SetLevel.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;

import cn.com.qmth.export.bean.Knowledge;
import cn.com.qmth.export.bean.Param;

public class SetLevel {

    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";

            connect = DriverManager.getConnection(url, Param.dbUser, Param.dbPass);
            List<Knowledge> ks = getKnowledge(connect);
            setLevel(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 mdl_question_categories set level=? where id=?";
            preState = connect.prepareStatement(sql);

            for (Knowledge k : ks) {
                preState.setInt(1, k.getLevel());
                preState.setLong(2, k.getId());
                preState.addBatch();
            }
            preState.executeBatch();

        } finally {
            if (preState != null) {
                preState.close();
            }
        }
    }

    private static void setLevel(List<Knowledge> ks) {
        Map<Long, Knowledge> map = new HashMap<>();
        for (Knowledge k : ks) {
            map.put(k.getId(), k);
        }
        for (Knowledge k : ks) {
            getAndSetLevel(map, k);
        }
    }

    private static void getAndSetLevel(Map<Long, Knowledge> map, Knowledge k) {
        if (k.getParentId() == 0) {
            k.setLevel(0);
        } else {
            Knowledge pk = map.get(k.getParentId());
            if (pk.getLevel() == null) {
                getAndSetLevel(map, pk);
            }
            k.setLevel(pk.getLevel() + 1);
        }
    }

    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 from mdl_question_categories";
            preState = connect.prepareStatement(sql);

            resultSet = preState.executeQuery();

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