Connect to OceanBase Database by using Spring Boot|OceanBase Database|docs|OceanBase Documentations

Applicability

Spring Boot is applicable to OceanBase Database in MySQL and Oracle modes. This topic provides only an example for the MySQL mode.

This topic describes how to use the Spring Boot framework and OceanBase Database to build an application that performs basic database operations such as creating tables, inserting data, and querying data.

Download the java-oceanbase-springboot sample project

Prerequisites

You have installed OceanBase Database.
You have installed Java Development Kit (JDK) 1.8 and Maven.
You have installed IntelliJ IDEA.

Note

In this topic, IntelliJ IDEA Community Edition 2021.3.2 is used to run the sample code. You can also choose a suitable tool as needed.

Procedure

Note

The following procedure applies to Windows. If you use another operating system or compiler, the procedure can be slightly different.

Obtain the connection information of OceanBase Database.
Import the java-oceanbase-springboot project to IntelliJ IDEA.
Modify the database connection information in the java-oceanbase-springboot project.
Run the java-oceanbase-springboot project.

Step 1: Obtain the connection information of OceanBase Database

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection information.
```
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
```
Enter the URL of OceanBase Database.

Note

The URL of OceanBase Database is required in the application.properties file.
```
jdbc:mysql://host:port/schema_name?user=$user_name&password=$password&useSSL=false&useUnicode=true&characterEncoding=utf-8
```
The parameters are described as follows:
- host: the IP address for connecting to OceanBase Database. It is the IP address of ODP for connection through ODP, or the IP address of an OBServer node for direct connection.
- 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 be accessed.
- user_name: the username of the account in the format of user@tenant#cluster name or username@SERVICE:service name, which is specified by the -u parameter. When the value is in the user@tenant#cluster name format, the default tenant is sys and the default administrator is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.
- password: the password of the account.
- useSSL=false&useUnicode=true&characterEncoding=utf-8 specifies the additional connection properties.
  - useSSL: specifies whether to use SSL/TLS during forced connections. The default value is false.
  - useUnicode: specifies whether to use the Unicode character encoding formats. The default value is true.
  - characterEncoding: the character encoding format for the URL of OceanBase Database. The default value is utf8.

For more information about URL parameters, see Database URL.

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

Start IntelliJ IDEA and choose File > Open....
In the Open File or Project window, select the project files and click OK to import the files.
IntelliJ IDEA automatically identifies various files in the project. You can view project information such as the directory structure, file list, module list, and dependencies in the Project window. Generally, the Project window is at the leftmost of the UI of IntelliJ IDEA and is opened by default. If the Project window is closed, you can choose View > Tool Windows > Project in the menu bar or press the keyboard shortcut Alt + 1 to open it.

Note

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

springboot

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.mysql.cj.jdbc.Driver.
The IP address of the OBServer node is 10.10.10.1.
The access port is 2881.
The name of the schema to be accessed is test.
The tenant account is root@mysq001. mysql001 is a MySQL user tenant created in OceanBase Database, and root is the username of a user in the mysql001 tenant.
The password is ******.

Here is the sample code:

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://10.10.10.1:2881/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root@mysq001
spring.datasource.password=******

Step 4: Run the `java-oceanbase-springboot` project

Path

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

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

Project code

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

Decompress the package to obtain 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

The files and directories are described as follows:

pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build rules of the project.
.idea: a directory used in an Integrated Development Environment (IDE) to store configuration information related to the project.
src: a directory that stores the source code in 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 of the Java package.
oceanbase: the root directory of 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, which 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 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, which stores 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: a directory that stores 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: a directory that stores the test code and resource files.
TestSpringbootApplicationTests.java: stores the Java class for testing Spring Boot.
target: a directory that stores compiled class files and JAR packages.

Code in pom.xml

Note

You can retain the default code in this file for verification purposes or modify the code in the file as needed.

Perform the following steps to configure the pom.xml file:

Declare the file.

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

Here is the sample code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure namespaces and the POM version.
1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (https://maven.apache.org/xsd/maven-4.0.0.xsd).
4. <modelVersion>: the POM version used by the POM file, which is set to 4.0.0.
Here is the 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>
```
Configure parent project information.
1. <groupId>: the ID of the parent project group, which is set to org.springframework.boot.
2. <artifactId>: the name of the parent project, which is set to spring-boot-starter-parent.
3. <version>: the version of the parent project, which is set to 2.7.11.
4. <relativePath>: an empty path for the parent project.
Here is the sample code:
```
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.7.11</version>
 <relativePath/>
```
Configure basic information.
1. <groupId>: the ID of the project group, which is set to com.oceanbase.
2. <artifactId>: the name of the project, which is set to java-oceanbase-springboot.
3. <version>: the version of the project, which is set to 0.0.1-SNAPSHOT.
4. <description>: the project information, which is set to Demo project for Spring Boot.
Here is the 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>
```
Configure the Java version.

Specify to use Java 1.8 for the project.

Here is the sample code:
```
  <properties>
      <java.version>1.8</java.version>
  </properties>
```
Configure core dependencies.
1. Define a dependency named spring-boot-starter that belongs to the org.springframework.boot group. This dependency contains default features provided by Spring Boot, such as web, data processing, security, and testing.
  
  Here is the sample code:
```
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
```
2. Define a dependency named spring-boot-starter-jdbc that belongs to the org.springframework.boot group. This dependency contains Java Database Connectivity (JDBC) features provided by Spring Boot, such as connection pool and data source configuration.
  
  Here is the sample code:
```
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
```
3. Define a dependency named spring-boot-starter-test that belongs to the org.springframework.boot group. This dependency takes effect in the test scope and provides test frameworks and tools of Spring Boot, such as JUnit, Mockito, and Hamcrest.
  
  Here is the sample code:
```
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
```
4. Define a dependency named oceanbase-client that belongs to the com.oceanbase group and whose version is 2.4.3. With this dependency, you can use the features of OceanBase Command-Line Client (OBClient), such as connections, queries, and transactions.
  
  Here is the sample code:
```
    <dependencies>
    <dependency>
        <groupId>com.oceanbase</groupId>
        <artifactId>oceanbase-client</artifactId>
        <version>2.4.3</version>
    </dependency>
    </dependencies>
```
Configure the Maven plug-in.

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

Here is the sample code:
```
 <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.

spring.datasource.driverClassName: the database driver used to establish a connection with OceanBase Database, which is set to com.oceanbase.jdbc.Driver.
spring.datasource.url: the URL for connecting to the database.
spring.datasource.username: the username for connecting to the database.

spring.datasource.password: the password for connecting to the database.

Here is the sample code:

spring.datasource.driverClassName=com.oceanbase.jdbc.Driver
spring.datasource.url=jdbc:mysql://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.

Perform the following steps to configure the UserDaoImpl.java file:

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: transmits 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 accesses.
- Repository annotation: marks a Spring repository that is used for data access.
- List interface: operates the query result set.
Here is the 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;
```

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.

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.

Here is the 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;
    }

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.

Here is the 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;
}

Update user data.

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

Here is the 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;
}

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.

Here is the 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));
}

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.

Here is the sample code:
```
@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.

Perform the following steps to configure the UserDao.java file:

Reference other classes and interfaces.

Declare that the current file belongs to the package com.oceanbase.testspringboot.dao package and contains the following interfaces and classes:
- User class: transmits and stores user data.
- List interface: operates the query result set.
Here is the sample code:
```
import com.oceanbase.testspringboot.entity.User;
import java.util.List;
```

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.

Here is the sample code:

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.

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

Here is the sample code:
```
private Long id;
private String name;

public User() {
}
```
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.

Here is the sample code:
```
public User(Long id, String name) {
    this.id = id;
    this.name = name;
}
```
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 attributes. The getId method gets the id value. The setId method sets the id value. The getName method gets the name value. The setName method sets the name value.

Here is the 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;
}
```
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.

Here is the sample code:
```
@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.

Perform the following steps to configure the TestSpringbootApplication.java file:

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 a Spring Boot application.
- @SpringBootApplication annotation: marks the class as the entry to the Spring Boot application.
Here is the sample code:
```
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
```
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 in the SpringApplication class to launch the Spring Boot application.

Here is the sample code:
```
@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.

Perform the following steps to configure the TestSpringbootApplicationTests.java file:

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: transmits and stores 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: operates the query result set.
Here is the 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;
```
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.
  
  Here is the sample code:
```
@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 SQL statement drop table test_springboot to drop the test_springboot table.
    
    Here is the sample code:
```
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 SQL statement create table test_springboot (id int primary key, name varchar(50)) to create a table named test_springboot. The table contains the id and name fields.
    
    Here is the 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.
    
    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.
    
    Here is the sample code:
```
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.
    
    Here is the sample code:
```
  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.
    
    Here is the sample code:
```
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.
    
    Here is the sample code:
```
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.
    
    Here is the sample code:
```
List<User> userList = userDao.selectAllUsers();
userList.forEach(System.out::println);
```

Complete code

pom.xml

application.properties

UserDaoImpl.java

UserDao.java

User.java

TestSpringbootApplication.java

TestSpringbootApplicationTests.java

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

Applicability

Prerequisites

Note

Procedure

Note

Step 1: Obtain the connection information of OceanBase Database

Note

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

Note

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

Step 4: Run the java-oceanbase-springboot project

Path

Result

Project code

Code in pom.xml

Note

Code in application.properties

Code in UserDaoImpl.java

Code in UserDao.java

Code in User.java

Code in TestSpringbootApplication.java

Code in TestSpringbootApplicationTests.java

Complete code

References

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

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

Step 4: Run the `java-oceanbase-springboot` project