This topic describes how to build an application using the MyBatis framework and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
- You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
- 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 the OceanBase Cloud 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 OceanBase Cloud database connection string
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Obtain a connection string.
Fill in the corresponding information in the URL below based on your created OceanBase Cloud database.
Note
This URL information is required in the
jdbc.propertiesfile.jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password&useServerPrepStmts=true&rewriteBatchedStatements=trueParameter description:
$host: The connection address for the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.$port: The connection port for the OceanBase Cloud database, default is 1521.$schema_name: The name of the schema to access.$user_name: The account used to access the database.$password: The password for the account.useServerPrepStmts=true&rewriteBatchedStatements=true: Additional connection properties.rewriteBatchedStatements: For insert queries, rewritebatchedStatementto execute it in a singleexecuteQuery. WhenrewriteBatchedStatementsis active, theuseServerPrepStmtsoption is set tofalse. Default value:false.useServerPrepStmts: PreparesPreparedStatementon the server side before execution. Enabling this parameter can reduce the number of times the same SQL statement is repeatedly compiled and parsed in the database when the application needs to execute it multiple times.
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 recognizes 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 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 you import 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 OceanBase Cloud connection string.
Example:
- The database driver name is:
com.oceanbase.jdbc.Driver - The OceanBase Cloud connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The schema name to access is
sys. - The tenant connection account is oracle001.
- The password is
******.
Sample code:
jdbc.driver=com.oceanbase.jdbc.Driver
jdbc.url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys?useServerPrepStmts=true&rewriteBatchedStatements=true
jdbc.username=oracle001
jdbc.password=******
Step 4: Run the java-oceanbase-mybatis project
Run the application
- Locate the
TestMybatis.javafile in the src > test > java directory of the project structure. - In the tool menu bar, select Run (U) > Run > TestMybatis, or directly click the green triangle in the upper-right corner to run it.
- View the project's log information and output results through IDEA's console.
Output
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'}
Sample project overview
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
File description:
pom.xml: The configuration file for the Maven project, containing information about the project's dependencies, plugins, and build..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: The interface for the application data access layer.IUserMapper.java: The interface for the user data access layer.pojo: The directory that stores JavaBeans or entity classes.App.java: The application entity class.User.java: The user entity class.resources: The directory that stores resource files, such as configuration files and SQL files.jdbc.properties: The configuration file for 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.
Introduction to pom.xml code
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.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> </project>- Use
Basic information configuration.
- Use
<groupId>to set the group ID tocom.oceanbase.example. - Use
<artifactId>to set the artifact ID tojava-oceanbase-mybatis. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<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 set the group ID toorg.apache.maven.plugins. - Use
<artifactId>to set the artifact ID tomaven-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.
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>- 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.Sample code:
<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.Sample code:
<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.Sample code:
<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.Sample code:
<dependencies> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.3.0</version> </dependency> </dependencies>
Introduction to jdbc.properties
jdbc.properties is a property 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 OceanBase Cloud database connection string.
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.
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=******
Introduction to 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.
Declares that this file is an XML file. This declaration indicates that the current document is a MyBatis configuration file, using XML version
1.0, character encodingUTF-8. The version number is 3.0, the language is English, and it has been validated using the DTD (Document Type Definition) file provided by the official MyBatis website.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 the language of the document type is English.
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">Specify the path and filename of the property file to load.
<properties resource="jdbc.properties"></properties>Configure global settings for MyBatis.
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 needs.
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-generated keys. Requires driver support. Default: false. --> <setting name="useGeneratedKeys" value="true"/> <!-- Configure automatic column-to-property mapping: NONE, PARTIAL, or FULL. --> <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"/> <!-- Map underscore-separated column names to camelCase properties. --> <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- Set the local cache scope: SESSION shares cached data within a session; STATEMENT limits it to one statement. 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>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.
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>Configure the MyBatis environment. The environment content of MyBatis 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.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>Configure the MyBatis mapper.
Common elements for a MyBatis mapper include:
- 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.
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>
Introduction to 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 only 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 declaration section of an XML file specifies the version and encoding of the XML file. The declaration section of a DTD file introduces the MyBatis DTD file. 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">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, including 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, matching the record with a value of#{id}in theidfield. - Create an SQL statement named
updateUserto update a record in thetest_usertable, setting the value of thenamefield to#{name}, matching the record with a value of#{id}in theidfield. - Create an SQL statement named
selectUsersto query all user records in thetest_usertable. - Create an SQL statement named
selectUserByPageto query user objects for a specified number of pages from thetest_usertable. Use a subquery and therownumfield for pagination, where#{pageNum}and#{pageSize}represent the current page number and the number of records per page.
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>- Define a namespace for the
Introduction to IAppMapper.java
The IAppMapper.java file is used to define SQL mapping relationships.
The code in the IAppMapper.java file mainly includes the following parts:
Define the
Mapperpackage.Declare the package name of the current file as
com.oceanbase.mapper. TheMapperpackage contains the following interfaces and classes:Appinterface: Represents a mapping to a database table, implementing read and write operations for data.org.apache.ibatis.annotations.*: Imports annotation classes from MyBatis.java.util.List: Imports theListinterface from thejava.utilpackage. Code:
package com.oceanbase.mapper; import com.oceanbase.pojo.App; import org.apache.ibatis.annotations.*; import java.util.List;Define the
IAppMapperinterface. TheIAppMapperinterface defines the MyBatisMapperinterface. TheMapperinterface defines SQL mapping relationships to implement insert, delete, update, and query operations on the database. Specifically, theIAppMapperinterface defines methods for inserting, deleting, updating, and querying data in thetest_apptable, including:@Insertannotation: Used to insert data into the database.@Updateannotation: Used to update data in the database.@Deleteannotation: Used to delete data from the database.@Selectannotation: Used to query data from the database.@Resultsannotation: Represents the mapping relationship of query results, used to map 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 equals#{id}.@Delete("delete from test_app where id =#{id}") Integer deleteApp(Long id);Update data To modify a record in the
test_apptable, you need to 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 in the 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();
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 of 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. Code:
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 paginating user data queries. TheJavaDoccomment and@Paramannotation are used to improve code readability and maintainability. 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 of App.java
The App.java file represents an App application object. It contains two attributes: id and name. The class also defines accessors for these attributes, a constructor, and 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 updates the name and does not return a value. - By overriding the
toStringmethod, converts the application object into a string representation for easy output and debugging.
- Defines a class
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 of User.java
The User.java file represents a user object. It contains two attributes: id and name. The class also defines accessors for these attributes, a constructor, and a toString method that converts the object into a string. The creation of this class is similar to that in the App.java code above.
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 + '\'' +
'}';
}
}
Introduction to the MyBatisTest.java file
The MyBatisTest.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 imported:
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 the MyBatis configuration file.SqlSessionclass: Used to execute SQL statements and manage transactions.SqlSessionFactoryclass: Used to create a SqlSession object.SqlSessionFactoryBuilderclass: Used to create a SqlSessionFactory object.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.
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;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 manages 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 deletes the table namedtest_user. - Use the
execute()method of theStatementobject to create a table namedtest_user. This table contains two fields: one isid, typenumber(20), serving as the primary key; the other isname, 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" from 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.
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(); }- 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 anSqlSessionFactoryinstance. This is used to manage the creation and destruction ofSqlSessioninstances. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. You can use this instance to 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 using thisConnectioninstance. - Use the
createStatement()method of theConnectioninstance to create aStatementinstance. Execute SQL statements sequentially through thisStatementinstance. - Use the
execute()method of theStatementinstance to execute an SQL statement that drops a table namedtest_user. - Use the
execute()method of theStatementinstance to create a table namedtest_user. This table has two columns: one isid, which is anumber(20)type column serving as the 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 of the loop, create a newUserobject and use theinsertUser()method of theMapperinterface to perform the insertion. The result of each 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, and pass in the identifier of the SQL statement and a parameter object to complete the update. The specific SQL statement and parameter mapping can be found in the XML configuration file within the "com.oceanbase.mapper.IUserMapper" interface. Store the result of the update operation in theupdateResultvariable. - Use the
openSession()method of theSqlSessionFactoryto create anSqlSessioninstance. Use this instance to perform a query operation and store the query results in theuserListvariable. Finally, use theforEachmethod to traverse theuserListand output it to the console. - Close the
Statementobject in the database connection by callingstatement.close(). Then, commit the transaction by callingsqlSession.commit()to persist all modifications to the database. Finally, close theSqlSessionobject by callingsqlSession.close()to release related resources and disconnect from the database.
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(); }- Use the
Define the
testAppMappermethod.The
testAppMappermethod is used to test the functionality of the AppMapper.- Use the
build()method of theSqlSessionFactoryBuilderclass to create anSqlSessionFactoryinstance. This is used to manage the creation and destruction ofSqlSessioninstances. - Use the
openSession()method of theSqlSessionFactoryto create aSqlSessioninstance. You can use this instance to 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 using thisConnectioninstance. - Use the
createStatement()method of theConnectioninstance to create aStatementinstance. Execute SQL statements sequentially through thisStatementinstance. - Use the
execute()method of theStatementinstance to execute an SQL statement that drops a table namedtest_app. - Use the
execute()method of theStatementinstance to create a table namedtest_app. This table has two columns: one isid, which is anumber(20)type column serving as the 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 of the loop, create a new App object and use theinsertApp()method of theMapperinterface to perform the insertion. The result of each 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, and pass in the identifier of the SQL statement and a parameter object to complete the update. - Create an
Appobject namedupdateAppwith anidof2Land anameof "update". - Call the
updateAppmethod of themapperand pass in theupdateAppobject to perform the update. - Call the
commitmethod of thesqlSessionto commit the database transaction. - Call the
selectAppsmethod of themapperto query all App objects. Use theforEachmethod to traverse theuserListand output it to the console. - Call the
startPagemethod of thePageHelperand 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 of thePageInfoto obtain the paginated list of App objects. Use theSystem.out.printlnmethod to print the paginated list ofAppobjects. - Close the
SqlSessionobject by callingsqlSession.close()to release related resources.
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(); }- Use the
Complete sample 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 JDBC Driver