Connect to OceanBase Cloud using SpringBoot

This topic describes how to build an application by using the SpringBoot framework and OceanBase Cloud. The application can perform basic operations such as creating a table, inserting data, and querying data.

Download the Java OceanBase SpringBoot sample project

Prerequisites

• You have registered an account for OceanBase Cloud and created an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.
• You have installed JDK 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the connection information of the OceanBase Cloud database.
2. Import the java-oceanbase-springboot project into IntelliJ IDEA.
3. Modify the database connection information in the java-oceanbase-springboot project.
4. Run the java-oceanbase-springboot project.

Step 1: Obtain the connection information of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the following URL with the information of the created OceanBase Cloud database.

   Note

   The URL information is required in the application.properties file.

   jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&useSSL=false&useUnicode=true&characterEncoding=utf-8
   

   Parameter description:

   • host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • port: the connection port of the OceanBase Cloud database. The default value is 3306.

   • schema_name: the name of the schema to be accessed.

   • user_name: the account for accessing the database.

   • password: the password of the account.

   • useSSL=false&useUnicode=true&characterEncoding=utf-8: additional connection attributes.

     • useSSL: specifies whether to use SSL/TLS when connecting. Default value: false.
     • useUnicode: specifies the encoding and decoding format of characters. Default value: true.
     • characterEncoding: specifies the character encoding supported by the database URL. Default value: utf8.

For more information about the URL parameters, see Database URL.

Step 2: Import the java-oceanbase-springboot project into IntelliJ IDEA

1. Open IntelliJ IDEA and choose File > Open....

   

2. In the Open File or Project window that appears, select the project file and click OK.

3. IntelliJ IDEA automatically identifies various files in the project and displays the project structure, file list, module list, and dependency relationships in the Project tool window. The Project tool window is usually located on the left side of the IntelliJ IDEA interface and is open by default. If it is closed, you can click View > Tool Windows > Project in the menu bar or press the shortcut key Alt + 1 to reopen it.

   Note

   When you import a project into IntelliJ IDEA, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies in the file, and adds them to the project.

4. View the project.

   

Step 3: Modify the database connection information in the java-oceanbase-springboot project

Modify the database connection information in the application.properties file based on the information obtained in Step 1: Obtain the connection information of the OceanBase Cloud database.

Example:

• The name of the database driver is com.mysql.cj.jdbc.Driver.
• The connection address of the OceanBase Cloud database is t5******.********.oceanbase.cloud.
• The access port is 3306.
• The name of the schema to be accessed is test.
• The tenant connection account is mysql001.
• The password is ******.

Sample code:

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:oceanbase://t5******.********.oceanbase.cloud:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=mysql001
spring.datasource.password=******

Step 4: Run the java-oceanbase-springboot project

Running path

1. In the project structure, go to src > test > java and find the TestSpringbootApplicationTests.java file.
2. In the tool menu bar, choose Run(U) > Run > TestSpringbootApplicationTests, or click the green triangle in the upper-right corner to run.
3. View the project logs and output results in the IDEA console.

Running result

test_springboot delete successfully!
test_springboot create successfully!
user = User{id=2, name='update'}
User{id=2, name='update'}
User{id=3, name='insert3'}
User{id=4, name='insert4'}
User{id=5, name='insert5'}
User{id=6, name='insert6'}
User{id=7, name='insert7'}
User{id=8, name='insert8'}
User{id=9, name='insert9'}
User{id=10, name='insert10'}

FAQ

1. Connection timeout

If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. Character set

To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection

To enable an SSL connection to the cloud database of OceanBase Cloud, add the following parameter to the JDBC URL:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in the account password

If the username or password contains special characters (such as #), you need to URL-encode them:

String encodedPassword = URLEncoder.encode(password, "UTF-8");

Project code

Click java-oceanbase-springboot to download the project code, which is a compressed file named java-oceanbase-springboot.

After decompressing the file, you will find a folder named java-oceanbase-springboot. The directory structure is as follows:

│--pom.xml
│
├─.idea
├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─oceanbase
│  │  │          └─testspringboot
│  │  │              │--TestSpringbootApplication.java
│  │  │              │
│  │  │              ├─dao
│  │  │              │  │--UserDao.java
│  │  │              │  │
│  │  │              │  └─impl
│  │  │              │      └─--UserDaoImpl.java
│  │  │              │
│  │  │              └─entity
│  │  │                  └─--User.java
│  │  │
│  │  └─resources
│  │      └─--application.properties
│  │
│  └─test
│      └─java
│          └─com
│              └─oceanbase
│                  └─testspringboot
│                      └─--TestSpringbootApplicationTests.java
│
└─target

File description:

• pom.xml: the configuration file of the Maven project, which contains information about the project's dependencies, plugins, and build process.
• .idea: a directory used by the IDE (Integrated Development Environment) to store project-related configuration information.
• src: a directory typically used to store the source code of the project.
• main: a directory that stores the main source code and resource files.
• java: a directory that stores the Java source code.
• com: the root directory for storing Java packages.
• oceanbase: the root directory for storing the project.
• testspringboot: the root directory for storing Java packages, which contains all the Java classes of the project.
• TestSpringbootApplication.java: the main class of the project, which contains the main method.
• dao: a directory that stores the Data Access Object (DAO) package, which is used to access the database or other data storage services.
• UserDao.java: the user data access object, which is used to perform operations such as adding, deleting, modifying, and querying user data.
• impl: the directory for implementing the DAO interface.
• UserDaoImpl.java: the implementation class of the user DAO interface.
• entity: the directory for storing entity classes, which are used to store Java classes corresponding to database tables.
• User.java: the user persistence object, which is used to map the fields of the user data table.
• resources: a directory that stores resource files, such as configuration files and SQL files.
• application.properties: the project's configuration file, which is used to configure the application's properties and parameters.
• test: a directory that stores the test code and resource files.
• TestSpringbootApplicationTests.java: the Java class for testing Spring Boot.
• target: a directory that stores the compiled Class files, JAR packages, and other files.

pom.xml

The content of the pom.xml configuration file is as follows:

1. File declaration statement.

   This statement declares that the file is an XML file, using XML version 1.0 and character encoding UTF-8.

   Sample code:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure the namespace and POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file to https://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use the <modelVersion> element to set the POM model version used by the POM file to 4.0.0.

   Sample code:

   <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
   </project>
   

3. Configure the parent information.

   1. Use <groupId> to set the parent identifier to org.springframework.boot.
   2. Use <artifactId> to set the parent dependency to spring-boot-starter-parent.
   3. Use <version> to set the parent version to 2.7.11.
   4. Use relativePath to set the parent path to empty.

   Sample code:

    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.7.11</version>
    <relativePath/> 
   

4. Configure the basic information.

   1. Use <groupId> to set the project identifier to com.oceanbase.
   2. Use <artifactId> to set the project dependency to java-oceanbase-springboot.
   3. Use <version> to set the project version to 0.0.1-SNAPSHOT.
   4. Use description to set the project information to Demo project for Spring Boot.

   Sample code:

    <groupId>com.oceanbase</groupId>
    <artifactId>java-oceanbase-springboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>java-oceanbase-springboot</name>
    <description>Demo project for Spring Boot</description>
   

5. Configure the Java version.

   Set the Java version used by the project to 1.8.

   Sample code:

     <properties>
         <java.version>1.8</java.version>
     </properties>
   

6. Configure the core dependencies.

   1. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter, and the dependency library to include the components supported by Spring Boot by default, such as Web, data processing, security, and Test.

      Sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter</artifactId>
      </dependency>
      

   2. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter-jdbc, and the dependency to support JDBC-related features provided by Spring Boot, such as connection pools and data source configurations.

      Sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>
      

   3. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter-test, and the scope to test. The dependency supports the testing framework and tools provided by Spring Boot, such as JUnit, Mockito, and Hamcrest.

      Sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
      </dependency>
      

   4. Set the organization of the dependency to com.oceanbase, the name to oceanbase-client, and the version to 2.4.3. The dependency supports the client features provided by OceanBase, such as connections, queries, and transactions.

      Sample code:

          <dependencies>
          <dependency>
              <groupId>com.oceanbase</groupId>
              <artifactId>oceanbase-client</artifactId>
              <version>2.4.3</version>
          </dependency>
          </dependencies>
      

7. Configure Maven plugins.

   Set the organization of the dependency to org.springframework.boot, the name to spring-boot-maven-plugin, and the plugin to package Spring Boot applications into executable JAR or WAR packages.

   Sample code:

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
   

application.properties

The application.properties file specifies the data source of the Spring Boot application, including the driver class name, URL, username, and password required to connect to the OceanBase Cloud database. With these configurations, the application can connect to and operate on the OceanBase Cloud database.

• Use spring.datasource.driverClassName to set the database driver to com.oceanbase.jdbc.Driver, which is used to establish a connection to the OceanBase Cloud database.

• Use spring.datasource.url to set the URL for connecting to the database.

• Use spring.datasource.username to set the username for connecting to the database.

• Use spring.datasource.password to set the password for connecting to the database.

  Sample code:

  spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
  spring.datasource.url=jdbc:oceanbase://host:port/schema_name?useSSL=false&useUnicode=true&characterEncoding=utf-8
  spring.datasource.username=user_name
  spring.datasource.password=******
  

Introduction to UserDaoImpl.java

The UserDaoImpl.java file implements the insert, delete, update, and query operations on the User table by using the JdbcTemplate object to execute SQL statements.

The UserDaoImpl.java file contains the following main parts:

1. Import other classes and interfaces.

   Declare the package name of the current file as com.oceanbase.testspringboot.dao.impl. The file includes the following interfaces and classes:

   • UserDao interface: implements the methods defined in the UserDao interface.
   • User class: passes and stores user data.
   • Autowired annotation: injects the JdbcTemplate object into this class to execute SQL statements.
   • BeanPropertyRowMapper class: maps database query results to Java objects.
   • JdbcTemplate class: executes SQL statements and handles database access.
   • Repository annotation: marks Spring's repository component for data access.
   • List interface: operates on query result sets.

   Sample code:

   import com.oceanbase.testspringboot.dao.UserDao;
   import com.oceanbase.testspringboot.entity.User;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.jdbc.core.BeanPropertyRowMapper;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.stereotype.Repository;
   import java.util.List;
   

2. Define the UserDaoImpl class.

   Use the Repository annotation to specify the Bean name of this class as userDao. Use the JdbcTemplate object to execute SQL statements and implement the insert, delete, update, and query operations on the User table.

   1. Insert user information.

      Call the insert method of the JdbcTemplate object to insert the user's ID and name into the test_springboot table in the database and return the corresponding Boolean value based on the result of the insert operation.

      Sample code:

      @Autowired
          private JdbcTemplate jdbcTemplate; //Jdbc connection tool class
          @Override
          public boolean insertUser(User user) {
              String sql = "insert into test_springboot(id,name)values(?,?)";
              Object[] params = {user.getId(), user.getName()};
              return jdbcTemplate.update(sql, params) > 0;
          }
      

   2. Delete user information.

      Call the delete method of the JdbcTemplate object to delete user information from the database based on the user's ID and return the corresponding Boolean value based on the result of the delete operation.

      Sample code:

      @Override
      public boolean deleteById(Long id) {
          String sql = "delete from test_springboot where id=?";
          Object[] params = {id};
          return jdbcTemplate.update(sql, params) > 0;
      }
      

   3. Update user information.

      Call the update method of the JdbcTemplate object to update the user's name in the specified ID record of the test_springboot table in the database and return the corresponding Boolean value based on the result of the update operation.

      Sample code:

      @Override
      public boolean updateUser(User user) {
          String sql = "update test_springboot set name=? where id=?";
          Object[] params = {user.getName(), user.getId()};
          return jdbcTemplate.update(sql, params) > 0;
      }
      

   4. Query user information.

      Use the JdbcTemplate object to execute an SQL query statement to query the user record with the specified ID from the test_springboot table and use BeanPropertyRowMapper to map the results. Return the query result object.

      Sample code:

      @Override
      public User selectUserById(Long id) {
          String sql = "select * from test_springboot where id=?";
          Object[] params = new Object[]{id};
          return jdbcTemplate.queryForObject(
                  sql,
                  params,
                  new BeanPropertyRowMapper<>(User.class));
      }
      

   5. Query all user information.

      Call the query method of the JdbcTemplate object to query all user information from the database and map the query results to a list of User objects and return them.

      Sample code:

      @Override
      public List<User> selectAllUsers() {
          String sql = "select * from test_springboot";
          return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
      }
      

Introduction to UserDao.java

The UserDao.java file defines the operations on user data by using the UserDao interface.

The UserDao.java file contains the following main parts:

1. Import other classes and interfaces.

   Declare the package name of the current file as package com.oceanbase.testspringboot.dao. The file includes the following interfaces and classes:

   • User class: passes and stores user data.
   • List interface: operates on query result sets.

   Sample code:

   import com.oceanbase.testspringboot.entity.User;
   import java.util.List;
   

2. Define the UserDao interface.

   Use the UserDao interface to define the operations on user data. The operations include inserting user information, deleting user information, updating user information, querying user information based on the user ID, and querying all user information.

   Sample code:

   public interface UserDao {
       boolean insertUser(User user);
       boolean deleteById(Long id);
       boolean updateUser(User user);
       User selectUserById(Long id);
       List<User> selectAllUsers();
   }
   

Introduction to User.java

The User.java file defines the User class to represent a user object.

1. Declare the User object. Declare the User class to contain two private fields, id and name, and provide a no-argument constructor method. Set and get the values of the id and name fields to operate on user information.

   Sample code:

   private Long id;
   private String name;
   
   public User() {
   }
   

2. Create the User object. Define a constructor method of the User class to create a User object with the specified id and name.

   Sample code:

   public User(Long id, String name) {
       this.id = id;
       this.name = name;
   }
   

3. Get and set the id and name values. Define four methods in the User class to get and set the values of the id and name properties. The getId method is used to get the id value, and the setId method is used to set the id value. The getName method is used to get the username name value. The setName method is used to set the username name value.

   Sample code:

   public Long getId() {
       return id;
   }
   
   public void setId(Long id) {
       this.id = id;
   }
   
   public String getName() {
       return name;
   }
   
   public void setName(String name) {
       this.name = name;
   }
   

4. Return the string representation of the User object.

   Override the toString method in the User class to return the string representation of the User object. Define @Override to override the method with the same name in the parent class. Define the toString method to return the string representation of the User object. Concatenate strings to format the values of the id and name properties into a string and return it to the caller User.

   Sample code:

   @Override
   public String toString() {
       return "User{" +
               "id=" + id +
               ", name='" + name + '\'' +
               '}';
   }
   

Introduction to TestSpringbootApplication.java

The TestSpringbootApplication.java file uses the UserDao interface to start a Spring Boot application.

The TestSpringbootApplication.java file contains the following parts:

1. Define classes and interfaces.

   Declare the package name of the current file as com.oceanbase.testspringboot, and include the following interfaces and classes:

   • SpringApplication class: used to start a Spring Boot application.
   • @SpringBootApplication annotation: used to indicate that the class is the entry point of a Spring Boot application.

   Sample code:

   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   

2. Define the TestSpringbootApplication class.

   Use the SpringBootApplication annotation to mark the class as the entry point of a Spring Boot application. Call the run method of the SpringApplication class to start the Spring Boot application.

   Sample code:

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

Introduction to TestSpringbootApplicationTests.java

The TestSpringbootApplicationTests.java file uses the UserDao interface to start a Spring Boot application.

The TestSpringbootApplicationTests.java file contains the following parts:

1. Reference other classes and interfaces.

   Declare the package name of the current file as com.oceanbase.testspringboot, and include the following interfaces and classes:

   • UserDao interface: used to implement the methods defined in the UserDao interface.
   • User class: used to pass and store user data.
   • Test annotation: used to mark test methods.
   • Autowired annotation: injects the JdbcTemplate object into the class to execute SQL statements.
   • SpringBootTest annotation: used to mark the class as a Spring Boot test class.
   • JdbcTemplate class: used to execute SQL statements and handle database access.
   • List interface: used to operate on query result sets.

   Sample code:

   import com.oceanbase.testspringboot.dao.UserDao;
   import com.oceanbase.testspringboot.entity.User;
   import org.junit.jupiter.api.Test;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.test.context.SpringBootTest;
   import org.springframework.jdbc.core.JdbcTemplate;
   import java.util.List;
   
   

2. Define the TestSpringbootApplicationTests class.

   Use the SpringBootTest annotation to mark the class as a test class for a Spring Boot application. Use the UserDao and JdbcTemplate objects to perform CRUD operations on user data and output the results.

   1. Define objects.

      Use the Autowired annotation to automatically inject the UserDao and JdbcTemplate objects.

      Sample code:

      @Autowired
      private UserDao userDao;
      @Autowired
      private JdbcTemplate jdbcTemplate; 
      

   2. Define the contextLoads method.

      Use the contextLoads method to implement the test method.

      1. Drop the test_springboot table.

         Use the jdbcTemplate.execute method to execute the SQL statement drop table test_springboot to drop the test_springboot table.

         Sample code:

         try {
               jdbcTemplate.execute("drop table test_springboot");
               System.out.println("test_springboot delete successfully!");
             } 
         

      2. Create the test_springboot table.

         Use the jdbcTemplate.execute method to execute the SQL statement create table test_springboot (id int primary key, name varchar(50)) to create the test_springboot table, which contains the id and name fields.

         Sample code:

         catch (Exception ignore) {
         
                 } finally {
                     jdbcTemplate.execute("create table test_springboot (" +
                             "id int primary key," +
                             "name varchar(50))");
                     System.out.println("test_springboot create successfully!");
                 }
         

      3. Insert data.

         Use the userDao.insertUser method to insert 10 user records into the test_springboot table, where the id ranges from 1 to 10 and the name is "insert" + i.

         Sample code:

         for (int i = 1; i <= 10; i++) {
             userDao.insertUser(new User((long) i, "insert" + i));
         }
         

      4. Delete data.

         Use the userDao.deleteById method to delete the user record with id equal to 1.

         Sample code:

           userDao.deleteById(1L);
         

      5. Update data.

         Use the userDao.updateUser method to update the user record with id equal to 2, changing the name to update.

         Sample code:

         userDao.updateUser(new User(2L, "update"));
         

      6. Query data.

         Use the userDao.selectUserById method to query the user record with id equal to 2 and print the result.

         Sample code:

         User user = userDao.selectUserById(2L);
         System.out.println("user = " + user);
         

      7. Query all data.

         Use the userDao.selectAllUsers method to query all user records from the test_springboot table and print the result.

         Sample code:

         List<User> userList = userDao.selectAllUsers();
         userList.forEach(System.out::println);
         

Full code

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.11</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.oceanbase</groupId>
    <artifactId>java-oceanbase-springboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>java-oceanbase-springboot</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

  spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
  spring.datasource.url=jdbc:oceanbase://host:port/schema_name?useSSL=false&useUnicode=true&characterEncoding=utf-8
  spring.datasource.username=user_name
  spring.datasource.password=******

package com.oceanbase.testspringboot.dao.impl;

import com.oceanbase.testspringboot.dao.UserDao;
import com.oceanbase.testspringboot.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("userDao")
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate; //Jdbc connection tool class

    @Override
    public boolean insertUser(User user) {
        String sql = "insert into test_springboot(id,name)values(?,?)";
        Object[] params = {user.getId(), user.getName()};
        return jdbcTemplate.update(sql, params) > 0;
    }

    @Override
    public boolean deleteById(Long id) {
        String sql = "delete from test_springboot where id=?";
        Object[] params = {id};
        return jdbcTemplate.update(sql, params) > 0;
    }

    @Override
    public boolean updateUser(User user) {
        String sql = "update test_springboot set name=? where id=?";
        Object[] params = {user.getName(), user.getId()};
        return jdbcTemplate.update(sql, params) > 0;
    }

    @Override
    public User selectUserById(Long id) {
        String sql = "select * from test_springboot where id=?";
        Object[] params = new Object[]{id};
        return jdbcTemplate.queryForObject(
                sql,
                params,
                new BeanPropertyRowMapper<>(User.class));
    }

    @Override
    public List<User> selectAllUsers() {
        String sql = "select * from test_springboot";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
    }
}

package com.oceanbase.testspringboot.dao;

import com.oceanbase.testspringboot.entity.User;

import java.util.List;


public interface UserDao {
    boolean insertUser(User user);

    boolean deleteById(Long id);

    boolean updateUser(User user);

    User selectUserById(Long id);

    List<User> selectAllUsers();
}

package com.oceanbase.testspringboot.entity;


public class User {
    private Long id;
    private String name;

    public User() {
    }

    public User(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

package com.oceanbase.testspringboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class TestSpringbootApplication {

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

package com.oceanbase.testspringboot;

import com.oceanbase.testspringboot.dao.UserDao;
import com.oceanbase.testspringboot.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

@SpringBootTest
class TestSpringbootApplicationTests {
    @Autowired
    private UserDao userDao;
    @Autowired
    private JdbcTemplate jdbcTemplate; //Jdbc connection tool class

    @Test
    void contextLoads() {
        try {
            // Use the execute() method to execute SQL statements and delete the user table test_springboot
            jdbcTemplate.execute("drop table test_springboot");
            System.out.println("test_springboot delete successfully!");
        } catch (Exception ignore) {

        } finally {
            // Use the execute() method to execute SQL statements and create user table tests_ user
            jdbcTemplate.execute("create table test_springboot (" +
                    "id int primary key," +
                    "name varchar(50))");
            System.out.println("test_springboot create successfully!");
        }
        //UserDao userDao=new UserDaoImpl();

        //ApplicationContext ioc=new ApplicationContext("/appli");`
        //add
        for (int i = 1; i <= 10; i++) {
            userDao.insertUser(new User((long) i, "insert" + i));
        }
        //delete
        userDao.deleteById(1L);
        //update
        userDao.updateUser(new User(2L, "update"));
        //selectUserById
        User user = userDao.selectUserById(2L);
        System.out.println("user = " + user);
        //query all users
        List<User> userList = userDao.selectAllUsers();
        userList.forEach(System.out::println);
    }

}

References

For more information about OceanBase Connector/J, see OceanBase JDBC driver.