SpringBoot应用多只读数据源读取并写入新数据库方案咨询

这个需求非常典型，完全可以实现！我来给你一步步拆解具体的方案，确保只读库的高效读取和写入库的ORM简化操作：

我们可以把整个流程拆成两部分：

• 对于只读数据源：用轻量的JdbcTemplate直接做JDBC操作，避免JPA/Hibernate的额外开销，完美适配只读场景
• 对于写入目标库：用Hibernate作为JPA实现，借助它的实体映射、自动DDL、事务管理等特性简化存储逻辑

首先要在Spring Boot中配置多个数据源，区分只读库和写入库。

1.1 只读数据源配置 #

给每个只读数据源创建独立的DataSource和JdbcTemplate Bean，用@Qualifier做区分：

@Configuration
public class ReadOnlyDataSourcesConfig {

    // 第一个只读数据源
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.readonly1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSource1JdbcTemplate")
    public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    // 第二个只读数据源，同理复制上述代码修改名称即可
    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.readonly2")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSource2JdbcTemplate")
    public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

对应的application.yml配置示例：

spring:
  datasource:
    readonly1:
      url: jdbc:mysql://localhost:3306/readonly_db1
      username: read_user
      password: read_pass
      driver-class-name: com.mysql.cj.jdbc.Driver
    readonly2:
      url: jdbc:mysql://localhost:3306/readonly_db2
      username: read_user
      password: read_pass
      driver-class-name: com.mysql.cj.jdbc.Driver
    write:
      url: jdbc:mysql://localhost:3306/write_db
      username: write_user
      password: write_pass
      driver-class-name: com.mysql.cj.jdbc.Driver

1.2 写入数据源（Hibernate/JPA）配置 #

配置写入库的EntityManagerFactory和TransactionManager，指定Hibernate作为JPA实现：

@Configuration
@EnableJpaRepositories(
        basePackages = "com.yourpackage.write.repository", // 写入Repository所在包
        entityManagerFactoryRef = "writeEntityManagerFactory",
        transactionManagerRef = "writeTransactionManager"
)
public class WriteDataSourceConfig {

    @Bean(name = "writeDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "writeEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean writeEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("writeDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.write.entity") // 写入实体类所在包
                .persistenceUnit("writePU")
                .properties(hibernateProperties())
                .build();
    }

    @Bean(name = "writeTransactionManager")
    public PlatformTransactionManager writeTransactionManager(
            @Qualifier("writeEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> hibernateProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.hbm2ddl.auto", "update"); // 根据需求调整，比如create、none
        props.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
        props.put("hibernate.jdbc.batch_size", "50"); // 批量写入优化
        return props;
    }
}

给每个只读数据源编写DAO类，注入对应的JdbcTemplate，直接写SQL查询：

@Repository
public class ReadOnlyDB1DAO {
    private final JdbcTemplate jdbcTemplate;

    // 用@Qualifier指定注入的JdbcTemplate
    public ReadOnlyDB1DAO(@Qualifier("dataSource1JdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // 示例：读取数据并映射到普通POJO（无需JPA注解）
    public List<RawUserData> fetchAllUsers() {
        String sql = "SELECT id, username, email FROM user";
        return jdbcTemplate.query(sql, (rs, rowNum) -> new RawUserData(
                rs.getLong("id"),
                rs.getString("username"),
                rs.getString("email")
        ));
    }
}

// 普通POJO，仅用于数据传输
public class RawUserData {
    private Long id;
    private String username;
    private String email;

    // 全参构造器、getter/setter
}

编写JPA实体类和Repository接口，借助Hibernate简化存储：

// JPA实体类，映射到目标库的表
@Entity
@Table(name = "synced_user")
public class SyncedUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "original_username")
    private String originalUsername;
    private String email;
    private LocalDateTime syncTime;

    // 构造器、getter/setter
}

// Repository接口，继承JpaRepository获得CRUD能力
@Repository
public interface SyncedUserRepository extends JpaRepository<SyncedUser, Long> {
}

编写Service类，串联读取、转换、存储的流程：

@Service
public class DataSyncService {
    private final ReadOnlyDB1DAO readOnlyDB1DAO;
    private final ReadOnlyDB2DAO readOnlyDB2DAO;
    private final SyncedUserRepository syncedUserRepository;

    public DataSyncService(ReadOnlyDB1DAO readOnlyDB1DAO, ReadOnlyDB2DAO readOnlyDB2DAO, SyncedUserRepository syncedUserRepository) {
        this.readOnlyDB1DAO = readOnlyDB1DAO;
        this.readOnlyDB2DAO = readOnlyDB2DAO;
        this.syncedUserRepository = syncedUserRepository;
    }

    // 事务注解确保写入操作的原子性
    @Transactional(transactionManager = "writeTransactionManager")
    public void syncUserData() {
        // 从只读库1读取数据
        List<RawUserData> rawUsers = readOnlyDB1DAO.fetchAllUsers();
        // 转换为JPA实体
        List<SyncedUser> syncedUsers = rawUsers.stream()
                .map(raw -> new SyncedUser(null, raw.getUsername(), raw.getEmail(), LocalDateTime.now()))
                .toList();
        // 批量存储
        syncedUserRepository.saveAll(syncedUsers);

        // 同理处理只读库2的数据...
    }
}

• 事务隔离：写入操作一定要指定对应的事务管理器（transactionManager = "writeTransactionManager"），避免和只读库的事务混淆
• 性能优化：对于大量数据，只读库建议分页读取（用jdbcTemplate.query(sql, params, rowMapper, pageNum, pageSize)），写入库开启Hibernate批量写入（配置hibernate.jdbc.batch_size）
• 异常处理：给同步逻辑添加异常捕获，比如SQLException、转换异常，保证同步过程的健壮性，必要时可以实现重试机制
• 只读事务：如果只读操作涉及多个查询，可以添加@Transactional(readOnly = true)提升性能，同时避免意外的写操作

内容的提问来源于stack exchange，提问作者Juan Martin Buireo