HIS读接口创表工具类

zhaowenxuan

2024-05-24 7e5d9856c8f1b6af4fd55706bb974ac469417a74

HIS读接口创表工具类

 ltkj-common/src/main/java/com/ltkj/common/utils/DictionaryUtil.java

New file
@@ -0,0 +1,255 @@
package com.ltkj.common.utils;

import cn.hutool.core.util.RandomUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Company: 西安路泰科技有限公司
 * @Author: zhaowenxuan
 * @Date: 2024/5/21 16:41
 */
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 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";

    static {
        methods.add("JGDM");
//        methods.add("ZDZYZ");
    }

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

    private void exec() {
        JSONObject entries = execRequest();
        if (entries != null) {
            JSONObject response = entries.getJSONObject("Response");
            // 0 成功 -1失败
            if ("0".equals(response.getStr("ResultCode"))) {
                JSONArray jsonArray = response.getJSONArray("ResultData");
                JSONObject resultDataIndex1 = jsonArray.getJSONObject(0);
                String method = entries.getStr("method");
                String tabName = "ltkj_" + method;
                Connection connection = getConnection();
                try {
                    if (!tabIsExists(connection, tabName)) {
                        // 创建表
                        creatTable(resultDataIndex1, tabName, connection);
                    }
                    // 对比字段 并插入数据
                    operationTable(jsonArray, tabName, connection);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * 执行http请求
     *
     * @return
     */
    private JSONObject execRequest() {
        URL url = null;
        HttpURLConnection connection = null;
        try {
            url = new URL(BASE_API_URL);
            connection = (HttpURLConnection) url.openConnection();
            connection.setRequestMethod("POST");
            connection.setDoOutput(true);
            connection.setRequestProperty("Accept", "application/json");
            connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
            OutputStream stream = connection.getOutputStream();
            int index = 0;
            if (methods.size() > 1) {
                index = RandomUtil.randomInt(0, methods.size() - 1);
            }
            String method = methods.get(index);
            String param = "method=" + method;
            stream.write(param.getBytes(StandardCharsets.UTF_8));
            BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
            StringBuilder builder = new StringBuilder();
            String str;
            while ((str = reader.readLine()) != null) {
                builder.append(str);
            }
            log.info("执行请求响应：{}", builder.toString());
            JSONObject entries = JSONUtil.parseObj(builder.toString());
            entries.set("method", method);
            return entries;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return null;
    }

    private Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 表是否存在
     *
     * @param connection
     * @param tableName
     * @return
     * @throws SQLException
     */
    private Boolean tabIsExists(Connection connection, String tableName) throws SQLException {
        String tabSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
        PreparedStatement statement = connection.prepareStatement(tabSql);
        statement.setString(1, "test_urlreq");
        statement.setString(2, tableName);
        log.info("判断表是否存在 sql-> {}", statement.toString());
        ResultSet resultSet = statement.executeQuery();
        boolean next = resultSet.next();
        statement.close();
        return next;
    }

    /**
     * 操作表
     *
     * @param jsonArray
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONArray jsonArray, String tabName, Connection connection) throws SQLException {
        List<String> columns = getColumns(tabName, connection);
        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());
            }
            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);
            }
        }
    }

    /**
     * 插入数据
     *
     * @param tabName
     * @param connection
     * @param jsonObject
     * @throws SQLException
     */
    private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
        // 插入数据前 先查询数据是否存在
        StringBuilder selectSqlBuilder = new StringBuilder();
        StringBuilder insertSqlBuilder = new StringBuilder();
        StringBuilder valueBuilder = new StringBuilder();
        insertSqlBuilder.append("insert into ").append(tabName).append(" (");
        selectSqlBuilder.append("select count(1) as count from ").append(tabName).append(" where ");
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            insertSqlBuilder.append(entry.getKey()).append(", ");
            valueBuilder.append("'").append(entry.getValue()).append("', ");
            selectSqlBuilder.append(entry.getKey()).append(" = '").append(entry.getValue()).append("' and ");
        }
        insertSqlBuilder.delete(insertSqlBuilder.length() - 2, insertSqlBuilder.length());
        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());
        Statement statement = connection.prepareStatement(selectSqlBuilder.toString());
        ResultSet resultSet = statement.executeQuery(selectSqlBuilder.toString());
        resultSet.next();
        String string = resultSet.getString("count");
        statement.close();
        // 如果不为0 则这条数据存在 不进行插入
        if (!"0".equals(string)) {
            log.info("数据存在不需要插入 {}", jsonObject);
            return;
        }
        // 插入数据
        statement = connection.createStatement();
        log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
        statement.execute(insertSqlBuilder.toString());
        statement.close();
    }

    /**
     * 获取表的列
     *
     * @param tabName
     * @param connection
     * @return
     * @throws SQLException
     */
    private List<String> getColumns(String tabName, Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(null, null, tabName, null);
        ArrayList<String> tabColumns = new ArrayList<>();
        while (columns.next()) {
            String columnName = columns.getString("column_name");
            tabColumns.add(columnName);
        }
        return tabColumns;
    }

    /**
     * 创建表
     *
     * @param resultDataIndex1 返回数据中的第一个参数
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void creatTable(JSONObject resultDataIndex1, String tabName, Connection connection) throws SQLException {
        StringBuilder sql = new StringBuilder("CREATE TABLE " + tabName + " (");
        for (Map.Entry<String, Object> entry : resultDataIndex1.entrySet()) {
            sql.append(entry.getKey()).append(" text null,");
        }
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
        Statement statement = connection.createStatement();
        statement.execute(sql.toString());
    }
}