新的同步工具雷

zhaowenxuan

2024-07-01 696cdbc19d6e0a84a12da0370b8d2abdcafac1ff

新的同步工具雷

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

New file
@@ -0,0 +1,470 @@
package com.example.utils;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.domain.HisSyncDict;
import com.example.service.HisSyncDictService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

/**
 * @Company: 西安路泰科技有限公司
 * @Author: zhaowenxuan
 * @Date: 2024/5/21 16:41
 */
@Slf4j
@Component
public class DictionaryUtilNew {
    @Autowired
    private HisSyncDictService hisSyncDictService;
    private static String CONFIG_PATH;
    private static String BASE_API_URL = "";
    private static final int SIZE = 1000;
    private static String USER = "";
    private static String PASSWORD = "";
    private static String URL = "";
    private static String NAME = "";
    @Value("${config.path}")
    public void setConfigPath(String configPath) {
        CONFIG_PATH = configPath;
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(CONFIG_PATH);
            Properties props = new Properties();
            props.load(inputStream);
            NAME = props.getProperty("name");
            URL = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + NAME + "" +
                    "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
            USER = props.getProperty("username");
            PASSWORD = props.getProperty("password");
            String apiUrl = props.getProperty("his_api_url");
            String apiPort = props.getProperty("his_api_port");
            BASE_API_URL = apiUrl+":"+apiPort+"/api/His/HisRequest";
        } catch (IOException e) {
            System.out.println("初始化数据库异常 ->"+e.getMessage());
        }
    }


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

// 分页每次1000条
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            System.out.println("静态代码块异常 ->"+e.getMessage());
        }
    }

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

    /**
     * 增加默认参数
     * @param jsonObject
     * @return
     */
    public JSONObject addInputVal(JSONObject jsonObject){
        JSONObject obj = JSONUtil.createObj();
        String method = jsonObject.getStr("method");
        if (method.equals("MZJFZHMXXM") || method.equals("MXJFXMZD")){
            obj.putOpt("mode","1");
        }
        obj.putOpt("jgbm","");
        obj.putOpt("bm","");
        obj.putOpt("flbm","");
        jsonObject.putOpt("input",obj);
        return jsonObject;
    }

    /**
     * 进行同步
     */
    public void exec1(){
        log.info("开始执行同步");
        Connection connection = getConnection();
        if (connection == null)
            return;
        List<HisSyncDict> hisSyncDicts = hisSyncDictService.list();
        for (HisSyncDict hisSyncDict : hisSyncDicts) {
            extracted(NAME, connection, hisSyncDict);
        }
        try {
            connection.close();
        } catch (SQLException ignored) { }
        log.info("执行同步完毕");
    }

    /**
     * 通过指定字典进行同步
     */
    public void execMethods(List<String > methods){
        log.info("开始执行同步");
        Connection connection = getConnection();
        if (connection == null)
            return;
        for (String method : methods) {
            LambdaQueryWrapper<HisSyncDict> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(HisSyncDict::getDictName,method);
            HisSyncDict one = hisSyncDictService.getOne(wrapper);
            if (one == null){
                log.error("{}字典在数据库中不存在,请检查数据库或请求参数是否填写错误",method);
                continue;
            }
            extracted(NAME,connection,one);
        }
        try {
            connection.close();
        } catch (SQLException ignored) { }
        log.info("执行同步完毕");
    }

    private void extracted(String name, Connection connection, HisSyncDict hisSyncDict) {
        String method = hisSyncDict.getDictName();
        log.info("开始请求代码 ->{}", method);
        String tabName = "ltkj_" + method.toLowerCase();
        boolean isLimit = false;
        int page = 1;
        int maxPage = 1;
        JSONObject object = JSONUtil.createObj();
        object.putOpt("method", method);
        object = addInputVal(object);
        if (hisSyncDict.getIsLimit() == 1) {
            isLimit = true;
            JSONObject input = object.getJSONObject("input");
            input.putOpt("pagecount", SIZE);
            input.putOpt("page", page);
        }
        log.info("请求接口 ->{}, 请求参数 ->{}", BASE_API_URL, object.toString());
        JSONObject entries = execRequest(object.toString());
//            log.info("请求返回 ->{}", entries.toString());
        JSONObject response = entries.getJSONObject("Response");
        if ("0".equals(response.getStr("ResultCode"))) {
            try {
                dropTable(tabName, connection);
            } catch (SQLException e) {
                log.error("删除表异常 ->{}", e.getMessage());
            }
            if (isLimit) {
                maxPage = LimitInsertData(connection, tabName, response, name, page);
                log.info("计算页码为 ->{}", maxPage);
                if (maxPage > 1) {
                    for (page = 2; page <= maxPage; page++) {
                        object.clear();
                        object.putOpt("method", method);
                        object = addInputVal(object);
                        JSONObject input = object.getJSONObject("input");
                        input.putOpt("pagecount", SIZE);
                        input.putOpt("page", page);
                        log.info("请求接口 ->{}, 请求参数 ->{}", BASE_API_URL, object.toString());
                        entries = execRequest(object.toString());
//                            log.info("请求返回 ->{}", entries.toString());
                        response = entries.getJSONObject("Response");
                        if ("0".equals(response.getStr("ResultCode"))) {
                            LimitInsertData(connection, tabName, response, name, page);
                        } else {
                            log.error("{} 请求失败：{}", method, object.toString());
                        }
                    }
                }
            } else {
                JSONArray resultData = response.getJSONArray("ResultData");
                List<JSONObject> list = JSONUtil.toList(resultData, JSONObject.class);
                for (JSONObject jsonObject : list) {
                    editDataBase(connection, tabName, jsonObject, name);
                }
            }
        } else {
            log.error("{}请求失败 - 请求状态码不为0, 请求参数 ->{}, 请求返回 ->{}", method, object.toString(), response.toString());
        }
    }

    /**
     * 针对于分页接口 创建表、插入数据
     *
     * @param connection
     * @param tabName
     * @param response
     * @return
     */
    private int LimitInsertData(Connection connection, String tabName, JSONObject response,String name,Integer page) {
        JSONArray jsonArray = response.getJSONArray("ResultData");
        JSONObject entries = (JSONObject) jsonArray.get(0);
        int maxPage;
        // 行数 数据返回的第几行
        Integer rowNumber = entries.getInt("RowNumber");
        // 总条数
        Integer totalCount = entries.getInt("totalCount");
        maxPage = (totalCount + 1000 - 1) / 1000;
        log.info("请求返回总条数 ->{},当前页 ->{},总页数 ->{}",totalCount,page,maxPage);
        jsonArray.forEach(obj -> {
            editDataBase(connection, tabName, (JSONObject) obj,name);
        });
        return maxPage;
    }

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

    /**
     * 执行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 {
            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;charset=UTF-8");
            connection.setDoOutput(true);
            OutputStream stream = connection.getOutputStream();
            stream.write(params.getBytes(StandardCharsets.UTF_8));
            BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream(),StandardCharsets.UTF_8));
            StringBuilder builder = new StringBuilder();
            String str;
            while ((str = reader.readLine()) != null) {
                builder.append(str);
            }
            return JSONUtil.parseObj(builder.toString());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                connection.disconnect();
            }
        }
        return null;
    }

    private Connection getConnection(String user,String password,String url) {
        try {
            log.info("数据库信息 ->{},{},{}",user,password,url);
            return DriverManager.getConnection(url, user, password);
        } catch (Exception throwables) {
            log.error("获取sql连接失败");
            throwables.printStackTrace();
        }
        return null;
    }

    public Connection getConnection() {
        try {
            log.info("数据库信息 ->{},{},{}",USER,PASSWORD,URL);
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (Exception throwables) {
            log.error("获取sql连接失败");
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 表是否存在
     *
     * @param connection
     * @param tableName
     * @return
     * @throws SQLException
     */
    private Boolean tabIsExists(Connection connection, String tableName,String name) 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, name);
        statement.setString(2, tableName);
//        log.info("判断表是否存在 sql-> {}", statement.toString());
        ResultSet resultSet = statement.executeQuery();
        boolean next = resultSet.next();
        statement.close();
        return next;
    }

    /**
     * 操作表
     *
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
        List<String> columns = getColumns(tabName, connection);
//        log.info("当前表字段为 ->{}", columns);
        ArrayList<String> responseColums = new ArrayList<>();
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            String key = entry.getKey().trim().toLowerCase();
            responseColums.add(key);
        }
//        log.info("请求返回的字段为 ->{}", responseColums);
        responseColums.removeAll(columns);
//        log.info("需要增加的字段 ->{}",responseColums);
        if (!responseColums.isEmpty()) {
            // 需要增加字段 并插入数据
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
//                log.info("修改字段 ->{}",sql.toString());
                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()) {
            String defaultVal = entry.getValue().toString();
            String key = entry.getKey().trim().toLowerCase();
            if (StrUtil.isBlank(defaultVal) || defaultVal.equals("null"))
                continue;
            String val = defaultVal.trim().replaceAll("\\s+", "").replace("\\","");
            insertSqlBuilder.append(key).append(", ");
            valueBuilder.append("'").append(val.replaceAll("'","‘")).append("', ");
//            selectSqlBuilder.append(entry.getKey()).append(" = '").append(entry.getValue().toString().replaceAll("'","‘")).append("' and ");
        }
        insertSqlBuilder.append("insert_time, ");
        String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
        valueBuilder.append("'").append(time).append("'").append(", ");
        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 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()) {
            String key = entry.getKey().trim().toLowerCase();
            sql.append(key).append(" VARCHAR(200) null,");
        }
        sql.append("insert_time").append(" VARCHAR(100) null,");
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
        log.info("创建表格 -> {}",sql.toString());
        Statement statement = connection.createStatement();
        statement.execute(sql.toString());
    }

    /**
     * 删除表
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void dropTable(String tabName,Connection connection) throws SQLException {
        String sql = "DROP TABLE IF EXISTS " + tabName;
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
}