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

「爱情、让人受尽委屈。」 2023-10-16 10:28 15阅读 0赞

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

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

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

![e046e41b7aae4294aefb76abbd72c14b.png][]

![7265df83069e4eebac5e21b293f74280.png][]

项目是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">
        SELECT
          column_name,
          data_type
        FROM
          information_schema.COLUMNS
        WHERE
          table_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 {
    
        @Autowired
        private UserService userService;
    
        @Resource
        MinIoUtil 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_SS
                            Date 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_name
        public static String convertCamelCaseToSnakeCase(String input) {
            return input.replaceAll("([a-z])([A-Z]+)", "$1_$2").toLowerCase();
        }
    
    
    }

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

![2b2190877c294136ad6f6c39e4983878.png][]

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

![0026a40bf8e64941b4c3556a106d3af6.png][]

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

[e046e41b7aae4294aefb76abbd72c14b.png]: https://img-blog.csdnimg.cn/e046e41b7aae4294aefb76abbd72c14b.png
[7265df83069e4eebac5e21b293f74280.png]: https://img-blog.csdnimg.cn/7265df83069e4eebac5e21b293f74280.png
[2b2190877c294136ad6f6c39e4983878.png]: https://img-blog.csdnimg.cn/2b2190877c294136ad6f6c39e4983878.png
[0026a40bf8e64941b4c3556a106d3af6.png]: https://img-blog.csdnimg.cn/0026a40bf8e64941b4c3556a106d3af6.png