This topic describes how to build an application using the Spring Boot framework and OceanBase AI Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data updating, and data query.
Prerequisites
- You have installed OceanBase AI Database.
- You have installed Java Development Kit (JDK) 1.8 and Maven.
- You have installed IntelliJ IDEA.
Note
The tool used to run the sample code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Obtain the connection information for OceanBase AI Database.
- Import the
java-oceanbase-springbootproject into IntelliJ IDEA. - Modify the database connection information in the
java-oceanbase-springbootproject. - Run the
java-oceanbase-springbootproject.
Step 1: Obtain the connection information for OceanBase AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the corresponding database connection information.
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -AFill in the corresponding information in the URL below based on the deployed OceanBase AI Database.
Note
This URL information is required in the
application.propertiesfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&useSSL=false&useUnicode=true&characterEncoding=utf-8Parameter description:
host: The IP address for connecting to OceanBase AI Database. For ODP connections, use an ODP address; for direct connections, use the IP address of an OBServer node.port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment; for direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.schema_name: The name of the schema to access.user_name: Specified using the-uparameter, in the format user@tenant#cluster_name or username@SERVICE:service_name. When using the user@tenant#cluster_name format, the default tenant issysand the administrator user isroot. The cluster name can be omitted when directly connecting to the database, but must be provided for ODP connections.password: The account password.useSSL=false&useUnicode=true&characterEncoding=utf-8: Additional connection properties.useSSL: Whether to useSSL/TLSfor forced connections. Default value:false.useUnicode: Specifies the encoding and decoding format for characters. Default value:true.characterEncoding: Supports character encoding for database URL options. Default value:utf8.
For more information about URL parameters, see Database URL.
Step 2: Import the java-oceanbase-springboot project into IntelliJ 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 automatically recognizes the various file types in the project. In the Project tool window, you can view information such as the project's directory structure, file list, module list, and dependencies. The Project tool window is usually located on the far-left side of the IntelliJ IDEA interface and is open by default. If the Project tool window is closed, you can reopen it by clicking View > Tool Windows > Project in the menu bar or using the shortcut key Alt + 1.
Note
When you import a project in IntelliJ IDEA, it 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.

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 for OceanBase AI Database.
Here is a connection information 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@mysql001, wheremysql001is a user tenant created in OceanBase AI Database, androotis the username of themysql001tenant. - The password is
******.
The sample code is as follows:
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@mysq001
spring.datasource.password=******
Step 4: Run the java-oceanbase-springboot project
Run path
- Locate the
TestSpringbootApplicationTests.javafile in the src > test > java directory of the project structure. - Select Run (U) > Run > TestSpringbootApplicationTests from the toolbar menu, or directly click the green triangle in the upper-right corner to run it.
- View the project's log information and output results through IntelliJ IDEA's 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'}
Project code
Click java-oceanbase-springboot to download the project code, which is a compressed file named java-oceanbase-springboot.
After decompressing it, you will get 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 for the Maven project, containing information such as project dependencies, plugins, and build settings..idea: A directory used by the IDE (Integrated Development Environment) to store project-related configuration information.src: Typically denotes the directory that holds the project's source code.main: The directory for storing the main source code and resource files.java: The directory for storing Java source code.com: The root directory for Java packages.oceanbase: The root directory for the project.testspringboot: The root directory for Java packages, containing all the project's Java classes.TestSpringbootApplication.java: The main class of the project, containing the main method.dao: The directory for storing Data Access Object (DAO) packages, used to access databases or other data storage services.UserDao.java: The user data access object, used for operations such as creating, reading, updating, and deleting user data.impl: The implementation directory for the DAO interface.UserDaoImpl.java: The implementation class for the user DAO interface.entity: The directory for entity classes, used to store Java classes corresponding to database tables.User.java: The user persistence object, used to map fields from the user data table.resources: The directory for storing resource files, such as configuration files and SQL files.application.properties: The project's configuration file, used to configure application properties and parameters.test: The directory for storing test code and resource files.TestSpringbootApplicationTests.java: The Java class for testing Spring Boot.target: The directory for storing compiled class files, JAR packages, and other files.
Code in pom.xml
Note
If you just want to verify the example, use the default code without modification. 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:
The file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure namespaces and the POM model version.
xmlns: the default XML namespace for the POM, which is set tohttp://maven.apache.org/POM/4.0.0.xmlns:xsi: the XML namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.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).<modelVersion>: the POM model version used by the POM file, which is set to4.0.0.
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>Configure parent project information.
<groupId>: the ID of the parent project group, which is set toorg.springframework.boot.<artifactId>: the name of the parent project, which is set tospring-boot-starter-parent.<version>: the version of the parent project, which is set to2.7.11.<relativePath>: 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/>Configure basic information.
<groupId>: the ID of the project group, which is set tocom.oceanbase.<artifactId>: the name of the project, which is set tojava-oceanbase-springboot.<version>: the version of the project, which is set to0.0.1-SNAPSHOT.<description>: the project information, which is set toDemo 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>Configure the Java version.
Specify the Java version used by the project as 1.8.
The sample code is as follows:
<properties> <java.version>1.8</java.version> </properties>Configure core dependencies.
Define a dependency named
spring-boot-starterthat belongs to theorg.springframework.bootgroup. This dependency contains default features provided by Spring Boot, such as web, data processing, security, and testing.The sample code is as follows:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency>Define a dependency named
spring-boot-starter-jdbcthat belongs to theorg.springframework.bootgroup. This dependency contains Java Database Connectivity (JDBC) features provided by Spring Boot, such as 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>Define a dependency named
spring-boot-starter-testthat belongs to theorg.springframework.bootgroup. This dependency takes effect in thetestscope 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>Define a dependency named
oceanbase-clientthat belongs to thecom.oceanbasegroup and whose version is2.4.3. With this dependency, you can use the features of OceanBase Command-Line Client (OBClient), such as connections, queries, and transactions.The sample code is as follows:
<dependencies> <dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.3</version> </dependency> </dependencies>
Configure the Maven plugin.
Define a plugin named
spring-boot-maven-pluginthat belongs to theorg.springframework.bootgroup. This plugin 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 for the Spring Boot application, specifying the driver class name, URL, username, and password required to connect to OceanBase AI Database. With these configurations, the application can connect to and operate OceanBase AI Database.
Use
spring.datasource.driverClassNameto specify the database driver ascom.oceanbase.jdbc.Driver, which is used to establish a connection to OceanBase AI Database.Use
spring.datasource.urlto specify the URL for connecting to the database.Use
spring.datasource.usernameto specify the username for connecting to the database.Use
spring.datasource.passwordto specify the password for connecting to the database.The sample code is as follows:
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=******
Code in UserDaoImpl.java
The UserDaoImpl.java file uses a JdbcTemplate object to execute SQL statements, implementing insert, delete, update, and query operations on the User table.
The code in the UserDaoImpl.java file mainly consists of the following parts:
References to other classes and interfaces.
Declare the package name of this file as
com.oceanbase.testspringboot.dao.impl, which includes the following interfaces and classes:UserDaointerface: Defines methods implemented by the UserDao interface.Userclass: Used to pass and store user data.Autowiredannotation: Injects aJdbcTemplateobject into this class for executing SQL statements.BeanPropertyRowMapperclass: Maps database query results to Java objects.JdbcTemplateclass: Executes SQL statements and handles database access.Repositoryannotation: Marks a Spring repository component for data access.Listinterface: Used to manipulate 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;Definition of the
UserDaoImplclass.Use the
Repositoryannotation to specify the bean name of this class asuserDao. Use aJdbcTemplateobject to execute SQL statements, implementing insert, delete, update, and query operations on theUsertable.Insert user information.
Call the
insertmethod of theJdbcTemplateobject to insert a user's ID and name into thetest_springboottable in the database, and return a corresponding boolean value based on the result of the insertion 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; }Delete user information.
Call the
deletemethod of theJdbcTemplateobject to delete user information from the database based on the user's ID, and return a corresponding boolean value based on the result of the deletion 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; }Update user information.
Call the
updatemethod of theJdbcTemplateobject to update a user's name in the record with the specified ID in thetest_springboottable in the database, and return a corresponding 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; }Query user information.
Use the
JdbcTemplateobject to execute an SQL query, retrieve the user record with the specified ID from thetest_springboottable, and useBeanPropertyRowMapperto 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)); }Query all user information.
Call the
querymethod of theJdbcTemplateobject to query all user information in the database, and map the query results into a list of objects of the User type before returning it.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 methods for operating on user data through the UserDao interface.
The code in the UserDao.java file mainly includes the following parts:
References to other classes and interfaces.
Declare the package name of this file as
package com.oceanbase.testspringboot.dao, which contains the following interfaces and classes:- The
Userclass: used to pass and store user data. - The
Listinterface: used to operate on query result sets.
The sample code is as follows:
import com.oceanbase.testspringboot.entity.User; import java.util.List;- The
Definition of the
UserDaointerface.Using the
UserDaointerface, define methods for operating on user data. These include inserting user information, deleting user information, updating user information, querying user information by ID, and querying all user information.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 a user object.
Declaration of a
Userobject. Declare that theUserclass contains two private fields,idandname, and provides a no-argument constructor. You can set and get the values of theidandnamefields to manipulate user information.The sample code is as follows:
private Long id; private String name; public User() { }Creation of a
Userobject. Define a parameterized constructor for theUserclass to create aUserobject with specifiedidandname.The sample code is as follows:
public User(Long id, String name) { this.id = id; this.name = name; }Getting and setting the values of
idandname. Define four methods in theUserclass to get and set the values of theidandnameattributes. ThegetIdmethod retrieves theidvalue, and thesetIdmethod sets theidvalue. ThegetNamemethod retrieves the value of the usernamename. ThesetNamemethod sets the value of the usernamename.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; }Returns a string representation of the
Userobject.Override the
toStringmethod in theUserclass to return a string representation of theUserobject. Define@Overrideto override the method with the same name in the parent class. Define thetoStringmethod to return a string representation of theUserobject. Format the values of theidandnameattributes into a string using string concatenation and return it to the callingUser.The sample code is as follows:
@Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + '}'; }
Code in TestSpringbootApplication.java
The TestSpringbootApplication.java file uses the UserDao interface to start a Spring Boot application.
The code in the TestSpringbootApplication.java file mainly includes the following parts:
Define classes and interfaces.
Declare that the package name of the current file is
com.oceanbase.testspringboot, which contains the following interfaces and classes:SpringApplicationclass: used to start a Spring Boot application.@SpringBootApplicationannotation: used to indicate that this class is the entry point of the Spring Boot application.
The sample code is as follows:
import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;Define the
TestSpringbootApplicationclass.Use the SpringBootApplication annotation to identify this class as the entry class of a Spring Boot application. You can start a Spring Boot application by calling the run method of the SpringApplication class.
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 uses the UserDao interface to start a Spring Boot application.
The code in the TestSpringbootApplicationTests.java file mainly includes the following parts:
Reference other classes and interfaces.
Declare that the package name of the current file is
com.oceanbase.testspringboot, which contains the following interfaces and classes:UserDaointerface: implements the methods defined in the UserDao interface.Userclass: used to pass and store user data.Testannotation: used to identify test methods.Autowiredannotation: injects a JdbcTemplate object into this class for executing SQL statements.SpringBootTestannotation: used to identify this class as a Spring Boot test class.JdbcTemplateclass: used to execute SQL statements and handle database access.Listinterface: used to manipulate 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;Define the
TestSpringbootApplicationTestsclass.Use the
SpringBootTestannotation to indicate that you are testing a feature in a Spring Boot application. UseUserDaoandJdbcTemplateobjects to perform CRUD operations on user data and output the results.Define objects.
Use the
Autowiredannotation to automatically injectUserDaoandJdbcTemplateobjects.The sample code is as follows:
@Autowired private UserDao userDao; @Autowired private JdbcTemplate jdbcTemplate;Define the
contextLoadsmethod.Use the
contextLoadsmethod as the specific implementation of the test method.Drop the
test_springboottable.Use the
jdbcTemplate.executemethod to execute the SQL statementdrop table test_springbootto drop a table namedtest_springboot.The sample code is as follows:
try { jdbcTemplate.execute("drop table test_springboot"); System.out.println("test_springboot delete successfully!"); }Create the
test_springboottable.Use the
jdbcTemplate.executemethod to execute the SQL statementcreate table test_springboot (id int primary key, name varchar(50))to create a table namedtest_springbootwith two columns:idandname.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!"); }Insert data.
Use the
userDao.insertUsermethod to insert 10 rows of user data into thetest_springboottable. Theidvalues range from 1 to 10, and thenamevalues are"insert" + i.The sample code is as follows:
for (int i = 1; i <= 10; i++) { userDao.insertUser(new User((long) i, "insert" + i)); }Delete data.
Use the
userDao.deleteByIdmethod to delete the user data withidequal to 1.The sample code is as follows:
userDao.deleteById(1L);Update data.
Use the
userDao.updateUsermethod to update the user withid2 by setting itsnametoupdate.The sample code is as follows:
userDao.updateUser(new User(2L, "update"));Query data.
Use the
userDao.selectUserByIdmethod to query the user withid2 and print the result.The sample code is as follows:
User user = userDao.selectUserById(2L); System.out.println("user = " + user);Query all data.
Use the
userDao.selectAllUsersmethod to query all user data in thetest_springboottable and print the result.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.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:oceanbase://host:port/schema_name?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=user_name
spring.datasource.password=******
package com.oceanbase.testspringboot.dao.impl;
import com.oceanbase.testspringboot.dao.UserDao;
import com.oceanbase.testspringboot.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository("userDao")
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate; //Jdbc connection tool class
@Override
public boolean insertUser(User user) {
String sql = "insert into test_springboot(id,name)values(?,?)";
Object[] params = {user.getId(), user.getName()};
return jdbcTemplate.update(sql, params) > 0;
}
@Override
public boolean deleteById(Long id) {
String sql = "delete from test_springboot where id=?";
Object[] params = {id};
return jdbcTemplate.update(sql, params) > 0;
}
@Override
public boolean updateUser(User user) {
String sql = "update test_springboot set name=? where id=?";
Object[] params = {user.getName(), user.getId()};
return jdbcTemplate.update(sql, params) > 0;
}
@Override
public User selectUserById(Long id) {
String sql = "select * from test_springboot where id=?";
Object[] params = new Object[]{id};
return jdbcTemplate.queryForObject(
sql,
params,
new BeanPropertyRowMapper<>(User.class));
}
@Override
public List<User> selectAllUsers() {
String sql = "select * from test_springboot";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
}
}
package com.oceanbase.testspringboot.dao;
import com.oceanbase.testspringboot.entity.User;
import java.util.List;
public interface UserDao {
boolean insertUser(User user);
boolean deleteById(Long id);
boolean updateUser(User user);
User selectUserById(Long id);
List<User> selectAllUsers();
}
package com.oceanbase.testspringboot.entity;
public class User {
private Long id;
private String name;
public User() {
}
public User(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
package com.oceanbase.testspringboot;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class TestSpringbootApplication {
public static void main(String[] args) {
SpringApplication.run(TestSpringbootApplication.class, args);
}
}
package com.oceanbase.testspringboot;
import com.oceanbase.testspringboot.dao.UserDao;
import com.oceanbase.testspringboot.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
@SpringBootTest
class TestSpringbootApplicationTests {
@Autowired
private UserDao userDao;
@Autowired
private JdbcTemplate jdbcTemplate; //Jdbc connection tool class
@Test
void contextLoads() {
try {
// Use the execute() method to execute SQL statements and delete the user table test_springboot
jdbcTemplate.execute("drop table test_springboot");
System.out.println("test_springboot delete successfully!");
} catch (Exception ignore) {
} finally {
// Use the execute() method to execute SQL statements and create user table tests_ user
jdbcTemplate.execute("create table test_springboot (" +
"id int primary key," +
"name varchar(50))");
System.out.println("test_springboot create successfully!");
}
//UserDao userDao=new UserDaoImpl();
//ApplicationContext ioc=new ApplicationContext("/appli");`
//add
for (int i = 1; i <= 10; i++) {
userDao.insertUser(new User((long) i, "insert" + i));
}
//delete
userDao.deleteById(1L);
//update
userDao.updateUser(new User(2L, "update"));
//selectUserById
User user = userDao.selectUserById(2L);
System.out.println("user = " + user);
//query all users
List<User> userList = userDao.selectAllUsers();
userList.forEach(System.out::println);
}
}
References
For more information about OceanBase Connector/J, see OceanBase JDBC Driver
Download the java-oceanbase-springboot sample project