使用Java根据表名导出与导入Sql

/**
     * 导出批次作业
     *
     * @author <a href = "mailto:zysicyj@163.com" > 公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.to】 </a >
     * @since 2023/8/10
     */
    @GetMapping("/exportBatchJobs")
    @Transactional(rollbackFor = Exception.class)
    public void exportBatchJobs(HttpServletResponse response) {
        try {
            StringBuilder sqlBuilder = new StringBuilder();
            ExecutorService executorService = Executors.newFixedThreadPool(getTableNames().size());
            List<Future<?>> futures = new ArrayList<>();

            for (String tableName : getTableNames()) {
                Future<?> future = executorService.submit(() -> {
                    try {
                        downloadTable(tableName, sqlBuilder);
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                });
                futures.add(future);
            }

            // Wait for all threads to finish
            for (Future<?> future : futures) {
                future.get();
            }

            // Set response headers for file download
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("UTF-8");
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + format(DateUtil.now(), "YYYY-MM-dd HH:mm:ss") + ".sql");

            response.getWriter().write(sqlBuilder.toString());

            executorService.shutdown();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取所有表名
     *
     * @return java.util.List<java.lang.String>
     *
     * @author <a href = "mailto:zysicyj@163.com" > 公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.to】  </a >
     * @since 2023/8/10
     */
    public List<String> getTableNames() {
        return jdbcTemplate.queryForList("SHOW TABLES", String.class);
    }

/**
     * 导出指定表SQL
     *
     * @param tableName
     *         表明
     * @param sqlBuilder
     *         拼接sql
     *
     * @author <a href = "mailto:zysicyj@163.com" > 公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.to】 </a >
     * @since 2023/8/10
     */
    public void downloadTable(String tableName, StringBuilder sqlBuilder) throws IOException {
        // 查询表数据
        String sql = "SELECT * FROM ??";
        List<Map<String, Object>> tableData = jdbcTemplate.queryForList(sql, tableName);
        // 拼接SQL插入语句
        for (Map<String, Object> row : tableData) {
            StringBuilder valuesBuilder = new StringBuilder();
            StringBuilder columnsBuilder = new StringBuilder();

            for (Map.Entry<String, Object> entry : row.entrySet()) {
                columnsBuilder.append(entry.getKey()).append(", ");

                if (entry.getValue() instanceof byte[]) {
                    byte[] binaryData = (byte[]) entry.getValue();
                    String hexString = bytesToHexString(binaryData);
                    valuesBuilder.append("0x").append(hexString).append(", ");
                } else {
                    valuesBuilder.append("'").append(entry.getValue()).append("', ");
                }
            }

            String columns = columnsBuilder.substring(0, columnsBuilder.length() - 2);
            String values = valuesBuilder.substring(0, valuesBuilder.length() - 2);

            sqlBuilder.append("INSERT INTO ").append(tableName).append(" (").append(columns).append(") VALUES (")
                    .append(values).append(");\n");
        }
    }

/**
     * 导入SQL
     *
     * @return com.nari.common.core.domain.AjaxResult
     *
     * @author <a href = "mailto:zysicyj@163.com" > 公众号：【程序员朱永胜】 个人博客：【blog.zysicyj.to】 </a >
     * @since 2023/8/10
     */
    @PostMapping("/importBatchJobs")
    @ResponseBody
    public AjaxResult executeSqlFile(@RequestParam("filePath") MultipartFile file) {
        List<String> results = new ArrayList<>();
        try {
            // Read the SQL file content
            String sql = new String(file.getBytes());

            // Split the SQL file content into individual statements
            String[] statements = sql.split(";");

            // Execute each statement using parameterized queries
            for (String statement : statements) {
                // Skip empty statements
                if (statement.trim().isEmpty()) {
                    continue;
                }

                // Execute the statement using parameterized queries
                jdbcTemplate.update(statement);
            }

            return AjaxResult.success("SQL file executed successfully");
        } catch (IOException e) {
            results.add(e.getMessage());
        }
        log.info("导入失败的SQL数量:{},{}", results.size(), results);
        return success();
    }