使用springboot结合线程池，分批分页读取数据库表源数据，经过逻辑加工处理后，生成结果数据插入另一张结果表

1. 配置数据库和线程池

首先，在application.yml或application.properties中配置数据源和线程池。

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/your_database
    username: your_username
    password: your_password
    driver-class-name: com.mysql.cj.jdbc.Driver

  task:
    execution:
      pool:
        core-size: 10
        max-size: 20
        queue-capacity: 50

2.添加MyBatis的配置：

mybatis:
  type-aliases-package: com.example.yourapp.model
  mapper-locations: classpath:mapper/*.xml

3. 定义实体类

public class SourceData {
    private Long id;
    private String data;
    // getters and setters
}

public class ResultData {
    private Long id;
    private String processedData;
    // getters and setters
}

4.定义源数据查询的service类

@Service
public class SourceDataService {
  @Resource
  private SourceDataMapper sourceDataMapper;

  public List<SourceData> getSourceData(int offset, int pageSize) {
    return sourceDataMapper.findAll(offset, pageSize);
  }
}

5. 定义Mapper接口和XML文件

创建Mapper接口：获取源数据

@Mapper
public interface SourceDataMapper extends BaseMapperPlus<SourceDataMapper,SourceData> {

   List<SourceData> findAll(@Param("offset") int offset, @Param("pageSize") int pageSize);

编写mapper.xml

 <select id="findAll" resultType="com.jdyx.cgp_demo.domain.SourceData">
    SELECT *
    FROM you_sourceDate_table
      LIMIT #{pageSize} OFFSET #{offset}

  </select>

创建Mapper接口：插入数据

@Mapper
public interface ResultDataMapper {

  void insertBatch(@Param("list") List<ResultData> resultTableDataList);

}

编写mapper.xml

<insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO result_data (processed_data) VALUES 
        <foreach collection='list' item='item' index='index' separator=','>
        (#{item.processedData})
        </foreach>"
  </insert>

6. 编写分页读取服务

编写一个服务类，用于分页读取源数据。

@Service
public class SourceDataService {
    @Autowired
    private SourceDataMapper sourceDataMapper;

    public List<SourceData> getSourceData(int offset, int pageSize) {
        return sourceDataMapper.findAll(offset, pageSize);
    }
}

7. 处理逻辑

在服务类中实现数据的逻辑加工处理。

@Service
public class DataProcessingService {
    public ResultData processData(SourceData sourceData) {
        // 实现你的逻辑加工
        String processedData = "Processed: " + sourceData.getData();
        ResultData resultData = new ResultData();
        resultData.setProcessedData(processedData);
        return resultData;
    }
}

8. 使用线程池并发处理

使用线程池实现并发处理，将处理后的数据批量插入结果表。

@Service
public class BatchProcessingService {
    @Autowired
    private SourceDataService sourceDataService;

    @Autowired
    private DataProcessingService dataProcessingService;

    @Autowired
    private ResultDataMapper resultDataMapper;

    @Autowired
    private ThreadPoolTaskExecutor taskExecutor;

    public void processInBatches(int pageSize) {
        int pageNumber = 0;
        List<SourceData> sourceDataList;

        do {
            int offset = pageNumber * pageSize;
            sourceDataList = sourceDataService.getSourceData(offset, pageSize);

            List<Future<ResultData>> futures = new ArrayList<>();

            for (SourceData sourceData : sourceDataList) {
                futures.add(taskExecutor.submit(() -> dataProcessingService.processData(sourceData)));
            }

            List<ResultData> resultDataList = new ArrayList<>();
            for (Future<ResultData> future : futures) {
                try {
                    resultDataList.add(future.get());
                } catch (InterruptedException | ExecutionException e) {
                    // 处理异常
                    e.printStackTrace();
                }
            }

            if (!resultDataList.isEmpty()) {
                resultDataMapper.insertBatch(resultDataList);
            }

            pageNumber++;

        } while (!sourceDataList.isEmpty());
    }
}

9. 调用批处理服务

最后，在你的主应用或调度程序中调用批处理服务。

 

@SpringBootApplication
public class YourApplication {

    @Autowired
    private BatchProcessingService batchProcessingService;

    public static void main(String[] args) {
        SpringApplication.run(YourApplication.class, args);
    }

    @EventListener(ApplicationReadyEvent.class)
    public void processDataAfterStartup() {
        batchProcessingService.processInBatches(100); // 设置每页的大小
    }
}

当然，你也可以不在这里设置每页的大小，而采取手动输入每页的大小

设置控制类调用你的processInBatches接口

@SaIgnore
@PostMapping("/processInBatches")
public R<Void> processInBatches(@RequestParam (name = "pageSize" )  int pageSize) {
  batchProcessingService.processInBatches(pageSize);
  return R.ok();
}

10.依赖和配置文件

确保在你的pom.xml中包含了MyBatis和Spring Boot的相关依赖：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- 其他依赖 -->
</dependencies>