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 lombok.extern.slf4j.Slf4j; 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.*; import java.util.Date; /** * @Company: 西安路泰科技有限公司 * @Author: zhaowenxuan * @Date: 2024/5/21 16:41 */ @Slf4j @Component public class DictionaryUtil { // private static final String CONFIG_PATH = "/Users/chacca/开发相关/代码/ltkj_peis_sjpt/src/main/resources/config.properties"; // private static final String CONFIG_PATH = "/Users/chacca/开发相关/代码/ltkj_peis_sjpt/src/main/resources/ltkj.properties"; private static final String CONFIG_PATH = "D:\\ltkjprojectconf\\config.properties"; private static String BASE_API_URL = ""; // 请求代码总集合 private static List methods = new ArrayList<>(); // 需要分页的代码集合 private static List limits = new ArrayList<>(); /** * 暂时不做 * 1.90 门诊明细计费及组合计费项目 MZJFZHMXXM * */ // 分页每次1000条 static { try { Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) {} 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("MXJFXMZD"); methods.add("MZJFZHMXXM"); 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"); limits.add("MXJFXMZD"); limits.add("MZJFZHMXXM"); } public static void main(String[] args) { DictionaryUtil util = new DictionaryUtil(); try { util.exec1(); } catch (IOException e) { e.printStackTrace(); log.error("异常"); } } /** * 增加默认参数 * @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 execSync(){ } /** * 单线程执行 * @throws IOException */ public void exec1() throws IOException { log.info("开始执行同步"); int size = 1000; FileInputStream inputStream = new FileInputStream(CONFIG_PATH); Properties props = new Properties(); props.load(inputStream); String name = props.getProperty("name"); String url = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + name + "" + "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8"; String user = props.getProperty("username"); String 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"; Connection connection = getConnection(user,password,url); for (String method : methods) { 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 (limits.contains(method)) { 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 list = JSONUtil.toList(resultData, JSONObject.class); for (JSONObject jsonObject : list) { editDataBase(connection, tabName, jsonObject,name); } } } else { log.error("{}请求失败 - 请求状态码不为0, 请求参数 ->{}, 请求返回 ->{}", method, object.toString(), response.toString()); } } log.info("执行同步完毕"); } /** * 针对于分页接口 创建表、插入数据 * * @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¶m2=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; } /** * 表是否存在 * * @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 columns = getColumns(tabName, connection); // log.info("当前表字段为 ->{}", columns); ArrayList responseColums = new ArrayList<>(); for (Map.Entry 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 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 getColumns(String tabName, Connection connection) throws SQLException { DatabaseMetaData metaData = connection.getMetaData(); ResultSet columns = metaData.getColumns(null, null, tabName, null); ArrayList 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 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(); } }