Connect to OceanBase Database by using Spring Data JPA

This topic describes how to use the Spring Data JPA framework and OceanBase Database to build an application that performs basic database operations such as creating tables, inserting data, and querying data.

Download the java-oceanbase-springdatajpa sample project

Prerequisites

• You have installed OceanBase Database.
• You have installed Java Development Kit (JDK) 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the OceanBase Database connection string.
2. Import the java-oceanbase-springdatajpa project to IntelliJ IDEA.
3. Modify the database connection information in the java-oceanbase-springdatajpa project.
4. Run the java-oceanbase-springdatajpa project.

Step 1: Obtain the OceanBase Database connection string

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

   obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Enter the URL of OceanBase Database.

   Note

   The URL of OceanBase Database is required in the application.yml file.

   jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
   

   The parameters are described as follows:

   • host: the IP address for connecting to OceanBase Database. It is the IP address of ODP for connection through ODP, or the IP address of an OBServer node for direct connection.

   • port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

   • schema_name: the name of the schema to be accessed.

   • user_name: the username of the account in the format of user@tenant#cluster name or username@SERVICE:service name, which is specified by the -u parameter. When the value is in the user@tenant#cluster name format, the default tenant is sys and the default administrator is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through ODP.

   • password: the password of the account.

   • characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8 specifies the additional connection properties.

     • characterEncoding: the character encoding format for the URL of OceanBase Database. The default value is utf8.
     • useSSL: specifies whether to use SSL/TLS during forced connections. The default value is false.
     • serverTimezone: the server time zone. The default value is China Standard Time (CST).

For more information about URL parameters, see Database URL.

Step 2: Import the java-oceanbase-springdatajpa project to IntelliJ IDEA

1. Start IntelliJ IDEA and choose File > Open....

   

2. In the Open File or Project window, select the project files and click OK to import the files.

3. IntelliJ IDEA automatically identifies various files in the project. You can view project information such as the directory structure, file list, module list, and dependencies in the Project window. Generally, the Project window is at the leftmost of the UI of IntelliJ IDEA and is opened by default. If the Project window is closed, you can choose View > Tool Windows > Project in the menu bar or press the keyboard shortcut Alt + 1 to open it.

   Note

   When you use IntelliJ IDEA to import a project, IntelliJ IDEA 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.

4. View the project.

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

Modify the database connection string in the application.yml file based on the information obtained in Step 1: Obtain the OceanBase Database connection string.

Here is an 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. mysql001 is a MySQL user tenant created in OceanBase Database, and root is the username of a user in the mysql001 tenant.
• The password is ******.

Here is the sample code:

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

Path

1. Find the TestSpringDataJpaApplicationTests.java file under src > test > java in the project package.
2. Choose Run > Run... > TestSpringDataJpaApplicationTests.contextLoads in the menu bar or click the green triangle in the upper-right corner to run the file.
3. View the logs and output of the project in the Console window of IntelliJ IDEA.

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 here to download the project code, which is a package named java-oceanbase-springdatajpa.

Decompress the package to obtain 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 of the Maven project, which contains the dependencies, plug-ins, and build rules of the project.
• .idea: a directory used in an Integrated Development Environment (IDE) to store configuration information related to the project.
• src: a directory that stores the source code in the project.
• main: a directory that stores the main source code and resource files.
• java: a directory that stores the Java source code.
• com: the root directory of the Java package.
• oceanbase: the root directory of the project.
• testspringdatajpa: the root directory of the Java package, which contains all the Java classes of the project.
• TestSpringDataJpaApplication.java: the entry class for the Spring Boot application.
• dao: stores the data access object (DAO) package for accessing databases or other data storage services.
• UserRepository.java: the user data access interface, which defines the create, read, update, and delete (CRUD) operations on user data.
• entity: the entity class directory, which stores the Java classes corresponding to the database tables.
• User.java: the user persistent object that is mapped to fields in a user data table.
• service: the service interface directory, which defines business logic interfaces.
• UserService.java: the user service interface, which defines operations on user data.
• impl: the service implementation directory, which stores the implementation classes of business logic.
• UserServiceImpl.java: the user service implementation class, which implements the methods defined in the UserService interface.
• resources: a directory that stores resource files, such as configuration files and SQL files.
• application.yml: the configuration file of the application, which is used to configure information such as database connection information.
• static: stores static files, such as CSS and JavaScript files.
• templates: stores template files, such as HTML templates.
• test: a directory that stores the test code and resource files.
• TestSpringDataJpaApplicationTests.java: stores classes that implement and verify features related to Spring Data JPA.
• target: a directory that stores compiled class files and JAR packages.

Code in pom.xml

Perform the following steps to configure the pom.xml file:

1. Declare the file.

   Declare the file to be an XML file that uses the XML standard 1.0 and character encoding format UTF-8.

   Here is the sample code:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure namespaces and the POM version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. 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).
   4. <modelVersion>: the POM version used by the POM file, which is set to 4.0.0.

   Here is the sample 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>
   

3. Configure parent project information.

   1. <groupId>: the ID of the parent project group, which is set to org.springframework.boot.
   2. <artifactId>: the name of the parent project, which is set to spring-boot-starter-parent.
   3. <version>: the version of the parent project, which is set to 2.7.0.
   4. <relativePath>: an empty path for the parent project.

   Here is the sample code:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/>
    </parent>
   

4. Configure basic information.

   1. <groupId>: the ID of the project group, which is set to com.oceanbase.
   2. <artifactId>: the name of the project, which is set to java-oceanbase-springdatajpa.
   3. <version>: the version of the project, which is set to 0.0.1-SNAPSHOT.
   4. <description>: the project information, which is set to Demo project for Spring Boot.

   Here is the sample 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>
   

5. Configure the Java version.

   Specify to use Java 1.8 for the project.

   Here is the sample code:

     <properties>
         <java.version>1.8</java.version>
     </properties>
   

6. Configure core dependencies.

   1. Define a dependency named spring-boot-starter-data-jpa that belongs to the org.springframework.boot group. This dependency contains necessary dependencies and configurations for JPA-based data accesses, and is a Spring Boot starter.

      Here is the sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>
      

   2. Define a dependency named spring-boot-starter-web that belongs to the org.springframework.boot group. This dependency contains necessary dependencies and configurations for Spring Boot-based web development, and is a Spring Boot starter.

      Here is the sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      

   3. Define a dependency named spring-boot-starter-test that belongs to the org.springframework.boot group. This dependency takes effect in the test scope and provides test frameworks and tools of Spring Boot, such as JUnit, Mockito, and Hamcrest.

      Here is the sample code:

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
      </dependency>
      

   4. Define a dependency named druid that belongs to the com.alibaba group and whose version is 1.2.8. This dependency allows the project to use the Druid library to manage and optimize the acquisition and release of database connections.

      Here is the sample code:

      <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid</artifactId>
          <version>1.2.8</version>
      </dependency>
      

   5. Define a dependency named oceanbase-client that belongs to the com.oceanbase group and whose version is 2.4.2. With this dependency, you can use the features of OceanBase Command-Line Client (OBClient), such as connections, queries, and transactions.

      Here is the sample code:

          <dependencies>
          <dependency>
              <groupId>com.oceanbase</groupId>
              <artifactId>oceanbase-client</artifactId>
              <version>2.4.2</version>
          </dependency>
          </dependencies>
      

7. Configure the Maven plug-in.

   Define a plug-in named spring-boot-maven-plugin that belongs to the org.springframework.boot group. This plug-in 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 plug-ins or tools, exclude the lombok dependency during building.

   Here is the 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>
   

Code in application.yml

The application.yml file is the configuration file of the Spring Boot application, which contains some key parameters. The file configures the context path and listening port number of the application, database connection information, and JPA-related properties.

Code in the application.yml file contains the following parts:

• Server part

  • servlet: the servlet properties.
  • context-path: the context path of the application, which is set to testspringdatajpa.
  • port: the listening port number of the application, which is set to 8890.

  Here is the sample code:

  server:
  servlet:
      context-path: /testspringdatajpa
  port: 8890
  

• Spring part

  • datasource: the data source properties, that is, the database connection information.

    • driver-class-name: the class name of the database driver, which is set to com.mysql.cj.jdbc.Driver.
    • url: the connection URL of the database, which consists of the IP address, port number, and name of the database.
    • username: the username for connecting to the database.
    • password: the password for connecting to the database.
    • type: specifies to use the Druid connection pool as the data source.

    Here is the sample code:

    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.DruidDataSource
    

  • JPA part

    • hibernate: the Hibernate-related properties.
      • ddl-auto: the operation performed by Hibernate. The value update here specifies that Hibernate automatically updates the database schema when the application starts.
      • show-sql: specifies whether to print SQL statements to the console. The value is true.
      • format-sql: specifies whether to format the printed SQL statements. The value is true.
    • open-in-view: specifies whether to enable the Open-in-View mode. The value is false.

    Here is the sample code:

    jpa:
        hibernate:
        ddl-auto: update
        show-sql: true
        format-sql: true
        open-in-view: false
    

Code in UserRepository.java

The userRepository.java file defines a DAO interface that contains methods of performing database operations on the User entity. The file contains custom update and query operation methods that use SQL statements and Hibernate Query Language (HQL) statements.

Perform the following steps to configure the userRepository.java file:

1. Reference other classes and interfaces.

   Declare this file to contain the following interfaces and classes:

   • User class: operates and processes user objects in the service class.
   • JpaRepository interface: provides basic CRUD operation methods for interfaces that inherit it.
   • Modifying annotation: marks modification operation methods.
   • Query annotation: defines custom query methods.
   • Param annotation: maps method parameters to parameters in query statements.
   • Repository annotation: marks a DAO interface as a Spring repository.

   Here is the 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;
   

2. Define the UserRepository interface.

   The UserRepository interface is used to access user data. Mark the UserRepository interface as a Spring repository and specify userRepository as the bean name.

   1. Use the @Query annotation with nativeQuery = true to specify a native SQL statement.
   2. Use the @Modifying annotation to mark a custom method that uses a native SQL statement for update.
   3. Use the @Query annotation to specify an HQL statement, and use the @Param annotation to map method parameters to parameters in the HQL statement.

   Here is the 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);
   }
   

Code in User.java

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

Perform the following steps to configure the User.java file:

1. Import other classes. javax.persistence.* specifies to import all classes in the javax.persistence package. javax.persistence is a standard JPA package that provides a set of interfaces and annotations for object-relational mapping (ORM).

   Here is the sample code:

   import javax.persistence.*;
   

2. Define the User object. Use JPA annotations to map an entity class to a table, including the table name, primary key, and field names. Use the @Entity annotation to mark a class as an entity class. Use the @Table annotation to specify the name of the mapped-to table. Use the @Id annotation to mark the primary key of the entity class. Use the @GeneratedValue annotation to specify the primary key generation strategy. Use the @Column annotation to specify field names and constraints. The class has two attributes: id and username, which represent the unique identifier and name of a user, respectively. The class also provides a default constructor for creating an empty User object.

   Here is the 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() {
       }
   }
   

3. Get and set the id and name values. The entity class provides a constructor with parameters for creating entity class objects and initializing the id and username attributes. Define four methods to get and set the values of the id and name attributes.

   • The getId method gets the id value.
   • The setId method sets the id value.
   • The getName method gets the name value.
   • The setName method sets the name value.

   Here is the 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;
   }
   

4. Return a string that represents the User object.

   Override the toString method in the User class to return a string that represents the User object. Use the @Override annotation to override the method of the same name in the parent class. Define the toString method that returns a string representation of the User object. Concatenate the id and name values to generate a string and return it to the caller.

   Here is the sample code:

   @Override
   public String toString() {
       return "User{" +
               "id=" + id +
               ", username='" + username + '\'' +
               '}';
   }
   

Code in UserServiceImpl.java

The UserServiceImpl.java file defines the CRUD operations on user data in the database by using JPA.

Perform the following steps to configure the UserServiceImpl.java file:

1. Reference other classes and interfaces.

   Declare this file to contain the following interfaces and classes:

   • UserRepository class: injects the methods defined in the DAO to the service class and calls these methods.
   • User class: operates and processes user objects in the service class.
   • UserService class: injects methods defined in the service class to other classes and calls these methods.
   • Autowired annotation: automatically injects dependencies.
   • Page class: encapsulates results of pagination queries.
   • PageRequest class: creates pagination request objects.
   • Pageable interface: defines pagination requests.
   • Sort class: defines sorting rules.
   • Service annotation: marks a class as a service class.
   • Transactional annotation: marks a class as transactional.
   • Iterator class: traverses a set.
   • Optional class: processes objects that may be empty.

   Here is the 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;
   

2. Use the UserServiceImpl class to implement the methods declared in the UserService interface.

   Inject the UserRepository dependency to implement access to the database. Specifically, implement the methods to insert, delete, update, and query user data, with pagination queries supported. Use the @Transactional annotation to enable transaction support, which ensures that database operations are performed in transactions.

   1. Construct a UserRepository attribute. Define a private attribute named userRepository and use the @Autowired annotation on the constructor to inject the UserRepository dependency, so that the userRepository attribute can be used to access and operate user data.

      Here is the sample code:

      private final UserRepository userRepository;
      
      @Autowired
      public UserServiceImpl(UserRepository userRepository) {
          this.userRepository = userRepository;
      }
      

   2. Insert user data. In the insertUser method, accept a User object as a parameter and save the object to the database by calling the save method of UserRepository.

      Here is the sample code:

      @Override
      public void insertUser(User user) { userRepository.save(user); }
      

   3. Delete user data. In the deleteUser method, call the existsById method of UserRepository to check whether the user with the specified ID exists. If the user exists, the method deletes the user. If the user does not exist, the method exits.

      Here is the sample code:

      @Override
      public void deleteUser(Integer id) {
          if (!userRepository.existsById(id)) {
              return;
          }
      }
      

   4. Modify user data. In the updateUser method, call the save method of UserRepository to save the updates of a user object to the database. Then, return 1 to indicate that the user object is updated. In addition, define a method that updates user data based on the name and ID of a user by calling the updateById method of UserRepository, and return the operation result.

      Here is the 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);
      }
      

   5. Query user data.

      1. Query user data by ID. In the selectUserById method, call the findById method of UserRepository to query data and encapsulate the result as an Optional object. Then, call the orElse method of Optional to get the value in the Optional object and assign it to the user variable. Finally, return the queried user object.
      2. Query user data by username. In the selectUserByName method, call the findUser method of UserRepository to query user data by username, and directly return the queried user object.

      Here is the 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);
      }
      

   6. Query all user data with pagination. In the selectUserAll method, create a Sort object to specify sorting rules, create a Pageable object to specify the pagination parameters, call the findAll method of UserRepository to query user data, and return a Page object. Then, get a user data iterator by calling the iterator method of the Page object. Finally, return the user data iterator.

      Here is the 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;
      }
      

Code in UserService.java

The UserService.java file defines the CRUD operations on user data in the database by using JPA.

Perform the following steps to configure the UserService.java file:

1. Reference other classes and interfaces.

   Declare that the current file contains the following classes:

   • User class: operates and processes user objects in the service class.
   • Iterator class: traverses a set.

   Here is the sample code:

   import com.oceanbase.testspringdatajpa.entity.User;
   
   import java.util.Iterator;
   

2. Define the UserService interface. Define methods in the UserService interface to insert, delete, update, and query user data. The interface contains the following methods and classes:

   • insertUser method: inserts user data.

   • deleteUser method: deletes user data.

   • updateUser method: updates user data.

   • updateUserById method: modifies the name value for a user of the specified ID.

   • selectUserById method: queries user data by ID.

   • selectUserByName method: queries user data by username.

   • selectUserAll method: queries all user data with pagination.

   • User class: operates and processes user objects in the service class.

   • Iterator class: traverses a set.

   Here is the 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);
   }
   

Code in TestSpringDataJpaApplication.java

The TestSpringDataJpaApplication.java file is used to launch and configure a Spring Boot application.

Perform the following steps to configure the TestSpringDataJpaApplication.java file:

1. Define classes and interfaces. Declare that the current file contains the following classes:

   • SpringApplication class: launches a Spring Boot application.
   • @SpringBootApplication annotation: marks a class as the entry class of the Spring Boot application.

   Here is the sample code:

   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   

2. Define the TestSpringDataJpaApplication class. Use the @SpringBootApplication annotation to mark this class as the entry class of the Spring Boot application, and call the SpringApplication.run method in the main method to launch the application.

   Here is the sample code:

   @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 launch and configure a Spring Boot application.

Perform the following steps to configure the TestSpringDataJpaApplicationTests.java file:

1. Reference other classes and interfaces. Declare that the current file contains the following classes:

   • User class: operates and processes user objects in the service class.
   • UserService class: injects methods defined in the service class to other classes and calls these methods.
   • @Test annotation: marks a test method.
   • Autowired annotation: automatically injects dependencies.
   • @SpringBootTest annotation: marks a test class for a Spring Boot application.
   • Iterator class: traverses a set.

   Here is the 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;
   

2. Define the UserService method. This method tests various methods in the UserService service of the Spring Boot application. The method injects the UserService dependency to call various methods to test the user data insert, delete, update, and query operations, and prints results.

   1. Use the @Autowired annotation to automatically inject the implementation class of the UserService interface to the userService variable.

      Here is the sample code:

      @Autowired
      private UserService userService;
      

   2. Use the contextLoads method for testing.

      1. Insert user data. Use the FOR loop and call the insertUser method of userService to insert 10 user data records.

         Here is the sample code:

         for (int i = 1; i <= 10; i++) {
             userService.insertUser(new User(i, "insert" + i));
         }
         

      2. Delete user data. Call the deleteUser method of userService to delete the data of the user with the ID 1.

         Here is the sample code:

         userService.deleteUser(1);
         

      3. Modify user data. Call the updateUser method of userService to update the data of the user with the ID 2.

         Here is the sample code:

         userService.updateUser(new User(2, "update"));
         

      4. Query user data.

         • Call the selectUserById method of userService to query user data by ID, and assign the result to the user variable. Print the user object.
         • Call the selectUserByName method of userService to query user data by user name, and assign the result to the userByName variable. Print the userByName object.

         Here is the sample code:

         User user = userService.selectUserById(2);
         System.out.println("user = " + user);
         User userByName = userService.selectUserByName("insert");
         System.out.println("userByName = " + userByName);
         

      5. Query user data with pagination. Call the selectUserAll method of userService to query all user data with pagination, and assign the results to the userIterator variable. Use the forEachRemaining method to traverse the userIterator variable and print each user object.

         Here is the 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.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 Connector/J.