Connect to OceanBase Database by using MyBatis

This topic introduces how to build an application by using the MyBatis 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-mybatis 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-mybatis project into IDEA.
3. Modify the database connection information in the java-oceanbase-mybatis project.
4. Run the java-oceanbase-mybatis 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 jdbc.properties file.

   jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&useServerPrepStmts=true&rewriteBatchedStatements=true
   

   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.

   • useServerPrepStmts=true&rewriteBatchedStatements=true: the additional connection properties.

     • rewriteBatchedStatements: specifies whether to rewrite batchedStatement in an INSERT query for execution in a single executeQuery. If you set rewriteBatchedStatements to true, useServerPrepStmts is set to false. The default value is false.
     • useServerPrepStmts: specifies whether to execute PrepareStatement on the server before execution. When the application needs to repeatedly execute the same SQL statement, you can enable this parameter to reduce the number of times that the same SQL statement is compiled and parsed in the database.

   For more information about URL parameters, see Database URL.

Step 2: Import the java-oceanbase-mybatis 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-mybatis project

Modify the database connection information in the jdbc.properties 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 additional connection properties of the URL are useServerPrepStmts=true&rewriteBatchedStatements=true.
• The tenant account is sys@xyoracle, where xyoracle is a user tenant created in Oracle-compatible mode of OceanBase Database. sys is a username in the xyoracle tenant.
• The password is ******.

The sample code is as follows:

jdbc.driver=com.oceanbase.jdbc.Driver
jdbc.url=jdbc:oceanbase://10.10.10.1:2881/sys?useServerPrepStmts=true&rewriteBatchedStatements=true
jdbc.username=sys@xyoracle
jdbc.password=******

Step 4: Run the java-oceanbase-mybatis project

Run path

1. Find the TestMybatis.java file under src > test > java in the project package.
2. Choose Run > Run... > TestMybatis 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

1. The output of the testUserMapper method is as follows:

   User{id=2, name='update'}
   User{id=3, name='insert'}
   User{id=4, name='insert'}
   User{id=5, name='insert'}
   User{id=6, name='insert'}
   User{id=7, name='insert'}
   User{id=8, name='insert'}
   User{id=9, name='insert'}
   User{id=10, name='insert'}
   usersByPage = [User{id=5, name='insert'}, User{id=6, name='insert'}, User{id=7, name='insert'}]
   

2. The output of the testSqlSession method is as follows:

   User{id=2, name='update'}
   User{id=3, name='insert'}
   User{id=4, name='insert'}
   User{id=5, name='insert'}
   User{id=6, name='insert'}
   User{id=7, name='insert'}
   User{id=8, name='insert'}
   User{id=9, name='insert'}
   User{id=10, name='insert'}
   

3. The output of the testAppMapper method is as follows:

   App{id=2, name='update'}
   App{id=3, name='insert3'}
   App{id=4, name='insert4'}
   App{id=5, name='insert5'}
   App{id=6, name='insert6'}
   App{id=7, name='insert7'}
   App{id=8, name='insert8'}
   App{id=9, name='insert9'}
   App{id=10, name='insert10'}
   pageList = Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=9, pages=3, reasonable=false, pageSizeZero=true}[App{id=5, name='insert5'}, App{id=6, name='insert6'}, App{id=7, name='insert7'}]
   

4. The complete output is as follows:

   User{id=2, name='update'}
   User{id=3, name='insert'}
   User{id=4, name='insert'}
   User{id=5, name='insert'}
   User{id=6, name='insert'}
   User{id=7, name='insert'}
   User{id=8, name='insert'}
   User{id=9, name='insert'}
   User{id=10, name='insert'}
   usersByPage = [User{id=5, name='insert'}, User{id=6, name='insert'}, User{id=7, name='insert'}]
   App{id=2, name='update'}
   App{id=3, name='insert3'}
   App{id=4, name='insert4'}
   App{id=5, name='insert5'}
   App{id=6, name='insert6'}
   App{id=7, name='insert7'}
   App{id=8, name='insert8'}
   App{id=9, name='insert9'}
   App{id=10, name='insert10'}
   pageList = Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=9, pages=3, reasonable=false, pageSizeZero=true}[App{id=5, name='insert5'}, App{id=6, name='insert6'}, App{id=7, name='insert7'}]
   User{id=2, name='update'}
   User{id=3, name='insert'}
   User{id=4, name='insert'}
   User{id=5, name='insert'}
   User{id=6, name='insert'}
   User{id=7, name='insert'}
   User{id=8, name='insert'}
   User{id=9, name='insert'}
   User{id=10, name='insert'}
   

Project code introduction

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

After decompressing it, you will find a folder named java-oceanbase-mybatis. The directory structure is as follows:

│--pom.xml
│
├─.idea
│
├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─oceanbase
│  │  │          ├─mapper
│  │  │          │------IAppMapper.java
│  │  │          │------IUserMapper.java
│  │  │          │
│  │  │          └─pojo
│  │  │              │---App.java
│  │  │              └─--User.java
│  │  │
│  │  └─resources
│  │      │--jdbc.properties
│  │      │--mybatis-config.xml
│  │      │
│  │      └─com
│  │          └─oceanbase
│  │              └─mapper
│  │                  └─---IUserMapper.xml
│  │
│  └─test
│      └─java
│          └─---TestMybatis.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.
• mapper: stores the mapper interfaces and XML files of MyBatis.
• IAppMapper.java: stores the application data access layer interface.
• IUserMapper.java: stores the user data access layer interface.
• pojo: stores JavaBeans or entity classes.
• App.java: stores the application entity class.
• User.java: stores the user entity class.
• resources: the directory for storing resource files, such as configuration files and SQL files.
• jdbc.properties: the configuration file that stores database connection information.
• mybatis-config.xml: the configuration file of MyBatis.
• IUserMapper.xml: the XML configuration file for the user data access layer.
• test: the directory for storing the test code and resource files.
• TestMybatis.java: stores the Java class for MyBatis tests.
• target: the directory for storing 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 (http://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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
   </project>
   

3. Configure basic project information.

   1. Use <groupId> to specify com.oceanbase.example as the ID of the project group.
   2. Use <artifactId> to specify java-oceanbase-mybatis as the ID of the project.
   3. Use <version> to specify 1.0-SNAPSHOT as the project version.

   The sample code is as follows:

   <groupId>com.oceanbase.example</groupId>
   <artifactId>java-oceanbase-mybatis</artifactId>
   <version>1.0-SNAPSHOT</version>
   

4. Use <build> to define the build process for the project.

   1. Use <plugins> to list plug-ins in the project.
   2. Use <plugin> to specify a plug-in for the project.
   3. Use <groupId> to specify org.apache.maven.plugins as the ID of the plug-in group.
   4. Use <artifactId> to specify maven-compiler-plugin as the ID of the plug-in.
   5. Use <configuration> to configure parameters of the plug-in.
   6. Use <source> to specify 8 as the source code version for the compiler.
   7. Use <target> to specify 8 as the version of the code generated by the compiler.

   The sample code is as follows:

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
   

5. Configure project dependencies in <dependencies>.

   1. Specify com.oceanbase as the ID of the dependency group, oceanbase-client as the ID of the dependency, and 2.4.2 as the version of the dependency.

      The sample code is as follows:

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

   2. Specify junit as the ID of the dependency group, junit as the ID of the dependency, and 4.10 as the version of the dependency.

      The sample code is as follows:

          <dependencies>
          <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.10</version>
          </dependency>
          </dependencies>
      

   3. Specify org.mybatis as the ID of the dependency group, mybatis as the ID of the dependency, and 3.5.9 as the version of the dependency.

      The sample code is as follows:

          <dependencies>
          <dependency>
              <groupId>org.mybatis</groupId>
              <artifactId>mybatis</artifactId>
              <version>3.5.9</version>
          </dependency>
          </dependencies>
      

   4. Specify com.github.pagehelper as the ID of the dependency group, pagehelper as the ID of the dependency, and 5.3.0 as the version of the dependency.

      The sample code is as follows:

          <dependencies>
          <dependency>
              <groupId>com.github.pagehelper</groupId>
              <artifactId>pagehelper</artifactId>
              <version>3.5.0</version>
          </dependency>
          </dependencies>
      

Code in jdbc.properties

jdbc.properties is a property file that stores database connection configurations, including the URL, username, and password of a database. Code in the file contains the following content:

• jdbc.driver: the class name of the database driver, which is used for loading the database driver.
• jdbc.url: the URL of the database to connect to.
• jdbc.username: the username used to verify database connection.
• jdbc.password: the password used to verify database connection.

The sample code is as follows:

jdbc.driver=com.oceanbase.jdbc.Driver
jdbc.url=jdbc:oceanbase://host:port/TEST?useServerPrepStmts=true&rewriteBatchedStatements=true
jdbc.username=user_name
jdbc.password=******

Code in mybatis-config.xml

The mybatis-config.xml file configures global settings and plug-ins of the MyBatis framework.

To configure the mybatis-config.xml file, perform the following steps:

1. Declare the file.

   Declare that the current file is an XML file with MyBatis configurations, the XML version is 1.0, and the character encoding method is UTF-8. Declare to use a Document Type Definition (DTD) file provided at the official website of MyBatis for validation. The version of the DTD file is 3.0, and the language of the DTD is EN.

   The declaration contains the following parts:

   • version: the version of the XML file.
   • encoding: the encoding method of the XML file.
   • DOCTYPE: the document type declaration.
   • configuration: specifies that the XML file is a MyBatis configuration file.
   • PUBLIC: specifies that the DTD file is a public one.
   • mybatis.org: the official MyBatis website.
   • DTD Config 3.0: the version of the DTD file.
   • EN: specifies that the language of the DTD is English.

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
   

2. Specify the path and file name of the property file to load.

   <properties resource="jdbc.properties"></properties>
   

3. Configure global settings for MyBatis.

   Global settings for MyBatis contain the following parts:

   • setting: a single setting item, such as the cache or log.
   • cacheEnabled: enables or disables caching, which is set to true.
   • lazyLoadingEnabled: enables or disables lazy loading, which is set to true.
   • aggressiveLazyLoading: enables or disables aggressive lazy loading, which is set to true.
   • multipleResultSetsEnabled: enables or disables the support for multiple result sets, which is set to true.
   • useColumnLabel: specifies whether to use column labels as column names in the result set, which is set to true.
   • useGeneratedKeys: specifies whether to use automatically generated primary keys, which is set to true.
   • autoMappingBehavior: the automatic mapping behavior, which is set to PARTIAL.
   • defaultExecutorType: the default executor type, which is set to SIMPLE.
   • mapUnderscoreToCamelCase: specifies whether to convert database column names containing underscores to camel case Java object attribute names, which is set to true.
   • localCacheScope: the local cache scope, which is set to SESSION.
   • jdbcTypeForNull: the JDBC type for null values, which is set to NULL.

   Note

   Child elements of the settings element are optional. You can add or delete child elements as needed.

   The sample code is as follows:

   <settings>
       <!-- Enable or disable caching for global mappers. -->
       <setting name="cacheEnabled" value="true"/>
       <!-- Enable or disable lazy loading globally. When disabled, all associated objects are loaded immediately. -->
       <setting name="lazyLoadingEnabled" value="true"/>
       <!-- When enabled, objects with delayed loading properties will fully load any properties when called. Otherwise, each attribute will be loaded as needed. -->
       <setting name="aggressiveLazyLoading" value="true"/>
       <!-- Allow a single SQL statement to return multiple datasets (depending on driver compatibility) default: true -->
       <setting name="multipleResultSetsEnabled" value="true"/>
       <!-- Can column aliases be used (depending on driver compatibility) default: true -->
       <setting name="useColumnLabel" value="true"/>
       <!-- Allow JDBC to generate primary keys. Drive support is required. If set to true, this setting will force the use of the generated primary key, and some drives may not be compatible but can still be executed. default:false -->
       <setting name="useGeneratedKeys" value="true"/>
       <!-- Specify how MyBatis automatically maps the columns of the data base table NONE: Not Implicit PART: Partial FULL: All -->
       <setting name="autoMappingBehavior" value="PARTIAL"/>
       <!-- This is the default execution type (SIMPLE: simple; REUSE: executor may repeatedly use prepared statements; BATCH: executor can repeatedly execute statements and batch updates) -->
       <setting name="defaultExecutorType" value="SIMPLE"/>
       <!-- Convert fields using camel naming. -->
       <setting name="mapUnderscoreToCamelCase" value="true"/>
       <!-- Set the local cache range session: there will be data sharing statement: statement range (so there will be no data sharing) defalut: session -->
       <setting name="localCacheScope" value="SESSION"/>
       <!-- When the JDBC type is set to null, some drivers need to specify a value, default: Other, and there is no need to specify a type when inserting null values -->
       <setting name="jdbcTypeForNull" value="NULL"/>
   </settings>
   

4. Configure MyBatis plug-ins.

   MyBatis plug-in configurations include the following parts:

   • plugin: configures a single plug-in.
   • property: a plug-in property.
   • interceptor: the implementation class of a plug-in.
   • helperDialect: the database to connect to.
   • offsetAsPageNum: specifies whether to use the offset parameter as the pageNum parameter.
   • rowBoundsWithCount: specifies whether to perform a count query.
   • pageSizeZero: specifies whether to support queries with a pageSize of 0.
   • reasonable: specifies whether to enable reasonable queries.
   • params: the mapping between parameter names and parameter values, which is configured to pass pagination parameters.
   • supportMethodsArguments: specifies whether to support passing pagination parameters through method parameters.
   • returnPageInfo: the type of the return value.

   The sample code is as follows:

   <plugin interceptor="com.github.pagehelper.PageInterceptor">
      <!-- this parameter indicates which database to connect to -->
      <!--MySQLMode dialect<property name="helperDialect" value="mysql"/>-->
      <!--OracleMode dialect -->
      <property name="helperDialect" value="oracle"/>
      <!-- This parameter defaults to false. When set to true, the first parameter offset of RowBounds will be used as the pageNum page number, similar to the pageNum effect in startPage -->
      <property name="offsetAsPageNum" value="true"/>
      <!-- This parameter defaults to false, and when set to true, using RowBounds pagination will result in a count query -->
      <property name="rowBoundsWithCount" value="true"/>
      <!-- When set to true, if pageSize=0 or RowBounds. limit=0, all results will be queried (equivalent to not executing a pagination query, but the returned results are still of type Page) -->
      <property name="pageSizeZero" value="true"/>
      <!-- Version 3.3.0 is available - pagination parameter rationalization is disabled by default as false. When rationalization is enabled, if pageNum<1, the first page will be queried, and if pageNum>pages, the last page will be queried. Rationalization of paging parameters. When rationalization is disabled, if pageNum<1 or pageNum>pages returns empty data -->
      <property name="reasonable" value="false"/>
      <!-- Version 3.5.0 is available - In order to support the startPage (Object params) method, a 'params' parameter has been added to configure parameter mapping, which can be used to retrieve values from Map or ServletRequest. PageNum, pageSize, count, pageSizeZero, reasonable, orderBy can be configured. If mapping is not configured, the default value will be used. If you do not understand the meaning, do not copy this configuration casually -->
      <property name="params" value="pageNum=start;pageSize=limit;"/>
      <!-- Support passing paging parameters through Mapper interface parameters -->
      <property name="supportMethodsArguments" value="true"/>
      <!-- Always always returns PageInfo type, check to check if the return type is PageInfo, and none returns Page -->
      <property name="returnPageInfo" value="check"/>
   </plugin>
   

5. Configure MyBatis environments.

   MyBatis environment configurations include the following parts:

   • environment: configures a single environment.
   • transactionManager: the implementation class of the transaction manager.
   • dataSource: the implementation class of the data source.
   • property: the class name of the database driver.

   Note

   In other MyBatis configuration elements, you can use the ${} placeholder to reference data sources, transaction managers, and executors configured in environments.

   The sample code is as follows:

   <environments default="development">
       <environment id="development">
         <transactionManager type="JDBC"/>
           <dataSource type="POOLED">
               <property name="driver" value="${jdbc.driver}"/>
               <property name="url" value="${jdbc.url}"/>
               <property name="username" value="${jdbc.username}"/>
               <property name="password" value="${jdbc.password}"/>
           </dataSource>
       </environment>
   </environments>
   

6. Configure MyBatis mappers.

   MyBatis mapper configurations include the following parts:

   • resource: the path and name of the XML configuration file of the mapper.
   • class: the Java class name of the mapper.

   Note

   To use an XML configuration file, you must specify the path and name of the XML file in the mapper element. To use a Java interface, you must specify the fully qualified name of the Java class in the mapper element.

   The sample code is as follows:

   <mappers>
     <!-- IUserMapper.xml mapping file -->
     <mapper resource="com/alipay/oceanbase/mapper/IUserMapper.xml"></mapper>
     <!-- IAppMapper mapping class -->
     <mapper class="com.oceanbase.mapper.IAppMapper"></mapper>
   </mappers>
   

Code in IUserMapper.xml

The IUserMapper.xml file is a mapping file that defines SQL statements related to user objects. The file defines a namespace named IUserMapper, which stores SQL statements related to user objects.

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

1. Declare the file.

   In the XML declaration part, declare the version and encoding method of the XML file. In the DTD declaration part, reference the DTD file of MyBatis.

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   

2. Configure the mapping to the IUserMapper.java file.

   1. Define the namespace of the Mapper interface, which corresponds to the Mapper interface in Java code.
   2. Create an SQL statement named insertUser to insert a record where id is #{id} and name is #{name} into the test_user table. The parameterType attribute specifies that the type of parameters passed into the statement is com.oceanbase.pojo.User.
   3. Create an SQL statement named deleteUser to delete a record where id is #{id} from the test_user table.
   4. Create an SQL statement named updateUser to update the value of the name field to #{name} for the record where id is #{id} in the test_user table.
   5. Create an SQL statement named selectUsers to query all user records in the test_user table.
   6. Create an SQL statement named selectUserByPage to query the test_user table for user objects on a specified number of pages. Use subqueries and the rownum field for pagination queries, where #{pageNum} and #{pageSize} specify the current page number and the number of records per page, respectively.

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.oceanbase.mapper.IUserMapper">
       <insert id="insertUser" parameterType="com.oceanbase.pojo.User">
           INSERT INTO test_user (id,name) VALUES (#{id},#{name})
       </insert>
       <delete id="deleteUser" parameterType="long">
           DELETE FROM test_user WHERE id = #{id}
       </delete>
       <update id="updateUser" parameterType="com.oceanbase.pojo.User">
           UPDATE test_user SET name = #{name} WHERE id = #{id}
       </update>
       <select id="selectUsers" resultType="com.oceanbase.pojo.User">
           SELECT id,name FROM test_user
       </select>
       <!--    There are two ways to paginate queries: 1. Use the pagehelper plugin; 2. Use SQL statements to paginate  -->
       <!-- SQL statement pagination: Oracle-compatible mode does not support the limit keyword, and instead uses the unique field 'rownum'-->
       <select id="selectUserByPage" resultType="com.oceanbase.pojo.User">
           select id,name from ( select row_.*, rownum rownum_ from ( select * from test_user ) row_ where rownum
           <![CDATA[ <= ]]>  #{pageNum} * #{pageSize} ) where rownum_ <![CDATA[ >]]>  ( #{pageNum}- 1) * #{pageSize}
       </select>
   </mapper>
   

Code in IAppMapper.java

The IAppMapper.java file defines SQL mappings.

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

1. Define the package and import necessary classes and interfaces.

   Declare that the current file belongs to the com.oceanbase.mapper package. Import the following classes and interfaces for the IAppMapper.java file:

   • App class: the class from which an App object is created.
   • org.apache.ibatis.annotations.* classes: all annotation classes used in MyBatis.
   • java.util.List interface: the List interface in the java.util package.

   The sample code is as follows:

   package com.oceanbase.mapper;
   import com.oceanbase.pojo.App;
   import org.apache.ibatis.annotations.*;
   import java.util.List;
   

2. Define the IAppMapper interface.

   The IAppMapper interface defines the Mapper interface of MyBatis. The Mapper interface defines SQL mappings for the add, delete, modify, and query operations on the database. Specifically, the IAppMapper interface defines the methods to add, delete, modify, and query data in the test_app table.

   • @Insert method: inserts data into the database.
   • @Update method: updates data to the database.
   • @Delete method: deletes data from the database.
   • @Select method: queries data in the database.
   • @Results method: maps the query result to the attributes of the Java object.

   The The database CRUD operations are as follows:

   1. Insert data.

      Insert a record into the test_app table. That is, map an App object to a record in the table. Attribute values in the App object are represented by placeholders in the format of #{attribute name}. The return value of the Integer type indicates the automatically generated ID returned after the SQL statement inserts the record, that is, the value of the id field in the test_app table.

      @Insert("insert into test_app(id,name) values(#{id},#{name})")
      Integer insertApp(App app);
      

   2. Delete data.

      Delete the record where id is #{id} from the test_app table.

      @Delete("delete from test_app  where id =#{id}")
      Integer deleteApp(Long id);
      

   3. Update data.

      Modify the record where id is #{id} in the test_app table.

      @Update("update test_app set name= #{name} where id = #{id}")
      Integer updateApp(App user);
      

   4. Query and map data.

      Query all data in the test_app table. Use the @Results and @Result annotations to map query results to the id and name attributes in App objects. Then, return a list of App objects.

      @Update("update test_app set name= #{name} where id = #{id}")
      Integer updateApp(App user);
      @Results({
          @Result(id = true, column = "id", property = "id"),
          @Result(column = "name", property = "name")
          })
      List<App> selectApps();
      

   The sample code is as follows:

   package com.oceanbase.mapper;
   import com.oceanbase.pojo.App;
   import org.apache.ibatis.annotations.*;
   import java.util.List;
   public interface IAppMapper {
       @Insert("insert into test_app(id,name) values(#{id},#{name})")
       Integer insertApp(App app);
   
       @Delete("delete from test_app  where id =#{id}")
       Integer deleteApp(Long id);
   
       @Update("update test_app set name= #{name} where id = #{id}")
       Integer updateApp(App user);
   
       @Select("select * from test_app")
       @Results({
               @Result(id = true, column = "id", property = "id"),
               @Result(column = "name", property = "name")
       })
       List<App> selectApps();
   }
   

Code in IUserMapper.java

The IUserMapper.java file defines methods for database operations.

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

1. Reference other classes and interfaces.

   Declare the interfaces and classes in the current file:

   • User class: the user object.
   • org.apache.ibatis.annotations.Param class: the Param annotation in the MyBatis framework.
   • List interface: the list type.

   The sample code is as follows:

   package com.oceanbase.mapper;
   import com.oceanbase.pojo.User;
   import org.apache.ibatis.annotations.Param;
   import java.util.List;
   

2. Define the IUserMapper interface.

   Define an interface called IUserMapper that contains the methods to insert, delete, update, and query user data and the method to query user data with pagination. Use JavaDoc comments and @Param annotations to improve code readability and maintainability.

   The sample code is as follows:

    public interface IUserMapper {
   
        Integer insertUser(User user);
   
        Integer deleteUser(Long id);
   
        Integer updateUser(User user);
   
        List<User> selectUsers();
   
        public List<User> selectUserByPage(@Param("user") User user, @Param("pageNum") Integer pageNum,
                                        @Param("pageSize") Integer pageSize);
    }
   

Code in App.java

The App.java file defines the App class from which App objects are created. Each object contains two attributes: id and name. The class also defines the methods to get and set attribute values, and the toString method to convert objects into strings.

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

1. Define the pojo package.

   Declare that the current file belongs to the com.oceanbase.pojo package.

2. Define the App class.

   1. Define the App class that contains two private attributes: id and name, and provides the no-parameter constructor public App(){}.
   2. Define the public App(Long id, String name) constructor for creating an App object with the specified id and name attributes.
   3. Define the getId, setId, getName, and setName methods to get and set the id and name attributes of App objects. Return the application name set by setName.
   4. Override the toString method to convert App objects into strings for easy output and debugging.

The sample code is as follows:

   package com.oceanbase.pojo;

   public class App {
       private Long id;
       private String name;

       public App() {
       }

       public App(Long id, String name) {
            this.id = id;
           this.name = name;
       }

       public Long getId() {
           return id;
       }

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

       public String getName() {
           return name;
       }

       public void setName(String name) {
           this.name = name;
       }

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

Code in User.java

The User.java file represents the user object. Each object contains two attributes: id and name. The class also defines the methods to get and set attribute values, and the toString method to convert objects into strings. The creation of this class is similar to that of the class in App.java.

The sample code is as follows:

package com.oceanbase.pojo;

public class User {
    private Long id;
    private String name;

    public User() {
    }

    public User(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

Code in MyBatisTest.java

The MyBatisTest.java file demonstrates how to use MyBatis to perform data operations. With this file, you can test the basic features of the MyBatis framework, the execution of SQL statements, methods to call Mapper interfaces, and the parameters and return values of SQL statements.

1. Reference other classes and interfaces.

   Here are the classes and interfaces to import:

   • IAppMapper interface: defines SQL statements related to App objects.
   • IUserMapper interface: defines SQL statements related to User objects.
   • App class: the class from which an App object is created. You can use App objects to test the execution of SQL statements.
   • User class: the class from which a User object is created. You can use User objects to test the execution of SQL statements.
   • PageHelper plug-in: implements pagination queries.
   • PageInfo plug-in: encapsulates results of pagination queries.
   • Resources class: loads the MyBatis configuration file.
   • SqlSession class: executes SQL statements and manages transactions.
   • SqlSessionFactory class: creates SqlSession objects.
   • SqlSessionFactoryBuilder class: creates SqlSessionFactory objects.
   • org.junit.Test: the annotation that marks a test method for the JUnit test framework.
   • IOException class: errors in input and output operations.
   • SQLException class: errors in SQL operations.
   • Statement interface: executes SQL statements and returns results.
   • java.util.List interface: an ordered collection where duplicate elements are allowed.

   The sample code is as follows:

   import com.oceanbase.mapper.IAppMapper;
   import com.oceanbase.mapper.IUserMapper;
   import com.oceanbase.pojo.App;
   import com.oceanbase.pojo.User;
   import com.github.pagehelper.PageHelper;
   import com.github.pagehelper.PageInfo;
   import org.apache.ibatis.io.Resources;
   import org.apache.ibatis.session.SqlSession;
   import org.apache.ibatis.session.SqlSessionFactory;
   import org.apache.ibatis.session.SqlSessionFactoryBuilder;
   import org.junit.Test;
   import java.io.IOException;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.List;
   

2. Define the testUserMapper method.

   The testUserMapper method tests the execution of SQL statements defined in the Mapper interface of the User object.

   1. Call the build() method of the SqlSessionFactoryBuilder class to create a SqlSessionFactory instance. The instance is used to manage the creation and destruction of SqlSession instances.
   2. Call the openSession() method of the SqlSessionFactory class to create a SqlSession instance. The instance can be used to execute various SQL statements, including query, insert, update, and delete operations.
   3. Call the getConnection() method of the SqlSession instance to get a Connection instance. The instance can be used to perform various database operations.
   4. Call the createStatement() method of the Connection instance to create a Statement instance. The instance can be used to execute SQL statements in sequence.
   5. Call the execute() method of the Statement object to execute the SQL statement that drops the table named test_user.
   6. Call the execute() method of the Statement object to execute the SQL statement that creates a table named test_user. The table contains two fields: id of the number(20) type and name of the varchar2(100) type. The id field is the primary key.
   7. Call the getMapper() method of the SqlSession instance to get an instance of the IUserMapper interface. Define methods for various database operations.
   8. Use a for loop to insert 10 records into the test_user table. In each iteration, create a new User object and call the insertUser() method of the Mapper interface to insert the object. Store the result of the insert operation in the insertResult variable.
   9. Delete, update, and query the data in the test_user table, and then call the forEach() method to print the information of each user in the user list.
   10. Create a User object where the name attribute is insert. Call the selectUserByPage() method, which takes the object as an input parameter, to query the data of all users named insert in the test_user table, and return the list of users on page 2 with each page showing 3 records. Print the user data to the console. Close both the Statement and SqlSession instances and commit the transaction to release resources.

   The sample code is as follows:

       public void testUserMapper() throws SQLException, IOException {
           //mybatis xml usecases
           SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
           SqlSession sqlSession = sqlSessionFactory.openSession();
           Statement statement = sqlSession.getConnection().createStatement();
           try {
               statement.execute("drop table test_user");
           } catch (SQLException ex) {
           } finally {
               statement.execute("create table test_user(id number(20) primary key,name varchar2(100))");
           }
           IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
           //insert 10 users
           for (int i = 1; i <= 10; i++) {
               User user = new User((long) i, "insert");
               Integer insertResult = mapper.insertUser(user);
           }
           //delete id==1
           Integer deleteResult = mapper.deleteUser(1L);
           //update id==2L name=update
           User updateUser = new User(2L, "update");
           Integer updateResult = mapper.updateUser(updateUser);
           //selectUsers query all
           List<User> userList = mapper.selectUsers();
           userList.forEach(System.out::println);
           //selectUsersByPage: use the rownum keyword in SQL statements to manually perform pagination queries,
           // example: data on page 2 (3 items per page)
           User user = new User();
           user.setName("insert");
           List<User> usersByPage = mapper.selectUserByPage(user, 2, 3);
           System.out.println("usersByPage = " + usersByPage);
           statement.close();
           sqlSession.commit();
           sqlSession.close();
       }
   

3. Define the testSqlSession method.

   The testSqlSession method tests the basic features of SqlSession objects, including executing SQL statements, committing transactions, and closing SqlSession objects.

   1. Call the build() method of the SqlSessionFactoryBuilder class to create a SqlSessionFactory instance. The instance is used to manage the creation and destruction of SqlSession instances.
   2. Call the openSession() method of the SqlSessionFactory class to create a SqlSession instance. The instance can be used to execute various SQL statements, including query, insert, update, and delete operations.
   3. Call the getConnection() method of the SqlSession instance to get a Connection instance. The instance can be used to perform various database operations.
   4. Call the createStatement() method of the Connection instance to create a Statement instance. The instance can be used to execute SQL statements in sequence.
   5. Call the execute() method of the Statement object to execute the SQL statement that drops the table named test_user.
   6. Call the execute() method of the Statement object to execute the SQL statement that creates a table named test_user. The table contains two fields: id of the number(20) type and name of the varchar2(100) type. The id field is the primary key.
   7. Use a for loop to insert 10 records into the test_user table. In each iteration, create a new User object and call the insertUser() method of the Mapper interface to insert the object. Store the result of the insert operation in the insertResult variable.
   8. Call the delete() method of the sqlSession object to perform the delete operation. Set the delete condition by passing in the parameter 1L. Store the result of the delete operation in the deleteResult variable.
   9. Use the sqlSession object to execute a database update operation. Create a User object and call the update method to pass in the ID and parameter object of the SQL statement to complete the update operation. You can find specific SQL statements and parameter mappings in the XML configuration file of the com.oceanbase.mapper.IUserMapper interface. Store the result of the update operation in the updateResult variable.
   10. Call the openSession() method of SqlSessionFactory to create a SqlSession instance. Perform a query operation by using the instance and store the query results in the userList variable. Call the forEach method to traverse the userList variable and output the results to the console.
   11. Call statement.close() to close the Statement object in the database connection. Call sqlSession.commit() to commit the transaction and persist all changes to the database. Call sqlSession.close() to close the SqlSession object, release relevant resources, and close the connection to the database.

   The sample code is as follows:

       public void testSqlSession() throws SQLException, IOException {
           //SqlSession usecases
           SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
           SqlSession sqlSession = sqlSessionFactory.openSession();
           Statement statement = sqlSession.getConnection().createStatement();
           try {
               statement.execute("drop table test_user");
           } catch (SQLException ex) {
           } finally {
               statement.execute("create table test_user(id number(20) primary key,name varchar2(100))");
           }
           //insert
           for (int i = 1; i <= 10; i++) {
               User user = new User((long) i, "insert");
               //Integer insertResult = mapper.insertUser(user);
               int insertResult = sqlSession.insert("com.oceanbase.mapper.IUserMapper.insertUser", user);
           }
           //delete
           int deleteResult = sqlSession.delete("com.oceanbase.mapper.IUserMapper.deleteUser", 1L);
           //update
           User updateUser = new User(2L, "update");
           int updateResult = sqlSession.update("com.oceanbase.mapper.IUserMapper.updateUser", updateUser);
           //selectUsers
           List<User> userList = sqlSession.selectList("com.oceanbase.mapper.IUserMapper.selectUsers", null);
           userList.forEach(System.out::println);
           //System.out.println("userList = " + userList);
           statement.close();
           sqlSession.commit();
           sqlSession.close();
       }
   

4. Define the testAppMapper method.

   The testAppMapper method tests the features of AppMapper.

   1. Call the build() method of the SqlSessionFactoryBuilder class to create a SqlSessionFactory instance. The instance is used to manage the creation and destruction of SqlSession instances.
   2. Call the openSession() method of the SqlSessionFactory class to create a SqlSession instance. The instance can be used to execute various SQL statements, including query, insert, update, and delete operations.
   3. Call the getConnection() method of the SqlSession instance to get a Connection instance. The instance can be used to perform various database operations.
   4. Call the createStatement() method of the Connection instance to create a Statement instance. The instance can be used to execute SQL statements in sequence.
   5. Call the execute() method of the Statement object to execute the SQL statement that drops the table named test_app.
   6. Call the execute() method of the Statement object to execute the SQL statement that creates a table named test_app. The table contains two fields: id of the number(20) type and name of the varchar2(100) type. The id field is the primary key.
   7. Call the getMapper() method of the SqlSession instance to get an IAppMapper instance. Define methods for various database operations.
   8. Use a for loop to insert 10 records into the test_app table. In each iteration, create a new App object and call the insertApp() method of the Mapper interface to insert the object. Store the result of the insert operation in the insertResult variable.
   9. Call the deleteApp() method of mapper to perform the delete operation. Set the delete condition by passing in the parameter 1L. Store the result of the delete operation in the deleteResult variable.
   10. Use the mapper object to perform a database update operation. Create an App object and call the update method to pass in the ID and parameter object of the SQL statement to complete the update operation.
   11. Create an App object named updateApp where id is 2L and name is update.
   12. Call the updateApp method of mapper and pass in the updateApp object to perform the update operation.
   13. Call the commit method of sqlSession to commit the database transaction.
   14. Call the selectApps method of mapper to query all App objects. Call the forEach method to traverse the appList variable and output the results to the console.
   15. Call the startPage method of PageHelper and pass in pagination parameters, including the page number and the number of data entries per page. Query all App objects and return a List object that contains all App objects.
   16. Call the getList method of PageInfo to obtain the paged list of App objects, and call the System.out.println method to print the paged list of App objects.
   17. Call sqlSession.close() to close the SqlSession object and release relevant resources.

   The sample code is as follows:

       public void testAppMapper() throws SQLException, IOException {
           //mybatis annotation usecases
           SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
           SqlSession sqlSession = sqlSessionFactory.openSession();
           Statement statement = sqlSession.getConnection().createStatement();
           try {
               statement.execute("drop table test_app");
           } catch (SQLException ex) {
           } finally {
               statement.execute("create table test_app(id number(20) primary key,name varchar2(100))");
           }
   
           IAppMapper mapper = sqlSession.getMapper(IAppMapper.class);
   
           //insert
           for (int i = 1; i <= 10; i++) {
               App app = new App((long) i, "insert" + i);
               Integer insertResult = mapper.insertApp(app);
           }
   
           //delete
           Integer deleteResult = mapper.deleteApp(1L);
           //update
           App updateApp = new App(2L, "update");
           Integer updateResult = mapper.updateApp(updateApp);
           //commit
           sqlSession.commit();
   
           //selectApps
           List<App> appList = mapper.selectApps();
           appList.forEach(System.out::println);
   
           //selectbyPage
           //set page parameters
           PageHelper.startPage(2, 3);
           //selectApps
           List<App> appList1 = mapper.selectApps();
           //get pageList
           PageInfo pageInfo = new PageInfo(appList1);
           List<App> pageList = pageInfo.getList();
           System.out.println("pageList = " + pageList);
   
           sqlSession.close();
       }
   

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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>

        <groupId>com.oceanbase.example</groupId>
        <artifactId>java-oceanbase-mybatis</artifactId>
        <version>1.0-SNAPSHOT</version>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <configuration>
                        <source>8</source>
                        <target>8</target>
                    </configuration>
                </plugin>
            </plugins>
        </build>

        <dependencies>
            <dependency>
                <groupId>com.oceanbase</groupId>
                <artifactId>oceanbase-client</artifactId>
                <version>2.4.2</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.10</version>
            </dependency>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.9</version>
            </dependency>
            <!--  pagehelper plug-in -->
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>5.3.0</version>
            </dependency>
        </dependencies>
    </project>

jdbc.driver=com.oceanbase.jdbc.Driver
jdbc.url=jdbc:oceanbase://host:port/TEST?useServerPrepStmts=true&rewriteBatchedStatements=true
jdbc.username=user_name
jdbc.password=******

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
      <properties resource="jdbc.properties"></properties>
      <settings>
          <setting name="cacheEnabled" value="true"/>
          <setting name="lazyLoadingEnabled" value="true"/>
          <setting name="aggressiveLazyLoading" value="true"/>
          <setting name="multipleResultSetsEnabled" value="true"/>
          <setting name="useColumnLabel" value="true"/>
          <setting name="useGeneratedKeys" value="true"/>
          <setting name="autoMappingBehavior" value="PARTIAL"/>
          <setting name="defaultExecutorType" value="SIMPLE"/>
          <setting name="mapUnderscoreToCamelCase" value="true"/>
          <setting name="localCacheScope" value="SESSION"/>
          <setting name="jdbcTypeForNull" value="NULL"/>
      </settings>
      <plugins>
          <plugin interceptor="com.github.pagehelper.PageInterceptor">

              <property name="helperDialect" value="oracle"/>

              <property name="offsetAsPageNum" value="true"/>
              <property name="rowBoundsWithCount" value="true"/>
              <property name="pageSizeZero" value="true"/>

              <property name="reasonable" value="false"/>

              <property name="params" value="pageNum=start;pageSize=limit;"/>
              <property name="supportMethodsArguments" value="true"/>
              <property name="returnPageInfo" value="check"/>
          </plugin>
      </plugins>
      <environments default="development">
          <environment id="development">
              <transactionManager type="JDBC"/>
              <dataSource type="POOLED">
                  <property name="driver" value="${jdbc.driver}"/>
                  <property name="url" value="${jdbc.url}"/>
                  <property name="username" value="${jdbc.username}"/>
                  <property name="password" value="${jdbc.password}"/>
              </dataSource>
          </environment>
      </environments>
      <mappers>
          <mapper resource="com/alipay/oceanbase/mapper/IUserMapper.xml"></mapper>
          <mapper class="com.oceanbase.mapper.IAppMapper"></mapper>
      </mappers>
    </configuration>

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.oceanbase.mapper.IUserMapper">
        <insert id="insertUser" parameterType="com.oceanbase.pojo.User">
            INSERT INTO test_user (id,name) VALUES (#{id},#{name})
        </insert>
        <delete id="deleteUser" parameterType="long">
            DELETE FROM test_user WHERE id = #{id}
        </delete>
        <update id="updateUser" parameterType="com.oceanbase.pojo.User">
            UPDATE test_user SET name = #{name} WHERE id = #{id}
        </update>
        <select id="selectUsers" resultType="com.oceanbase.pojo.User">
            SELECT id,name FROM test_user
        </select>
        <!--    There are two ways to paginate queries: 1. Use the pagehelper plugin; 2. Use SQL statements to paginate  -->
        <!-- SQL statement pagination: Oracle-compatible mode does not support the limit keyword, and instead uses the unique field 'rownum'-->
        <select id="selectUserByPage" resultType="com.oceanbase.pojo.User">
            select id,name from ( select row_.*, rownum rownum_ from ( select * from test_user ) row_ where rownum
            <![CDATA[ <= ]]>  #{pageNum} * #{pageSize} ) where rownum_ <![CDATA[ >]]>  ( #{pageNum}- 1) * #{pageSize}
        </select>
    </mapper>

package com.oceanbase.mapper;

import com.oceanbase.pojo.App;
import org.apache.ibatis.annotations.*;

import java.util.List;

//using annotations
public interface IAppMapper {
    @Insert("insert into test_app(id,name) values(#{id},#{name})")
    Integer insertApp(App app);

    @Delete("delete from test_app  where id =#{id}")
    Integer deleteApp(Long id);

    @Update("update test_app set name= #{name} where id = #{id}")
    Integer updateApp(App user);

    @Select("select * from test_app")
    @Results({
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "name", property = "name")
    })
    List<App> selectApps();
}

package com.oceanbase.mapper;

import com.oceanbase.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

//using XML
public interface IUserMapper {

    Integer insertUser(User user);


    Integer deleteUser(Long id);

    Integer updateUser(User user);


    List<User> selectUsers();


    public List<User> selectUserByPage(@Param("user") User user, @Param("pageNum") Integer pageNum,
                                       @Param("pageSize") Integer pageSize);
}

package com.oceanbase.pojo;

public class App {
    private Long id;
    private String name;

    public App() {
    }

    public App(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

package com.oceanbase.pojo;

public class User {
    private Long id;
    private String name;

    public User() {
    }

    public User(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

import com.oceanbase.mapper.IAppMapper;
import com.oceanbase.mapper.IUserMapper;
import com.oceanbase.pojo.App;
import com.oceanbase.pojo.User;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;


public class TestMybatis {
    @Test
    public void testUserMapper() throws SQLException, IOException {
        //mybatis xml usecases
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Statement statement = sqlSession.getConnection().createStatement();
        try {
            statement.execute("drop table test_user");
        } catch (SQLException ex) {
        } finally {
            statement.execute("create table test_user(id number(20) primary key,name varchar2(100))");
        }
        IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
        //insert 10 users
        for (int i = 1; i <= 10; i++) {
            User user = new User((long) i, "insert");
            Integer insertResult = mapper.insertUser(user);
        }
        //delete id==1
        Integer deleteResult = mapper.deleteUser(1L);
        //update id==2L name=update
        User updateUser = new User(2L, "update");
        Integer updateResult = mapper.updateUser(updateUser);
        //selectUsers query all
        List<User> userList = mapper.selectUsers();
        userList.forEach(System.out::println);
        //selectUsersByPage: use the rownum keyword in SQL statements to manually perform pagination queries,
        // example: data on page 2 (3 items per page)
        User user = new User();
        user.setName("insert");
        List<User> usersByPage = mapper.selectUserByPage(user, 2, 3);
        System.out.println("usersByPage = " + usersByPage);
        statement.close();
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testSqlSession() throws SQLException, IOException {
        //SqlSession usecases
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Statement statement = sqlSession.getConnection().createStatement();
        try {
            statement.execute("drop table test_user");
        } catch (SQLException ex) {
        } finally {
            statement.execute("create table test_user(id number(20) primary key,name varchar2(100))");
        }
        //insert
        for (int i = 1; i <= 10; i++) {
            User user = new User((long) i, "insert");
            //Integer insertResult = mapper.insertUser(user);
            int insertResult = sqlSession.insert("com.oceanbase.mapper.IUserMapper.insertUser", user);
        }
        //delete
        int deleteResult = sqlSession.delete("com.oceanbase.mapper.IUserMapper.deleteUser", 1L);
        //update
        User updateUser = new User(2L, "update");
        int updateResult = sqlSession.update("com.oceanbase.mapper.IUserMapper.updateUser", updateUser);
        //selectUsers
        List<User> userList = sqlSession.selectList("com.oceanbase.mapper.IUserMapper.selectUsers", null);
        userList.forEach(System.out::println);
        //System.out.println("userList = " + userList);
        statement.close();
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testAppMapper() throws SQLException, IOException {
        //mybatis annotation usecases
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Statement statement = sqlSession.getConnection().createStatement();
        try {
            statement.execute("drop table test_app");
        } catch (SQLException ex) {
        } finally {
            statement.execute("create table test_app(id number(20) primary key,name varchar2(100))");
        }

        IAppMapper mapper = sqlSession.getMapper(IAppMapper.class);

        //insert
        for (int i = 1; i <= 10; i++) {
            App app = new App((long) i, "insert" + i);
            Integer insertResult = mapper.insertApp(app);
        }

        //delete
        Integer deleteResult = mapper.deleteApp(1L);
        //update
        App updateApp = new App(2L, "update");
        Integer updateResult = mapper.updateApp(updateApp);
        //commit
        sqlSession.commit();

        //selectApps
        List<App> appList = mapper.selectApps();
        appList.forEach(System.out::println);

        //selectbyPage
        //set page parameters
        PageHelper.startPage(2, 3);
        //selectApps
        List<App> appList1 = mapper.selectApps();
        //get pageList
        PageInfo pageInfo = new PageInfo(appList1);
        List<App> pageList = pageInfo.getList();
        System.out.println("pageList = " + pageList);

        sqlSession.close();
    }
}

References

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