Connect to OceanBase Database by using Spring Data JPA

This topic introduces how to build an application by using the Spring Data JPA framework and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the java-oceanbase-springdatajpa sample project

Prerequisites

• You have installed OceanBase Database.
• You have installed 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 into 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 database connection string.

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

2. Fill in the URL below based on the deployed OceanBase database.

   Note

   The URL here 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
   

   Parameters in the URL are described as follows:

   • host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.

   • 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 access.

   • user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

   • password: the account password.

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

     • characterEncoding: the character encoding method that supports the database URL options. 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 into IDEA

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

   

2. In the Open File or Project window that appears, select the corresponding project file and click OK to import the project file.

3. IntelliJ IDEA automatically identifies all types of files in the project. In the Project window, you can view the directory structure, list of files, list of modules, and dependencies of the project. The Project window is usually on the far left side in IntelliJ IDEA and is displayed by default. If the Project window is closed, you can choose View > Tool Windows > Project from the menu or use the Alt + 1 shortcut to open the window.

   Note

   When you use IntelliJ IDEA to import a project, IntelliJ IDEA automatically detects the pom.xml file in the project, downloads the required libraries based on the dependencies defined in the file, and adds the libraries to the project.

4. 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 Database connection string.

Here is an example:

• The name of the database driver is com.oceanbase.jdbc.Driver.
• The IP address of the OBServer node is 10.10.10.1.
• The port is 2881.
• The name of the schema to access is sys.
• The tenant account is sys@xyoracle, where xyoracle is a user tenant created in the Oracle mode of OceanBase Database, and sys is a username in the xyoracle tenant.
• The password is ******.

The sample code is as follows:

spring:
  datasource:
    driver-class-name: com.oceanbase.jdbc.Driver
    url: jdbc:oceanbase://10.10.10.1:2881/sys?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    username: sys@xyoracle
    password:******
    type: com.alibaba.druid.pool.DruidDataSource

Step 4: Run the java-oceanbase-springdatajpa project

Run 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 project.
3. View the logs and output of the project in the IDEA 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'}

Project code introduction

Click java-oceanbase-springdatajpa to download the project code, which is a compressed file named java-oceanbase-springdatajpa.zip.

After decompressing it, you will find 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

Here is a breakdown of the files and directories:

• pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build information of the project.
• .idea: the directory used in the Integrated Development Environment (IDE) for storing project-related configurations.
• src: the directory for storing source code of the project.
• main: the directory for storing main source code and resource files.
• java: the directory for storing Java source code.
• com: the root directory for storing the Java package.
• oceanbase: the root directory for storing 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 add, delete, modify, and query operations on user data.
• entity: the entity class directory that 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 that defines business logic interfaces.
• UserService.java: the user service interface that defines operations on user data.
• impl: the service implementation directory that stores the implementation classes of business logic.
• UserServiceImpl.java: the user service implementation class that implements the methods defined in the UserService interface.
• resources: the directory that stores resource files, such as configuration files and SQL files.
• application.yml: the application configuration file that contains configurations such as the database connection information.
• static: stores static files, such as CSS and JavaScript files.
• templates: stores template files, such as HTML templates.
• test: the directory that stores the test code and resource files.
• TestSpringDataJpaApplicationTests.java: stores classes that implement and verify features related to Spring Data JPA.
• target: the directory that stores compiled class files and JAR packages.

Code in pom.xml

To configure the pom.xml file, perform the following steps:

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. Use xmlns to specify http://maven.apache.org/POM/4.0.0 as the default XML namespace for the POM.
   2. Use xmlns:xsi to specify http://www.w3.org/2001/XMLSchema-instance as the XML namespace for xsi-prefixed elements.
   3. Use xsi:schemaLocation to provide a mapping from the default XML namespace for the POM (http://maven.apache.org/POM/4.0.0) to the location of the POM's XML schema definition (XSD) file (https://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. Use <modelVersion> to specify 4.0.0 as the model version used by the POM.

   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>
   

3. Configure parent project information.

   1. Use <groupId> to specify org.springframework.boot as the ID of the parent project group.
   2. Use <artifactId> to specify spring-boot-starter-parent as the ID of the parent project.
   3. Use <version> to specify 2.7.0 as the version of the parent project.
   4. Use <relativePath> to specify 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>
   

4. Configure basic project information.

   1. Use <groupId> to specify com.oceanbase as the ID of the project group.
   2. Use <artifactId> to specify java-oceanbase-springdatajpa as the ID of the project.
   3. Use <version> to specify 0.0.1-SNAPSHOT as the version of the project.
   4. Use <description> to describe the project as Demo 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>
   

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

6. Configure core dependencies.

   1. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter-data-jpa as the dependency ID. This dependency contains necessary dependencies and configurations for JPA-based database 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>
      

   2. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter-web as the dependency ID. 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>
      

   3. Specify org.springframework.boot as the ID of the group that the dependency belongs to, and spring-boot-starter-test as the dependency ID. This dependency takes effect on test 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>
      

   4. Specify com.alibaba as the ID of the group that the dependency belongs to, druid as the dependency ID, and 1.2.8 as the dependency version. 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>
      

   5. Specify com.oceanbase as the ID of the group that the dependency belongs to, oceanbase-client as the dependency ID, and 2.4.3 as the dependency version. This dependency allows the project to use the client features, such as connection, query, and transaction, provided by OceanBase.

      The sample code is as follows:

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

7. Configure the Maven plug-in.

   Specify org.springframework.boot as the ID of the group that the plug-in belongs to, and spring-boot-maven-plugin as the plug-in ID. This plug-in is used to package Spring Boot applications into executable JAR or WAR files, which can be directly executed. To avoid conflicts with other plug-ins or tools during the build process, the lombok dependency is included.

   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 of a 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.

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 of the application, which is set to 8890.

  The sample code is as follows:

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

    The sample code is as follows:

    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 part

    • hibernate: the Hibernate-related properties.
      • ddl-auto: the value update specifies that Hibernate automatically updates the database schema upon startup.
      • 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.

    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 a DAO interface that contains methods of performing database operations on User. The file contains custom update and query operation methods that use SQL statements and Hibernate Query Language (HQL) statements.

To configure the UserRepository.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file contains the following interfaces and classes:

   • User class: used for operating and processing 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.

   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;
   

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.

   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);
   }
   

Code in User.java

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

To configure the User.java file, perform the following steps:

1. Import other classes.

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

   The sample code is as follows:

   import javax.persistence.*;
   

2. Define a 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 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.

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

3. Get and set the id and username 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 username attributes.

   • The getId method gets the id value.
   • The setId method sets the id value.
   • The getUsername method gets the username value.
   • The setUsername method sets the username value.

   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;
   }
   

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.

   The sample code is as follows:

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

Code in UserServiceImpl.java

The UserServiceImpl.java file implements the add, delete, modify, and query operations on user data in the database by using JPA.

To configure the UserServiceImpl.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file contains the following interfaces and classes:

   • UserRepository class: injects the methods defined in the data access object (DAO) to the service class and calls these methods.
   • User class: used for operating and processing 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 dependent objects.
   • 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 collection.
   • Optional class: processes objects that may be empty.

   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;
   

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

   Inject the UserRepository dependency to implement access to the database. Specifically, implement the methods to insert, delete, update, and query user data, and support pagination queries. 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 UserRepository 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.

      The sample code is as follows:

      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.

      The sample code is as follows:

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

      The sample code is as follows:

      @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 successfully. 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.

      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);
      }
      

   5. Query user data.

      1. Query user data by user 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 user name. In the selectUserByName method, call the findUser method of UserRepository to query user data by user name, 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);
      }
      

   6. Query all user data by 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.

      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 defines the add, delete, modify, and query operations on user data in the database by using JPA.

To configure the UserService.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file contains the following classes:

   • User class: used for operating and processing user objects in the service class.
   • Iterator class: traverses a collection.

   The sample code is as follows:

   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 user name.

   • selectUserAll method: queries all user data by pagination.

   • User class: used for operating and processing user objects in the service class.

   • Iterator class: traverses a collection.

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

To configure the TestSpringDataJpaApplication.java file, perform the following steps:

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 a Spring Boot application.

   The sample code is as follows:

   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 a Spring Boot application, and call the SpringApplication.run method in the main method to launch 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 launch and configure a test Spring Boot application.

To configure the TestSpringDataJpaApplicationTests.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file contains the following classes:

   • User class: used for operating and processing 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 dependent objects.
   • @SpringBootTest annotation: marks a test class for an Spring Boot application.
   • Iterator class: traverses a collection.

   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;
   

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.

      The sample code is as follows:

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

         The sample code is as follows:

         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 user data record with ID 1.

         The sample code is as follows:

         userService.deleteUser(1);
         

      3. Modify user data.

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

         The sample code is as follows:

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

      4. Query user data.

         • Call the selectUserById method of userService to query user data by user 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.

         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);
         

      5. Query user data by pagination.

         Call the selectUserAll method of userService to query all user data by pagination, and assign the results to the userIterator variable. Use the forEachRemaining method to traverse the userIterator variable 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.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> {
    //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.