package com.ltkj.web.controller.his;
|
|
import cn.hutool.core.bean.BeanUtil;
|
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.ltkj.common.core.domain.AjaxResult;
|
import com.ltkj.hosp.domain.ApiConfig;
|
import com.ltkj.hosp.domain.HisApiConfig;
|
import com.ltkj.hosp.service.ApiConfigService;
|
import com.ltkj.hosp.service.HisApiConfigService;
|
import com.ltkj.web.wxUtils.HttpClientUtils;
|
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 org.springframework.transaction.annotation.Propagation;
|
import org.springframework.transaction.annotation.Transactional;
|
|
import java.io.FileInputStream;
|
import java.io.IOException;
|
import java.io.InputStreamReader;
|
import java.nio.charset.StandardCharsets;
|
import java.sql.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.Date;
|
import java.util.stream.Collectors;
|
|
/**
|
* @Company: 西安路泰科技有限公司
|
* @Author: zhaowenxuan
|
* @Date: 2024/5/31 14:38
|
*/
|
@Slf4j
|
@Component
|
public class HisApiGetMethodService {
|
|
|
// 数据库配置文件路径
|
// private static final String CONFIG_PATH = url;
|
// private static final String CONFIG_PATH = "/Users/chacca/开发相关/代码/ltkj_peis/ltkj-admin/src/main/resources/config.properties";
|
// 数据库名
|
private static String DB_NAME = "";
|
private static String HIS_URL = "";
|
private static String DATA_URL = "";
|
private static String DATA_USER ="";
|
private static String DATA_PASS = "";
|
private static String LIS_URL = "";
|
private static String PACS_URL = "";
|
@Autowired
|
private HisApiMethodService controller;
|
@Autowired
|
private HisApiConfigService hisApiConfigService;
|
@Autowired
|
private ApiConfigService apiConfigService;
|
|
private static String url;
|
|
|
@Value ("${config.properties}")
|
public void set(String urls){
|
url=urls;
|
FileInputStream inputStream = null;
|
try {
|
inputStream = new FileInputStream(url);
|
Properties props = new Properties();
|
props.load(inputStream);
|
DB_NAME = props.getProperty("name");
|
DATA_URL = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + DB_NAME + "" +
|
"?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
|
DATA_USER = props.getProperty("username");
|
DATA_PASS = props.getProperty("password");
|
String apiUrl = props.getProperty("his_api_url");
|
String apiPort = props.getProperty("his_api_port");
|
HIS_URL = apiUrl+":"+apiPort;
|
String lisApiUrl = props.getProperty("lis_api_url");
|
String lisApiPort = props.getProperty("lis_api_port");
|
LIS_URL = lisApiUrl+":"+lisApiPort;
|
String pacsApiUrl = props.getProperty("pacs_api_url");
|
String pacsApiPort = props.getProperty("pacs_api_port");
|
PACS_URL = pacsApiUrl+":"+pacsApiPort;
|
} catch (IOException e) {
|
System.out.println("初始化数据库异常 ->"+e.getMessage());
|
}
|
}
|
|
static {
|
try {
|
Class.forName("com.mysql.cj.jdbc.Driver");
|
} catch (ClassNotFoundException e) {
|
log.error(String.valueOf(e),e.getMessage());
|
}
|
// try {
|
// InputStreamReader reader = new InputStreamReader(new FileInputStream(url), StandardCharsets.UTF_8);
|
// Properties props = new Properties();
|
// props.load(reader);
|
// String api_url = props.getProperty("his_api_url");
|
// String port = props.getProperty("his_api_port");
|
// DB_NAME = props.getProperty("name");
|
// DATA_URL = "jdbc:mysql://" + props.getProperty("ip") + ":" + props.getProperty("prot") + "/" + DB_NAME +
|
// "?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8";
|
// DATA_USER = props.getProperty("username");
|
// DATA_PASS = props.getProperty("password");
|
// HIS_URL=api_url+":"+port;
|
// } catch (IOException throwables) {
|
// throwables.printStackTrace();
|
// }
|
}
|
|
|
/**
|
* 获取数据
|
*
|
* @param type 接口代码
|
* @param params 请求参数
|
* @return
|
* @deprecated
|
* 方法已弃用,将switch更改为数据库匹配 使用新方法{@link #getHISDataNew(String, Map)}
|
*/
|
@Deprecated
|
@Transactional
|
public AjaxResult getHISData(String type, Map<String, Object> params) {
|
AjaxResult result = null;
|
// TODO 创建表 弄成数据库动态获取 接口地址、代码、是否分页、主键id、主键字段
|
// TODO 从2.4.2获取到数据 根据his_registration_id删除旧数据在插入新数据 下层业务均一样根据返回的JYBGID删除旧数据在插入新数据
|
switch (type) {
|
case "Getoutaccountrecord":
|
result = controller.Getoutaccountrecord(params);
|
// result.put("limit", true);
|
break;
|
case "Getoutpatientcostinfo":
|
result = controller.Getoutpatientcostinfo(params);
|
// result.put("limit", true);
|
break;
|
case "Getlaburgentinfo":
|
result = controller.Getlaburgentinfo(params);
|
// result.put("limit", false);
|
break;
|
case "Getlabgermdetailinfo":
|
result = controller.Getlabgermdetailinfo(params);
|
// result.put("limit", false);
|
break;
|
case "Getlabgermrepinfo":
|
result = controller.Getlabgermrepinfo(params);
|
// result.put("limit", false);
|
break;
|
case "Getlabreportinfo":
|
result = controller.Getlabreportinfo(params);
|
// result.put("limit", true);
|
break;
|
case "Getlabapplyinfo":
|
result = controller.Getlabapplyinfo(params);
|
// result.put("limit", false);
|
break;
|
case "Getexamurgentinfo":
|
result = controller.Getexamurgentinfo(params);
|
// result.put("limit", true);
|
break;
|
case "Getexamreportinfo":
|
result = controller.Getexamreportinfo(params);
|
// result.put("limit", true);
|
break;
|
case "Getexamapplyinfo":
|
result = controller.Getexamapplyinfo(params);
|
// result.put("limit", false);
|
break;
|
case "Getlabdetailinfo":
|
result = controller.Getlabdetailinfo(params);
|
// result.put("limit", false);
|
break;
|
}
|
String json = result.get("data").toString();
|
JSONObject response = JSONUtil.parseObj(json).getJSONObject("Response");
|
if (response.getStr("ResultCode").toString().equals("0")) {
|
AjaxResult ajaxResult = AjaxResult.success();
|
// if ((boolean) result.get("limit")) {
|
// ajaxResult = saveArray(json, type);
|
JSONArray resultData = response.getJSONArray("ResultData");
|
List<Map<String, String>> list = new ArrayList<>();
|
for (Object resultDatum : resultData) {
|
JSONObject object = (JSONObject) resultDatum;
|
ajaxResult = save(object, type,null,JSONUtil.toJsonStr(params));
|
Map<String, String> map = new HashMap<>();
|
for (String key : object.keySet()) {
|
String defaultVal = object.get(key).toString();
|
String val = defaultVal.trim().replaceAll("\\s+", "");
|
map.put(key, val);
|
}
|
list.add(map);
|
}
|
ajaxResult.put("data", list);
|
// } else {
|
// // 以集合返回
|
// JSONArray resultData = response.getJSONArray("ResultData");
|
// List<JSONObject> list = JSONUtil.toList(resultData, JSONObject.class);
|
// ArrayList<Map<String, String>> maps = new ArrayList<>();
|
// for (JSONObject data : list) {
|
// ajaxResult = save(data, type);
|
// HashMap<String, String> map1 = new HashMap<>();
|
// for (String key : data.keySet()) {
|
// String defaultVal = data.get(key).toString();
|
// String val = defaultVal.trim().replaceAll("\\s+", "");
|
// map1.put(key, val);
|
// }
|
// maps.add(map1);
|
// }
|
// ajaxResult.put("data", maps);
|
// }
|
return ajaxResult;
|
} else {
|
return AjaxResult.error().put("result", JSONUtil.parseObj(json));
|
}
|
}
|
|
// @Transactional
|
public AjaxResult getHISDataNew(String type, Map<String, Object> params){
|
try {
|
LambdaQueryWrapper<HisApiConfig> lambdaQueryWrapper = new LambdaQueryWrapper<>();
|
lambdaQueryWrapper.eq(HisApiConfig::getApiMethod, type);
|
HisApiConfig hisApiConfig = hisApiConfigService.getOne(lambdaQueryWrapper);
|
String responseJson = HttpClientUtils.sendPost(HIS_URL + hisApiConfig.getApiUrl(), params);
|
Integer isResponse = hisApiConfig.getIsResponse();
|
JSONObject response = null;
|
if (isResponse == 1) {
|
response = JSONUtil.parseObj(responseJson).getJSONObject("Response");
|
} else{
|
response = JSONUtil.parseObj(responseJson);
|
}
|
if (response.getStr(hisApiConfig.getResultCodeKey()).equals("0")) {
|
AjaxResult ajaxResult = AjaxResult.success();
|
JSONArray resultData = response.getJSONArray(hisApiConfig.getResultDataKey());
|
List<Map<String, String>> list = new ArrayList<>();
|
for (Object resultDatum : resultData) {
|
JSONObject object = (JSONObject) resultDatum;
|
ajaxResult = save(object, type,hisApiConfig,JSONUtil.toJsonStr(params));
|
// save(object, type,hisApiConfig,JSONUtil.toJsonStr(params));
|
Map<String, String> map = new HashMap<>();
|
for (String key : object.keySet()) {
|
String defaultVal = object.get(key).toString();
|
String val = defaultVal.trim().replaceAll("\\s+", "");
|
map.put(key, val);
|
}
|
list.add(map);
|
}
|
ajaxResult.put("data", list);
|
return ajaxResult;
|
} else {
|
return AjaxResult.error().put("result", JSONUtil.parseObj(responseJson));
|
}
|
} catch (Exception e) {
|
// throw new RuntimeException(e);
|
log.error(String.valueOf(e),e.getMessage());
|
return AjaxResult.error();
|
}
|
}
|
|
public AjaxResult getLisData(String type,Map<String ,Object> params){
|
LambdaQueryWrapper<ApiConfig> wrapper = new LambdaQueryWrapper<>();
|
wrapper.eq(ApiConfig::getType,"lis");
|
wrapper.eq(ApiConfig::getApiMethod,type);
|
return getAjaxResult("lis", params, wrapper);
|
}
|
|
public AjaxResult getPacsData(String type,Map<String ,Object> params){
|
LambdaQueryWrapper<ApiConfig> wrapper = new LambdaQueryWrapper<>();
|
wrapper.eq(ApiConfig::getType,"pacs");
|
wrapper.eq(ApiConfig::getApiMethod,type);
|
return getAjaxResult("pacs", params, wrapper);
|
}
|
|
private AjaxResult getAjaxResult(String type, Map<String, Object> params, LambdaQueryWrapper<ApiConfig> wrapper) {
|
ApiConfig apiConfig = apiConfigService.getOne(wrapper);
|
String responseJson;
|
switch (type){
|
case "pacs":
|
responseJson = HttpClientUtils.sendPost(PACS_URL + apiConfig.getApiUrl(), params);
|
break;
|
default:
|
responseJson = HttpClientUtils.sendPost(LIS_URL + apiConfig.getApiUrl(), params);
|
break;
|
}
|
Integer isResponse = apiConfig.getIsResponse();
|
JSONObject response = null;
|
if (isResponse == 1) {
|
response = JSONUtil.parseObj(responseJson).getJSONObject("Response");
|
} else{
|
response = JSONUtil.parseObj(responseJson);
|
}
|
if (response.getStr(apiConfig.getResultCodeKey()).equals("1")) {
|
AjaxResult ajaxResult = AjaxResult.success();
|
if (response.getStr(apiConfig.getResultDataKey()) != null && StrUtil.isNotBlank(response.getStr(apiConfig.getResultDataKey()))){
|
JSONArray resultData = response.getJSONArray(apiConfig.getResultDataKey());
|
List<Map<String, String>> list = new ArrayList<>();
|
for (Object resultDatum : resultData) {
|
JSONObject object = (JSONObject) resultDatum;
|
ajaxResult = save(object, type,apiConfig,JSONUtil.toJsonStr(params));
|
Map<String, String> map = new HashMap<>();
|
for (String key : object.keySet()) {
|
String defaultVal = object.get(key).toString();
|
String val = defaultVal.trim().replaceAll("\\s+", "");
|
map.put(key, val);
|
}
|
list.add(map);
|
}
|
ajaxResult.put("data", list);
|
}else {
|
ajaxResult.put("data",response);
|
}
|
return ajaxResult;
|
} else {
|
return AjaxResult.error().put("result", JSONUtil.parseObj(responseJson));
|
}
|
}
|
|
/**
|
* ResultData为对象
|
* 根据his接口返回值 插入数据
|
* 如果没有表则创建表 接口增加了字段则为表增加字段
|
*
|
* @param code 接口代码
|
* @return 执行是否成功
|
*/
|
public AjaxResult save(JSONObject object, String code,Object hisApiConfig,String paramsJson) {
|
String tabName = "";
|
if (code.equals("lis") || code.equals("pacs"))
|
tabName = ((ApiConfig)hisApiConfig).getTabName();
|
else tabName = ((HisApiConfig) hisApiConfig).getTabName();
|
Connection connection = getConnection();
|
Boolean isExists = tabIsExists(connection, tabName);
|
if (null == isExists)
|
return AjaxResult.error();
|
if (!isExists) {
|
try {
|
creatTable(object, tabName, connection);
|
} catch (SQLException e) {
|
log.error(String.valueOf(e),e.getMessage());
|
// return AjaxResult.error();
|
}
|
}
|
//插入数据
|
try {
|
operationTable(object,connection,hisApiConfig,paramsJson,code);
|
} catch (SQLException e) {
|
log.error(String.valueOf(e),e.getMessage());
|
// return AjaxResult.error();
|
}finally {
|
try {
|
connection.close();
|
} catch (SQLException ignored) {
|
}
|
}
|
return AjaxResult.success();
|
}
|
|
/**
|
* 操作表
|
*
|
* @param connection
|
* @throws SQLException
|
*/
|
private void operationTable(JSONObject jsonObject, Connection connection,Object hisApiConfig,String paramsJson,String code) throws SQLException {
|
String tabName = "";
|
if (code.equals("lis") || code.equals("pacs"))
|
tabName = ((ApiConfig)hisApiConfig).getTabName();
|
else tabName = ((HisApiConfig) hisApiConfig).getTabName();
|
List<String> columns = getColumns(tabName, connection);
|
ArrayList<String> responseColums = new ArrayList<>();
|
for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
|
String key = entry.getKey().trim().toLowerCase();
|
responseColums.add(key);
|
}
|
Map<String, Object> beanToMap = BeanUtil.beanToMap(JSONUtil.parseObj(paramsJson));
|
Set<Map.Entry<String, Object>> entries = beanToMap.entrySet();
|
for (Map.Entry<String, Object> entry : entries) {
|
String key = "request_params_"+entry.getKey().trim().toLowerCase();
|
if (!columns.contains(key))
|
responseColums.add(key);
|
}
|
responseColums.removeAll(columns);
|
if (!responseColums.isEmpty()) {
|
for (String colum : responseColums) {
|
String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
|
Statement statement = connection.createStatement();
|
statement.executeUpdate(sql);
|
statement.close();
|
}
|
insertDataNew(connection, jsonObject,hisApiConfig,paramsJson,code);
|
} else {
|
insertDataNew(connection, jsonObject,hisApiConfig,paramsJson,code);
|
}
|
}
|
|
/**
|
* 插入数据
|
*
|
* @param hisApiConfig
|
* @param connection
|
* @param jsonObject
|
* @throws SQLException
|
*/
|
private void insertData(HisApiConfig hisApiConfig, Connection connection, JSONObject jsonObject) throws SQLException {
|
StringBuilder insertSqlBuilder = new StringBuilder();
|
StringBuilder valueBuilder = new StringBuilder();
|
StringBuilder selectSqlBuilder = new StringBuilder();
|
insertSqlBuilder.append("insert into ").append(hisApiConfig.getTabName()).append(" (");
|
selectSqlBuilder.append("select count(1) as count from ").append(hisApiConfig.getTabName()).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+", "");
|
insertSqlBuilder.append(key).append(", ");
|
valueBuilder.append("'").append(val).append("', ");
|
selectSqlBuilder.append(key).append(" = '").append(val).append("' and ");
|
// 判断数据是否大于字段创建值如果大于则增加超过阈值则使用text
|
String columnType = getColumnType(connection, hisApiConfig.getTabName(), key);
|
if (!"text".equalsIgnoreCase(columnType)){
|
int currentSize = getColumnSize(connection, hisApiConfig.getTabName(), key);
|
if (currentSize < val.length()){
|
if (val.length()>= 1000){
|
alterColumnTypeToText(connection, hisApiConfig.getTabName(), key);
|
}else {
|
int newSize = ((val.length() / 100) + 1) * 100;
|
alterColumnSize(connection, hisApiConfig.getTabName(), key, newSize);
|
}
|
}
|
}
|
}
|
selectSqlBuilder.delete(selectSqlBuilder.length() - 5, selectSqlBuilder.length());
|
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;
|
}
|
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 = connection.createStatement();
|
statement.execute(insertSqlBuilder.toString());
|
statement.close();
|
}
|
|
/**
|
* 插入数据
|
*
|
* @param connection
|
* @param jsonObject
|
* @throws SQLException
|
*/
|
@Transactional
|
public void insertDataNew(Connection connection, JSONObject jsonObject, Object hisApiConfig, String paramsJson, String code) throws SQLException {
|
String tabName = "";
|
List<String> primaryKesList;
|
if (code.equals("lis") || code.equals("pacs")) {
|
tabName = ((ApiConfig) hisApiConfig).getTabName();
|
primaryKesList = Arrays.stream(((ApiConfig)hisApiConfig).getPrimaryKeys().split(",")).map(String::toLowerCase).collect(Collectors.toList());
|
} else {
|
tabName = ((HisApiConfig) hisApiConfig).getTabName();
|
primaryKesList = Arrays.stream(((HisApiConfig)hisApiConfig).getPrimaryKeys().split(",")).map(String::toLowerCase).collect(Collectors.toList());
|
}
|
StringBuilder deleteSqlBuilder = new StringBuilder();
|
StringBuilder insertSqlBuilder = new StringBuilder();
|
StringBuilder valueBuilder = new StringBuilder();
|
insertSqlBuilder.append("insert into ").append(tabName).append(" (");
|
deleteSqlBuilder.append("delete 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+", "").replaceAll("[^\\x00-\\x7F]", "");
|
if (primaryKesList.contains(key)) {
|
deleteSqlBuilder.append(key).append(" = '").append(val).append("' and ");
|
}
|
insertSqlBuilder.append(key).append(", ");
|
valueBuilder.append("'").append(val).append("', ");
|
// 判断数据是否大于字段创建值如果大于则增加超过阈值则使用text
|
String columnType = getColumnType(connection, tabName, key);
|
if (!"text".equalsIgnoreCase(columnType)){
|
int currentSize = getColumnSize(connection, tabName, key);
|
if (currentSize < val.length()){
|
if (val.length()>= 1000){
|
alterColumnTypeToText(connection, tabName, key);
|
}else {
|
int newSize = ((val.length() / 100) + 1) * 100;
|
alterColumnSize(connection, tabName, key, newSize);
|
}
|
}
|
}
|
}
|
deleteSqlBuilder.delete(deleteSqlBuilder.length() - 5, deleteSqlBuilder.length());
|
Statement statement = connection.prepareStatement(deleteSqlBuilder.toString());
|
log.info("HIS数据保存类 执行删除 ->{}",deleteSqlBuilder.toString());
|
int i = statement.executeUpdate(deleteSqlBuilder.toString());
|
log.info("HIS数据保存类 删除条数 ->{}",i);
|
statement.close();
|
insertSqlBuilder.append("insert_time, ");
|
insertSqlBuilder.append("request_params, ");
|
DatabaseMetaData metaData = connection.getMetaData();
|
try (ResultSet resultSet = metaData.getColumns(null, null, tabName, "request_params")) {
|
if (!resultSet.next()){
|
String alterTableSQL = String.format("ALTER TABLE %s ADD COLUMN %s %s", tabName, "request_params", "text");
|
statement = connection.createStatement();
|
statement.execute(alterTableSQL);
|
}
|
}
|
String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
|
valueBuilder.append("'").append(time).append("'").append(", ");
|
valueBuilder.append("'").append(paramsJson).append("'").append(", ");
|
Map<String, Object> beanToMap = BeanUtil.beanToMap(JSONUtil.parseObj(paramsJson));
|
Set<Map.Entry<String, Object>> entries = beanToMap.entrySet();
|
for (Map.Entry<String, Object> entry : entries) {
|
String key = "request_params_"+entry.getKey().trim().toLowerCase();
|
insertSqlBuilder.append(key).append(", ");
|
valueBuilder.append("'").append(entry.getValue().toString()).append("', ");
|
}
|
insertSqlBuilder.delete(insertSqlBuilder.length() - 2, insertSqlBuilder.length());
|
valueBuilder.delete(valueBuilder.length() - 2, valueBuilder.length());
|
insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
|
// log.info("执行插入 ->{}",insertSqlBuilder.toString());
|
// 插入数据
|
// boolean execute = statement.execute(insertSqlBuilder.toString());
|
// for (int j = 0; j < 10; j++) {
|
// statement = connection.createStatement();
|
// log.info("HIS数据保存类 执行插入 ->{}",insertSqlBuilder.toString());
|
// boolean execute = statement.execute(insertSqlBuilder.toString());
|
// log.info("HIS数据保存类 执行插入状态 ->{},次数->{}",execute,(j+1));
|
// statement.close();
|
// if (execute)
|
// break;
|
// }
|
statement = connection.createStatement();
|
log.info("HIS数据保存类 执行插入 ->{}",insertSqlBuilder.toString());
|
boolean execute = statement.execute(insertSqlBuilder.toString());
|
if (!execute){
|
int updateCount = statement.getUpdateCount();
|
log.info("HIS数据保存类 执行插入成功 影响行数->{}",updateCount);
|
}
|
statement.close();
|
}
|
|
/**
|
* 获取列类型
|
* @param connection
|
* @param tableName
|
* @param columnName
|
* @return
|
* @throws SQLException
|
*/
|
private static String getColumnType(Connection connection, String tableName, String columnName) throws SQLException {
|
String query = "SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
|
try (PreparedStatement statement = connection.prepareStatement(query)) {
|
statement.setString(1, tableName);
|
statement.setString(2, columnName);
|
ResultSet resultSet = statement.executeQuery();
|
if (resultSet.next()) {
|
return resultSet.getString("DATA_TYPE");
|
}
|
}
|
return "";
|
}
|
|
/**
|
* 获取列长度
|
* @param connection
|
* @param tableName
|
* @param columnName
|
* @return
|
* @throws SQLException
|
*/
|
private static int getColumnSize(Connection connection, String tableName, String columnName) throws SQLException {
|
String query = "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
|
try (PreparedStatement statement = connection.prepareStatement(query)) {
|
statement.setString(1, tableName);
|
statement.setString(2, columnName);
|
ResultSet resultSet = statement.executeQuery();
|
if (resultSet.next()) {
|
return resultSet.getInt("CHARACTER_MAXIMUM_LENGTH");
|
}
|
}
|
return 0;
|
}
|
|
/**
|
* 修改列长度
|
* @param connection
|
* @param tableName
|
* @param columnName
|
* @param newSize
|
* @throws SQLException
|
*/
|
private static void alterColumnSize(Connection connection, String tableName, String columnName, int newSize) throws SQLException {
|
String alterQuery = String.format("ALTER TABLE %s MODIFY %s VARCHAR(%d)", tableName, columnName, newSize);
|
try (Statement statement = connection.createStatement()) {
|
statement.executeUpdate(alterQuery);
|
}
|
}
|
|
/**
|
* 超过阈值则修改为text类型
|
* @param connection
|
* @param tableName
|
* @param columnName
|
* @throws SQLException
|
*/
|
private static void alterColumnTypeToText(Connection connection, String tableName, String columnName) throws SQLException {
|
String alterQuery = String.format("ALTER TABLE %s MODIFY %s TEXT", tableName, columnName);
|
try (Statement statement = connection.createStatement()) {
|
statement.executeUpdate(alterQuery);
|
}
|
}
|
|
/**
|
* 获取表的列
|
*
|
* @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 data
|
* @param tabName
|
* @param connection
|
* @throws SQLException
|
*/
|
private void creatTable(JSONObject data, String tabName, Connection connection) throws SQLException {
|
StringBuilder sql = new StringBuilder("CREATE TABLE " + tabName + " (");
|
for (Map.Entry<String, Object> entry : data.entrySet()) {
|
String key = entry.getKey().trim().toLowerCase();
|
sql.append(key).append(" VARCHAR(200) null,");
|
}
|
sql.append("insert_time").append(" VARCHAR(200) null,");
|
sql.append("request_params").append(" text null COMMENT '请求参数',");
|
sql = new StringBuilder(sql.substring(0, sql.length() - 1));
|
sql.append(");");
|
Statement statement = connection.createStatement();
|
statement.execute(sql.toString());
|
}
|
|
/**
|
* 判断表是否存在
|
*
|
* @param connection
|
* @param tableName
|
* @return
|
*/
|
private Boolean tabIsExists(Connection connection, String tableName) {
|
String tabSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
|
PreparedStatement statement = null;
|
try {
|
statement = connection.prepareStatement(tabSql);
|
statement.setString(1, DB_NAME);
|
statement.setString(2, tableName);
|
ResultSet resultSet = statement.executeQuery();
|
boolean next = resultSet.next();
|
statement.close();
|
return next;
|
} catch (SQLException throwables) {
|
log.error(String.valueOf(throwables),throwables.getMessage());
|
}
|
log.error("查询表执行sql返回为null ->{},{},{}",tabSql,DB_NAME,tableName);
|
return null;
|
}
|
|
/**
|
* 获取数据库连接
|
*
|
* @return
|
*/
|
private Connection getConnection() {
|
try {
|
return DriverManager.getConnection(DATA_URL, DATA_USER, DATA_PASS);
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
return null;
|
}
|
}
|
