SpringDataJPA sample application for connecting to OceanBase Cloud

This topic describes how to use the SpringDataJPA framework and OceanBase Cloud to build an application that can perform basic operations such as creating tables, inserting data, and querying data.

Download the Java OceanBase SpringDataJPA sample project

Prerequisites

• You have registered an account for OceanBase Cloud and created an instance and a MySQL compatible tenant. For more information, see Create an instance and Create a tenant.
• You have installed JDK 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the connection string of the OceanBase Cloud database.
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 connection string of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the corresponding information in the following URL based on the created OceanBase Cloud database.

   Note

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

   Parameter description:

   • host: the connection address of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.

   • port: the connection port of the OceanBase Cloud database. The default value is 3306.

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

   • user_name: the account for accessing the database.

   • password: the password of the account.

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

     • characterEncoding: the character encoding supported by the database URL option. The default value is utf8.
     • useSSL: whether to use SSL/TLS when connecting. The default value is false.
     • serverTimezone: the server time zone. The default value is China Standard Time.

For more information about URL parameters, see Database URL.

Step 2: Import the java-oceanbase-springdatajpa project into IDEA

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

   

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

3. IntelliJ IDEA automatically identifies various files in the project and displays the project directory structure, file list, module list, and dependency relationships in the Project tool window. The Project tool window is usually located on the far left of the IntelliJ IDEA interface and is open by default. If it is closed, you can click View > Tool Windows > Project in the menu bar or press the shortcut key Alt + 1 to reopen it.

   Note

   When you import a project using IntelliJ IDEA, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies 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 information in the application.yml file based on the information obtained in Step 1: Obtain the connection string of the OceanBase Cloud database.

Here is an example:

• The name of the database driver is com.mysql.cj.jdbc.Driver.
• The IP address of the OBServer node is t5******.********.oceanbase.cloud.
• The access port is 3306.
• The name of the schema to be accessed is test.
• The tenant connection account is mysql001.
• The password is ******.

Here is the sample code:

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    username: mysql001
    password: ******
    type: com.alibaba.druid.pool.DruidDataSource

Step 4: Run the java-oceanbase-springdatajpa project

Run path

1. In the project structure, go to src > test > java and find the TestSpringDataJpaApplicationTests.java file.
2. In the tool menu bar, choose Run > Run... > TestSpringDataJpaApplicationTests.contextLoads, or click the green triangle in the upper right corner to run.
3. View the project logs and output results in the IDEA console.

Run result

User{id=1, username='insert1'}
User{id=2, username='update'}
User{id=3, username='insert3'}
User{id=4, username='insert4'}
User{id=5, username='insert5'}

FAQ

1. Connection timeout

If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. Character set issues

To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection

To enable an SSL connection to the cloud database of OceanBase Cloud, add the following parameter to the JDBC URL:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in the username or password

If the username or password contains special characters (such as #), you need to URL-encode them:

String encodedPassword = URLEncoder.encode(password, "UTF-8");

Project code

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

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

File description:

• pom.xml: the configuration file of the Maven project, which contains information about the project's dependencies, plugins, and build process.
• .idea: a directory used by the Integrated Development Environment (IDE) to store project-related configuration information.
• src: a directory typically used to store the source code of 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 for storing Java packages.
• oceanbase: the root directory for storing the project.
• testspringdatajpa: the root directory for storing Java packages, which contains all the Java classes of the project.
• TestSpringDataJpaApplication.java: the entry class of the Spring Boot application.
• dao: a directory that stores the Data Access Object (DAO) packages for accessing databases or other data storage services.
• UserRepository.java: the user data access interface, which defines the operations for adding, deleting, modifying, and querying user data.
• entity: a directory that stores entity classes corresponding to database tables.
• User.java: the user persistence object, which maps the fields of the user data table.
• service: a directory for storing service interface definitions.
• UserService.java: the user service interface, which defines the methods for operating on user data.
• impl: a directory for storing the implementation classes of the business logic.
• UserServiceImpl.java: the implementation class of the user service, which implements the methods defined in the UserService interface.
• resources: a directory for storing resource files, such as configuration files and SQL files.
• application.yml: the configuration file of the application, which is used to configure database connections and other information.
• static: a directory for storing static files, such as CSS and JavaScript files.
• templates: a directory for storing template files, such as HTML templates.
• test: a directory for storing test code and resource files.
• TestSpringDataJpaApplicationTests.java: a class for executing and verifying the functionality related to Spring Data JPA.
• target: a directory for storing compiled Class files, JAR packages, and other files.

Introduction to the pom.xml file

The content of the pom.xml configuration file is as follows:

1. File declaration statement.

   This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure the namespaces and model version of the POM.

   1. Use xmlns to specify the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file as https://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use the <modelVersion> element to specify the POM model version used by the POM file as 4.0.0.

   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 the parent project information.

   1. Use <groupId> to specify the parent project identifier as org.springframework.boot.
   2. Use <artifactId> to specify the parent project dependency as spring-boot-starter-parent.
   3. Use <version> to specify the parent project version as 2.7.0.
   4. Use relativePath to indicate that the parent project path is empty.

   Sample code:

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

4. Configure the basic information.

   1. Use <groupId> to specify the project identifier as com.oceanbase.
   2. Use <artifactId> to specify the project dependency as java-oceanbase-springdatajpa.
   3. Use <version> to specify the project version as 0.0.1-SNAPSHOT.
   4. Use description to introduce the project information as Demo project for Spring Boot.

   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 the Java version used by the project as 1.8.

   Sample code:

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

6. Configure the core dependencies.

   1. Specify the organization as org.springframework.boot, the name as spring-boot-starter-data-jpa, and the dependency library as containing 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>
      

   2. Specify the organization as org.springframework.boot, the name as spring-boot-starter-web, and the dependency library as containing the necessary dependencies and configurations 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>
      

   3. Specify the organization as org.springframework.boot, the name as spring-boot-starter-test, and the scope as test. This dependency allows you to use the testing framework 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>
      

   4. Specify the organization as com.alibaba, the name as druid, and the version as 1.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>
      

   5. Specify the organization as com.oceanbase, the name as oceanbase-client, and the version as 2.4.2. 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>
      

7. Configure Maven plugins.

   Specify the organization as org.springframework.boot, the name as spring-boot-maven-plugin, and the plugin as used to package Spring Boot applications into executable JAR or WAR packages that can be directly run. To avoid conflicts with other plugins or tools, the Lombok dependency is included during the build process.

   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>
   

application.yml

The application.yml file is a configuration file for a Spring Boot application. It contains key configuration items that set the application context path, listening port number, and database connection information and JPA-related properties.

The code in the application.yml file mainly includes the following parts:

• Server section

  • servlet: used to configure Servlet-related properties.
  • context-path: specifies the application context path as testspringdatajpa.
  • port: specifies the listening port number of the application as 8890.

  Code:

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

• Spring section

  • datasource: used to configure data source-related properties, that is, database connection information.

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

    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 section

    • hibernate: used to configure Hibernate-related properties.
      • ddl-auto: specifies that Hibernate automatically updates the database structure to update when it starts.
      • show-sql: specifies whether to print SQL statements in the console, with a value of true.
      • format-sql: specifies whether to format the printed SQL statements, with a value of true.
    • open-in-view: specifies whether to enable the Open-in-View mode, with a value of false.

    Code:

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

userRepository.java

The userRepository.java file defines methods for database operations on the User entity through a DAO interface. It includes custom update and query methods that use SQL and HQL statements.

The code in the userRepository.java file mainly includes the following parts:

1. Import other classes and interfaces.

   Declare the following interfaces and classes in the current file:

   • User class: used to operate and process user objects in service classes.
   • JpaRepository interface: used to inherit the interface to obtain basic CRUD operation methods.
   • Modifying annotation: used to mark methods for modification operations.
   • Query annotation: used to define custom query methods.
   • Param annotation: used to map method parameters to parameters in query statements.
   • Repository annotation: used to mark the DAO interface as a Spring repository component.

   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. It is marked as a Spring Repository component and the bean name is specified as userRepository.

   1. Use the @Query annotation to specify an SQL statement and set nativeQuery = true to use native SQL statements.
   2. Use the @Modifying annotation to define a custom update method that uses native SQL statements for updates.
   3. Use the @Query annotation to specify an HQL (Hibernate Query Language) statement and use the @Param annotation to map method parameters to parameters in the HQL statement.

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

User.java

The User.java file defines the User class to represent a user object.

The code in the User.java file mainly includes the following parts:

1. Import other classes. javax.persistence.*: used to import all classes from the javax.persistence package. javax.persistence is the standard package for Java Persistence API (JPA), providing a set of interfaces and annotations for object-relational mapping (ORM).

   Code:

   import javax.persistence.*;
   

2. Define the User object. Use JPA annotations to identify the mapping relationships between the entity class and the table, including the table name, primary key, and field name. Use the @Entity annotation to mark the class as an entity class, the @Table annotation to specify the corresponding database table name, the @Id annotation to mark the primary key of the entity class, the @GeneratedValue annotation to specify the primary key generation strategy, and the @Column annotation to specify the field name and constraints. The class has two properties, id and username, which represent the unique identifier and username of the user, respectively. It also provides a default constructor to create an empty User object.

   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 parameterized constructor to create an entity class object and initialize the id and username properties. Define four methods to get and set the values of the id and name properties.

   • The getId method is used to get the id value.
   • The setId method is used to set the id value.
   • The getName method is used to get the username name value.
   • The setName method is used to set the username name value.

   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 the string representation of the User object.

   Override the toString method in the User class to return the string representation of the User object. Define @Override to override the method with the same name in the parent class. Define the toString method to return the string representation of the User object. Use string concatenation to format the values of the id and name properties into a string and return it to the caller User.

   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.

The code in the UserServiceImpl.java file mainly includes the following parts:

1. Import other classes and interfaces.

   Declare the following interfaces and classes in the current file:

   • UserRepository class: used to inject and call methods defined in data access objects (DAOs) in service classes.
   • User class: used to operate and process user objects in service classes.
   • UserService class: used to inject and call methods defined in service classes in other classes.
   • Autowired annotation: used to automatically inject dependency objects.
   • Page class: used to encapsulate the results of a paginated query.
   • PageRequest class: used to create a paginated request object.
   • Pageable interface: used to define a paginated request interface.
   • Sort class: used to define a sorting rule.
   • Service annotation: used to mark a class as a service class.
   • Transactional annotation: used to mark a transaction method.
   • Iterator class: used to traverse a collection.
   • Optional class: used to process 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;
   

2. The UserServiceImpl class implements the methods declared in the UserService interface.

   The UserRepository class is used to access the database through dependency injection. The methods for inserting, deleting, updating, and querying user data are implemented. The methods support paginated queries. The @Transactional annotation is used to enable transaction support and ensure that database operations are performed within a transaction.

   1. Define the UserRepository attribute. Define a private UserRepository attribute and use the @Autowired annotation in the constructor for dependency injection. Inject the UserRepository dependency through the constructor and use the userRepository attribute to access and operate user data.

      Sample code:

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

   2. Insert user data. The insertUser method accepts a User object as a parameter. The UserRepository class's save method is called to save the user object to the database.

      Sample code:

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

   3. Delete user data. The deleteUser method calls the existsById method of the UserRepository class to check whether a user with the specified ID exists. If the user exists, the user is deleted. If the user does not exist, the method returns directly.

      Sample code:

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

   4. Modify user data. The updateUser method calls the save method of the UserRepository class to save the user object to the database, thereby performing an update. The method returns the integer value 1, indicating that the update was successful. Additionally, the method for updating user data based on the username and id is implemented. The updateById method of the UserRepository class is called to perform the update, and the result of the update is returned.

      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 based on the ID. The selectUserById method calls the findById method of the UserRepository class to query user data and encapsulates the result as an Optional object. The orElse method of the Optional class is then called to obtain the value in the Optional object and assign it to the user variable. Finally, the queried user object is returned.
      2. Query user data based on the username. The selectUserByName method calls the findUser method of the UserRepository class to query user data based on the username and directly returns 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);
      }
      

   6. Query all user data in a paginated manner. The selectUserAll method creates a Sort object to specify the sorting rule and creates a Pageable object to specify the paginated parameters. The findAll method of the UserRepository class is called to query user data, and a Page object is returned. The iterator method of the Page object is then called to obtain an iterator for the user data. Finally, the user data iterator is returned.

      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 the UserService.java file

The UserService.java file uses JPA to perform CRUD operations on user data.

The code in the UserService.java file mainly includes the following parts:

1. Import other classes and interfaces.

   Declare the following classes in the current file:

   • User class: used to operate and process user objects in service classes.
   • Iterator class: used to traverse a collection.

   Sample code:

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

2. Define the UserService interface. The UserService interface defines methods for inserting, deleting, updating, and querying user data. The interface contains the following methods and classes:

   • insertUser method: used to insert user data.

   • deleteUser method: used to delete user data.

   • updateUser method: used to update user data.

   • updateUserById method: used to modify the name of a user based on the id.

   • selectUserById method: used to query user data based on the id.

   • selectUserByName method: used to query user data based on the username.

   • selectUserAll method: used to query all user data in a paginated manner.

   • User class: used to operate and process user objects in service classes.

   • Iterator class: used to traverse a collection.

   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 TestSpringDataJpaApplication.java

The TestSpringDataJpaApplication.java file is used to start and configure the Spring Boot application.

The TestSpringDataJpaApplication.java file contains the following code:

1. Define classes and interfaces. The following classes are declared in this file:

   • SpringApplication class: used to start the Spring Boot application.
   • @SpringBootApplication annotation: used to mark the entry class of the Spring Boot application.

   Sample code:

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

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

   Sample code:

   @SpringBootApplication
   public class TestSpringDataJpaApplication {
   
       public static void main(String[] args) {
           SpringApplication.run(TestSpringDataJpaApplication.class, args);
       }
   }
   

Introduction to TestSpringDataJpaApplicationTests.java

The TestSpringDataJpaApplicationTests.java file is used to start and configure the Spring Boot application.

The TestSpringDataJpaApplicationTests.java file contains the following code:

1. Import other classes and interfaces. The following classes are declared in this file:

   • User class: used to operate and process user objects in the service class.
   • UserService class: used to inject and call methods defined in the service class in other classes.
   • @Test annotation: used to mark test methods.
   • Autowired annotation: used to automatically inject dependent objects.
   • @SpringBootTest annotation: used to test the Spring Boot application.
   • Iterator class: used to traverse a collection.

   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 is used to test the methods of the UserService service in the Spring Boot application. It injects the UserService dependency and calls each method to test the functionality of inserting, deleting, updating, and querying user data, and prints the related results.

   1. Use the @Autowired annotation. Automatically inject the implementation class of the UserService interface into the userService variable.

      Sample code:

      @Autowired
      private UserService userService;
      

   2. Use the contextLoads method for testing.

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

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

         Sample code:

         userService.deleteUser(1);
         

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

         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 username and assign the result to the userByName variable. Print the userByName object.

         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 in pages. Call the selectUserAll method of userService to query all user data in pages and assign the result to the userIterator variable. Use the forEachRemaining method to traverse userIterator 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.mysql.cj.jdbc.Driver
    url: jdbc:oceanbase://host:port/schema_name?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    username: user_name
    password: ******
    type: com.alibaba.druid.pool.DruidDataSource
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    format-sql: true
    open-in-view: false

package com.oceanbase.testspringdatajpa.dao;

import com.oceanbase.testspringdatajpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;


@Repository("userRepository")
public interface UserRepository extends JpaRepository<User, Integer> {
    //Custom repository handwritten SQL, placeholder value transfer form
    @Query(value = "update test_springdatajpa_2 set username=?1 where id=?2", nativeQuery = true)
    @Modifying
    int updateById(String name, String id);

    //SPEL expression, hql syntax
    @Query("SELECT u FROM User u WHERE u.username = :username")
    User findUser(@Param("username") String username);//Mapping parameter username to database field username
}

package com.oceanbase.testspringdatajpa.entity;

import javax.persistence.*;

@Entity
@Table(name = "test_springdatajpa_2")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)

    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "username")
    private String username;

    public User() {
    }

    public User(Integer id, String username) {
        this.id = id;
        this.username = username;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

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

package com.oceanbase.testspringdatajpa.service.impl;

import com.oceanbase.testspringdatajpa.dao.UserRepository;
import com.oceanbase.testspringdatajpa.entity.User;
import com.oceanbase.testspringdatajpa.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Iterator;
import java.util.Optional;


@Transactional
@Service("userService")
public class UserServiceImpl implements UserService {
    private final UserRepository userRepository;

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


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

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

    //update
    @Override
    public int updateUser(User user) {
        userRepository.save(user);
        return 1;
    }

    //update based on name and id
    @Override
    public int updateUserById(String name, String id) {
        return userRepository.updateById(name, id);
    }

    // select one user
    @Override
    public User selectUserById(Integer id) {
        Optional<User> optional = userRepository.findById(id);
        User user = optional.orElse(null);
        return user;
    }

    //query user based on username
    @Override
    public User selectUserByName(String username) {
        return userRepository.findUser(username);
    }


    @Override
    public Iterator<User> selectUserAll(Integer pageNum, Integer pageSize) {
        //By passing parameters to this method, physical pagination can be achieved, which is very simple.
        Sort sort = Sort.by(Sort.Direction.ASC, "id");
        Pageable pageable = PageRequest.of(pageNum, pageSize, sort);
        Page<User> users = userRepository.findAll(pageable);
        Iterator<User> userIterator = users.iterator();
        return userIterator;
    }
}

package com.oceanbase.testspringdatajpa.service;

import com.oceanbase.testspringdatajpa.entity.User;

import java.util.Iterator;



public interface UserService {
    //insert
    void insertUser(User user);

    //delete
    void deleteUser(Integer id);

    //update
    int updateUser(User user);

    //customize SQL and modify name based on id
    int updateUserById(String name, String id);

    //select one user
    User selectUserById(Integer id);

    //customize SQL to query users based on username
    User selectUserByName(String username);

    //query all users
    Iterator<User> selectUserAll(Integer pageNum, Integer pageSize);
}

package com.oceanbase.testspringdatajpa;

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



@SpringBootApplication
public class TestSpringDataJpaApplication {

    public static void main(String[] args) {
        SpringApplication.run(TestSpringDataJpaApplication.class, args);
    }

}

package com.oceanbase.testspringdatajpa;

import com.oceanbase.testspringdatajpa.entity.User;
import com.oceanbase.testspringdatajpa.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Iterator;


@SpringBootTest
class TestSpringDataJpaApplicationTests {
    @Autowired
    private UserService userService;

    @Test
    void contextLoads() {
        //insert
        for (int i = 1; i <= 10; i++) {
            userService.insertUser(new User(i, "insert" + i));
        }
        //delete
        userService.deleteUser(1);
        //update
        userService.updateUser(new User(2, "update"));
        //selectUserById
        User user = userService.selectUserById(2);
        System.out.println("user = " + user);
        //selectUserByName
        User userByName = userService.selectUserByName("insert");
        System.out.println("userByName = " + userByName);
        //query all users
        Iterator<User> userIterator = userService.selectUserAll(0, 5);
        userIterator.forEachRemaining(System.out::println);
    }
}

References

For more information about OceanBase Connector/J, see OceanBase JDBC driver.