去除查询、每次插入前删除表重建、修改字段类型

赵文轩

2024-06-04 24867bdaf1dbdcc855e86ab0284d2836252de768

去除查询、每次插入前删除表重建、修改字段类型

 src/main/java/com/example/utils/DictionaryUtil.java

@@ -25,9 +25,9 @@
@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/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 final String CONFIG_PATH = "D:\\ltkjprojectconf\\config.properties";
    private static String BASE_API_URL = "";
    // 请求代码总集合
    private static List<String> methods = new ArrayList<>();
@@ -139,13 +139,13 @@
    }

    public static void main(String[] args) {
        DictionaryUtil util = new DictionaryUtil();
        try {
            util.exec1();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("异常");
        }
//        DictionaryUtil util = new DictionaryUtil();
//        try {
//            util.exec1();
//        } catch (IOException e) {
//            e.printStackTrace();
//            log.error("异常");
//        }
    }

    /**
@@ -380,7 +380,7 @@
        if (!responseColums.isEmpty()) {
            // 需要增加字段 并插入数据
            for (String colum : responseColums) {
                String sql = "alter table " + tabName + " add column " + colum + " text null";
                String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
//                log.info("修改字段 ->{}",sql.toString());
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
@@ -402,38 +402,39 @@
     */
    private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
        // 插入数据前 先查询数据是否存在
        StringBuilder selectSqlBuilder = new StringBuilder();
//        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 ");
//        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"))
            String str = entry.getValue().toString().replaceAll("\\\\", "");
            if (str.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 ");
            valueBuilder.append("'").append(str.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());
//        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();
//        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)) {
//        if (!"0".equals(string)) {
//            log.info("数据存在不需要插入 {}", jsonObject);
            return;
        }
//            return;
//        }
        // 插入数据
        statement = connection.createStatement();
        Statement statement = connection.createStatement();
//        log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
        statement.execute(insertSqlBuilder.toString());
        statement.close();
@@ -469,9 +470,9 @@
    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(entry.getKey()).append(" VARCHAR(200) null,");
        }
        sql.append("insert_time").append(" text null,");
        sql.append("insert_time").append(" VARCHAR(100) null,");
        sql = new StringBuilder(sql.substring(0, sql.length() - 1));
        sql.append(");");
//        log.info("创建表格 -> {}",sql.toString());
@@ -489,5 +490,6 @@
        String sql = "DROP TABLE IF EXISTS " + tabName;
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
}