his业务数据保存工具类

zhaowenxuan

2024-05-31 8ae95806d69f66c2b654b4e51ccd4944758fae62

his业务数据保存工具类

 ltkj-admin/src/main/java/com/ltkj/web/controller/his/HisApiGetMethodService.java

New file
@@ -0,0 +1,212 @@
package com.ltkj.web.controller.his;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.ltkj.common.core.domain.AjaxResult;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * @Company: 西安路泰科技有限公司
 * @Author: zhaowenxuan
 * @Date: 2024/5/31 14:38
 */
public class HisApiGetMethodService {
    // 数据库配置文件路径
    private static final String CONFIG_PATH = "D:\\ltkjprojectconf\\config.properties";
    // 数据库名
    private static String DB_NAME = "";

    /**
     * 根据his接口返回值 插入数据
     * 如果没有表则创建表 接口增加了字段则为表增加字段
     * @param json 请求返回json
     * @param code 接口代码
     * @return 执行是否成功
     */
    public AjaxResult save(String json, String code) {
        code = code.toLowerCase();
        JSONObject jsonObject = JSONUtil.parseObj(json);
        JSONObject response = jsonObject.getJSONObject("Response");
        String tabName = "ltkj_" + code;
        if (response.getStr("ResultCode").equals("0")) {
            JSONArray resultData = response.getJSONArray("ResultData");
            Connection connection = getConnection();
            for (Object resultDatum : resultData) {
                JSONObject object = (JSONObject) resultDatum;
                Boolean isExists = tabIsExists(connection, tabName);
                if (null == isExists)
                    return AjaxResult.error();
                if (!isExists) {
                    try {
                        creatTable(object, tabName, connection);
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                        return AjaxResult.error();
                    }
                }
                //插入数据
                try {
                    operationTable(object, tabName, connection);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    return AjaxResult.error();
                }
            }
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException throwables) {
            }
        }
        return AjaxResult.success();
    }

    /**
     * 操作表
     *
     * @param tabName
     * @param connection
     * @throws SQLException
     */
    private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
        List<String> columns = getColumns(tabName, connection);
        ArrayList<String> responseColums = new ArrayList<>();
        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
            responseColums.add(entry.getKey());
        }
        responseColums.removeAll(columns);
        if (!responseColums.isEmpty()) {
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " text null";
                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()) {
            insertSqlBuilder.append(entry.getKey()).append(", ");
            valueBuilder.append("'").append(entry.getValue()).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();
        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 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()) {
            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(");");
        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) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection() {
        try {
            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");
            Connection connection = DriverManager.getConnection(url, user, password);
            DB_NAME = name;
            return connection;
        } catch (SQLException | IOException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}