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 Java Development Kit (JDK) 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the OceanBase Database connection string.
2. Import the java-oceanbase-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 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
   

   where:

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

   • port specifies 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 specifies the name of the schema to be accessed.

   • user_name specifies the tenant account. For connection through ODP, two account formats are supported: username@tenant name#cluster name and cluster name:tenant name:username. For direct connection, the username@tenant name format is supported.

   • password specifies the password of the account.

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

     • rewriteBatchedStatements specifies whether to rewrite batchedStatement in an INSERT query for execution in a single executeQuery. If you set this parameter 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 to IntelliJ IDEA

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

   

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

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

   Note

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

4. View the project.

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

Modify the database connection string 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 access port is 2881.
• The name of the schema to be accessed is sys.
• The additional connection properties in the URL are useServerPrepStmts=true&rewriteBatchedStatements=true.
• The tenant account is sys@xyoracle. xyoracle is an Oracle user tenant created in OceanBase Database, and sys is the username of a user in the xyoracle tenant.
• The password of the account is ******.

Here is the sample code:

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

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 Console window of IntelliJ IDEA.

Result

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

Click here to download the project code, which is a package named java-oceanbase-mybatis.

Decompress the package to obtain 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 the XML standard 1.0 and character encoding format UTF-8.

   Here is the sample code:

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

2. Configure namespaces and the POM version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM version used by the POM file, which is set to 4.0.0.

   Here is the sample code:

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

3. Configure basic project information.

   1. <groupId>: the ID of the project group, which is set to com.oceanbase.example.
   2. <artifactId>: the name of the project, which is set to java-oceanbase-mybatis.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   Here is the sample code:

   <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. <plugins>: the plug-ins in the project.
   2. <plugin> a plug-in in the project.
   3. <groupId>: the ID of the project group, which is set to org.apache.maven.plugins.
   4. <artifactId>: the name of the project, which is set to maven-compiler-plugin.
   5. <configuration>: the parameters of the plug-in.
   6. <source>: the source code version for the compiler, which is set to 8.
   7. <target>: the target code version for the compiler, which is set to 8.

   Here is the sample code:

    <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. Define a dependency named oceanbase-client that belongs to the com.oceanbase group and whose version is 2.4.2.

      Here is the sample code:

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

   2. Define a dependency named junit that belongs to the junit group and whose version is 4.10.

      Here is the sample code:

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

   3. Define a dependency named mybatis that belongs to the org.mybatis group and whose version is 3.5.9.

      Here is the sample code:

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

   4. Define a dependency named pagehelper that belongs to the com.github.pagehelper group and whose version is 5.3.0.

      Here is the sample code:

          <dependencies>
          <dependency>
              <groupId>com.github.pagehelper</groupId>
              <artifactId>pagehelper</artifactId>
              <version>5.3.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 for connecting to the database.
• jdbc.password: the password for connecting to the database.

Here is the sample code:

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 format 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 file is EN.

   The declaration contains the following parts:

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

   Here is the sample code:

   <?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: specifies whether to enable caching, which is set to true.
   • lazyLoadingEnabled: specifies whether to enable lazy loading, which is set to true.
   • aggressiveLazyLoading: specifies whether to enable aggressive lazy loading, which is set to true.
   • multipleResultSetsEnabled: specifies whether to enable 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, as with 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.

   Here is the sample code:

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

   Here is the sample code:

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

   Here is the sample code:

   <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 a mapper element. To use a Java interface, you must specify the fully qualified name of the Java class in a mapper element.

   Here is the sample code:

   <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 format of the XML file. In the DTD declaration part, import the DTD file of MyBatis. Here is the sample code:

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

   Here is the sample code:

   <?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 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 Mapper package.

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

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

   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 create, read, update, and delete (CRUD) operations on the database. Specifically, the IAppMapper interface defines the CRUD methods to manipulate 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. CRUD operations on the database 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 specified record 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. 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();
      

   Here is the sample code:

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

   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 named 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. Here is the sample code:

    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 parameterless 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 the setName method.
   4. Override the toString method to convert App objects into strings for easy output and debugging.

Here is the sample code:

   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 defines the User class from which User 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. The creation of this class is similar to that of the class in App.java.

Here is the sample code:

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.

   Here is the sample code:

   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 an 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 an 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 IUserMapper instance. 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.

   Here is the sample code:

       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 an 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 an 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 sqlSession 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 perform 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 an 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.

   Here is the sample code:

       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 an 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 an 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 delete() 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 userList 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.

   Here is the sample code:

       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

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