spring boot 实现Mysql数据脚本导出和数据库脚本的导入

前言

在开发过程中这样一个需求，有些数据需要从数据库导出，然后导入到另外的数据库中。

数据导出

    @SneakyThrows
    public String export() {

        // 获取数据库连接对象
        String pathDb = System.getProperty("user.dir") +"/"+ dbName + ".sql";;
        Connection connection = dataSource.getConnection();
        // 获取连接信息
        DatabaseMetaData metaData = connection.getMetaData();
        // 创建sql文件对象
        FileWriter sqlFileWriter = FileWriter.create(new File(pathDb));
        sqlFileWriter.write("");
        sqlFileWriter.append("USE " + dbName + ";\n");
        sqlFileWriter.append("/*\n");
        sqlFileWriter.append(" --------------------------------------------------\n");
        sqlFileWriter.append(" Target Server Type    : " + metaData.getDatabaseProductName() + ";\n");
        sqlFileWriter.append(" Target Server Version : " + metaData.getDatabaseProductVersion() + ";\n");
        sqlFileWriter.append(" \n");
        sqlFileWriter.append(" Target Server Date : " + DateTime.now() + ";\n");
        sqlFileWriter.append(" \n");
        sqlFileWriter.append(" --------------------------------------------------\n");
        sqlFileWriter.append("*/\n");
        sqlFileWriter.append("SET NAMES utf8mb4;\n");
        sqlFileWriter.append("SET FOREIGN_KEY_CHECKS = 0;\n");
        for (TableBean tableBean : tableBeanList) {
            String table = tableBean.table;
            sqlFileWriter.append("\n\n\n");
            // DROP TABLE
            sqlFileWriter.append("DROP TABLE IF EXISTS `" + table + "`;\n");
            // CREATE TABLE
            Entity createTableEntity = Db.use(dataSource).queryOne("SHOW CREATE TABLE " + table);
            sqlFileWriter.append((String) createTableEntity.get("Create Table"));
            sqlFileWriter.append(";\n");
            // 看配置，是否需要insert语句
            if (!tableBean.insert) {
                continue;
            }
            // INSERT INTO
            List<Entity> dataEntityList = Db.use(dataSource).query("SELECT * FROM " + table);
            for (Entity dataEntity : dataEntityList) {
                StrBuilder field = StrBuilder.create();
                StrBuilder data = StrBuilder.create();

                dataEntity.forEach((key, value) -> {
                    field.append(key).append(", ");
                    if (ObjectUtil.isNotNull(value)) {
                        if (StrUtil.equals("true", String.valueOf(value))) {
                            data.append("b'1'");
                        } else if (StrUtil.equals("false", String.valueOf(value))) {
                            data.append("b'0'");
                        } else {
                            data.append("'").append(value).append("'");
                        }
                    } else {
                        data.append("NULL");
                    }
                    data.append(", ");
                });

                sqlFileWriter.append("INSERT INTO `" + table + "`(");
                String fieldStr = field.subString(0, field.length() - 2);
                sqlFileWriter.append(fieldStr);
                sqlFileWriter.append(") VALUES (");
                String dataStr = data.subString(0, data.length() - 2);
                sqlFileWriter.append(dataStr);
                sqlFileWriter.append(");\n");
            }
        }
        sqlFileWriter.append("\n\n\n");
        sqlFileWriter.append("SET FOREIGN_KEY_CHECKS = 1;\n");
        return pathDb;
    }

这个导出的格式，既有创建数据库和数据库的插入语句。

USE innovation-platform;
/*
 --------------------------------------------------
 Target Server Type    : MySQL;
 Target Server Version : 5.7.38;
 
 Target Server Date : 2022-09-27 23:15:15;
 
 --------------------------------------------------
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `u_company1`;
CREATE TABLE `u_company1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据库自增id',
  `company_id` bigint(20) DEFAULT NULL COMMENT '公司id',
  `company_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '公司名称',
  `company_des` text COLLATE utf8_bin COMMENT '公司描述',
  `c_short` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '一句话简介',
  `crate_date` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `update_date` bigint(20) DEFAULT NULL COMMENT '更新时间',
  `status` char(1) COLLATE utf8_bin DEFAULT 'N' COMMENT '数据状态',
  `c_logo_url` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '公司logo',
  `c_addr` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '公司地址',
  `c_legal` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '公司法人/团队负责人',
  `c_type` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '公司类型',
  `c_scale` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '公司规模',
  `c_license_url` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '营业执照',
  `c_register_date` bigint(20) DEFAULT NULL COMMENT '公司注册时间',
  `c_contacts` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '联系人',
  `c_tel` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '联系电话',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父Id',
  `c_status` int(11) DEFAULT NULL COMMENT '状态 1待审核 2已审核使用中 3审核未通过 4已终止',
  `attach_file_url` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '附件链接',
  `bg_img` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '背景图',
  `city_name` varchar(40) COLLATE utf8_bin DEFAULT NULL COMMENT '市名称',
  `homepage` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '主页',
  `c_category` int(11) DEFAULT '1' COMMENT '企业类别 1表示企业，2表示团队',
  `legal_phone` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '法人/团队负责人电话',
  `legal_id` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '法人身份证',
  `industry_type` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '行业类型，多个用逗号分割',
  `business_scope` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '营业范围',
  `org_code` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '组织机构代码',
  `homepage_img` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '企业主页图片',
  `attach_file_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '附件名称',
  `legal_post` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '职务',
  `team_keyword` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '主题词',
  `support_com_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '依托单位名称',
  `support_com_addr` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT '依托单位所在地',
  `introduce_pic_url` varchar(1024) COLLATE utf8_bin DEFAULT NULL COMMENT '介绍图片',
  `introduce_video_url` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT '介绍视频',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `u_company1`(id, company_id, company_name, company_des, c_short, crate_date, update_date, status, c_logo_url, c_addr, c_legal, c_type, c_scale, c_license_url, c_register_date, c_contacts, c_tel, parent_id, c_status, attach_file_url, bg_img, city_name, homepage, c_category, legal_phone, legal_id, industry_type, business_scope, org_code, homepage_img, attach_file_name, legal_post, team_keyword, support_com_name, support_com_addr, introduce_pic_url, introduce_video_url) VALUES ('1', '1', '西北工业大学', '西北工业大学兼职。', '科创企业', '1656469107425', '1661919796888', 'N', 'http://43.138.63.233:4040/20220803/20220803-72dda257c69a438faaec5409e8ca8a67.', ' 西安市碑林区友谊西路127号12', '张三', '', '', '', '0', '张三', '17700000000', '0', '2', NULL, NULL, NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

调用实现

    @GetMapping(value = "/getDbSql")
    public void getDbSql(HttpServletRequest request, HttpServletResponse response) {

        String token = request.getHeader("token");
        if(StringUtils.isEmpty(token)){
            return;
        }
        // 穿件输入对象
        FileInputStream fis = null;
        OutputStream os = null;
        try {
            // 文件地址，真实环境是存放在数据库中的
            String filepath = exportSQL.export();
            fis = new FileInputStream(filepath);
            log.info(filepath);
            // 设置相关格式
            response.setContentType("application/force-download");
            // 设置下载后的文件名以及header
            response.addHeader("Content-disposition", "attachment;fileName="+ "db_"+ TimerUtils.getTime(System.currentTimeMillis()) +".sql");
            // 创建输出对象
            os = response.getOutputStream();
            // 常规操作
            byte[] buf = new byte[512];
            int len = 0;
            while ((len = fis.read(buf)) != -1) {
                os.write(buf, 0, len);
            }
            if (fis != null) {
                fis.close();
            }
            if (os != null) {
                os.close();
            }
            exportSQL.deleteFile(filepath);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }

数据导入

数据导入是使用mybatis方式

    /**
     * 使用ScriptRunner执行SQL脚本
     */
    @SneakyThrows
    public void doExecuteSql(String execuSql) {
        //通过数据源获取数据库链接
        Connection connection = dataSource.getConnection();
        //创建脚本执行器
        ScriptRunner scriptRunner = new ScriptRunner(connection);
        //创建字符输出流，用于记录SQL执行日志
        StringWriter writer = new StringWriter();
        PrintWriter print = new PrintWriter(writer);
        //设置执行器日志输出
        scriptRunner.setLogWriter(print);
        //设置执行器错误日志输出
        scriptRunner.setErrorLogWriter(print);
        //scriptRunner.setSendFullScript(true);
        //设置读取文件格式
        Reader reader = null;
        try {
            //获取资源文件的字符输入流
             reader = new FileReader(new File(execuSql));
        } catch (IOException e) {
            //文件流获取失败，关闭链接
            scriptRunner.closeConnection();
            return;
        }
        //执行SQL脚本
        scriptRunner.runScript(reader);
        //关闭文件输入流
        try {
            reader.close();
        } catch (IOException e) {
            System.out.println(e);
        }
        scriptRunner.closeConnection();
    }

调用方式显示

    @PostMapping(value = "/executeDbSql")
    public BaseResponse executeDbSql(@RequestParam("file") MultipartFile file) {

        String name = file.getOriginalFilename();
        long fileSize = file.getSize();
        String path = System.getProperty("user.dir") +"/"+name;
        log.info("path to upload file - " + path);
        try {
            byte[] bytes = file.getBytes();

            BufferedOutputStream stream =
                    new BufferedOutputStream(
                            new FileOutputStream(new File(path)
                            )
                    );

            stream.write(bytes);
            stream.close();

            log.info("执行sql建表语句");
            exportSQL.doExecuteSql(path);
            exportSQL.deleteFile(path);
        } catch (Exception e) {
            log.error("error save file by path " + path, e);
        }

        return BaseResponse.Ok();
    }