This topic describes how to build an application using the MyBatis framework and OceanBase AI Database to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have installed OceanBase AI Database.
- You have installed JDK 1.8 and Maven.
- You have installed IntelliJ IDEA.
Note
The tool used to run the sample code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Obtain the connection string for OceanBase AI Database.
- Import the
java-oceanbase-mybatisproject into IntelliJ IDEA. - Modify the database connection information in the
java-oceanbase-mybatisproject. - Run the
java-oceanbase-mybatisproject.
Step 1: Obtain the connection string for OceanBase AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the corresponding database connection string.
obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -AFill in the corresponding information in the URL below based on the deployed OceanBase AI Database.
Note
This URL information is required in the
jdbc.propertiesfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$passwordParameter description:
host: The IP address for connecting to OceanBase AI Database. For an ODP connection, this is an ODP address; for a direct connection, this is the IP address of an OBServer node.port: The port for connecting to OceanBase AI Database. For an ODP connection, the default port is2883, which can be customized during ODP deployment. For a direct connection, the default port is2881, which can be customized during OceanBase AI Database deployment.schema_name: The name of the schema to access.user_name: Specified using the-uparameter, in the format of user@tenant#cluster_name or username@SERVICE:service_name. When using the user@tenant#cluster_name format, the default tenant issysand the administrator user isroot. The cluster name can be omitted when connecting directly to the database, but must be filled in for an ODP connection.password: The account password.
For more information about URL parameters, see Database URL.
Step 2: Import the java-oceanbase-mybatis project into IntelliJ IDEA
Open IntelliJ IDEA and select File > Open....

In the Open File or Project window that appears, select the corresponding project file and click OK to complete the import.
IntelliJ IDEA automatically identifies the various files in the project. In the Project tool window, you can view the project's directory structure, file list, module list, dependencies, and other information. The Project tool window is usually located on the far left side of the IntelliJ IDEA interface and is open by default. If the Project tool window is closed, you can reopen it by clicking View > Tool Windows > Project in the menu bar or using the shortcut key Alt + 1.
Note
When importing a project in IntelliJ IDEA, it 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.
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 connection string for OceanBase AI Database.
Here is a connection information 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
test. - The URL extra connection attributes are
useServerPrepStmts=true&rewriteBatchedStatements=true. - The tenant connection account is
root@mysql001.mysql001is a user tenant created in OceanBase AI Database, androotis the username of themysql001tenant. - 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=root@mysql001
jdbc.password=******
Step 4: Run the java-oceanbase-mybatis project
Running path
- In the project structure, locate the
TestMybatis.javafile under src > test > java. - In the tool menu bar, select Run (U) > Run > TestMybatis, or directly click the green triangle in the upper-right corner to run it.
- Use the console in IDEA to view the project's log information and output results.
Running result
The output result of running the
testUserMappermethod 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'}]The output result of running the
testSqlSessionmethod 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'}The output result of running the
testAppMappermethod 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'}]The complete output result 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'}
FAQs
1. Connection timeout
If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:
jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000
2. Character set issues
To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:
jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true
3. SSL connection
To enable an SSL connection to OceanBase AI Database, add the following parameter to the JDBC URL:
jdbc:mysql://host:port/database?useSSL=true&requireSSL=true
4. Special characters in account or password
If the username or password contains special characters (such as #), you need to URL-encode them:
String encodedPassword = URLEncoder.encode(password, "UTF-8");
Notice
When using MySQL Connector/J 8.x, ensure that the account password does not contain a #. Otherwise, you may encounter a connection error.
Project code
Click java-oceanbase-mybatis to download the project code, which is a compressed file named java-oceanbase-mybatis.
After decompressing it, you will get 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
The files and directories are described as follows:
pom.xml: The configuration file for the Maven project, containing information such as project dependencies, plugins, and builds..idea: A directory used by the IDE (Integrated Development Environment) to store project-related configuration information.src: Typically denotes the directory that holds the project's source code.main: The directory that stores the main source code and resource files.java: The directory that stores Java source code.com: The root directory for Java packages.oceanbase: The root directory for the project.mapper: The directory that stores MyBatis Mapper interfaces and XML files.IAppMapper.java: Contains the application data access layer interface.IUserMapper.java: Contains 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 that stores 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 for MyBatis.IUserMapper.xml: The XML configuration file for the user data access layer.test: The directory that stores test code and resource files.TestMybatis.java: The Java class for testing MyBatis.target: The directory that stores compiled Class files, JAR packages, and other files.
Code in pom.xml
Note
If you just want to verify the example, use the default code without modification. You can also modify the pom.xml file according to your needs as described below.
The content of the pom.xml configuration file is as follows:
The file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure namespaces and the POM model version.
xmlns: the default XML namespace for the POM, which is set tohttp://maven.apache.org/POM/4.0.0.xmlns:xsi: the XML namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.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).<modelVersion>: the POM model version used by the POM file, which is set to4.0.0.
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>Configure basic information.
- Use
<groupId>to specify the project identifier ascom.oceanbase.example. - Use
<artifactId>to specify the project dependency asjava-oceanbase-mybatis. - Use
<version>to specify the project version as1.0-SNAPSHOT.
The sample code is as follows:
<groupId>com.oceanbase.example</groupId> <artifactId>java-oceanbase-mybatis</artifactId> <version>1.0-SNAPSHOT</version>- Use
Define the project build process using
<build>.- Use
<plugins>to specify the plugins configured in the project. - Use
<plugin>to specify a plugin to configure in the project. - Use
<groupId>to specify the project identifier asorg.apache.maven.plugins. - Use
<artifactId>to specify the project dependency asmaven-compiler-plugin. - Use
<configuration>to specify the parameters for the configured plugin. - Use
<source>to specify the compiler source code version as 8. - Use
<target>to specify the compiler target code version as 8.
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>- Use
Define the components on which the project depends using
<dependencies>.Specify the dependency's organization as
com.oceanbase, name asoceanbase-client, and version as2.4.2.The sample code is as follows:
<dependencies> <dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.2</version> </dependency> </dependencies>Specify the dependency's test framework as
junit, name asjunit, and version as4.10.The sample code is as follows:
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> </dependency> </dependencies>Specify the dependency's framework as
org.mybatis, name asmybatis, and version as3.5.9.The sample code is as follows:
<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.9</version> </dependency> </dependencies>Specify the dependency's plugin as
com.github.pagehelper, name aspagehelper, and version as5.3.0.The sample code is as follows:
<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 properties file used to store configuration information related to database connections, including the database URL, username, password, and other details. The content of the file is as follows:
Note
The values for each field in the code are directly obtained from Step 1: Obtain the connection string for OceanBase AI Database.
jdbc.driver: The class name of the database driver, used to load the database driver.jdbc.url: The URL address of the database, used to specify the database to connect to.jdbc.username: The username for the database, used to authenticate the database connection.jdbc.password: The password for the database, used to authenticate the database connection.
The sample code is as follows:
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://host:port/TEST?useServerPrepStmts=true&rewriteBatchedStatements=true
jdbc.username=user_name
jdbc.password=******
Code in mybatis-config.xml
The mybatis-config.xml file is used to configure global properties and plugins for the MyBatis framework.
The code in the mybatis-config.xml file mainly includes the following parts:
File declaration.
This declaration indicates that the file is an XML file and specifies that the current document is a MyBatis configuration file, using XML version
1.0and character encodingUTF-8. The version number is 3.0, the language is English, and the DTD (Document Type Definition) file provided by the official MyBatis website is used for validation.The declaration contains the following parts:
version: Specifies the version of the XML file.encoding: Specifies the encoding method of the XML file.DOCTYPE: Declares the type as a document type declaration.configuration: Specifies the document type as a MyBatis configuration file.PUBLIC: Specifies the document type as a public document type.mybatis.org: Indicates the official MyBatis website.DTD Config 3.0: Indicates the version number of the MyBatis configuration file.EN: Indicates that the language of the document type 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">Specify the path and filename of the property file to load.
<properties resource="jdbc.properties"></properties>Configure global MyBatis settings.
The content of global MyBatis settings includes the following parts:
setting: Used to configure individual settings, such as caching and logging.cacheEnabled: Used to enable or disable the caching feature, set totrue.lazyLoadingEnabled: Used to enable or disable lazy loading, set totrue.aggressiveLazyLoading: Used to enable or disable aggressive lazy loading, set totrue.multipleResultSetsEnabled: Used to configure whether to support multiple result sets, set totrue.useColumnLabel: Used to configure whether to use column labels as column names in result sets, set totrue.useGeneratedKeys: Used to configure whether to use automatically generated primary keys, set totrue.autoMappingBehavior: Used to configure the handling behavior for auto-mapping, set toPARTIAL.defaultExecutorType: Used to configure the default executor type, set toSIMPLE.mapUnderscoreToCamelCase: Used to configure whether to convert underscores in database column names to camelCase in Java object property names, set totrue.localCacheScope: Used to configure the scope of the local cache, set toSESSION.jdbcTypeForNull: Used to configure the JDBC type to use when handling null values, set toNULL.
Note
The child elements within the settings element are optional and can be added or deleted based on actual requirements.
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>Configure the MyBatis plugin.
The content of a MyBatis plugin includes the following parts:
plugin: Configures a single plugin.property: Specifies the properties of the plugin.interceptor: Specifies the implementation class of the plugin.helperDialect: Specifies the selected database.offsetAsPageNum: Indicates whether to use the offset parameter as the pageNum parameter.rowBoundsWithCount: Indicates whether to perform a count query.pageSizeZero: Indicates whether to support queries with a pageSize of 0.reasonable: Indicates whether to enable rationalization queries.params: Indicates the mapping between parameter names and parameter values used to pass pagination parameters.supportMethodsArguments: Indicates whether to support using method parameters to pass pagination parameters.returnPageInfo: Indicates the return value type.
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="mysql"/> <!-- 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>Configure the MyBatis environment. The content of a MyBatis environment includes the following parts:
environment: Configures a single environment.transactionManager: Specifies the implementation class of the transaction manager.dataSource: Specifies the implementation class of the data source.property: Specifies the class name of the database driver.
Note
In other MyBatis configuration elements, you can use the
${}placeholder to reference data sources, transaction managers, executors, and other components configured in the environment.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>Configure the MyBatis mapper.
Common elements for a MyBatis mapper include the following:
- resource: Specifies the path and filename of the mapper's XML configuration file.
- class: Specifies the Java class name of the mapper.
Note
If using an XML configuration file, specify the path and filename of the XML file in the mapper element. If using a Java interface, 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. This file defines a namespace named IUserMapper to store SQL statements related to user objects.
Note
If you just want to verify the example, use the default code without modification. If modifications are needed, make them according to your actual situation.
The code in the IUserMapper.xml file mainly includes the following parts:
File declaration statement. The XML declaration specifies the version and encoding of the XML file. The DTD declaration introduces the MyBatis DTD file. 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">Configure the mapping relationship with
IUserMapper.java.- Define a namespace for the
Mapperinterface to correspond to theMapperinterface in the Java code. - Create an SQL statement named
insertUserto insert a record into thetest_usertable. The record includes two fields:idandname, with values#{id}and#{name}respectively. TheparameterTypeattribute specifies that the passed parameter type iscom.oceanbase.pojo.User. - Create an SQL statement named
deleteUserto delete a record from thetest_usertable. The record is matched based on the value of theidfield, which is#{id}. - Create an SQL statement named
updateUserto update a record in thetest_usertable. The value of thenamefield is updated to#{name}. The record is matched based on the value of theidfield, which is#{id}. - Create an SQL statement named
selectUsersto query all user records in thetest_usertable.- Create an SQL statement named
selectUserByPageto query user objects from thetest_usertable for a specified page. Use subqueries and therownumfield for pagination, where#{pageNum}and#{pageSize}represent the current page number and the number of records per page, respectively.
- Create an SQL statement named
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 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>- Define a namespace for the
Code in IAppMapper.java
The IAppMapper.java file is used to define SQL mapping relationships.
The code in the IAppMapper.java file mainly consists of the following parts:
Define the
Mapperpackage.Declare the package name of the current file as
com.oceanbase.mapper. TheMapperpackage includes the following interfaces and classes:Appinterface: Represents a mapping to a database table, implementing read and write operations.org.apache.ibatis.annotations.*: Imports annotation classes from MyBatis.java.util.List: Imports theListclass from thejava.utilpackage. The sample code is as follows:
package com.oceanbase.mapper; import com.oceanbase.pojo.App; import org.apache.ibatis.annotations.*; import java.util.List;Define the
IAppMapperinterface. TheIAppMapperinterface defines a MyBatisMapperinterface. AMapperinterface defines SQL mapping relationships to implement insert, delete, update, and query operations on a database. Specifically, theIAppMapperinterface defines methods for inserting, deleting, updating, and querying data in thetest_apptable, including:@Insertmethod: Inserts data into the database.@Updatemethod: Updates data in the database.@Deletemethod: Deletes data from the database.@Selectmethod: Queries data from the database.@Resultsmethod: Defines the mapping relationship of query results, mapping fields in the query results to attributes of a Java object. The insert, delete, update, and query operations on the database are as follows:
Insert data Map an App object to a record in the
test_apptable. The attribute values in the App object are represented by placeholders in the format#{attribute name}. The return value of typeIntegerrepresents the automatically generated ID returned by the SQL statement after inserting data, that is, the value corresponding to the ID field in thetest_apptable.@Insert("insert into test_app(id,name) values(#{id},#{name})") Integer insertApp(App app);Delete data Delete data from the
test_apptable where the id is equal to#{id}.@Delete("delete from test_app where id =#{id}") Integer deleteApp(Long id);Update data To modify a record in the
test_apptable, perform an Update operation.@Update("update test_app set name= #{name} where id = #{id}") Integer updateApp(App user);Query data and map data Query all data in the
test_apptable. At the same time, use the@Resultsand@Resultannotations to map the query results to theidandnameattributes of an App object. Finally, 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.
The code in the IUserMapper.java file mainly includes the following parts:
References to other classes and interfaces.
Declares the interfaces and classes contained in this file:
Userclass: Represents a user object.org.apache.ibatis.annotations.Paramclass: A parameter annotation in the MyBatis framework.Listinterface: Represents a 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;Definition of the
IUserMapperinterface.Defines an interface named
IUserMapper, which includes methods for inserting, deleting, updating, and querying user data, as well as a method for paginated querying of user data. JavaDoc comments and@Paramannotations are used 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 represents an App application object. It contains two attributes: id and name. The class also defines accessors and constructors for these attributes, as well as a toString method that converts the object into a string.
The code in the App.java file mainly includes the following parts:
- Definition of the
pojopackage. Declares the package name of this file ascom.oceanbase.pojo. - Definition of the
Appclass.- Defines a class
App, which contains two private attributesidandname, and a no-argument constructorpublic App(){}. - Defines a parameterized constructor
public App(Long id, String name), used to create an application object with specifiedidandname. - Defines the
getIdmethod,setIdmethod,getNamemethod, andsetNamemethod to retrieve and set the application name. ThesetNamemethod returns the set application name. - By overriding the
toStringmethod, converts the application object into a string representation for easy output and debugging.
- Defines a class
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 a user object. It contains two attributes: id and name. The class also defines accessors and constructors for these attributes, as well as a toString method that converts the object into a string. The creation of this class is similar to the creation in the preceding App.java code.
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 TestMyBatis.java
The TestMyBatis.java file demonstrates how to use MyBatis for data operations. It tests the basic functionality of the MyBatis framework, the execution of SQL statements, as well as the invocation of Mapper interfaces and the parameters and return values of SQL statements.
References to other classes and interfaces.
The following classes and interfaces are used:
IAppMapperinterface: Defines SQL statements related to App objects.IUserMapperinterface: Defines SQL statements related to User objects.Appclass: An App object used to test the execution of SQL statements.Userclass: A User object used to test the execution of SQL statements.PageHelperplugin: Used to implement pagination query functionality.PageInfoplugin: Used to encapsulate pagination query results.Resourcesclass: Used to load MyBatis configuration files.SqlSessionclass: Used to execute SQL statements and manage transactions.SqlSessionFactoryclass: Used to create SqlSession objects.SqlSessionFactoryBuilderclass: Used to create SqlSessionFactory objects.org.junit.Test: An annotation from the JUnit testing framework, used to mark test methods.IOExceptionclass: Represents errors in input/output operations.SQLExceptionclass: Represents errors in SQL operations.Statementinterface: Used to execute SQL statements and return results.java.util.Listinterface: Represents an ordered collection where elements can be repeated.
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;Define the
testUserMappermethod.The
testUserMappermethod tests the execution of SQL statements defined in theMapperinterface forUserobjects.- Use the
build()method of theSqlSessionFactoryBuilderclass to create aSqlSessionFactoryinstance. This is used to manage the creation and destruction ofSqlSessioninstances. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. This can execute various SQL statements, including queries, inserts, updates, and deletes. - Use the
getConnection()method of theSqlSessioninstance to obtain aConnectioninstance. This can perform various database operations. - Use the
createStatement()method of theConnectioninstance to create aStatementinstance. Execute SQL statements in sequence. - Use the
execute()method of theStatementobject to execute an SQL statement that drops a table namedtest_user. - Use the
execute()method of theStatementobject to create a table namedtest_user. This table contains two fields: one isid, of typenumber(20), serving as the primary key; the other isname, of typevarchar2(100). - Use the
getMapper()method of theSqlSessioninstance to obtain an instance of theIUserMapperinterface. Define various database operation methods. - Use a
forloop to insert 10 rows of data into thetest_usertable. In each iteration, create a new User object and use theinsertUser()method of theMapperinterface to perform the insertion. The result of the insertion operation is stored in theinsertResultvariable. - Perform delete, update, and query operations on the data in the
test_usertable. Finally, use theforEach()method to print the information for each user in the user list. - Create a user named
insert. Use theselectUserByPage()method to query all user data with the name "insert" in thetest_usertable and return the user list for the specified page. Query the second page, displaying 3 pieces of data per page. Print this user data to the console. Also, close theStatementandSqlSessioninstances, commit the transaction, and 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(); }- Use the
Define the
testSqlSessionmethod.The
testSqlSessionmethod is used to test the basic functionality of aSqlSessionobject, including executing SQL statements, committing transactions, and closing theSqlSessionobject.- Use the
build()method of theSqlSessionFactoryBuilderclass to create aSqlSessionFactoryinstance. This is used to manage the creation and destruction ofSqlSessioninstances. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. You can execute various SQL statements, including queries, inserts, updates, and deletes. - Use the
getConnection()method of theSqlSessioninstance to obtain aConnectioninstance. You can then perform various database operations. - Use the
createStatement()method of theConnectioninstance to create aStatementinstance. Execute SQL statements in sequence. - Use the
execute()method of theStatementobject to execute an SQL statement that drops a table namedtest_user. - Use the
execute()method of theStatementobject to create a table namedtest_user. This table contains two columns: one isid, which is anumber(20)type primary key; the other isname, which is avarchar2(100)type column. - Use a
forloop to insert 10 rows of data into thetest_usertable. In each iteration, create a newUserobject and use theinsertUser()method of theMapperinterface to perform the insertion. The result of the insertion operation is stored in theinsertResultvariable. - Use the
delete()method of thesqlSessionto perform a delete operation. Set the deletion condition by passing in the parameter1L. Store the result of the delete operation in thedeleteResultvariable. - Use the
sqlSessionobject to perform a database update operation. Create aUserobject, call theupdatemethod, pass in the SQL statement identifier and parameter objects, to complete the update operation. The specific SQL statement and parameter mapping can be found in the XML configuration file within the "com.oceanbase.mapper.IUserMapper" interface. The result of the update operation is stored in theupdateResultvariable. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. Execute a query operation through this instance and store the query result in theuserListvariable. Finally, use theforEachmethod to traverseuserListand output it to the console. - Close the
Statementobject in the database connection usingstatement.close(). Then, commit the transaction usingsqlSession.commit()to persist all modifications to the database. Finally, close theSqlSessionobject usingsqlSession.close()to release related resources and disconnect from 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(); }- Use the
Define the
testAppMappermethod.The
testAppMappermethod is used to test the functionality of AppMapper.- Use the
build()method of theSqlSessionFactoryBuilderclass to create aSqlSessionFactoryinstance. This is used to manage the creation and destruction ofSqlSessioninstances. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. You can execute various SQL statements, including queries, inserts, updates, and deletes. - Use the
getConnection()method of theSqlSessioninstance to obtain aConnectioninstance. You can then perform various database operations. - Use the
createStatement()method of theConnectioninstance to create aStatementinstance. Execute SQL statements in sequence. - Use the
execute()method of theStatementobject to execute an SQL statement that drops a table namedtest_app. - Use the
execute()method of theStatementobject to create a table namedtest_app. This table contains two columns: one isid, which is anumber(20)type primary key; the other isname, which is avarchar2(100)type column. - Use the
getMapper()method of theSqlSessioninstance to obtain an instance of theIAppMapperinterface. Define various database operation methods. - Use a
forloop to insert 10 rows of data into thetest_apptable. In each iteration, create a new App object and use theinsertApp()method of theMapperinterface to perform the insertion. The result of the insertion operation is stored in theinsertResultvariable. - Use the
delete()method of themapperto perform a delete operation. Set the deletion condition by passing in the parameter1L. Store the result of the delete operation in thedeleteResultvariable. - Use the
mapperobject to perform a database update operation. Create an App object, call theupdatemethod, pass in the SQL statement identifier and parameter objects, to complete the update operation. - Create an
AppobjectupdateAppwithidset to2Landnameset to "update". - Call the
updateAppmethod of themapperand pass in theupdateAppobject to perform the update operation. - Call the
commitmethod of thesqlSessionto commit the database transaction. - Call the
selectAppsmethod of themapperto query all App objects. Use theforEachmethod to traverseuserListand output it to the console. - Call the
startPagemethod ofPageHelperand pass in the page number and the number of items per page to set the pagination parameters. Query allAppobjects and return aListobject containing allAppobjects. - Call the
getListmethod ofPageInfoto obtain the paginated list of App objects. Use theSystem.out.printlnmethod to print the paginated list ofAppobjects. - Close the
SqlSessionobject usingsqlSession.close()to release related 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(); }- Use the
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.mysql.cj.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 JDBC driver
Download the java-oceanbase-mybatis sample project