springboot 生成表结构和表数据 sql

需求

业务背景是需要某单机程序需要把正在进行的任务导出，然后另一台电脑上单机继续运行，我这里选择的方案是同步 SQL 形式，并保证 ID 随机，多个数据库不会重复。

实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
package com.nari.
![](image.png)
web.controller.demo.controller;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/download")
public class DemoController {


    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 二进制转成十六进制
     * 这里主要是处理图片数据，因为数据库存了图片 Base64
     */
    private String bytesToHexString(byte[] bytes) {
        StringBuilder hexString = new StringBuilder();
        for (byte b : bytes) {
            String hex = Integer.toHexString(0xff & b);
            if (hex.length() == 1) {
                hexString.append('0');
            }
            hexString.append(hex);
        }
        return hexString.toString();
    }

    /**
     * 导出指定表 SQL
     *
     */
    @GetMapping("/table/{tableName}")
    public void downloadTable(@PathVariable String tableName, HttpServletResponse response) throws IOException {
        // 查询表数据
        List<Map<String, Object>> tableData = jdbcTemplate.queryForList("SELECT * FROM " + tableName);

        // 查询表结构
        List<Map<String, Object>> tableStructure = jdbcTemplate.queryForList("SHOW CREATE TABLE " + tableName);

        // 拼接 SQL 插入语句
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append(tableStructure.get(0).get("Create Table")).append(";\n\n");

        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");
        }

        // 构建响应
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + tableName+".sql");
        response.getWriter().write(sqlBuilder.toString());
    }

}

结果

能看到这边图片已经正常导出了

image-20230808213359441
ud.com/blog/202308082133731.png)