package com.example.utils.synczd;
|
|
import cn.hutool.core.util.StrUtil;
|
import cn.hutool.json.JSONArray;
|
import cn.hutool.json.JSONObject;
|
import cn.hutool.json.JSONUtil;
|
import com.example.config.ConfigValue;
|
import com.example.domain.HisSyncDict;
|
import com.example.mapper.HisSyncDictMapper;
|
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.BufferedReader;
|
import java.io.IOException;
|
import java.io.InputStreamReader;
|
import java.io.OutputStreamWriter;
|
import java.net.HttpURLConnection;
|
import java.net.URL;
|
import java.net.URLEncoder;
|
import java.sql.*;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @Company: 西安路泰科技有限公司
|
* @Author: zhaowenxuan
|
* @Date: 2025/1/2 21:57
|
*/
|
@Slf4j
|
@Component
|
public class DictionaryUtilGanSuPingLiangXianRenMinYy {
|
@Autowired
|
private HisSyncDictService hisSyncDictService;
|
@Autowired
|
private ConfigValue configValue;
|
@Autowired
|
private HisSyncDictMapper hisSyncDictMapper;
|
|
private static String BASE_API_URL = "";
|
private static String USER = "";
|
private static String PASSWORD = "";
|
private static String URL = "";
|
private static String NAME = "";
|
@Value("${config.path}")
|
public void setConfigPath(String configPath) {
|
NAME = configValue.getConfigValue("GanSu_PingLiang_XianRenMen.name");
|
URL = "jdbc:mysql://" + configValue.getConfigValue("GanSu_PingLiang_XianRenMen.ip") + ":" + configValue.getConfigValue("GanSu_PingLiang_XianRenMen.prot") + "/" + NAME +
|
"?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
|
USER = configValue.getConfigValue("GanSu_PingLiang_XianRenMen.username");
|
PASSWORD = configValue.getConfigValue("GanSu_PingLiang_XianRenMen.password");
|
String apiUrl = configValue.getConfigValue("GanSu_PingLiang_XianRenMen.his_api_url");
|
String apiPort = configValue.getConfigValue("GanSu_PingLiang_XianRenMen.his_api_port");
|
BASE_API_URL = apiUrl+":"+apiPort+configValue.getConfigValue("GanSu_PingLiang_XianRenMen.hisapiappend");
|
}
|
|
|
// 分页每次1000条
|
static {
|
try {
|
Class.forName("com.mysql.cj.jdbc.Driver");
|
} catch (Exception e) {
|
System.out.println("静态代码块异常 ->"+e.getMessage());
|
}
|
}
|
|
/**
|
* 进行同步
|
*/
|
public void exec(List<HisSyncDict> hisSyncDicts){
|
log.info("开始执行同步");
|
Connection connection = getConnection();
|
if (connection == null)
|
return;
|
for (HisSyncDict hisSyncDict : hisSyncDicts) {
|
hisSyncDict.setDictName(hisSyncDict.getHospId()+"_"+hisSyncDict.getDictName());
|
extracted(NAME, connection, hisSyncDict);
|
}
|
try {
|
connection.close();
|
} catch (SQLException ignored) { }
|
hisSyncDictMapper.proSyncCommonDict();
|
log.info("执行同步完毕");
|
}
|
|
private void extracted(String name, Connection connection, HisSyncDict hisSyncDict) {
|
String method = hisSyncDict.getDictName();
|
log.info("开始请求代码 ->{}", method);
|
String tabName = "ltkj_" + method.toLowerCase();
|
JSONObject object = JSONUtil.createObj();
|
log.info("请求接口 ->{}, 请求参数 ->{}", BASE_API_URL+hisSyncDict.getUrl(), object);
|
JSONObject entries = execRequest(object.toString(),hisSyncDict);
|
log.info("请求返回 ->{}", entries.toString());
|
if ("1".equals(entries.getStr("code"))) {
|
Object returnData = entries.get("data");
|
if (returnData instanceof JSONObject){
|
JSONObject response = entries.getJSONObject("data");
|
try {
|
dropTable(tabName, connection);
|
} catch (SQLException e) {
|
log.error("删除表异常 ->{}", e.getMessage());
|
}
|
editDataBase(connection, tabName, response, name);
|
}else {
|
JSONArray response = entries.getJSONArray("data");
|
try {
|
dropTable(tabName, connection);
|
List<JSONObject> list = JSONUtil.toList(response, JSONObject.class);
|
for (JSONObject jsonObject : list) {
|
editDataBase(connection, tabName, jsonObject, name);
|
}
|
} catch (SQLException e) {
|
log.error("删除表异常 ->{}", e.getMessage());
|
}
|
}
|
}
|
}
|
|
/**
|
* 针对于分页接口 创建表、插入数据
|
*
|
* @param connection
|
* @param tabName
|
* @param response
|
* @return
|
*/
|
private int LimitInsertData(Connection connection, String tabName, JSONObject response,String name,Integer page) {
|
JSONArray jsonArray = response.getJSONArray("list");
|
JSONObject entries = (JSONObject) jsonArray.get(0);
|
int maxPage;
|
// 行数 数据返回的第几行
|
Integer rowNumber = response.getInt("RowNumber");
|
// 总条数
|
Integer totalCount = response.getInt("totalRows");
|
Integer pageCount = response.getInt("pageCount");
|
maxPage = (totalCount + 100 - 1) / 100;
|
log.info("请求返回总条数 ->{},当前页 ->{},总页数 ->{}",totalCount,page,maxPage);
|
jsonArray.forEach(obj -> {
|
editDataBase(connection, tabName, (JSONObject) obj,name);
|
});
|
return pageCount;
|
}
|
|
/**
|
* 操作数据库
|
*
|
* @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());
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 执行post请求
|
*
|
* @param params 请求参数 例如： param1=val1¶m2=val2
|
* @return 请求返回的json转换后的JSONObject对象
|
*/
|
private JSONObject execRequest(String params,HisSyncDict hisSyncDict) {
|
java.net.URL url = null;
|
HttpURLConnection connection = null;
|
OutputStreamWriter writer = null;
|
BufferedReader reader = null;
|
StringBuilder response = new StringBuilder();
|
try {
|
url = new URL(BASE_API_URL+hisSyncDict.getUrl());
|
connection = (HttpURLConnection) url.openConnection();
|
connection.setRequestMethod("POST");
|
connection.setRequestProperty("Content-Type", "application/json;charset=UTF-8");
|
log.info("请求接口 ->{}", hisSyncDict.getUrl());
|
connection.setDoOutput(true);
|
StringBuilder postData = new StringBuilder();
|
JSONObject jsonObject = JSONUtil.parseObj(params);
|
for (String key : jsonObject.keySet()) {
|
if (postData.length() > 0) {
|
postData.append("&");
|
}
|
String encode = URLEncoder.encode(key, "UTF-8");
|
String encode1 = URLEncoder.encode(String.valueOf(jsonObject.get(key)), "UTF-8");
|
postData.append(encode).append("=").append(encode1);
|
}
|
|
writer = new OutputStreamWriter(connection.getOutputStream());
|
writer.write(postData.toString());
|
writer.flush();
|
reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
|
String line;
|
while ((line = reader.readLine()) != null) {
|
response.append(line);
|
}
|
return JSONUtil.parseObj(response);
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
if (connection != null) {
|
connection.disconnect();
|
}
|
}
|
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 insertSqlBuilder = new StringBuilder();
|
StringBuilder valueBuilder = new StringBuilder();
|
insertSqlBuilder.append("insert into ").append(tabName).append(" (");
|
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("', ");
|
}
|
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());
|
insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
|
// 插入数据
|
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();
|
}
|
}
|
