package com.example.utils;
|
|
import cn.hutool.json.JSONArray;
|
import cn.hutool.json.JSONObject;
|
import cn.hutool.json.JSONUtil;
|
import lombok.extern.slf4j.Slf4j;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
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<String> methods = new ArrayList<>();
|
// 需要分页的代码集合
|
private static List<String> limits = new ArrayList<>();
|
|
/**
|
* 暂时不做
|
* 1.18 计费明细项目 MXJFXMZD
|
* 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");
|
|
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();
|
try {
|
util.exec1();
|
} catch (IOException e) {
|
e.printStackTrace();
|
log.error("异常");
|
}
|
}
|
|
/**
|
* 增加默认参数
|
* @param jsonObject
|
* @return
|
*/
|
public JSONObject addInputVal(JSONObject jsonObject){
|
JSONObject obj = JSONUtil.createObj();
|
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);
|
methods.forEach(method -> {
|
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());
|
assert entries != null;
|
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) {
|
page += 1;
|
for (int i = page; i <= maxPage; i++) {
|
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());
|
}
|
page++;
|
}
|
}
|
} else {
|
JSONArray resultData = response.getJSONArray("ResultData");
|
JSONObject jsonObject = JSONUtil.toList(resultData, JSONObject.class).get(0);
|
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);
|
}
|
// 对比字段 并插入数据
|
operationTable(obj, tabName, connection);
|
} catch (SQLException throwables) {
|
log.error("数据库操作异常");
|
log.error(throwables.getSQLState());
|
log.error(throwables.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<String> columns = getColumns(tabName, connection);
|
// log.info("当前表字段为 ->{}", columns);
|
ArrayList<String> responseColums = new ArrayList<>();
|
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();
|
}
|
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()) {
|
if (entry.getValue().toString().equals("null"))
|
continue;
|
insertSqlBuilder.append(entry.getKey()).append(", ");
|
valueBuilder.append("'").append(entry.getValue().toString().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 = 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.append("insert_time").append(" text 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);
|
}
|
}
|
