Connect to OceanBase Database by using Spring Boot

This topic introduces how to build an application by using the Spring Boot framework and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the java-oceanbase-springboot sample project

Connect to OceanBase Database by using Spring Boot (Oracle mode)

Prerequisites

• You have installed OceanBase Database.
• You have installed JDK 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the connection information of OceanBase Database.
2. Import the java-oceanbase-springboot project into 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 OceanBase Database

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection information.

   obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Fill in the URL below based on the deployed OceanBase database.

   Note

   The URL here 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
   

   Parameters in the URL are described as follows:

   • host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.

   • port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

   • schema_name: the name of the schema to access.

   • user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

   • password: the account password.

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

     • useSSL: specifies whether to use SSL/TLS during forced connections. The default value is false.
     • useUnicode: specifies whether to use Unicode character encodings. The default value is true.
     • characterEncoding: the character encoding that supports the database URL options. The default value is utf8.
     • socketTimeout: the network socket timeout (SO_TIMEOUT), in ms. The value 0 specifies to disable socket timeout.

   For more information about URL parameters, see Database URL.

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

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

   

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

3. IntelliJ IDEA automatically identifies all types of files in the project. In the Project window, you can view the directory structure, list of files, list of modules, and dependencies of the project. The Project window is usually on the far left side in IntelliJ IDEA and is displayed by default. If the Project window is closed, you can choose View > Tool Windows > Project from the menu or use the Alt + 1 shortcut to open the window.

   Note

   When you use IntelliJ IDEA to import a project, IntelliJ IDEA automatically detects the pom.xml file in the project, downloads the required libraries based on the dependencies defined in the file, and adds the libraries 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 OceanBase Database.

Here is an example:

• The name of the database driver is com.oceanbase.jdbc.Driver.
• The IP address of the OBServer node is 10.10.10.1.
• The port is 2881.
• The name of the schema to access is sys.
• The tenant account is sys@xyoracle, where xyoracle is a user tenant created in the Oracle mode of OceanBase Database, and sys is a username in the xyoracle tenant.
• The password is ******.

The sample code is as follows:

spring.datasource.driverClassName=com.oceanbase.jdbc.Driver
spring.datasource.url=jdbc:oceanbase://10.10.10.1:2881/sys?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=sys@xyoracle
spring.datasource.password=******

Step 4: Run the java-oceanbase-springboot project

Run path

1. Find the TestSpringbootApplicationTests.java file under src > test > java in the project package.
2. Choose Run > Run... > TestSpringbootApplicationTests in the menu bar or click the green triangle in the upper-right corner to run the project.
3. View the logs and output of the project in the IDEA console.

Output

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'}

Project code introduction

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

After decompressing it, 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

Here is a breakdown of the files and directories:

• pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build information of the project.
• .idea: the directory used in the Integrated Development Environment (IDE) for storing project-related configurations.
• src: the directory for storing source code of the project.
• main: the directory for storing main source code and resource files.
• java: the directory for storing Java source code.
• com: the root directory for storing the Java package.
• oceanbase: the root directory for storing the project.
• testspringboot: the root directory of the Java package, which contains all the Java classes of the project.
• TestSpringbootApplication.java: the main class of the project and contains the main method.
• dao: stores the data access object (DAO) package for accessing databases or other data storage services.
• UserDao.java: the user DAO object that is used to add, delete, modify, and query user data.
• impl: the implementation directory of DAO interfaces.
• UserDaoImpl.java: the implementation class of the user DAO interface.
• entity: the entity class directory for storing the Java classes corresponding to the database tables.
• User.java: the user persistent object that is mapped to fields in a user data table.
• resources: the directory for storing resource files, such as configuration files and SQL files.
• application.properties: the configuration file of the project, which defines the properties and parameters of the application.
• test: the directory for storing the test code and resource files.
• TestSpringbootApplicationTests.java: the directory for storing the Java class for testing Spring Boot.
• target: the directory for storing compiled class files and JAR packages.

Code in pom.xml

To configure the pom.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. Use xmlns to specify http://maven.apache.org/POM/4.0.0 as the default XML namespace for the POM.
   2. Use xmlns:xsi to specify http://www.w3.org/2001/XMLSchema-instance as the XML namespace for xsi-prefixed elements.
   3. Use xsi:schemaLocation to provide a mapping from the default XML namespace for the POM (http://maven.apache.org/POM/4.0.0) to the location of the POM's XML schema definition (XSD) file (https://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. Use <modelVersion> to specify 4.0.0 as the model version used by the POM.

   The sample code is as follows:

   <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 parent project information.

   1. Use <groupId> to specify org.springframework.boot as the ID of the parent project group.
   2. Use <artifactId> to specify spring-boot-starter-parent as the ID of the parent project.
   3. Use <version> to specify 2.7.11 as the version of the parent project.
   4. Use <relativePath> to specify an empty path for the parent project.

   The sample code is as follows:

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

4. Configure basic project information.

   1. Use <groupId> to specify com.oceanbase as the ID of the project group.
   2. Use <artifactId> to specify java-oceanbase-springboot as the ID of the project.
   3. Use <version> to specify 0.0.1-SNAPSHOT as the version of the project.
   4. Use <description> to describe the project as Demo project for Spring Boot.

   The sample code is as follows:

    <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.

   Specify to use Java 1.8 for the project.

   The sample code is as follows:

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

6. Configure core dependencies.

   1. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter as the dependency ID. This dependency contains default features provided by Spring Boot, such as web, data processing, security, and test.

      The sample code is as follows:

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

   2. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter-jdbc as the dependency ID. This dependency contains JDBC features provided by Spring Boot, such as the connection pool and data source configuration.

      The sample code is as follows:

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

   3. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter-test as the dependency ID. This dependency takes effect on test and provides test frameworks and tools of Spring Boot, such as JUnit, Mockito, and Hamcrest.

      The sample code is as follows:

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

   4. Specify com.oceanbase as the ID of the group that the dependency belongs to, oceanbase-client as the dependency ID, and 2.4.3 as the dependency version. This dependency allows the project to use the client features, such as connection, query, and transaction, provided by OceanBase.

      The sample code is as follows:

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

7. Configure the Maven plug-in.

   Specify org.springframework.boot as the ID of the group that the plug-in belongs to, and spring-boot-maven-plugin as the plug-in ID. This plug-in can be used to package Spring Boot applications as executable JAR packages or WAR packages, or directly run Spring Boot applications.

   The sample code is as follows:

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

Code in application.properties

The application.properties file configures the data source of the Spring Boot application by specifying the driver class name, URL, username, and password for connecting to OceanBase Database. With these configurations, the application can connect to and operate OceanBase Database.

• Use spring.datasource.driverClassName to specify com.oceanbase.jdbc.Driver as the database driver for establishing a connection with OceanBase Database.

• Use spring.datasource.url to specify the URL of the database.

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

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

  The sample code is as follows:

  spring.datasource.driverClassName=com.oceanbase.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=******
  

Code in UserDaoImpl.java

In the UserDaoImpl.java file, a JdbcTemplate object is used to execute SQL statements that insert, delete, update, and query user data.

Code in the UserDaoImpl.java file contains the following parts:

1. Reference other classes and interfaces.

   Declare that the current file belongs to the com.oceanbase.testspringboot.dao.impl package and contains the following interfaces and classes:

   • UserDao interface: the interface whose methods are implemented in this file.
   • User class: used to pass and store 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 accesses.
   • Repository annotation: marks a Spring repository that is used for data access.
   • List interface: handles query result sets.

   The sample code is as follows:

   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 userDao as the bean name of the class, and use the JdbcTemplate object to execute SQL statements that insert, delete, update, and query user data.

   1. Insert user data.

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

      The sample code is as follows:

      @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 data.

      Call the delete method of the JdbcTemplate object to delete user data from a table by user ID and return a Boolean value based on the result of the delete operation.

      The sample code is as follows:

      @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 data.

      Call the update method of the JdbcTemplate object to update a user name in the test_springboot table by user ID, and return a Boolean value based on the result of the update operation.

      The sample code is as follows:

      @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 data.

      Call the JdbcTemplate object to execute the SQL statement that queries the test_springboot table for the user record with the specified ID, and call BeanPropertyRowMapper to map the results. Return the query result object.

      The sample code is as follows:

      @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 data.

      Call the query method of the JdbcTemplate object to query all user data in a table, map the query results to a list of objects of the User type, and return the list.

      The sample code is as follows:

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

Code in UserDao.java

The UserDao.java file defines user data operations in the UserDao interface.

To configure the UserDao.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file belongs to the com.oceanbase.testspringboot.dao package and contains the following interfaces and classes:

   • User class: used to pass and store user data.
   • List interface: handles query result sets.

   The sample code is as follows:

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

2. Define the UserDao interface.

   Define user data operations in the UserDao interface, including the insert, delete, and update operations, and the operations to query user data by user ID and query all user data.

   The sample code is as follows:

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

Code in User.java

The User.java file defines the User class to represent user objects.

1. Declare a User object.

   Declare that the User class contains two private fields: id and name, and provides a no-parameter constructor. The application operates user data by getting and setting the id and name fields.

   The sample code is as follows:

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

2. Create a User object.

   Define the constructor with parameters of the User class for creating a User object with the specified id and name fields.

   The sample code is as follows:

   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 id and name. The getId method gets the id value, and the setId method sets the id value. The getName method gets the name value. The setName method sets the name value.

   The sample code is as follows:

   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 a string that represents the User object.

   Override the toString method in the User class to return a string that represents the User object. Use the @Override annotation to override the method of the same name in the parent class. Define the toString method that returns a string representation of the User object. Concatenate the id and name values to generate a string and return it to the caller.

   The sample code is as follows:

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

Code in TestSpringbootApplication.java

The TestSpringbootApplication.java file launches the Spring Boot application by using the UserDao interface.

To configure the TestSpringbootApplication.java file, perform the following steps:

1. Define classes and interfaces.

   Declare that the current file belongs to the com.oceanbase.testspringboot package and contains the following interfaces and classes:

   • SpringApplication class: launches the Spring Boot application.
   • @SpringBootApplication annotation: marks the class as the entry to the Spring Boot application.

   The sample code is as follows:

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

2. Define the TestSpringbootApplication class.

   Use the SpringBootApplication annotation to specify that this class is the entry class for the Spring Boot application. Call the run method of the SpringApplication class to launch the Spring Boot application.

   The sample code is as follows:

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

Code in TestSpringbootApplicationTests.java

The TestSpringbootApplicationTests.java file launches the Spring Boot application by using the UserDao interface.

To configure the TestSpringbootApplicationTests.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file belongs to the com.oceanbase.testspringboot package and contains the following interfaces and classes:

   • UserDao interface: the interface whose methods are implemented in this file.
   • User class: used to pass and store user data.
   • Test annotation: marks a test method.
   • Autowired annotation: injects the JdbcTemplate object into this class to execute SQL statements.
   • SpringBootTest annotation: marks the class as the Spring Boot test class.
   • JdbcTemplate class: executes SQL statements and handles database accesses.
   • List interface: handles query result sets.

   The sample code is as follows:

   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 this class as one that tests features of the Spring Boot application. Use the UserDao and JdbcTemplate objects to add, delete, modify, and query user data and output the results.

   1. Define objects.

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

      The sample code is as follows:

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

   2. Define the contextLoads method.

      Call the contextLoads method as a concrete implementation of the test method.

      1. Drop the test_springboot table.

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

         The sample code is as follows:

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

      2. Create the test_springboot table.

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

         The sample code is as follows:

         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.

         Call the userDao.insertUser method to insert 10 user data records into the test_springboot table. The values of the id field are 1 to 10, and the values of name are "insert" + i.

         The sample code is as follows:

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

      4. Delete data.

         Call the userDao.deleteById method to delete the user data record where id is 1.

         The sample code is as follows:

           userDao.deleteById(1L);
         

      5. Update data.

         Call the userDao.updateUser method to change the value of name to update in the user data record where id is 2.

         The sample code is as follows:

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

      6. Query data.

         Call the userDao.selectUserById method to query the user data record where id is 2 and print the result.

         The sample code is as follows:

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

      7. Query all user data.

         Call the userDao.selectAllUsers method to query all user data in the test_springboot table and print the results.

         The sample code is as follows:

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

Complete code examples

<?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.oceanbase.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 Connector/J.