使用java代码导出数据库sql文件

导出目标

导出包含数据库表结构、索引、外键以及表里面的数据

导出分析
1. 我们不需要导出所有的表 比如 _copy、_bak之类的备份表。
2. 我们不需要导出日志表（如 _log表）中的数据 只需要表结构
3. 基于SaaS架构的数据库一般会有一个租户id，比如项目id、租户id之类的，假设我们有需求，只想导出某个项目下的数据

导出工具类

 private static final Logger logger = LoggerFactory.getLogger(MySQLDatabaseTableStructureAndDataSQLExporter.class);

    /**
     * 数据库sql采集
     * @param ignoreTab 忽略采集哪些表
     * @param logTab 哪些表只采集表结构、不采集insert语句
     * @param projectIds 导出的项目id 多个逗号隔开【方法底层去判断数据库表是否有 project_id参数 如果有导出insert语句时就只导出指定项目下的数据】
     */
    private static void collectSql(List<String> ignoreTab, List<String> logTab, String projectIds) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/数据库?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String username = "你的用户名";
        String password = "你的密码";
        String databaseName = "数据库"; // 修改为你要导出的数据库名

        String outputFilePath = databaseName+"output.sql"; // 修改为你想要输出的 SQL 文件路径

        long begin = System.currentTimeMillis();
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tableResultSet = metaData.getTables(databaseName, null, null, new String[]{"TABLE"});
            logger.info("数据库sql采集 开始 ");
            try (FileWriter fileWriter = new FileWriter(outputFilePath)) {
                fileWriter.write("SET FOREIGN_KEY_CHECKS = 0;" + "\n");	// 外键约束暂时不校验
                while (tableResultSet.next()) {
                    String tableName = tableResultSet.getString("TABLE_NAME");

                    if (hasRegex(tableName, ignoreTab)) {
                        // 表名含有 ignoreTab 中的字符，则忽略
                        continue;
                    }
                    long w1 = System.currentTimeMillis();
                    logger.info("数据库sql采集 tableName={} ", tableName);

                    boolean isLoginTab = hasRegex(tableName, logTab);    // 日志表里面的内容不同步

                    fileWriter.write("-- Table: " + tableName + "\n");

                    // 获取建表语句
                    StringBuilder createTableQuery = new StringBuilder();
                    Statement statement = connection.createStatement();
                    ResultSet resultSet = statement.executeQuery("SHOW CREATE TABLE " + tableName);
                    resultSet.next();
                    createTableQuery.append(resultSet.getString("Create Table")).append(";");
                    createTableQuery.append(System.lineSeparator());
                    fileWriter.write(createTableQuery.toString());

                    if (isLoginTab){
                        logger.info("数据库sql采集 tableName={}&耗时={}ms", tableName, (System.currentTimeMillis()-w1));
                        continue;
                    }


                    // 获取是否存在 "project_id" 字段
                    boolean hasProjectIdColumn = checkProjectIdColumn(connection, tableName);

                    String selectQuery ;
                    ResultSet dataResultSet;
                    PreparedStatement preparedStatement = null;
                    if (hasProjectIdColumn) {
                        selectQuery = "SELECT * FROM " + tableName + " WHERE project_id in (?)";
                        preparedStatement = connection.prepareStatement(selectQuery);
                        preparedStatement.setString(1, projectIds+""); // 修改为实际的 project_id   有些默认配置是项目id为0的
                        dataResultSet = preparedStatement.executeQuery();
                    } else{
                        selectQuery = "SELECT * FROM " + tableName;
                        dataResultSet = statement.executeQuery(selectQuery);
                    }

                    while (dataResultSet.next()) {
                        ResultSetMetaData rsMetaData = dataResultSet.getMetaData();
                        int columnCount = rsMetaData.getColumnCount();

                        StringBuilder insertQuery = new StringBuilder("INSERT INTO " + tableName + " VALUES (");

                        for (int i = 1; i <= columnCount; i++) {
                            Object value = dataResultSet.getObject(i);
                            if (i > 1) {
                                insertQuery.append(", ");
                            }
                            if (value == null) {
                                insertQuery.append("null");
                            }else {
                                if (rsMetaData.getColumnTypeName(i).equalsIgnoreCase("JSON")) {
                                    // Handle JSON type data
                                    String jsonValue = escapeJsonSpecialCharacters(value.toString());
                                    insertQuery.append("'").append(jsonValue).append("'");
                                } else {
                                    if (value instanceof Boolean) {
                                        insertQuery.append("'").append((Boolean)value ? 1 : 0).append("'");
                                    }else {
                                        insertQuery.append("'").append(value).append("'");
                                    }

                                }

                            }
                        }

                        insertQuery.append(");\n");
                        fileWriter.write(insertQuery.toString());
                    }

                    dataResultSet.close();
                    statement.close();
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    logger.info("数据库sql采集 tableName={}&耗时={}ms", tableName, (System.currentTimeMillis()-w1));

                }

                fileWriter.write("SET FOREIGN_KEY_CHECKS = 1;");

            } catch (IOException e) {
                e.printStackTrace();
            }

            tableResultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        logger.info("数据库sql采集 结束 耗时={}ms ", (System.currentTimeMillis() - begin));
    }

    private static boolean hasRegex(String tableName, List<String> ignoreTab) {
        if (CollectionUtils.isEmpty(ignoreTab)) {
            return false;
        }

        for (String s : ignoreTab) {
            if (tableName.contains(s)){
                return true;
            }
        }

        return false;
    }

    private static String escapeJsonSpecialCharacters(String jsonValue) {
        return jsonValue
                .replace("\n", "\\n")
                .replace("\t", "\\t")
                .replace("\r", "\\r")
                .replace("\b", "\\b")
                .replace("\f", "\\f")
                .replace("\\", "\\\\")
                .replace("'", "\\'");
    }

    private static boolean checkProjectIdColumn(Connection connection, String tableName) throws SQLException {
        String query = "SHOW COLUMNS FROM " + tableName + " LIKE 'project_id'";
        try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        }
    }