This topic describes how to build an application using the Spring Data JPA 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 an OceanBase Cloud tenant in Oracle-compatible 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 string for the OceanBase Cloud 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 Cloud database connection string
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 connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud 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 connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.port: The connection port of the OceanBase Cloud database, default is 1521.schema_name: The name of the schema to access.user_name: The account for accessing the database.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 useSSL/TLSfor the connection. Default value isfalse.serverTimezone: Specifies the server time zone for the connection. This example uses GMT+8. Adjust it to match your environment.
For more information about URL parameters, see Database URL.
Step 2: Import the java-oceanbase-springdatajpa 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 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-springdatajpa project
Modify the database connection information in the application.yml file based on the information obtained in Step 1: Obtain the OceanBase Cloud connection string.
Example:
- The name of the database driver is:
com.oceanbase.jdbc.Driver. - The OceanBase Cloud connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The name of the schema to access is
sys. - The tenant connection account is oracle001.
- The password is
******.
Sample code:
spring:
datasource:
driver-class-name: com.oceanbase.jdbc.Driver
url: jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
username: oracle001
password:******
type: com.alibaba.druid.pool.DruidDataSource
Step 4: Run the java-oceanbase-springdatajpa project
Run the application
- In the project structure, locate the
TestSpringDataJpaApplicationTests.javafile under src > test > java. - In the tool menu bar, 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 IDEA's console.
Output
User{id=4, username='insert4'}
User{id=5, username='insert5'}
User{id=6, username='insert6'}
User{id=7, username='insert7'}
User{id=8, username='insert8'}
Sample project overview
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
File description:
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 Data Access Object (DAO) packages, 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.
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>- 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.0. - Use
relativePathto indicate that the parent path is empty.
Code:
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.0</version> <relativePath/> </parent>- Use
Configure basic information.
- Use
<groupId>to set the group ID tocom.oceanbase. - Use
<artifactId>to set the artifact ID tojava-oceanbase-springdatajpa. - 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-springdatajpa</artifactId> <version>0.0.1-SNAPSHOT</version> <name>java-oceanbase-springdatajpa</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-data-jpa. This dependency library contains the necessary dependencies and configurations for data access using JPA. Spring Boot Starter Data JPA is a Spring Boot starter.Sample code:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>Specify the dependency's organization as
org.springframework.bootand its name asspring-boot-starter-web. This dependency library contains the dependencies and configurations required for web development using Spring Boot. It is a Spring Boot starter.Sample code:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</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 the testing 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>Specify the dependency's organization as
com.alibaba, its name asdruid, and its version as1.2.8. This dependency allows you to use the Druid library to manage and optimize the acquisition and release of database connections.Sample code:
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>Specify the dependency's organization as
com.oceanbase, its name asoceanbase-client, and its version as2.4.3. This dependency allows you to use the client features provided by OceanBase, such as connection, query, and transaction.Sample code:
<dependencies> <dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.2</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. To avoid conflicts with other plugins or tools, the configuration excludes Lombok from the packaged application.Sample code:
<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>
Introduction to application.yml code
The application.yml file is a configuration file for a Spring Boot application, containing 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 properties related to Servlet.context-path: Sets the application's context path totestspringdatajpa.port: Sets the port number that the application listens on to 8890.
Sample code:
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.oceanbase.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.
Sample code:
spring: datasource: driver-class-name: com.oceanbase.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.DruidDataSourceJPAsectionhibernate: Configures properties related to Hibernate.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.
Sample code:
jpa: hibernate: ddl-auto: update show-sql: true format-sql: true open-in-view: false
Introduction to userRepository.java
The userRepository.java file defines methods for performing database operations on user entities (User) through the 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.
Sample code:
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.
Sample code:
@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
Introduction to the User.java file
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 from 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).Sample code:
import javax.persistence.*;Define the
Userobject. Use JPA annotations to specify the mapping relationships between entity classes and tables, 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 user's unique identifier and username, respectively. It also provides a default constructor for creating an emptyUserobject.Sample code:
@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.
Sample code:
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. 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 + ", username='" + username + '\'' + '}'; }
Introduction to the UserServiceImpl.java file
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.
Declares 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.Pageinterface: Encapsulates pagination 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.Iteratorinterface: Used to traverse collections.Optionalclass: Handles objects that may be null.
Sample code:
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 pagination 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 on the constructor for dependency injection. Inject theUserRepositorydependency through the constructor, and use theuserRepositoryproperty to access and manipulate user data.Sample code:
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.Sample code:
@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.Sample code:
@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. Additionally, return an integer value1to indicate the update was successful. Furthermore, there is also a method to update user data by username andid. This is achieved by calling theupdateByIdmethod ofUserRepositoryto perform the update and return the result.Sample code:
@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.
Sample code:
@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.Sample code:
@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; }
Introduction to 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 includes 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
Iteratorinterface, used to traverse collections.
Sample code:
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 on theid.The
selectUserByIdmethod, used to query user data based on theid.The
selectUserByNamemethod, used to query user data based on the 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
Iteratorinterface, used to traverse collections.
Sample code:
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); }
Introduction to the TestSpringDataJpaApplication.java file
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.
Sample code:
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.Sample code:
@SpringBootApplication public class TestSpringDataJpaApplication { public static void main(String[] args) { SpringApplication.run(TestSpringDataJpaApplication.class, args); } }
Introduction to the TestSpringDataJpaApplicationTests.java file
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
Iteratorinterface: used to traverse collections.
Sample code:
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 aUserServicedependency 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.Sample code:
@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.Sample code:
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.Sample code:
userService.deleteUser(1);Modify user data. Call the
updateUsermethod ofuserServiceto update the user with id 2.Sample code:
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.
Sample code:
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 traverseuserIterator, and print each user object.Sample code:
Iterator<User> userIterator = userService.selectUserAll(0, 5); userIterator.forEachRemaining(System.out::println);
Complete 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.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.oceanbase.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> {
// Execute custom repository SQL with parameter placeholders.
@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) {
// Pass pagination parameters to retrieve a page of results from the database.
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