Connect to OceanBase Database by using Spring Batch

This topic provides a Spring Batch connection example.

Environment configuration

• JDK 1.8

• OceanBase Database V3.x (MySQL/Oracle mode)

• Spring Batch integrated based on Spring Boot

Configure dependencies

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

  <!--Other Spring Boot core dependencies are omitted here. -->

Configuration files

# yml file:
spring:
  datasource:
    url: jdbc:oceanbase://10.100.xxx.xxx:18815/test
    driver-class-name: com.alipay.oceanbase.jdbc.Driver
#    username: admin@mysql
    username: admin@oracle
    password: ******
  jpa:
    hibernate:
      ddl-auto: update

Sample code

Call the database write class method to write data as follows:

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. The sample result is as follows.

   

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

   The test result is as follows:

   

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