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

小灰灰 2024-03-26 23:08 62阅读 0赞

#### 使用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();
            }
        }