如何用Spring Boot将API数据映射至不同Schema的PostgreSQL数据库

1. 依赖准备 #

在Spring Boot项目的pom.xml中添加必要依赖：

<!-- MS SQL驱动 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- PostgreSQL驱动 -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- Spring Data JPA -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- 用于调用REST API -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

2. 多数据源配置 #

在application.yml中配置两个数据库的连接信息：

spring:
  datasource:
    mssql:
      url: jdbc:sqlserver://你的MS SQL地址:1433;databaseName=DatabaseA
      username: 用户名
      password: 密码
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    postgres:
      url: jdbc:postgresql://你的PostgreSQL地址:5432/DatabaseB
      username: 用户名
      password: 密码
      driver-class-name: org.postgresql.Driver
  jpa:
    mssql:
      hibernate:
        ddl-auto: none
      show-sql: true
      properties:
        hibernate:
          dialect: org.hibernate.dialect.SQLServerDialect
    postgres:
      hibernate:
        ddl-auto: update # 根据需求选择create/update/none
      show-sql: true
      properties:
        hibernate:
          dialect: org.hibernate.dialect.PostgreSQLDialect

分别编写两个数据源的配置类，注册EntityManagerFactory和TransactionManager：

// MS SQL数据源配置类
@Configuration
@EnableJpaRepositories(
        basePackages = "com.xxx.mssql.repository",
        entityManagerFactoryRef = "mssqlEntityManagerFactory",
        transactionManagerRef = "mssqlTransactionManager"
)
public class MssqlDataSourceConfig {

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

    @Primary
    @Bean(name = "mssqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mssqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("mssqlDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.xxx.mssql.entity")
                .persistenceUnit("mssql")
                .properties(jpaProperties())
                .build();
    }

    @Primary
    @Bean(name = "mssqlTransactionManager")
    public PlatformTransactionManager mssqlTransactionManager(
            @Qualifier("mssqlEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.SQLServerDialect");
        props.put("hibernate.show_sql", true);
        return props;
    }
}

// PostgreSQL数据源配置类
@Configuration
@EnableJpaRepositories(
        basePackages = "com.xxx.postgres.repository",
        entityManagerFactoryRef = "postgresEntityManagerFactory",
        transactionManagerRef = "postgresTransactionManager"
)
public class PostgresDataSourceConfig {

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

    @Bean(name = "postgresEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean postgresEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("postgresDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.xxx.postgres.entity")
                .persistenceUnit("postgres")
                .properties(jpaProperties())
                .build();
    }

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

    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        props.put("hibernate.show_sql", true);
        return props;
    }
}

3. 定义实体类 #

• MS SQL对应实体（匹配Database A字段）：

package com.xxx.mssql.entity;

@Entity
@Table(name = "你的DatabaseA表名")
public class DatabaseAEntity {
    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "age")
    private Integer age;

    // 生成getter、setter、无参/全参构造方法
}

• PostgreSQL对应实体（匹配Database B字段）：

package com.xxx.postgres.entity;

@Entity
@Table(name = "你的DatabaseB表名")
public class DatabaseBEntity {
    @Id
    @Column(name = "student_id")
    private Long studentId;

    @Column(name = "student_name")
    private String studentName;

    @Column(name = "student_age")
    private Integer studentAge;

    // 生成getter、setter、无参/全参构造方法
}

4. 编写Repository接口 #

• MS SQL侧Repository（如果直接调用已有API可跳过）：

package com.xxx.mssql.repository;

@Repository
public interface DatabaseARepository extends JpaRepository<DatabaseAEntity, Long> {
}

• PostgreSQL侧Repository：

package com.xxx.postgres.repository;

@Repository
public interface DatabaseBRepository extends JpaRepository<DatabaseBEntity, Long> {
}

5. 数据映射与同步逻辑 #

如果是调用已有REST API获取数据：

@Service
public class DataSyncService {
    private final RestTemplate restTemplate;
    private final DatabaseBRepository databaseBRepository;

    // 构造注入
    public DataSyncService(RestTemplate restTemplate, DatabaseBRepository databaseBRepository) {
        this.restTemplate = restTemplate;
        this.databaseBRepository = databaseBRepository;
    }

    public void syncFromApiToPostgres() {
        // 调用MS SQL的REST API获取数据
        DatabaseAEntity[] apiData = restTemplate.getForObject("http://你的API地址/数据接口", DatabaseAEntity[].class);
        
        if (apiData == null) return;
        
        // 字段映射并保存到PostgreSQL
        for (DatabaseAEntity source : apiData) {
            DatabaseBEntity target = new DatabaseBEntity();
            target.setStudentId(source.getId());
            target.setStudentName(source.getName());
            target.setStudentAge(source.getAge());
            
            databaseBRepository.save(target);
        }
    }
}

如果是直接从MS SQL读库同步：

@Service
public class DataSyncService {
    private final DatabaseARepository databaseARepository;
    private final DatabaseBRepository databaseBRepository;

    public DataSyncService(DatabaseARepository databaseARepository, DatabaseBRepository databaseBRepository) {
        this.databaseARepository = databaseARepository;
        this.databaseBRepository = databaseBRepository;
    }

    public void syncFromMssqlToPostgres() {
        List<DatabaseAEntity> sourceData = databaseARepository.findAll();
        
        for (DatabaseAEntity source : sourceData) {
            DatabaseBEntity target = new DatabaseBEntity();
            target.setStudentId(source.getId());
            target.setStudentName(source.getName());
            target.setStudentAge(source.getAge());
            
            databaseBRepository.save(target);
        }
    }
}

6. 触发同步 #

可以写接口手动触发：

@RestController
@RequestMapping("/sync")
public class SyncController {
    private final DataSyncService dataSyncService;

    public SyncController(DataSyncService dataSyncService) {
        this.dataSyncService = dataSyncService;
    }

    @GetMapping("/execute")
    public String executeSync() {
        dataSyncService.syncFromApiToPostgres();
        return "数据同步完成";
    }
}

也可以用定时任务自动触发：

@Service
@EnableScheduling
public class DataSyncService {
    // ... 其他代码
    
    @Scheduled(cron = "0 0 1 * * ?") // 每天凌晨1点执行
    public void scheduledSync() {
        syncFromApiToPostgres();
    }
}

内容的提问来源于stack exchange，提问作者Anmol Bajpai