Skip to main content

SpringDataJPA application that connects to seekdb

This topic describes how to build an application by using the SpringDataJPA framework and seekdb to perform basic operations such as table creation, data insertion, and data query.

Download the java-oceanbase-springdatajpa sample project

Prerequisites​

  • You have installed seekdb.

  • You have installed JDK 1.8 and Maven.

  • You have installed IntelliJ IDEA.

    info

    The tool used to run the code in this document is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool of your preference to run the sample code.

Procedure​

info

The following steps are based on the Windows environment. If you are using other operating systems or compilers, the steps may vary.

  1. Obtain the connection string of seekdb.
  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 seekdb connection string​

  1. Contact the deployment personnel or administrator of seekdb to obtain the corresponding database connection string.

    mysql -hxx.xx.xx.xx -P2881 -uroot -p**** -A

  2. Fill in the URL information based on the deployed seekdb.

    info

    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 IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address or 127.0.0.1.

    • port: the port for connecting to seekdb. Replace it with the actual port. The default value is 2881, which can be customized during the deployment of seekdb.

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

    • user_name: the username, specified by the -u parameter. The default username is root.

    • password: the account password.

    • 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 select File > Open....

    file

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

  3. IntelliJ IDEA will automatically identify various types of files in the project and display the project's directory structure, file list, module list, and dependencies in the Project tool window. The Project tool window is usually located on the far left of the IntelliJ IDEA interface and is typically open by default. If it is closed, you can click View > Tool Windows > Project in the menu bar or use the shortcut key Alt + 1 to reopen it.

    info

    When importing a project using IntelliJ IDEA, it will automatically detect the pom.xml file in the project and download the required dependency libraries based on the described dependencies in the file, then add them to the project.

  4. View the project.

springdatajpa

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 seekdb Connection String.

Example:

  • The name of the database driver is: com.mysql.cj.jdbc.Driver
  • The IP address of seekdb is 10.10.10.1.
  • The access port is 2881.
  • The name of the schema to be accessed is test.
  • The connection account is root.
  • The password is ******.

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
    password: ******
    type: com.alibaba.druid.pool.DruidDataSource

Step 4: Run the java-oceanbase-springdatajpa project​

Run path​

  1. In the project structure, locate the TestSpringDataJpaApplicationTests.java file in src > test > java.
  2. In the tool menu bar, select Run > Run... > TestSpringDataJpaApplicationTests.contextLoads, or click the green triangle in the upper right corner to run.
  3. View the project's log information 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'}

FAQs​

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​

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 SSL connections with seekdb, add the following parameter to the JDBC URL:

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

4. Special characters in the account 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");
tip

When using MySQL Connector/J 8.x, ensure that the account or password does not contain a number sign (#). Otherwise, you may encounter a connection error.

Project code​

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

After decompressing the file, 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. It contains information about the project's dependencies, plugins, and build settings.
  • .idea: a directory used by the IDE (Integrated Development Environment) to store project-related configuration information.
  • src: a directory typically used to store the source code of the project.
  • main: a directory for storing the main source code and resource files.
  • java: a directory for storing 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. It contains all the Java classes of the project.
  • TestSpringDataJpaApplication.java: the entry class of the Spring Boot application.
  • dao: a directory for storing the Data Access Object (DAO) packages, which are used to access 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 for storing entity classes that correspond to database tables.
  • User.java: the user persistence object, which is used to map the fields of the user data table.
  • service: a directory for storing service interface files that define business logic interfaces.
  • UserService.java: the user service interface, which defines the methods for operating on user data.
  • impl: a directory for storing the implementation files of 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 of Spring Data JPA.
  • target: a directory for storing compiled Class files and Jar packages.

Introduction to the pom.xml file​

tip

If you just want to verify the example, you can use the default code without any modifications. You can also modify the pom.xml file according to your needs based on the following instructions.

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.

    Code:

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

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

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

    Code:

    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
         <modelVersion>4.0.0</modelVersion>

  3. Configure the parent project information.

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

    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. Use <groupId> to set the project identifier to com.oceanbase.
    2. Use <artifactId> to set the project dependency to java-oceanbase-springdatajpa.
    3. Use <version> to set the project version to 0.0.1-SNAPSHOT.
    4. Use description to introduce the project information as Demo project for Spring Boot.

    Code:

     <groupId>com.oceanbase</groupId>
     <artifactId>java-oceanbase-springdatajpa</artifactId>
     <version>0.0.1-SNAPSHOT</version>
     <name>java-oceanbase-springdatajpa</name>
     <description>Demo project for Spring Boot</description>

  5. Configure the Java version.

    Set the Java version used by the project to 1.8.

    Code:

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

  6. Configure core dependencies.

    1. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter-data-jpa, and the dependency library to include the necessary dependencies and configurations for data access using JPA. Spring Boot Starter Data JPA is a Spring Boot starter.

      Code:

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

    2. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter-web, and the dependency library to include the necessary dependencies and configurations for web development using Spring Boot. It is a Spring Boot starter.

      Code:

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

    3. Set the organization of the dependency to org.springframework.boot, the name to spring-boot-starter-test, and the scope to test. This dependency allows you to use the testing framework and tools provided by Spring Boot, such as JUnit, Mockito, and Hamcrest.

      Code:

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

    4. Set the organization of the dependency to com.alibaba, the name to druid, and the version to 1.2.8. This dependency allows you to use the Druid library for managing and optimizing database connection acquisition and release.

      Code:

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

    5. Set the organization of the dependency to com.oceanbase, the name to oceanbase-client, and the version to 2.4.2. This dependency allows you to use the client features provided by seekdb, such as connection, query, and transaction.

      Code:

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

  7. Configure Maven plugins.

    Set the organization of the dependency to org.springframework.boot, the name to spring-boot-maven-plugin, and the plugin to be used for packaging Spring Boot applications into executable JAR or WAR packages. This plugin allows you to directly run the applications. To avoid conflicts with other plugins or tools, the Lombok dependency is included during the build process.

    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, including the application context path, listening port number, and database connection information and JPA-related attributes.

The application.yml file contains the following code:

  • Server section

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

    Sample code:

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

  • Spring section

    • datasource: used to configure data source-related attributes, 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 the use of the Druid connection pool as the data source.

      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 section

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

      Sample 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 userRepository.java file contains the following code:

  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 DAO interfaces as Spring repository components.

    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. 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 customize an update method and perform an update operation using native SQL statements.
    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.

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

User.java​

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

The User.java file contains the following code:

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

    Sample 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 for creating an empty User object.

    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 parameterized constructor for creating an entity class object and initializing the id and username properties. Define four methods for getting and setting the id and name property values.

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

    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 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 same-named method in the parent class. Define the toString method to return the string representation of the User object. Concatenate strings to format the id and name property values into a string and return it to the caller User.

    Sample code:

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

Introduction to the UserServiceImpl.java file​

The UserServiceImpl.java file uses JPA to perform database operations for user data.

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

  1. Import other classes and interfaces.

    Declare the interfaces and classes included in the current file:

    • UserRepository class: used to inject and call methods defined in the data access object (DAO) in the service class.
    • 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.
    • Autowired annotation: used to automatically inject dependency objects.
    • Page class: used to encapsulate the results of a query.
    • PageRequest class: used to create a pagination request object.
    • Pageable interface: used to define the pagination request interface.
    • Sort class: used to define the sorting rules.
    • Service annotation: used to mark a class as a service class.
    • Transactional annotation: used to mark transaction methods.
    • 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.

    Access the database by using dependency injection of the UserRepository class. The methods for inserting, deleting, updating, and querying user data are implemented. Pagination queries are supported. The @Transactional annotation is used to enable transaction support, ensuring that database operations are executed in 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 by using 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 determine whether a user with the specified ID exists. If the user exists, the user is deleted. Otherwise, the method returns directly.

      Sample code:

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

    4. Update 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 operation. The method returns the integer value 1, indicating that the update operation is successful. Additionally, the method for updating user data based on the username and id is implemented by calling the updateById method of the UserRepository class. The update result 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 object is then called to obtain the value in the Optional object and assign it to the user variable. Finally, the 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 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 pages. The selectUserAll method creates a Sort object to specify the sorting rules, creates a Pageable object to specify the pagination parameters, and calls the findAll method of the UserRepository class to query user data. A Page object is returned. The iterator method of the Page object is then called to obtain an iterator for 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 database operations for user data.

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

  1. Import other classes and interfaces.

    Declare the classes included in the current file:

    • User class: used to operate and process user objects in the service class.
    • 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 includes 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 pages.

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

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

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

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

    • SpringApplication class, which is used to start a Spring Boot application.
    • @SpringBootApplication annotation, which is used to mark the entry class of a 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 a 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 a Spring Boot application.

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

  1. Import other classes and interfaces. Declare the following classes in the current file:

    • User class, which is used to operate and process user objects in service classes.
    • UserService class, which is used to inject and call methods defined in service classes in other classes.
    • @Test annotation, which is used to mark test methods.
    • Autowired annotation, which is used to automatically inject dependent objects.
    • @SpringBootTest annotation, which is used to test Spring Boot applications.
    • Iterator class, which is 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 a Spring Boot application. It injects the UserService dependency, calls each method to test the insertion, deletion, update, and query of 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 by page. Call the selectUserAll method of userService to query all user data by page 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​

tab pom.xml

<?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>

tab application.yml

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

tab userRepository.java

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
}

tab User.java

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 + '\'' +
                '}';
    }
}

tab UserServiceImpl.java

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

tab UserService.java

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

tab TestSpringDataJpaApplication.java

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

}

tab TestSpringDataJpaApplicationTests.java

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.

Contents

Edit this page