当前位置：首页 > news >正文

springboot生成表结构和表数据sql

news 2025/8/24 3:15:49

需求

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

实现

package com.nari.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 {@Resourceprivate 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());}}