This topic describes how to build an application using the Spring Boot framework and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and a tenant in MySQL mode. For more information, see Create a cluster instance and Create a tenant.
- You have installed 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 the OceanBase Cloud 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 the OceanBase Cloud database
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get a connection string.
Fill in the corresponding information in the following URL based on your created OceanBase Cloud database.
Note
This URL information is required in the
application.propertiesfile.jdbc:mysql://host:port/schema_name?user=$user_name&password=$password&useSSL=false&useUnicode=true&characterEncoding=utf-8Parameter description:
host: The connection address for the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.port: The connection port for the OceanBase Cloud database, default is 3306.schema_name: The name of the schema to access.user_name: The account used to access the database.password: The account password.useSSL=false&useUnicode=true&characterEncoding=utf-8: Additional connection properties.useSSL: Whether to useSSL/TLSfor the connection. Default value:false.useUnicode: Specifies the encoding and decoding format for characters. Default value:true.characterEncoding: The character encoding supported by the database URL option. 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 import the project file.
IntelliJ IDEA automatically identifies the various files in the project. In the Project tool window, you can view the project's directory structure, file list, module list, dependencies, and other information. 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 OceanBase Cloud connection information.
Example:
- The database driver name is:
com.mysql.cj.jdbc.Driver - The OceanBase Cloud connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the schema to access is
test. - The tenant connection account is
mysql001. - The password is
******.
Sample code:
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=mysql001
spring.datasource.password=******
Step 4: Run the java-oceanbase-springboot project
Run the application
- Locate the
TestSpringbootApplicationTests.javafile within the project structure at src > test > java. - In the tool menu bar, select Run (U) > Run > TestSpringbootApplicationTests, or directly click the green triangle in the upper-right corner to run it.
- View the project's log information and output results through IDEA's console.
Output
test_springboot delete successfully!
test_springboot create successfully!
user = User{id=2, name='update'}
User{id=2, name='update'}
User{id=3, name='insert3'}
User{id=4, name='insert4'}
User{id=5, name='insert5'}
User{id=6, name='insert6'}
User{id=7, name='insert7'}
User{id=8, name='insert8'}
User{id=9, name='insert9'}
User{id=10, name='insert10'}
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 issues
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 connection with OceanBase Cloud, add the following parameter to the JDBC URL:
jdbc:mysql://host:port/database?useSSL=true&requireSSL=true
4. Special characters in account credentials
If the username or password contains special characters (such as #), they need to be URL-encoded:
String encodedPassword = URLEncoder.encode(password, "UTF-8");
Note
When using MySQL Connector/J 8.x, ensure that the account credentials do not contain the number sign (#). Otherwise, you may encounter a connection error.
Sample project overview
Download the springboot-mysql sample project.
Extract the archive and open the springboot-mysql directory. It has the following structure:
│--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 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 Java classes of the project.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.
Introduction to pom.xml code
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.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,https://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.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>- Use
Configure parent information.
- Use
<groupId>to specify the parent identifier asorg.springframework.boot. - Use
<artifactId>to specify the parent dependency asspring-boot-starter-parent. - Use
<version>to specify the parent version number as2.7.11. - Use
relativePathto indicate that the parent path is empty.
Code:
<groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.11</version> <relativePath/>- Use
Configure basic information.
- Use
<groupId>to set the group ID tocom.oceanbase. - Use
<artifactId>to set the artifact ID tojava-oceanbase-springboot. - Use
<version>to specify the project version number as0.0.1-SNAPSHOT. - Use
descriptionto introduce the project information asDemo 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>- Use
Configure the Java version.
Specify the Java version used by the project as 1.8.
Sample code:
<properties> <java.version>1.8</java.version> </properties>Configure core dependencies.
Specify the dependency's organization as
org.springframework.bootand its name asspring-boot-starter. This dependency library contains component dependencies that Spring Boot supports by default, supporting features such as web, data processing, security, and testing.Sample code:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency>Specify the dependency's organization as
org.springframework.bootand its name asspring-boot-starter-jdbc. This dependency allows you to use JDBC-related features provided by Spring Boot, such as connection pools and data source configuration.Sample code:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency>Specify the dependency's organization as
org.springframework.bootand its name asspring-boot-starter-test, with a scope oftest. This dependency allows you to use test frameworks and tools provided by Spring Boot, such as JUnit, Mockito, and Hamcrest.Sample code:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency>Use the MySQL Connector/J dependency
mysql:mysql-connector-java:8.0.27.Sample code:
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> </dependencies>
Configure the Maven plugin.
Specify the dependency's organization as
org.springframework.bootand its name asspring-boot-maven-plugin. This plugin is used to package Spring Boot applications into executable JAR or WAR packages that can be run directly.Sample code:
<build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>
Introduction to 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 Cloud. With these configurations, the application can connect to and operate OceanBase Cloud.
Use
spring.datasource.driverClassNameto specify the database driver ascom.mysql.cj.jdbc.Driver, which is used to establish a connection to OceanBase Cloud.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.Sample code:
spring.datasource.driverClassName=com.mysql.cj.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=******
Introduction to 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.
Declares the package name for this file as
com.oceanbase.testspringboot.dao.impl, containing the following interfaces and classes:UserDaointerface: Defines methods implemented in 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.
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;Definition of the
UserDaoImplclass.Uses the
Repositoryannotation to specify the class'sBeanname asuserDao, and uses aJdbcTemplateobject to execute SQL statements, implementing insert, delete, update, and query operations on theUsertable.Insert user information.
Calls the
updatemethod of theJdbcTemplateobject to insert a user's ID and name into thetest_springboottable in the database, and returns a corresponding boolean value based on the insertion result.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 information.
Calls the
updatemethod of theJdbcTemplateobject to delete user information from the database based on the user's ID, and returns a corresponding boolean value based on the deletion result.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 information.
Calls the
updatemethod of theJdbcTemplateobject to update a user's name in the record with the specified ID in thetest_springboottable in the database, and returns a corresponding boolean value based on the update result.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 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.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 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.Sample code:
@Override public List<User> selectAllUsers() { String sql = "select * from test_springboot"; return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class)); }
Introduction to UserDao.java
The UserDao.java file defines methods for manipulating 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 manipulate query result sets.
Code:
import com.oceanbase.testspringboot.entity.User; import java.util.List;- The
Define the
UserDaointerface.Using the
UserDaointerface, define methods for manipulating user data. These include inserting user information, deleting user information, updating user information, querying user information by ID, and querying all user information.Code:
public interface UserDao { boolean insertUser(User user); boolean deleteById(Long id); boolean updateUser(User user); User selectUserById(Long id); List<User> selectAllUsers(); }
Introduction to User.java
The User.java file defines the User class to represent a user object.
Declare a
Userobject. Declare that theUserclass contains two private fields,idandname, and provides a no-argument constructor. You can manipulate user information by setting and getting the values of theidandnamefields.Code:
private Long id; private String name; public User() { }Create a
Userobject. Define a parameterized constructor for theUserclass to create aUserobject with specifiedidandname.Code:
public User(Long id, String name) { this.id = id; this.name = name; }Get and set the values of
idandname. Define four methods in theUserclass to get and set the values of theidandnameattributes. ThegetIdmethod is used to get theidvalue, and thesetIdmethod is used to set theidvalue. ThegetNamemethod is used to get the value of the usernamename. ThesetNamemethod is used to set the value of the usernamename.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 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 caller.Sample code:
@Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + '}'; }
Introduction to TestSpringbootApplication.java
The TestSpringbootApplication.java file uses the UserDao interface to start a Spring Boot application.
The code in the TestSpringbootApplication.java file mainly consists of 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.
Sample code:
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.
Sample code:
@SpringBootApplication public class TestSpringbootApplication { public static void main(String[] args) { SpringApplication.run(TestSpringbootApplication.class, args); } }
Introduction to TestSpringbootApplicationTests.java
The TestSpringbootApplicationTests.java file uses the UserDao interface to start a Spring Boot application.
The code in the TestSpringbootApplicationTests.java file mainly consists of 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: Defines methods implemented in the UserDao interface.Userclass: Used to pass and store user data.Testannotation: Identifies a test method.Autowiredannotation: Injects a JdbcTemplate object into this class for executing SQL statements.SpringBootTestannotation: Identifies 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.
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
TestSpringbootApplicationTestsclass.Use the
SpringBootTestannotation to identify tests for features in the Spring Boot application. UseUserDaoandJdbcTemplateobjects to perform create, read, update, and delete operations on user data and output the results.Define objects.
Use the
Autowiredannotation to automatically injectUserDaoandJdbcTemplateobjects.Sample code:
@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.Sample code:
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 fields:idandname.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!"); }Insert data.
Use the
userDao.insertUsermethod to insert 10 rows of user data into thetest_springboottable. Theidranges from 1 to 10, and thenameis"insert" + i.Sample code:
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 withid1.Sample code:
userDao.deleteById(1L);Update data.
Use the
userDao.updateUsermethod to update the user withid2 by settingnametoupdate.Sample code:
userDao.updateUser(new User(2L, "update"));Query data.
Use the
userDao.selectUserByIdmethod to query the user withid2 and print the result.Sample code:
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.Sample code:
List<User> userList = userDao.selectAllUsers(); userList.forEach(System.out::println);
Complete sample code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.11</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.oceanbase</groupId>
<artifactId>java-oceanbase-springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>java-oceanbase-springboot</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</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:mysql://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 {
// Create the test_springboot table by calling execute().
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 driver configuration options, see the MySQL Connector/J Developer Guide.