ava代码将主库表结构、数据同步到从库

方案一：使用JDBC直接同步

import java.sql.*;
import java.util.*;

public class DatabaseSync {
    
    private Connection sourceConn;
    private Connection targetConn;
    
    public DatabaseSync(String sourceUrl, String sourceUser, String sourcePassword,
                       String targetUrl, String targetUser, String targetPassword) throws SQLException {
        this.sourceConn = DriverManager.getConnection(sourceUrl, sourceUser, sourcePassword);
        this.targetConn = DriverManager.getConnection(targetUrl, targetUser, targetPassword);
    }
    
    /**
     * 同步所有表结构
     */
    public void syncTableStructures() throws SQLException {
        List<String> tableNames = getSourceTableNames();
        
        for (String tableName : tableNames) {
            // 获取表结构DDL
            String createTableDDL = getTableDDL(tableName);
            
            // 在目标库创建表
            createTableInTarget(createTableDDL, tableName);
            
            System.out.println("已同步表结构: " + tableName);
        }
    }
    
    /**
     * 同步所有表数据
     */
    public void syncTableData() throws SQLException {
        List<String> tableNames = getSourceTableNames();
        
        for (String tableName : tableNames) {
            syncSingleTableData(tableName);
            System.out.println("已同步表数据: " + tableName);
        }
    }
    
    /**
     * 获取源库所有表名
     */
    private List<String> getSourceTableNames() throws SQLException {
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = sourceConn.getMetaData();
        
        try (ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        }
        return tables;
    }
    
    /**
     * 获取表的创建DDL语句（MySQL示例）
     */
    private String getTableDDL(String tableName) throws SQLException {
        String sql = "SHOW CREATE TABLE " + tableName;
        try (Statement stmt = sourceConn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getString(2); // 第二列是CREATE TABLE语句
            }
        }
        throw new SQLException("无法获取表 " + tableName + " 的DDL");
    }
    
    /**
     * 在目标库创建表
     */
    private void createTableInTarget(String ddl, String tableName) throws SQLException {
        // 先删除目标表（如果存在）
        try (Statement stmt = targetConn.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS " + tableName);
        }
        
        // 创建新表
        try (Statement stmt = targetConn.createStatement()) {
            stmt.execute(ddl);
        }
    }
    
    /**
     * 同步单个表的数据
     */
    private void syncSingleTableData(String tableName) throws SQLException {
        // 清空目标表数据
        try (Statement stmt = targetConn.createStatement()) {
            stmt.execute("TRUNCATE TABLE " + tableName);
        }
        
        // 查询源表数据
        String selectSQL = "SELECT * FROM " + tableName;
        String insertSQL = buildInsertSQL(tableName);
        
        try (Statement sourceStmt = sourceConn.createStatement();
             ResultSet rs = sourceStmt.executeQuery(selectSQL);
             PreparedStatement pstmt = targetConn.prepareStatement(insertSQL)) {
            
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            
            int batchSize = 1000;
            int count = 0;
            
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    pstmt.setObject(i, rs.getObject(i));
                }
                pstmt.addBatch();
                
                if (++count % batchSize == 0) {
                    pstmt.executeBatch();
                }
            }
            
            // 执行剩余的批次
            pstmt.executeBatch();
        }
    }
    
    /**
     * 构建INSERT语句
     */
    private String buildInsertSQL(String tableName) throws SQLException {
        DatabaseMetaData metaData = sourceConn.getMetaData();
        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        
        try (ResultSet rs = metaData.getColumns(null, null, tableName, null)) {
            boolean first = true;
            while (rs.next()) {
                if (!first) {
                    columns.append(", ");
                    values.append(", ");
                }
                String columnName = rs.getString("COLUMN_NAME");
                columns.append(columnName);
                values.append("?");
                first = false;
            }
        }
        
        return String.format("INSERT INTO %s (%s) VALUES (%s)", 
                           tableName, columns.toString(), values.toString());
    }
    
    /**
     * 同步特定表
     */
    public void syncSpecificTable(String tableName) throws SQLException {
        System.out.println("开始同步表: " + tableName);
        
        // 同步表结构
        String ddl = getTableDDL(tableName);
        createTableInTarget(ddl, tableName);
        
        // 同步数据
        syncSingleTableData(tableName);
        
        System.out.println("完成同步表: " + tableName);
    }
    
    public void close() throws SQLException {
        if (sourceConn != null) sourceConn.close();
        if (targetConn != null) targetConn.close();
    }
    
    public static void main(String[] args) {
        try {
            // 加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 配置数据库连接
            DatabaseSync sync = new DatabaseSync(
                "jdbc:mysql://localhost:3306/source_db",
                "root",
                "password",
                "jdbc:mysql://localhost:3306/target_db",
                "root",
                "password"
            );
            
            // 同步所有表
            sync.syncTableStructures();
            sync.syncTableData();
            
            // 或者只同步特定表
            // sync.syncSpecificTable("users");
            
            sync.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

方案二：使用Spring Boot + MyBatis

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import javax.annotation.PostConstruct;
import java.util.List;
import java.util.Map;

@Service
public class DatabaseSyncService {
    
    @Autowired
    private JdbcTemplate sourceJdbcTemplate;  // 主库
    
    @Autowired
    private JdbcTemplate targetJdbcTemplate;  // 从库
    
    /**
     * 同步指定表
     */
    public void syncTable(String tableName) {
        // 1. 同步表结构
        syncTableStructure(tableName);
        
        // 2. 同步数据
        syncTableData(tableName);
    }
    
    /**
     * 同步表结构
     */
    private void syncTableStructure(String tableName) {
        // 获取源表DDL
        String ddl = sourceJdbcTemplate.queryForObject(
            "SHOW CREATE TABLE " + tableName, 
            (rs, rowNum) -> rs.getString(2)
        );
        
        // 删除目标表
        targetJdbcTemplate.execute("DROP TABLE IF EXISTS " + tableName);
        
        // 创建目标表
        targetJdbcTemplate.execute(ddl);
    }
    
    /**
     * 同步表数据
     */
    private void syncTableData(String tableName) {
        // 查询源表所有数据
        List<Map<String, Object>> rows = sourceJdbcTemplate.queryForList(
            "SELECT * FROM " + tableName
        );
        
        if (rows.isEmpty()) return;
        
        // 获取列名
        Map<String, Object> firstRow = rows.get(0);
        StringBuilder columns = new StringBuilder();
        StringBuilder placeholders = new StringBuilder();
        
        boolean first = true;
        for (String column : firstRow.keySet()) {
            if (!first) {
                columns.append(", ");
                placeholders.append(", ");
            }
            columns.append(column);
            placeholders.append("?");
            first = false;
        }
        
        // 构建INSERT语句
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", 
                                  tableName, columns, placeholders);
        
        // 批量插入数据
        targetJdbcTemplate.batchUpdate(sql, rows.stream()
            .map(row -> rows.stream()
                .map(r -> r.values().toArray())
                .toArray(Object[][]::new))
            .toArray(Object[][]::new));
    }
    
    /**
     * 增量同步（根据时间戳）
     */
    public void incrementalSync(String tableName, String timestampColumn) {
        // 获取目标库最后更新时间
        Timestamp lastSyncTime = targetJdbcTemplate.queryForObject(
            String.format("SELECT MAX(%s) FROM %s", timestampColumn, tableName),
            Timestamp.class
        );
        
        String querySql;
        if (lastSyncTime == null) {
            querySql = "SELECT * FROM " + tableName;
        } else {
            querySql = String.format("SELECT * FROM %s WHERE %s > ?", 
                                    tableName, timestampColumn);
        }
        
        // 查询新增数据并插入
        // ... 实现类似上面的数据同步逻辑
    }
}

方案三：使用Apache Commons DbUtils

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DbUtilsSync {
    
    private QueryRunner sourceRunner;
    private QueryRunner targetRunner;
    
    public void syncTable(String tableName) throws SQLException {
        // 获取表结构
        String ddl = getTableDDL(tableName);
        
        // 在目标库创建表
        targetRunner.update("DROP TABLE IF EXISTS " + tableName);
        targetRunner.update(ddl);
        
        // 同步数据
        List<Map<String, Object>> data = sourceRunner.query(
            "SELECT * FROM " + tableName,
            new MapListHandler()
        );
        
        if (!data.isEmpty()) {
            insertData(tableName, data);
        }
    }
    
    private void insertData(String tableName, List<Map<String, Object>> data) throws SQLException {
        // 构建INSERT语句
        Map<String, Object> firstRow = data.get(0);
        StringBuilder sql = new StringBuilder("INSERT INTO ")
            .append(tableName)
            .append(" (");
        
        // 添加列名
        boolean first = true;
        for (String column : firstRow.keySet()) {
            if (!first) sql.append(", ");
            sql.append(column);
            first = false;
        }
        
        sql.append(") VALUES (");
        
        // 添加占位符
        first = true;
        for (int i = 0; i < firstRow.size(); i++) {
            if (!first) sql.append(", ");
            sql.append("?");
            first = false;
        }
        sql.append(")");
        
        // 批量插入
        Object[][] params = new Object[data.size()][];
        for (int i = 0; i < data.size(); i++) {
            params[i] = data.get(i).values().toArray();
        }
        
        targetRunner.batch(sql.toString(), params);
    }
    
    private String getTableDDL(String tableName) throws SQLException {
        // 实现获取DDL的逻辑
        return "";
    }
}

配置文件和依赖

pom.xml (Maven)

<dependencies>
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- Spring Boot JDBC -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    <!-- Apache Commons DbUtils -->
    <dependency>
        <groupId>commons-dbutils</groupId>
        <artifactId>commons-dbutils</artifactId>
        <version>1.7</version>
    </dependency>
</dependencies>

application.yml (Spring Boot)

spring:
  datasource:
    source:
      url: jdbc:mysql://localhost:3306/source_db
      username: root
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
    target:
      url: jdbc:mysql://localhost:3306/target_db
      username: root
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

增强功能建议

增量同步：添加时间戳字段，只同步新增或修改的数据
断点续传：记录同步进度，支持从断点继续
数据校验：同步后校验数据一致性
并发同步：多线程同步多个表
错误处理：记录同步失败的记录，支持重试
性能优化：使用连接池，调整批量大小

使用注意事项

数据库连接：确保有足够的数据库连接权限
大数据量：对于大表，建议分批次同步
外键约束：同步时可能需要禁用外键检查
事务处理：考虑使用事务保证数据一致性
网络延迟：跨机房同步需要考虑网络因素

选择哪种方案取决于你的具体需求：

简单场景：方案一
Spring Boot项目：方案二
需要更轻量级：方案三

posted @ 2025-12-18 14:14 红尘沙漏阅读(3) 评论(0) 收藏举报

刷新页面返回顶部

红尘沙漏

ava代码将主库表结构、数据同步到从库

方案一：使用JDBC直接同步

方案二：使用Spring Boot + MyBatis

方案三：使用Apache Commons DbUtils

配置文件和依赖

pom.xml (Maven)

application.yml (Spring Boot)

增强功能建议

使用注意事项

公告