数据备份文件生成--根据表名生成对应的sql语句文件

最近客户有个需求，希望在后台增加手动备份功能，将数据导出下载保存。

当然，此方法不适用于海量数据的备份，这只适用于少量数据的sql备份。

这是我生成的sql文件，以及sql文件里的insert语句，已亲测，可以直接执行：

项目是SSM框架，接下来就展示我的实现代码：

首先是接受字段的实体类：

@Data
public class ColumnsDto {/*** 表结构的主要字段**/private String column_name;//该字段则是表字段的数据类型  暂时不需要private String data_type;}

然后是用的到两个主要的sql：

1.此sql用于查询表的有效字段信息（table_schema：当前的数据库名）

  <select id="queryColumnsByTableName" resultType="com.hle.monitor.entity.vo.ColumnsDto">SELECTcolumn_name,data_typeFROMinformation_schema.COLUMNSWHEREtable_name = #{tableName}AND table_schema = 'supervision_data'ORDER BY ordinal_position</select>

2.再用sql查询表的所有数据：(注意：此处表名需要要用$而不是#号)

  <select id="findBackupAll"  resultType="java.util.Map">select * from ${tableName}</select>

此处我省略了相应的service和mapper文件内容，直接展示最重要的controller代码：

import com.hle.monitor.entity.Results;
import com.hle.monitor.entity.vo.ColumnsDto;
import com.hle.monitor.service.UserService;
import com.hle.monitor.util.DateUtils;
import com.hle.monitor.util.MinIoUtil;
import com.hle.monitor.util.ParameterUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.http.entity.ContentType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;import javax.annotation.Resource;
import java.io.*;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;/*** @author*/
@CrossOrigin(origins = "*")
@RestController()
@RequestMapping("/backup")
@Api(tags = "备份相关接口类")
@Scope("prototype")
public class BackupController {@Autowiredprivate UserService userService;@ResourceMinIoUtil minIoUtil;//本地文件夹路径private static String backupFilePath = "./sql/";@ApiOperation(value = "根据表名备份信息")@GetMapping("/{tableName}")public Results backupTable(@PathVariable("tableName") String tableName) {//表有效字段信息List<ColumnsDto> columnsDtoList = userService.queryColumnsByTableName(tableName);if(columnsDtoList.isEmpty()) return new Results(500, "该表无有效字段信息！");//文件名--先在本地创建写入后再进行删除String fileName =  System.currentTimeMillis() + "-" + tableName + ".sql";try {//获取所有备份数据List<Map<String, Object>> records = userService.findBackupAll(tableName);String path = backupFilePath + fileName;//不存在文件夹则创建File directory = new File(backupFilePath);directory.mkdirs();BufferedWriter writer = new BufferedWriter(new FileWriter(path));for (Map record : records) {String insertStatement = generateInsertStatement(tableName, record, columnsDtoList) + ";";writer.write(insertStatement);writer.newLine();}writer.close();File file = new File(path);InputStream inputStream = new FileInputStream(file);MultipartFile multipartFile = new MockMultipartFile(ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);minIoUtil.upload(multipartFile, ParameterUtil.bucketNameParam, fileName);//上传后 删除本地文件file.delete();System.out.println("Backup created successfully!");} catch (IOException e) {e.printStackTrace();return new Results(500, "操作失败！");}return new Results(200, "操作成功！", fileName);}//此方法需要以数据库取出来的字段信息为准--转换成sql的方法private String generateInsertStatement(String tableName, Map<String, Object> record, List<ColumnsDto> columnsDtoList) {StringBuilder builder = new StringBuilder();builder.append("INSERT INTO ").append(tableName).append(" (");//拼接列名columnsDtoList.forEach(columns -> {builder.append(columns.getColumn_name()).append(", ");});builder.setLength(builder.length() - 2);builder.append(") VALUES (");//拼接值columnsDtoList.forEach(columns -> {Object value = record.get(columns.getColumn_name());if(value instanceof Date){Date date = (Date) value;builder.append("'").append(DateUtils.parseDateToStr(date)).append("', ");}else if(value instanceof String){builder.append("'").append(value).append("', ");}else{builder.append(value).append(", ");}});builder.setLength(builder.length() - 2);builder.append(")");return builder.toString();}//此方法用于直接取Object的字段信息做sql拼接--转换成sql的方法--已验证过private String generateInsertStatement(String tableName, Object record) {StringBuilder builder = new StringBuilder();builder.append("INSERT INTO ").append(tableName).append(" (");Arrays.stream(record.getClass().getDeclaredFields()).map(Field::getName).forEach(fieldName -> builder.append(convertCamelCaseToSnakeCase(fieldName)).append(", "));builder.setLength(builder.length() - 2);builder.append(") VALUES (");Arrays.stream(record.getClass().getDeclaredFields()).map(field -> {field.setAccessible(true);try {return field.get(record);} catch (IllegalAccessException e) {e.printStackTrace();return null;}}).forEach(value -> {if(value instanceof Date){//时间格式 转换为-YYYY_MM_DD_HH_MM_SSDate date = (Date) value;builder.append("'").append(DateUtils.parseDateToStr(date)).append("', ");}else if(value instanceof String){builder.append("'").append(value).append("', ");}else{//int/double类型 不需要单引号builder.append(value).append(", ");}});builder.setLength(builder.length() - 2);builder.append(")");return builder.toString();}//驼峰命令转换 userName-转换为-user_namepublic static String convertCamelCaseToSnakeCase(String input) {return input.replaceAll("([a-z])([A-Z]+)", "$1_$2").toLowerCase();}}

在controller中提供了两种方法，可以参考一下～，然后直接调用接口就行：

因为项目需要，我是写入文件后再上传至minio文件服务器，所以我要查看还需要去minio服务器查看下载，或者调用现有的下载接口下载～

这样就完成了根据sql文件数据备份～