This topic describes how to build an application using the SpringDataJPA framework and OceanBase AI Database to perform basic operations such as creating tables, inserting data, and querying data.
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 string for OceanBase AI Database.
- Import the
java-oceanbase-springdatajpaproject into IntelliJ IDEA. - Modify the database connection information in the
java-oceanbase-springdatajpaproject. - Run the
java-oceanbase-springdatajpaproject.
Step 1: Obtain the OceanBase AI Database connection string
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the connection string.
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -AEnter the URL of OceanBase AI Database.
Note
This URL information is required in the
application.ymlfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8Parameter 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 connecting directly to the database, but is required for ODP connections.password: The account password.characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8: Additional connection properties.characterEncoding: The character encoding supported by the database URL option. Default value isutf8.useSSL: Whether to force the use ofSSL/TLSfor the connection. Default value isfalse.serverTimezone: Sets the server timezone. Default value is China Standard Time.
For more information about URL parameters, see Database URL.
Step 2: Import the java-oceanbase-springdatajpa project into IntelliJ IDEA
Start IntelliJ IDEA and choose 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 typically 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-springdatajpa project
Modify the database connection information in the application.yml file based on the information obtained in Step 1: Obtain the OceanBase AI Database connection string.
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.mysql001is a user tenant created in OceanBase AI Database, androotis the username of themysql001tenant. - The password is
******.
The sample code is as follows:
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://10.10.10.1:2881/test?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: root@mysql001
password: ******
type: com.alibaba.druid.pool.DruidDataSource
Step 4: Run the java-oceanbase-springdatajpa project
Run path
- Locate the
TestSpringDataJpaApplicationTests.javafile in the project structure under src > test > java. - In the toolbar, select Run > Run... > TestSpringDataJpaApplicationTests.contextLoads, 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
User{id=1, username='insert1'}
User{id=2, username='update'}
User{id=3, username='insert3'}
User{id=4, username='insert4'}
User{id=5, username='insert5'}
Project code
Click java-oceanbase-springdatajpa to download the project code, which is a compressed file named java-oceanbase-springdatajpa.
After decompressing it, you will get a folder named java-oceanbase-springdatajpa. The directory structure is as follows:
│--pom.xml
│
├─.idea
│
├─src
│ ├─main
│ │ ├─java
│ │ │ └─com
│ │ │ └─oceanbase
│ │ │ └─testspringdatajpa
│ │ │ │--TestSpringDataJpaApplication.java
│ │ │ │
│ │ │ ├─dao
│ │ │ │ └─UserRepository.java
│ │ │ │
│ │ │ ├─entity
│ │ │ │ └─User.java
│ │ │ │
│ │ │ └─service
│ │ │ │--UserService.java
│ │ │ │
│ │ │ └─impl
│ │ │ └─--UserServiceImpl.java
│ │ │
│ │ └─resources
│ │ │--application.yml
│ │ │
│ │ ├─static
│ │ └─templates
│ └─test
│ └─java
│ └─com
│ └─oceanbase
│ └─testspringdatajpa
│ └─--TestSpringDataJpaApplicationTests.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 builds..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.testspringdatajpa: The root directory for Java packages, containing all the project's Java classes.TestSpringDataJpaApplication.java: The entry class for the Spring Boot application.dao: The directory for storing the Data Access Object (DAO) package, used to access databases or other data storage services.UserRepository.java: The user data access interface, defining operations for creating, reading, updating, and deleting user data.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.service: The directory for service interfaces, used to define business logic interfaces.UserService.java: The user service interface, defining methods for operating on user data.impl: The directory for service implementations, used to store implementation classes for business logic.UserServiceImpl.java: The implementation class for the user service, implementing the methods defined in the UserService interface.resources: The directory for storing resource files, such as configuration files and SQL files.application.yml: The application configuration file, used to configure information such as database connections.static: The directory for storing static files, such as CSS and JavaScript.templates: The directory for storing template files, such as HTML templates.test: The directory for storing test code and resource files.TestSpringDataJpaApplicationTests.java: The class used to execute and verify Spring Data JPA-related functionality.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 document 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>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.0.<relativePath>: an empty path for the parent project.
The sample code is as follows:
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.0</version> <relativePath/> </parent>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-springdatajpa.<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-springdatajpa</artifactId> <version>0.0.1-SNAPSHOT</version> <name>java-oceanbase-springdatajpa</name> <description>Demo project for Spring Boot</description>Configure the Java version.
Specify to use Java 1.8 for the project.
The sample code is as follows:
<properties> <java.version>1.8</java.version> </properties>Configure core dependencies.
Define a dependency named
spring-boot-starter-data-jpathat belongs to theorg.springframework.bootgroup. This dependency contains necessary dependencies and configurations for JPA-based data accesses, and is a Spring Boot starter.The sample code is as follows:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>Define a dependency named
spring-boot-starter-webthat belongs to theorg.springframework.bootgroup. This dependency contains necessary dependencies and configurations for Spring Boot-based web development, and is a Spring Boot starter.The sample code is as follows:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</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
druidthat belongs to thecom.alibabagroup and whose version is1.2.8. This dependency allows the project to use the Druid library to manage and optimize the acquisition and release of database connections.The sample code is as follows:
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>Define a dependency named
oceanbase-clientthat belongs to thecom.oceanbasegroup and whose version is2.4.2. 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.2</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. To avoid conflicts with other plugins or tools, exclude thelombokdependency during building.The sample code is as follows:
<build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build>
Code in application.yml
The application.yml file is the configuration file for a Spring Boot application, containing several key parameters. It configures the application's context path, listening port number, database connection information, and JPA-related properties.
The code in the application.yml file mainly consists of the following sections:
Serversectionservlet: Configures Servlet-related properties.context-path: Sets the application's context path totestspringdatajpa.port: Sets the port number that the application listens on to 8890.
The sample code is as follows:
server: servlet: context-path: /testspringdatajpa port: 8890Springsectiondatasource: Configures data source-related properties, i.e., database connection information.driver-class-name: Specifies the database driver class name ascom.mysql.cj.jdbc.Driver.url: Specifies the database connection URL, including the database address, port number, and database name.username: Specifies the username for connecting to the database.password: Specifies the password for connecting to the database.type: Specifies to use the Druid connection pool as the data source.
The sample code is as follows:
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://host:port/schema_name?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8 username: user_name password: ****** type: com.alibaba.druid.pool.DruidDataSourceJPAsectionhibernate: Configures Hibernate-related properties.ddl-auto: Specifies to automatically update the database schema during startup. Value:update.show-sql: Specifies whether to print SQL statements to the console. Value:true.format-sql: Specifies whether to format printed SQL statements. Value:true.
open-in-view: Specifies whether to enableOpen-in-Viewmode. Value:false.
The sample code is as follows:
jpa: hibernate: ddl-auto: update show-sql: true format-sql: true open-in-view: false
Code in UserRepository.java
The userRepository.java file defines methods for performing database operations on user entities (User) through a DAO interface. It contains custom update and query methods that use SQL and HQL statements.
The code in the userRepository.java file mainly consists of the following sections:
References to other classes and interfaces.
Declares that this file contains the following interfaces and classes:
Userclass: used to operate on and process user objects within service classes.JpaRepositoryinterface: inherits from this interface to obtain basic CRUD operation methods.Modifyingannotation: marks methods that perform modification operations.Queryannotation: defines custom query methods.Paramannotation: maps method parameters to parameters in the query statement.Repositoryannotation: marks the DAO interface as a Spring repository component.
The sample code is as follows:
import com.oceanbase.testspringdatajpa.entity.User; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Modifying; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository;Define the
UserRepositoryinterface.The
UserRepositoryinterface is used to access user data. It is marked as a SpringRepositorycomponent, and thebeanname is specified asuserRepository.- Use the
@Queryannotation to specify an SQL statement, and setnativeQuery = trueto use native SQL statements. - Use the
@Modifyingannotation to define a custom update method that performs updates using native SQL statements. - Use the
@Queryannotation to specify an HQL (Hibernate Query Language) statement, and use the@Paramannotation to map method parameters to parameters in the HQL statement.
The sample code is as follows:
@Repository("userRepository") public interface UserRepository extends JpaRepository<User, Integer> { @Query(value = "update test_springdatajpa_2 set username=?1 where id=?2", nativeQuery = true) @Modifying int updateById(String name, String id); @Query("SELECT u FROM User u WHERE u.username = :username") User findUser(@Param("username") String username); }- Use the
Code in User.java
The User.java file defines the User class to represent a user object.
The code in the User.java file mainly includes the following parts:
Import other classes.
javax.persistence.*: Imports all classes in thejavax.persistencepackage.javax.persistenceis the standard package of the Java Persistence API (JPA), providing a set of interfaces and annotations for Object-Relational Mapping (ORM).The sample code is as follows:
import javax.persistence.*;Define the
Userobject. Use JPA annotations to identify the mapping relationship between the entity class and the table, including the table name, primary key, field names, etc. The@Entityannotation marks this class as an entity class. The@Tableannotation specifies the corresponding database table name. The@Idannotation marks the primary key of the entity class. The@GeneratedValueannotation specifies the primary key generation strategy. The@Columnannotation is used to specify the field name and constraints. This class has two attributes,idandusername, representing the unique identifier and username of the user, respectively. It also provides a default constructor for creating an emptyUserobject.The sample code is as follows:
@Entity @Table(name = "test_springdatajpa_2") public class User { @Id @GeneratedValue(strategy = GenerationType.TABLE) @Column(name = "id", nullable = false) private Integer id; @Column(name = "username") private String username; public User() { } }Get and set the values of
idandname. This entity class provides a parameterized constructor for creating an entity class object and initializing theidandusernameattributes. Four methods are defined to get and set the values of theidandnameattributes.- The
getIdmethod retrieves the value ofid. - The
setIdmethod sets the value ofid. - The
getNamemethod retrieves the value of the usernamename. - The
setNamemethod sets the value of the usernamename.
The sample code is as follows:
public User(Integer id, String username) { this.id = id; this.username = username; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; }- The
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. By concatenating strings, format the values of theidandnameattributes into a string and return it to the callingUser.The sample code is as follows:
@Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + '}'; }
Code in UserServiceImpl.java
The UserServiceImpl.java file uses JPA to perform CRUD operations on user data in the database.
The code in the UserServiceImpl.java file mainly includes the following parts:
Reference other classes and interfaces.
Declare that this file contains the following interfaces and classes:
UserRepositoryclass: Used to inject and call methods defined in the data access object (DAO) within service classes.Userclass: Used to manipulate and process user objects within service classes.UserServiceclass: Used to inject and call methods defined in service classes from other classes.Autowiredannotation: Used for automatic dependency injection.Pageclass: Encapsulates paginated query results.PageRequestclass: Used to create a pagination request object.Pageableinterface: Defines a pagination request.Sortclass: Defines sorting rules.Serviceannotation: Marks a class as a service class.Transactionalannotation: Marks a method as transactional.Iteratorclass: Used to traverse collections.Optionalclass: Handles objects that may be null.
The sample code is as follows:
import com.oceanbase.testspringdatajpa.dao.UserRepository; import com.oceanbase.testspringdatajpa.entity.User; import com.oceanbase.testspringdatajpa.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.Iterator; import java.util.Optional;The
UserServiceImplclass implements the methods declared in theUserServiceinterface.Access to the database is implemented through dependency injection of
UserRepository. It specifically implements methods for inserting, deleting, updating, and querying user data, and supports paginated queries. The@Transactionalannotation is used to enable transaction support, ensuring database operations are executed within a transaction.Constructor for the
UserRepositoryproperty. Define a privateUserRepositoryproperty and use the@Autowiredannotation in the constructor for dependency injection. Inject theUserRepositorydependency through the constructor, and use theuserRepositoryproperty to access and manipulate user data.The sample code is as follows:
private final UserRepository userRepository; @Autowired public UserServiceImpl(UserRepository userRepository) { this.userRepository = userRepository; }Insert user data. The
insertUsermethod accepts aUserobject as a parameter. By calling thesavemethod ofUserRepository, the user object is saved to the database.The sample code is as follows:
@Override public void insertUser(User user) { userRepository.save(user); }Delete user data. In the
deleteUsermethod, call theexistsByIdmethod ofUserRepositoryto check if a user with the specified ID exists. If the user exists, perform the deletion; if not, return directly.The sample code is as follows:
@Override public void deleteUser(Integer id) { if (!userRepository.existsById(id)) { return; } }Update user data. In the
updateUsermethod, call thesavemethod ofUserRepositoryto save the user object to the database, implementing the update operation. Also, return an integer value1to indicate the update operation was successful. Additionally, there is a method to update user data by username andid. This is achieved by calling theupdateByIdmethod ofUserRepositoryto perform the update and return the result.The sample code is as follows:
@Override public int updateUser(User user) { userRepository.save(user); return 1; } //update based on name and id @Override public int updateUserById(String name, String id) { return userRepository.updateById(name, id); }Query user data.
- Query user data by
ID. In theselectUserByIdmethod, call thefindByIdmethod ofUserRepositoryto query user data and encapsulate the result in anOptionalobject. Then, call theorElsemethod ofOptionalto obtain the value in theOptionalobject and assign it to theuservariable. Finally, return the queried user object. - Query user data by username. In the
selectUserByNamemethod, call thefindUsermethod ofUserRepositoryto query user data by username and directly return the queried user object.
The sample code is as follows:
@Override public User selectUserById(Integer id) { Optional<User> optional = userRepository.findById(id); User user = optional.orElse(null); return user; } @Override public User selectUserByName(String username) { return userRepository.findUser(username); }- Query user data by
Paginate and query all user data. In the
selectUserAllmethod, create aSortobject to specify the sorting rule and aPageableobject to specify the pagination parameters. Call thefindAllmethod ofUserRepositoryto query user data and return aPageobject. Then, call theiteratormethod of thePageobject to obtain an iterator for the user data. Finally, return the iterator for the user data.The sample code is as follows:
@Override public Iterator<User> selectUserAll(Integer pageNum, Integer pageSize) { Sort sort = Sort.by(Sort.Direction.ASC, "id"); Pageable pageable = PageRequest.of(pageNum, pageSize, sort); Page<User> users = userRepository.findAll(pageable); Iterator<User> userIterator = users.iterator(); return userIterator; }
Code in UserService.java
The UserService.java file uses JPA to perform CRUD operations on user data in the database.
The code in the UserService.java file mainly consists of the following parts:
Import other classes and interfaces.
Declare that this file contains the following classes:
- The
Userclass: used to operate on and process user objects in the service class. - The
Iteratorclass, used to traverse collections.
The sample code is as follows:
import com.oceanbase.testspringdatajpa.entity.User; import java.util.Iterator;- The
Define the
UserServiceinterface. Define methods for inserting, deleting, updating, and querying user data through theUserServiceinterface. Include the following methods and classes:The
insertUsermethod, used to insert user data.The
deleteUsermethod, used to delete user data.The
updateUsermethod, used to update user data.The
updateUserByIdmethod, used to modify a user'snamebased onid.The
selectUserByIdmethod, used to query user data based onid.The
selectUserByNamemethod, used to query user data based on username.The
selectUserAllmethod, used to query all user data in pages.The
Userclass, used to operate on and process user objects in the service class.The
Iteratorclass, used to traverse collections.
The sample code is as follows:
public interface UserService { void insertUser(User user); void deleteUser(Integer id); int updateUser(User user); int updateUserById(String name, String id); User selectUserById(Integer id); User selectUserByName(String username); Iterator<User> selectUserAll(Integer pageNum, Integer pageSize); }
Code in TestSpringDataJpaApplication.java
The TestSpringDataJpaApplication.java file is used to start and configure a Spring Boot application.
The code in the TestSpringDataJpaApplication.java file mainly consists of the following parts:
Define classes and interfaces. Declare that this file contains the following classes:
- The
SpringApplicationclass, used to start the Spring Boot application. - The
@SpringBootApplicationannotation, used to mark the entry class of the Spring Boot application.
The sample code is as follows:
import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;- The
Define the
TestSpringDataJpaApplicationclass. Use the@SpringBootApplicationannotation to mark the entry class of the Spring Boot application. In themainmethod, call theSpringApplication.runmethod to start the application.The sample code is as follows:
@SpringBootApplication public class TestSpringDataJpaApplication { public static void main(String[] args) { SpringApplication.run(TestSpringDataJpaApplication.class, args); } }
Code in TestSpringDataJpaApplicationTests.java
The TestSpringDataJpaApplicationTests.java file is used to start and configure a Spring Boot application.
The code in the TestSpringDataJpaApplicationTests.java file mainly consists of the following parts:
Reference other classes and interfaces. Declare that this file contains the following classes:
- The
Userclass: used to operate on and process user objects in service classes. - The
UserServiceclass: used to inject and call methods defined in service classes from other classes. - The
@Testannotation: used to mark test methods. - The
Autowiredannotation: used for automatic dependency injection. - The
@SpringBootTestannotation: used to test Spring Boot applications. - The
Iteratorclass: used to traverse collections.
The sample code is as follows:
import com.oceanbase.testspringdatajpa.entity.User; import com.oceanbase.testspringdatajpa.service.UserService; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import java.util.Iterator;- The
Define the
UserServicemethod. This method tests the various methods of theUserServiceservice in the Spring Boot application. By injecting theUserServicedependency and calling its methods, it tests the functionality for inserting, deleting, updating, and querying user data, and prints the relevant results.Use the
@Autowiredannotation. Automatically inject the implementation class of theUserServiceinterface into theuserServicevariable.The sample code is as follows:
@Autowired private UserService userService;Use the
contextLoadsmethod for testing.Insert user data. Use a
forloop to call theinsertUsermethod ofuserServiceand insert 10 sets of user data.The sample code is as follows:
for (int i = 1; i <= 10; i++) { userService.insertUser(new User(i, "insert" + i)); }Delete user data. Call the
deleteUsermethod ofuserServiceto delete the user with id 1.The sample code is as follows:
userService.deleteUser(1);Modify user data. Call the
updateUsermethod ofuserServiceto update the user with id 2.The sample code is as follows:
userService.updateUser(new User(2, "update"));Query user data.
- Call the
selectUserByIdmethod ofuserServiceto query user data by id, assign the result to theuservariable, and print theuserobject. - Call the
selectUserByNamemethod ofuserServiceto query user data by username, assign the result to theuserByNamevariable, and print theuserByNameobject.
The sample code is as follows:
User user = userService.selectUserById(2); System.out.println("user = " + user); User userByName = userService.selectUserByName("insert"); System.out.println("userByName = " + userByName);- Call the
Paginate through user data. Call the
selectUserAllmethod ofuserServiceto query all user data in pages, assign the result to theuserIteratorvariable, use theforEachRemainingmethod to iterate throughuserIterator, and print each user object.The sample code is as follows:
Iterator<User> userIterator = userService.selectUserAll(0, 5); userIterator.forEachRemaining(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.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.oceanbase</groupId>
<artifactId>java-oceanbase-springdatajpa</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>java-oceanbase-springdatajpa</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-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.2</version>
</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>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
server:
servlet:
context-path: /testspringdatajpa
port: 8890
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:oceanbase://host:port/schema_name?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: user_name
password: ******
type: com.alibaba.druid.pool.DruidDataSource
jpa:
hibernate:
ddl-auto: update
show-sql: true
format-sql: true
open-in-view: false
package com.oceanbase.testspringdatajpa.dao;
import com.oceanbase.testspringdatajpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository("userRepository")
public interface UserRepository extends JpaRepository<User, Integer> {
//Custom repository handwritten SQL, placeholder value transfer form
@Query(value = "update test_springdatajpa_2 set username=?1 where id=?2", nativeQuery = true)
@Modifying
int updateById(String name, String id);
//SPEL expression, hql syntax
@Query("SELECT u FROM User u WHERE u.username = :username")
User findUser(@Param("username") String username);//Mapping parameter username to database field username
}
package com.oceanbase.testspringdatajpa.entity;
import javax.persistence.*;
@Entity
@Table(name = "test_springdatajpa_2")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Column(name = "id", nullable = false)
private Integer id;
@Column(name = "username")
private String username;
public User() {
}
public User(Integer id, String username) {
this.id = id;
this.username = username;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
'}';
}
}
package com.oceanbase.testspringdatajpa.service.impl;
import com.oceanbase.testspringdatajpa.dao.UserRepository;
import com.oceanbase.testspringdatajpa.entity.User;
import com.oceanbase.testspringdatajpa.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.Iterator;
import java.util.Optional;
@Transactional
@Service("userService")
public class UserServiceImpl implements UserService {
private final UserRepository userRepository;
@Autowired
public UserServiceImpl(UserRepository userRepository) {
this.userRepository = userRepository;
}
//insert
@Override
public void insertUser(User user) { userRepository.save(user); }
//delete
@Override
public void deleteUser(Integer id) {
if (!userRepository.existsById(id)) {
return;
}
}
//update
@Override
public int updateUser(User user) {
userRepository.save(user);
return 1;
}
//update based on name and id
@Override
public int updateUserById(String name, String id) {
return userRepository.updateById(name, id);
}
// select one user
@Override
public User selectUserById(Integer id) {
Optional<User> optional = userRepository.findById(id);
User user = optional.orElse(null);
return user;
}
//query user based on username
@Override
public User selectUserByName(String username) {
return userRepository.findUser(username);
}
@Override
public Iterator<User> selectUserAll(Integer pageNum, Integer pageSize) {
//By passing parameters to this method, physical pagination can be achieved, which is very simple.
Sort sort = Sort.by(Sort.Direction.ASC, "id");
Pageable pageable = PageRequest.of(pageNum, pageSize, sort);
Page<User> users = userRepository.findAll(pageable);
Iterator<User> userIterator = users.iterator();
return userIterator;
}
}
package com.oceanbase.testspringdatajpa.service;
import com.oceanbase.testspringdatajpa.entity.User;
import java.util.Iterator;
public interface UserService {
//insert
void insertUser(User user);
//delete
void deleteUser(Integer id);
//update
int updateUser(User user);
//customize SQL and modify name based on id
int updateUserById(String name, String id);
//select one user
User selectUserById(Integer id);
//customize SQL to query users based on username
User selectUserByName(String username);
//query all users
Iterator<User> selectUserAll(Integer pageNum, Integer pageSize);
}
package com.oceanbase.testspringdatajpa;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class TestSpringDataJpaApplication {
public static void main(String[] args) {
SpringApplication.run(TestSpringDataJpaApplication.class, args);
}
}
package com.oceanbase.testspringdatajpa;
import com.oceanbase.testspringdatajpa.entity.User;
import com.oceanbase.testspringdatajpa.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.Iterator;
@SpringBootTest
class TestSpringDataJpaApplicationTests {
@Autowired
private UserService userService;
@Test
void contextLoads() {
//insert
for (int i = 1; i <= 10; i++) {
userService.insertUser(new User(i, "insert" + i));
}
//delete
userService.deleteUser(1);
//update
userService.updateUser(new User(2, "update"));
//selectUserById
User user = userService.selectUserById(2);
System.out.println("user = " + user);
//selectUserByName
User userByName = userService.selectUserByName("insert");
System.out.println("userByName = " + userByName);
//query all users
Iterator<User> userIterator = userService.selectUserAll(0, 5);
userIterator.forEachRemaining(System.out::println);
}
}
References
For more information about OceanBase Connector/J, see OceanBase JDBC Driver
Download the java-oceanbase-springdatajpa sample project