ApsaraDB for OceanBase：SpringBatch 串連 OceanBase 資料庫

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<!--資料庫驅動-->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<!--串連池-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!--基於實體類自動建表-->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!--spring-boot核心等其他依賴省略。。。。-->

#yml 檔案：
spring:
  datasource:
    url: jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/test
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: a****
    password: ******
  jpa:
    hibernate:
      ddl-auto: update

範例程式碼

通過調用資料庫寫入類方法寫入資料，具體步驟如下：

1. 建立需要的類。

   實體類：People 和 PeopleDESC。

   //基本 people 類
   
   @Entity
   @Table(name = "PEOPLE")
   public class People {
       @Id
       @GeneratedValue
       private int id;
       private String lastName;
       private String firstName;
       //略
   }

   //加工後的 peopledesc 類(增加 desc 屬性)
   
   @Entity
   @Table(name = "OK_PEOPLE")
   public class PeopleDESC {
   
       @Id
       @GeneratedValue  //strategy = AUTO / IDENTITY / SEQUENCE  三種主鍵建置原則均不支援
       private int id;
       private String lastName;
       private String firstName;
       private String desc1;
       //略
   }

   批處理類：AddPeopleDescProcessor 和 AddDescPeopleWriter。

   //用於給 people 添加 desc 屬性返回一個 peopledesc 類的處理器
   
   public class AddPeopleDescProcessor implements ItemProcessor<People, PeopleDESC> {
   
       public PeopleDESC process(People item) throws Exception {
   
           return new PeopleDESC(item.getId() , 
                                 item.getLastName(), 
                                 item.getFirstName(), 
                                 Thread.currentThread().getName());
       }
   }

2. 將 peopledesc 寫入資料庫的類。

   //將 peopledesc 寫入資料庫的類
   
   public class AddDescPeopleWriter implements ItemWriter<PeopleDESC> {
       private JdbcTemplate jdbcTemplate;
       public void setDataSource(DataSource dataSource) {
           this.jdbcTemplate = new JdbcTemplate(dataSource);
       }
   
       public JdbcTemplate getJdbcTemplate() {
           return jdbcTemplate;
       }
   
       public void write(List<? extends PeopleDESC> items) throws Exception {
           for (PeopleDESC peopleDESC : items) {
               this.jdbcTemplate
                   .update("insert into ok_people (id,first_name,last_name,desc1) values (?, ?, ?, ?)",
                           peopleDESC.getId(), peopleDESC.getFirstName(),
                           peopleDESC.getLastName(), peopleDESC.getDesc());
           }
       }
   }

3. 啟動工程 spring-data-jpa 會根據實體類 People 及 PeopleDESC 自動建表，樣本結果如下。

   

4. 大量新增測試資料至 people。

   public void addData(){
       jdbcTemplate = new JdbcTemplate(dataSource);
       StringBuilder stringBuilder = new StringBuilder("");
       for (int i = 1 ; i<=100 ; i++){
           stringBuilder.append("insert into people values ("+i+",'first_test"+i+"','last_test"+i+"');");
           jdbcTemplate.execute(stringBuilder.toString());
           stringBuilder.delete(0, stringBuilder.length());
       }
   }

   測試下結果，顯示如下：

   

5. 執行批處理方法。

   public void writerTest() throws Exception {
       //獲得 people 表的結果集
       String sql = "select * from people;";
       preparedStatement = dataSource.getConnection().prepareStatement(sql);
       ResultSet resultSet = preparedStatement.executeQuery();
       List list = new ArrayList<PeopleDESC>();
       //基於添加 desc 屬性的處理器對結果集進行加工，並封裝成一個 List<PeopleDESC>
       while (resultSet.next()){
           People people = peopleRowMapper.mapRow(resultSet, resultSet.getRow());
           PeopleDESC desc = addPeopleDescProcessor.process(people);
           list.add(desc);
       }
       //調用資料庫寫入類方法寫入資料
       addDescPeopleWriter.setDataSource(dataSource);
       addDescPeopleWriter.write(list);
   }