SpringBoot sample application for connecting to seekdb

This topic describes how to use the SpringBoot framework and seekdb to build an application that performs basic operations such as creating tables, inserting data, and querying data.

Download the java-oceanbase-springboot sample project

Prerequisites

You have installed seekdb.
You have installed JDK 1.8 and Maven.
You have installed IntelliJ IDEA.

info

The tool used to run the code in this document is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your preferences to run the sample code.

Procedure

info

The following steps are based on the Windows environment. If you use a different operating system or compiler, the steps may vary.

Obtain the connection information of seekdb.
Import the java-oceanbase-springboot project into IDEA.
Modify the database connection information in the java-oceanbase-springboot project.
Run the java-oceanbase-springboot project.

Step 1: Obtain the seekdb connection information

Contact the deployment personnel or administrator of seekdb to obtain the corresponding database connection information.
```
mysql -hxx.xx.xx.xx -P2881 -uroot -p**** -A
```
Fill in the URL information based on the deployed seekdb.

info
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 IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address 127.0.0.1.
- port: the port for connecting to seekdb. Replace it with the actual port. The default port is 2881, which can be customized when deploying seekdb.
- schema_name: the name of the schema to be accessed.
- user_name: the username specified by the -u parameter. The default username is root.
- 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 URL parameters, see Database URL.

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

Open IntelliJ IDEA and select File > Open....
In the Open File or Project window that appears, select the corresponding project file and click OK to complete the import.
IntelliJ IDEA will automatically recognize various files in the project and display the directory 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 use the shortcut key Alt + 1 to reopen it.

info
When importing a project using 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.
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 seekdb connection information.

Example:

The name of the database driver is: com.mysql.cj.jdbc.Driver
The IP address of seekdb is 10.10.10.1.
The access port is 2881.
The name of the schema to be accessed is test.
The username is root.
The password is ******.

Sample code:

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

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

Run path

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

Run 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 issue

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 SSL connections with seekdb, add the following parameter to the JDBC URL:

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

4. Special characters in 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");

tip

When using MySQL Connector/J 8.x, ensure that the username and password do not contain the number sign (#). Otherwise, you may encounter connection errors.

Project code

Click java-oceanbase-springboot to download the project code, which is a compressed package 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

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) packages for accessing the database or other data storage services.
UserDao.java: the user DAO package, 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: a directory for storing entity classes that correspond to database tables.
User.java: the user persistence object, which is used to map the fields of the user data table.
resources: a directory for storing 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 for storing test code and resource files.
TestSpringbootApplicationTests.java: the Java class for testing Spring Boot.
target: a directory for storing compiled Class files, Jar packages, and other files.

Code in pom.xml

info

If you only want to verify the example, you can use the default code without any modifications. You can also modify the pom.xml file according to your needs, as described below.

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

File declaration statement.

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

Code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the namespaces 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 <modelVersion> to set the POM model version used by this POM file to 4.0.0.
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 the parent project information.
1. Use <groupId> to set the parent project identifier to org.springframework.boot.
2. Use <artifactId> to set the parent project dependency to spring-boot-starter-parent.
3. Use <version> to set the parent project version to 2.7.11.
4. Use relativePath to indicate that the parent project path is empty.
Code:
```
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.7.11</version>
 <relativePath/> 
```
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 describe the project as Demo project for Spring Boot.
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.

Set the Java version used by the project to 1.8.

Code:
```
  <properties>
      <java.version>1.8</java.version>
  </properties>
```
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 Spring Boot's default supported components, such as Web, data processing, security, and Test.
  
  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 library to provide Spring Boot's JDBC-related features, such as connection pools and data source configurations.
  
  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. This dependency library provides Spring Boot's testing framework and tools, such as JUnit, Mockito, and Hamcrest.
  
  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. This dependency library provides OceanBase's client features, such as connection, query, and transaction.
  
  Code:
```
    <dependencies>
    <dependency>
        <groupId>com.oceanbase</groupId>
        <artifactId>oceanbase-client</artifactId>
        <version>2.4.3</version>
    </dependency>
    </dependencies>
```

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.

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 for the Spring Boot application and specifies the driver class name, URL, username, and password required to connect to seekdb. These configurations allow the application to connect to and operate on seekdb.

Use spring.datasource.driverClassName to set the database driver to com.oceanbase.jdbc.Driver, which is used to establish a connection with seekdb.
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.

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 the UserDaoImpl.java file

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 code in the UserDaoImpl.java file mainly includes the following parts:

Import other classes and interfaces.

Declare the package name of the current file as com.oceanbase.testspringboot.dao.impl, and import the following interfaces and classes:
- UserDao interface: defines the methods implemented by the UserDao interface.
- User class: used to pass and store user data.
- Autowired annotation: injects the JdbcTemplate object into the class to execute SQL statements.
- BeanPropertyRowMapper class: used to map database query results to Java objects.
- JdbcTemplate class: used to execute SQL statements and handle database access.
- Repository annotation: marks Spring's repository component for data 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.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 the Bean name of the class as userDao, and use the JdbcTemplate object to execute SQL statements to 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 ID and name into the test_springboot table in the database and return a 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 ID and return a 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 name in the record with the specified ID in the test_springboot table in the database and return a 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 the BeanPropertyRowMapper class to map the query 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 return a list of User objects mapped from the query results.
  
  Sample code:
```
@Override
public List<User> selectAllUsers() {
    String sql = "select * from test_springboot";
    return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
}
```

Introduction to the UserDao.java file

The UserDao.java file defines the methods for operating on user data by using the UserDao interface.

The code in the UserDao.java file mainly includes the following parts:

Import other classes and interfaces.

Declare the package name of the current file as package com.oceanbase.testspringboot.dao, and import the following interfaces and classes:
- User class: used to pass and store user data.
- List interface: used to operate on query result sets.
Sample code:
```
import com.oceanbase.testspringboot.entity.User;
import java.util.List;
```
Define the UserDao interface.

Use the UserDao interface to define the methods for operating on user data. These methods 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 the User.java file

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

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() {
}
```
Create the User object. Define a parameterized constructor method for 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;
}
```
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;
}
```
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 the @Override annotation to override the same-named method 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 + '\'' +
            '}';
}
```

TestSpringbootApplication.java

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

The TestSpringbootApplication.java file contains the following code:

Define classes and interfaces.

The package name of the current file is com.oceanbase.testspringboot, which contains the following interfaces and classes:
- SpringApplication class: used to start a Spring Boot application.
- @SpringBootApplication annotation: used to indicate that this class is the entry point of a Spring Boot application.
Sample code:
```
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
```
Define the TestSpringbootApplication class.

Use the SpringBootApplication annotation to indicate that this class is the entry point of a Spring Boot application. You can call the run method of the SpringApplication class to start a Spring Boot application.

Sample code:
```
@SpringBootApplication
public class TestSpringbootApplication {

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

TestSpringbootApplicationTests.java

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

The TestSpringbootApplicationTests.java file contains the following code:

Import other classes and interfaces.

The package name of the current file is com.oceanbase.testspringboot, which contains 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 identify a test method.
- Autowired annotation: used to inject the JdbcTemplate object into this class to execute SQL statements.
- SpringBootTest annotation: used to identify this class as a Spring Boot test class.
- JdbcTemplate class: used to execute SQL statements and handle database access.
- List interface: used to operate on a query result set.
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 identify the test class of a Spring Boot application. Use the UserDao and JdbcTemplate objects to perform insert, update, delete, and query 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. The id values range from 1 to 10, and the name values are "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 the id value of 1.
    
    Sample code:
    userDao.deleteById(1L);
  5. Update data.
    
    Use the userDao.updateUser method to update the user record with the id value of 2 and change the name value to update.
    
    Sample code:
    userDao.updateUser(new User(2L, "update"));
  6. Query data.
    
    Use the userDao.selectUserById method to query the user record with the id value of 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 in the test_springboot table and print the result.
    
    Sample code:
    List<User> userList = userDao.selectAllUsers(); userList.forEach(System.out::println);

Full code

pom.xml

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

application.properties

  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=******

UserDaoImpl.java

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

UserDao.java

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

User.java

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 + '\'' +
                '}';
    }
}

TestSpringbootApplication.java

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

TestSpringbootApplicationTests.java

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.

Prerequisites​

Procedure​

Step 1: Obtain the seekdb connection information​

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

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

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

Run path​

Run result​

FAQ​

1. Connection timeout​

2. Character set issue​

3. SSL connection​

4. Special characters in account password​

Project code​

Code in pom.xml​

Code in application.properties​

Introduction to the UserDaoImpl.java file​

Introduction to the UserDao.java file​

Introduction to the User.java file​

TestSpringbootApplication.java​

TestSpringbootApplicationTests.java​

Full code​

pom.xml​

application.properties​

UserDaoImpl.java​

UserDao.java​

User.java​

TestSpringbootApplication.java​

TestSpringbootApplicationTests.java​

References​

Contents

Prerequisites

Procedure

Step 1: Obtain the seekdb connection information

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

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

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

Run path

Run result

FAQ

1. Connection timeout

2. Character set issue

3. SSL connection

4. Special characters in account password

Project code

Code in pom.xml

Code in application.properties

Introduction to the UserDaoImpl.java file

Introduction to the UserDao.java file

Introduction to the User.java file

TestSpringbootApplication.java

TestSpringbootApplicationTests.java

Full code

pom.xml

application.properties

UserDaoImpl.java

UserDao.java

User.java

TestSpringbootApplication.java

TestSpringbootApplicationTests.java

References