ApsaraDB for OceanBase:Connect to an OceanBase database by using SpringBatch

Environment configuration

• JDK 1.8

• OceanBase Database V3.x (in Oracle mode)

• SpringBatch integrated based on SpringBoot

Configure dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<!--Database driver-->
<dependency>
    <groupId>com.oceanbase</groupId>
    <artifactId>oceanbase-client</artifactId>
    <version>2.4.0</version>
</dependency>
<!--Automatic table creation based on entity classes-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!--Other SpringBoot core dependencies are omitted here.    -->

Configuration file

# yml file:
spring:
  datasource:
    url: jdbc:oceanbase://xxx.xxx.xxx.xxx:1521/
    driver-class-name: com.oceanbase.jdbc.Driver
    username: a****
    password: ******
  jpa:
    hibernate:
      ddl-auto: update

Sample code

Call the database write class methods to write data by performing the following steps:

1. Create the required classes.

   Entity classes: People and PeopleDESC.

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

   //The peopledesc class after data processing, with the desc attribute added
   
   @Entity
   @Table(name = "OK_PEOPLE")
   public class PeopleDESC {
   
       @Id
       @GeneratedValue  //strategy = AUTO / IDENTITY / SEQUENCE  None of the three primary key generation strategies are supported.
       private int id;
       private String lastName;
       private String firstName;
       private String desc1;
       //Omitted
   }

   Batch processing classes: AddPeopleDescProcessor and AddDescPeopleWriter.

   //The processor that adds the desc attribute to the people class and returns the peopledesc class.
   
   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. Write peopledesc to the classes of the database.

   //Write peopledesc to the classes of the database.
   
   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. When you start the spring-data-jpa project, tables are automatically created based on the entity classes People and PeopleDESC. Sample result:

   

4. Add test data to people in batches.

   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());
       }
   }

   Test result:

   

5. Execute the batch processing method.

   public void writerTest() throws Exception {
       //Obtain the result set of the people table.
       String sql = "select * from people;";
       preparedStatement = dataSource.getConnection().prepareStatement(sql);
       ResultSet resultSet = preparedStatement.executeQuery();
       List list = new ArrayList<PeopleDESC>();
       //Process the result set by using the processor that adds the desc attribute, and encapsulate the data into a List<PeopleDESC>.
       while (resultSet.next()){
           People people = peopleRowMapper.mapRow(resultSet, resultSet.getRow());
           PeopleDESC desc = addPeopleDescProcessor.process(people);
           list.add(desc);
       }
       //Call the database write class methods to write data.
       addDescPeopleWriter.setDataSource(dataSource);
       addDescPeopleWriter.write(list);
   }