分页请求实现

zhaowenxuan

2024-05-31 51feb82561e07846b32e8e480cf57091dca87247

分页请求实现

 src/main/java/com/example/utils/DictionaryUtil.java

@@ -26,80 +26,225 @@
 */
public class DictionaryUtil {
    private static final Logger log = LoggerFactory.getLogger(DictionaryUtil.class);
    private static final String BASE_API_URL = "https://mock.mengxuegu.com/mock/664d3dbce45d2156fa209c80/example/api/His/HisRequest";
    private static String BASE_API_URL = "https://mock.mengxuegu.com/mock/664d3dbce45d2156fa209c80/example/api/His/HisRequest";
    // 请求代码总集合
    private static List<String> methods = new ArrayList<>();
    private static final String url = "jdbc:mysql://localhost:3306/test_urlreq";
    private static final String user = "root";
    private static final String password = "Root_ltkj123";
    // 需要分页的代码集合
    private static List<String> limits = new ArrayList<>();

    /**
     * 暂时不做
     * 1.18 计费明细项目 MXJFXMZD
     * 1.90 门诊明细计费及组合计费项目 MZJFZHMXXM
     *
     */

// 分页每次1000条
    static {
        methods.add("KSFLZD");
        methods.add("BQZD");
        methods.add("ZKKSZD");
        methods.add("KSZD");
        methods.add("YWCKZD");
        methods.add("GHZLZD");
        methods.add("YHZD");
        methods.add("XYZDZD");
        methods.add("ZYZDZD");
        methods.add("SSZD");
        methods.add("ZLXTXZD");
        methods.add("ZYZZZFZD");
        methods.add("YJJFXMZD");
        methods.add("EJJFXMZD");
        methods.add("MXJFXMZD");
        methods.add("ZHFLXMZD");
        methods.add("JFZHXMZD");
        methods.add("JFZHGLMXJFXMZD");
        methods.add("CFLXZD");
        methods.add("YPZLZD");
        methods.add("YPJXZD");
        methods.add("YPGXZD");
        methods.add("YPLXZD");
        methods.add("JLDWZD");
        methods.add("YFZD");
        methods.add("YPZD");
        methods.add("JYKSZD");
        methods.add("JYYSZD");
        methods.add("JYFZZD");
        methods.add("JYFLZD");
        methods.add("JYFFZD");
        methods.add("JYBBZD");
        methods.add("JYXMZD");
        methods.add("JYZBZD");
        methods.add("JYXMZBZD");
        methods.add("JFXMGLJYXMZD");
        methods.add("JFZHGLJYXMZD");
        methods.add("JCSBZD");
        methods.add("JCBWFLZD");
        methods.add("JCBWZD");
        methods.add("JCBWMSZD");
        methods.add("JFXMGLJCBWZD");
        methods.add("JFZHXMGLJCBWZD");
        methods.add("JGDM");
        methods.add("ZDZYZ");
//        methods.add("YQZD");
//        methods.add("KSFLZD");
//        methods.add("BQZD");
//        methods.add("ZKKSZD");
//        methods.add("KSZD");
//        methods.add("YWCKZD");
//        methods.add("GHZLZD");
//        methods.add("YHZD");
//        methods.add("XYZDZD");
//        methods.add("ZYZDZD");
//        methods.add("SSZD");
//        methods.add("ZLXTXZD");
//        methods.add("ZYZZZFZD");
//        methods.add("YJJFXMZD");
//        methods.add("EJJFXMZD");
//        methods.add("ZHFLXMZD");
//        methods.add("JFZHXMZD");
//        methods.add("JFZHGLMXJFXMZD");
//        methods.add("CFLXZD");
//        methods.add("YPZLZD");
//        methods.add("YPJXZD");
//        methods.add("YPGXZD");
//        methods.add("YPLXZD");
//        methods.add("JLDWZD");
//        methods.add("YFZD");
//        methods.add("YPZD");
//        methods.add("JYKSZD");
//        methods.add("JYYSZD");
//        methods.add("JYFZZD");
//        methods.add("JYFLZD");
//        methods.add("JYFFZD");
//        methods.add("JYBBZD");
//        methods.add("JYXMZD");
//        methods.add("JYZBZD");
//        methods.add("JYXMZBZD");
//        methods.add("JFXMGLJYXMZD");
//        methods.add("JFZHGLJYXMZD");
//        methods.add("JCSBZD");
//        methods.add("JCBWFLZD");
//        methods.add("JCBWZD");
//        methods.add("JCBWMSZD");
//        methods.add("JFXMGLJCBWZD");
//        methods.add("JFZHXMGLJCBWZD");
//
//        methods.add("TJDWXZZD");
//        methods.add("TJRYLBZD");
//        methods.add("TJLBZD");
//        methods.add("TJFQZD");
//        methods.add("TJKSZD");
//        methods.add("TJYSZD");
//        methods.add("TJXMZD");
//        methods.add("TJZHXMZD");
//        methods.add("TJZHGLMXZD");
//        methods.add("TJTCZD");
//        methods.add("TJTCGLTJZHZD");
//
//        methods.add("HTDWZD");
//        methods.add("ZFLXZD");
//        methods.add("MZJCJYSQD");
//        methods.add("MZJCJYSQXMFL");
//        methods.add("MZJCJYSQDDYXM");
//        methods.add("BRFB");

        methods.add("TJDWXZZD");
        methods.add("TJRYLBZD");
        methods.add("TJLBZD");
        methods.add("TJFQZD");
        methods.add("TJKSZD");
        methods.add("TJYSZD");
        methods.add("TJXMZD");
        methods.add("TJZHXMZD");
        methods.add("TJZHGLMXZD");
        methods.add("TJTCZD");
        methods.add("TJTCGLTJZHZD");
        methods.add("HTDWZD");
        methods.add("ZFLXZD");
        methods.add("MZJCJYSQD");
        methods.add("MZJCJYSQXMFL");
        methods.add("MZJCJYSQDDYXM");
        methods.add("MZJFZHMXXM");
        methods.add("BRFB");
        limits.add("ZDZYZ");
        limits.add("KSZD");
        limits.add("YHZD");
        limits.add("XYZDZD");
        limits.add("ZYZDZD");
        limits.add("SSZD");
        limits.add("ZLXTXZD");
        limits.add("ZYZZZFZD");
        limits.add("JFZHGLMXJFXMZD");
        limits.add("YPZD");
        limits.add("JYKSZD");
        limits.add("JYYSZD");
        limits.add("JYXMZD");
        limits.add("JYZBZD");
        limits.add("JYXMZBZD");
        limits.add("JFXMGLJYXMZD");
        limits.add("JFZHGLJYXMZD");
        limits.add("JFXMGLJCBWZD");
        limits.add("TJKSZD");
        limits.add("TJYSZD");
        limits.add("TJXMZD");
        limits.add("TJZHXMZD");
        limits.add("TJZHGLMXZD");
        limits.add("TJTCZD");
        limits.add("TJTCGLTJZHZD");
    }

    public static void main(String[] args) {
        DictionaryUtil util = new DictionaryUtil();
        util.exec();
//        util.exec();
        util.exec1();
    }

    private void exec1() {
        int size = 1000;
        Connection connection = getConnection();
        methods.forEach(method -> {
            System.out.println("循坏的接口代码 ->" + method);
            String tabName = "ltkj_" + method;
//            String params = "method="+method;
            boolean isLimit = false;
            int page = 1;
            int maxPage = 1;
            JSONObject object = JSONUtil.createObj();
            object.putOpt("method", method);
            if (limits.contains(method)) {
                isLimit = true;
//                params+="&pagecount="+size+"&page="+page;
                object.putOpt("pagecount", size);
                object.putOpt("page", page);
            }
            JSONObject entries = execRequest(object.toString());
//            JSONObject entries = execRequest(params);
            assert entries != null;
            JSONObject response = entries.getJSONObject("Response");
            if ("0".equals(response.getStr("ResultCode"))) {
                if (isLimit) {
                    maxPage = LimitInsertData(connection, tabName, response);
                    log.info("计算页码为 ->{}", maxPage);
                    if (maxPage > 1) {
                        page += 1;
                        for (int i = page; i <= maxPage; i++) {
//                                params+="&pagecount="+size+"&page="+page;
                            object.clear();
                            object.putOpt("method", method);
                            object.putOpt("pagecount", size);
                            object.putOpt("page", page);
//                                entries = execRequest(params);
                            entries = execRequest(object.toString());
                            assert entries != null;
                            response = entries.getJSONObject("Response");
                            if ("0".equals(response.getStr("ResultCode"))) {
                                LimitInsertData(connection, tabName, response);
                            } else {
//                                    log.error("请求失败："+params);
                                log.error("请求失败：" + object.toString());
                            }
                            page++;
                        }
                    }
                } else {
                    JSONArray resultData = response.getJSONArray("ResultData");
                    JSONObject jsonObject = JSONUtil.toList(resultData, JSONObject.class).get(0);
                    editDataBase(connection, tabName, resultData, jsonObject);
                }
            } else {
//                    log.error("请求失败："+params);
                log.error("请求失败：" + object.toString());
            }
        });
    }

    /**
     * 针对于分页接口 创建表、插入数据
     *
     * @param connection
     * @param tabName
     * @param response
     * @return
     */
    private int LimitInsertData(Connection connection, String tabName, JSONObject response) {
        int maxPage;
        // 行数
        Integer rowNumber = response.getInt("RowNumber");
        // 总条数
        Integer totalCount = response.getInt("totalCount");
        JSONArray jsonArray = response.getJSONArray("ResultData");
        maxPage = (totalCount + 1000 - 1) / 1000;
        System.out.println("集合大小 ->" + jsonArray.size());
        jsonArray.forEach(obj -> {
            editDataBase(connection, tabName, jsonArray, (JSONObject) obj);
        });
        return maxPage;
    }

    /**
     * 操作数据库
     *
     * @param connection
     * @param tabName
     * @param jsonArray
     * @param obj
     */
    private void editDataBase(Connection connection, String tabName, JSONArray jsonArray, JSONObject obj) {
        try {
            if (!tabIsExists(connection, tabName)) {
                // 创建表
                creatTable(obj, tabName, connection);
            }
            // 对比字段 并插入数据
            operationTable(obj, tabName, connection);
        } catch (SQLException throwables) {
            log.error("数据库操作异常");
            log.error(throwables.getSQLState());
            log.error(throwables.getMessage());
        }
    }

    private void exec() {
@@ -119,12 +264,53 @@
                        creatTable(resultDataIndex1, tabName, connection);
                    }
                    // 对比字段 并插入数据
                    operationTable(jsonArray, tabName, connection);
                    operationTable((JSONObject) jsonArray.get(0), tabName, connection);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * 执行post请求
     *
     * @param params 请求参数 例如： param1=val1&param2=val2
     * @return 请求返回的json转换后的JSONObject对象
     */
    private JSONObject execRequest(String params) {
//        System.out.println(params);
        URL url = null;
        HttpURLConnection connection = null;
        try {
            BASE_API_URL = "http://localhost:5011/api/His/HisRequest";
            url = new URL(BASE_API_URL);
            connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("POST");
            connection.setRequestProperty("Accept", "application/json");
//            connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
            connection.setRequestProperty("Content-Type", "application/json;utf-8");
            connection.setDoOutput(true);
            OutputStream stream = connection.getOutputStream();
            stream.write(params.getBytes(StandardCharsets.UTF_8));
//            System.out.println(connection.getResponseCode());
            BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
            StringBuilder builder = new StringBuilder();
            String str;
            while ((str = reader.readLine()) != null) {
                builder.append(str);
            }
            log.info("请求参数 ->{}", params);
//            log.info("执行请求响应：{}", builder.toString());
            return JSONUtil.parseObj(builder.toString());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return null;
    }

    /**
@@ -192,7 +378,7 @@
        PreparedStatement statement = connection.prepareStatement(tabSql);
        statement.setString(1, "test_urlreq");
        statement.setString(2, tableName);
        log.info("判断表是否存在 sql-> {}", statement.toString());
//        log.info("判断表是否存在 sql-> {}", statement.toString());
        ResultSet resultSet = statement.executeQuery();
        boolean next = resultSet.next();
        statement.close();
@@ -202,34 +388,31 @@
    /**
     * 操作表
     *
     * @param jsonArray
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONArray jsonArray, String tabName, Connection connection) throws SQLException {
    private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
        List<String> columns = getColumns(tabName, connection);
        log.info("当前表字段为 ->{}", columns);
//        log.info("当前表字段为 ->{}", columns);
        ArrayList<String> responseColums = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
                responseColums.add(entry.getKey());
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            responseColums.add(entry.getKey());
        }
//            log.info("请求返回的字段为 ->{}", responseColums);
        responseColums.removeAll(columns);
        if (!responseColums.isEmpty()) {
            // 需要增加字段 并插入数据
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " text null";
//                    log.info("修改字段 ->{}",sql.toString());
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
                statement.close();
            }
            log.info("请求返回的字段为 ->{}", responseColums);
            responseColums.removeAll(columns);
            if (!responseColums.isEmpty()) {
                // 需要增加字段 并插入数据
                for (String colum : responseColums) {
                    String sql = "alter table " + tabName + " add column " + colum + " text null";
                    Statement statement = connection.createStatement();
                    statement.executeUpdate(sql);
                    statement.close();
                }
                insertData(tabName, connection, jsonObject);
            } else {
                insertData(tabName, connection, jsonObject);
            }
            insertData(tabName, connection, jsonObject);
        } else {
            insertData(tabName, connection, jsonObject);
        }
    }

@@ -242,6 +425,7 @@
     * @throws SQLException
     */
    private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
//        System.out.println("jsonObject = " + jsonObject);
        // 插入数据前 先查询数据是否存在
        StringBuilder selectSqlBuilder = new StringBuilder();
        StringBuilder insertSqlBuilder = new StringBuilder();
@@ -257,7 +441,7 @@
        valueBuilder.delete(valueBuilder.length() - 2, valueBuilder.length());
        selectSqlBuilder.delete(selectSqlBuilder.length() - 5, selectSqlBuilder.length());
        insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
        log.info("插入前查询 sql ->{}", selectSqlBuilder.toString());
//        log.info("插入前查询 sql ->{}", selectSqlBuilder.toString());
        Statement statement = connection.prepareStatement(selectSqlBuilder.toString());
        ResultSet resultSet = statement.executeQuery(selectSqlBuilder.toString());
        resultSet.next();
@@ -265,12 +449,12 @@
        statement.close();
        // 如果不为0 则这条数据存在 不进行插入
        if (!"0".equals(string)) {
            log.info("数据存在不需要插入 {}", jsonObject);
//            log.info("数据存在不需要插入 {}", jsonObject);
            return;
        }
        // 插入数据
        statement = connection.createStatement();
        log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
//        log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
        statement.execute(insertSqlBuilder.toString());
        statement.close();
    }
@@ -309,6 +493,7 @@
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
//        log.info("创建表格 -> {}",sql.toString());
        Statement statement = connection.createStatement();
        statement.execute(sql.toString());
    }