Spring、SpringBoot导出数据库Sql文件、获取数据库表创建语句，查看数据库表字段

 

 

示例代码

数据库查询方法，可以根据自己整合的框架来写即可

import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 数据库备份
 */
public interface DateBaseBackupMapper {

    /**
     * 获取所有表list
     * @return
     */
    @Select(" select table_name from information_schema.tables\n" +
            "where table_schema = (select database())")
    List<String> getTableList();


    /**
     * 获取每个表的创建语句
     * @param tableName
     * @return
     */
    @Select("<script>  show create table ${tableName} </script>")
    Map getTableDDL(String tableName);


    /**
     * 查看表字段
     * @param tableName
     * @return
     */
    @Select("  desc ${tableName};")
    List<Map> getTableField(String tableName);

    /**
     * 查询表数据
     * @param tableName
     * @return
     */
    @Select(" select * from ${tableName}")
    List<Map> getTableData(String tableName);
}

 

 这下面是伪代码，可以根据自己的来处理

import com.ruoyi.generator.mapper.DateBaseBackupMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.math.BigInteger;
import java.util.*;

/**
 * 数据库备份处理
 */
@Controller
@Slf4j
public class SysDataBackupController {

    /**
     * 数据库备份路径
     */
    public static final String BACKUP_PATH = "/db/";

    /**
     * 数据库备份文本前缀
     */
    public static String ONESQL_PREFIX = "";

    private static String SUFFIX = "sql";
    private static String SPLIT = "`";
    private static String BR = "\r\n";
    private static String SLASH = "/";
    private static String SPACE = " ";
    private static String BRANCH = ";";
    private static String INSERT_INTO = " INSERT INTO ";
    private static String VALUES = "VALUES";
    private static String LEFTBRACE = "(";
    private static String RIGHTBRACE = ")";
    private static String QUOTES = "'";
    private static String COMMA = ",";
    private static String DISABLEFOREIGN = "SET FOREIGN_KEY_CHECKS = 0;\r\n";
    private static String ABLEFOREIGN = "SET FOREIGN_KEY_CHECKS = 1;\r\n";


    @Resource
    private DateBaseBackupMapper dateBaseBackupMapper;

    @RequestMapping("/mysql/o_backup")
    public String backup() {
        try {
            File directory = new File("");//参数为空
            String coursePath = directory.getCanonicalPath();
            log.info(">>>>>>>>>>>>项目路径：{}", coursePath);
            File parentFile = new File(coursePath).getParentFile();
            String backPath = parentFile.getCanonicalPath() + BACKUP_PATH;
            log.info(">>>>>>>>>>>>>db backup path:{}", backPath);
            File backDirectory = new File(backPath);
            if (!backDirectory.exists()) {
                backDirectory.mkdir();
            }
            //备份文件路径名称
            String backFilePath = backPath + SLASH + DateFormatUtils.format(new Date(), "yyyyMMdd") + "."
                    + SUFFIX;
            File file = new File(backFilePath);

            FileOutputStream out;
            OutputStreamWriter writer = null;

            out = new FileOutputStream(file);
            writer = new OutputStreamWriter(out, "utf8");
            writer.write(ONESQL_PREFIX + DISABLEFOREIGN);
            //获取所有的表
            List<String> tableList = dateBaseBackupMapper.getTableList();

            //遍历每个表 追加内容
            for (String tableName : tableList) {
                log.info(">>>>>>>>>>>backup table:{}", tableName);
                backupTable(writer, tableName);
            }

            writer.write(ONESQL_PREFIX + ABLEFOREIGN);
            writer.close();
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }


    private String backupTable(OutputStreamWriter writer, String tableName) throws IOException {
        writer.write(createOneTableSql(tableName));
        writer.flush();
        return tableName;
    }

    /**
     * 创建表语句
     *
     * @param tableName
     * @return
     */
    private String createOneTableSql(String tableName) {
        StringBuffer buffer = new StringBuffer();
        Object[] oneResult;
        buffer.append(ONESQL_PREFIX + "DROP TABLE IF EXISTS "
                + tableName + BRANCH + BR);

        Map tableDDL = dateBaseBackupMapper.getTableDDL(tableName);
        buffer.append(ONESQL_PREFIX
                + tableDDL.get("Create Table") + BRANCH + BR
                + ONESQL_PREFIX);
        List<Object[]> results = createTableData(tableName);
        for (int i = 0; i < results.size(); i++) {
            // 备份数据、、
            oneResult = results.get(i);
            buffer.append(createOneInsertSql(oneResult, tableName));
        }
        return buffer.toString();
    }

    /**
     * 插入表数据
     *
     * @param tableName
     * @return
     */
    public List<Object[]> createTableData(String tableName) {
        List<Map> tableField = dateBaseBackupMapper.getTableField(tableName);
        List<Map> tableData = dateBaseBackupMapper.getTableData(tableName);

        List<Object[]> results = new LinkedList<>();
        if (CollectionUtils.isEmpty(tableData)) {
            return results;
        }

        for (int i = 0; i < tableData.size(); i++) {
            Object[] oneResult = new Object[tableField.size()];
            for (int j = 0; j < tableField.size(); j++) {
                Map fieldMap = tableField.get(j);
                Object field = fieldMap.get("Field");
                oneResult[j] = tableData.get(i).get(field);
            }
            results.add(oneResult);
        }

        return results;
    }

    private String createOneInsertSql(Object[] oneResult, String tableName) {
        StringBuffer buffer = new StringBuffer();
        buffer.append(ONESQL_PREFIX + INSERT_INTO + SPLIT + tableName
                + SPLIT + SPACE + VALUES + LEFTBRACE);
        for (int j = 0; j < oneResult.length; j++) {
            if (oneResult[j] != null) {
                if (oneResult[j] instanceof Date) {
                    buffer.append(QUOTES + oneResult[j] + QUOTES);
                } else if (oneResult[j] instanceof String) {
                    buffer.append(QUOTES
                            + replaceKeyString((String) oneResult[j])
                            + QUOTES);
                } else if (oneResult[j] instanceof Boolean) {
                    if ((Boolean) oneResult[j]) {
                        buffer.append(1);
                    } else {
                        buffer.append(0);
                    }
                }else if (oneResult[j].getClass().toString().equalsIgnoreCase("class [B")){
                    //处理blob类型数据
                    buffer.append("0x")
                            .append(new BigInteger(1,byte[].class.cast(oneResult[j])).toString(16).toUpperCase());
                }else {
                    buffer.append(oneResult[j]);
                }
            } else {
                buffer.append(oneResult[j]);
            }
            buffer.append(COMMA);
        }
        buffer = buffer.deleteCharAt(buffer.lastIndexOf(COMMA));
        buffer.append(RIGHTBRACE + BRANCH + BR);
        return buffer.toString();
    }

    // 将""和'转义
    public static String replaceKeyString(String str) {
        if (containsKeyString(str)) {
            return str.replace("'", "\\'").replace("\"", "\\\"").replace("\r",
                    "\\r").replace("\n", "\\n").replace("\t", "\\t").replace(
                    "\b", "\\b").replace("\f", "\\f");
        } else {
            return str;
        }
    }

    public static boolean containsKeyString(String str) {
        if (StringUtils.isBlank(str)) {
            return false;
        }
        if (str.contains("'") || str.contains("\"") || str.contains("\r")
                || str.contains("\n") || str.contains("\t")
                || str.contains("\b") || str.contains("\f")) {
            return true;
        }
        return false;
    }
}