Using MyBatis to Connect to a Database

MyBatis is a first class persistence framework with support for custom SQL, stored procedures, and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or annotations for configuration and map primitive. It can map interfaces and Java Plain Old Java Objects (POJOs) to database records.

This section describes how to use MyBatis to connect to a GaussDB database.

Configuring the POM Dependency

<dependency>
    <groupId>com.huaweicloud.gaussdb</groupId>
    <artifactId>opengaussjdbc</artifactId>
    <version>503.2.T35</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.6</version>
</dependency>

The Maven environment must have been configured before you configure the POM dependency.

Configuring the Required File

Configuring the mybatis-config.xml file.

<?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">
<!-- Root element of the configuration file -->
<configuration>
    <!--Configure the global attributes.-->
    <settings>
        <!--Use getGeneratedKeys of JDBC to obtain the auto-increment primary key value of the database.-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--Replace the column alias with the column label. The default value is true.-->
        <setting name="useColumnLabel" value="true" />
        <!--Enable camel-case naming conversion: Table{create_time} -> Entity{createTime}-->
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="logImpl" value="STDOUT_LOGGING" />
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.huawei.opengauss.jdbc.Driver"/>
                <property name="url" value="jdbc:opengauss://***.***.***.*** (Replace ***.***.***.*** with the database IP address):20000 (Replace 20000 with the database port)/test? (Replace test with the corresponding database name.)connectionTimeout=10"/>
                <property name="username" value="*** (Replace *** with the correct username.)"/>
                <property name="password" value="******* (Replace ******* with the correct password.)"/>
            </dataSource>
        </environment>
    </environments>
    <!--Register mapper (address of mapper.xml).-->
    <mappers>
        <mapper resource="mapper/StudentDaoMapper.xml"></mapper>
    </mappers>
</configuration>

Examples

Test the entity class StudentEntity.java (in com.huawei.entity).

public class StudentEntity {
    Integer id;
    String name;
}

Configure the StudentDaoMapper.xml file corresponding to the entity class (in resources.mapper).

<?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="StudentMapper">
    <!-- Query by primary key -->
    <select id="getList" resultType="com.huawei.entity.StudentEntity" >
        select * from student;
    </select>
</mapper>

Test table query.

@Test
public void mainTest() throws IOException {
    // 1. Read the core configuration file of MyBatis (mybatis-config.xml).
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    // 2. Obtain a SqlSessionFactory factory object based on the configuration information.
    SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);
    // 3. Obtain a SqlSession object through the factory.
    SqlSession session = fac.openSession();
    // 4. Find the SQL statement to be executed based on the namespace and ID and execute the SQL statement.
    List<StudentEntity> list = session.selectList("StudentMapper.getList");
    // 5. Output the result.
    list.forEach(i -> {
        System.out.println(i.toString());
    });
}

Query result logs.
Figure 1 Result log

Currently, PaginationInnerInterceptor in the MybatisPlus plug-in does not adapt to the GaussDB driver. To resolve this problem, set DbType to POSTGRE_SQL when creating the PaginationInnerInterceptor object. Example: PaginationInnerInterceptor innerInterceptor = new PaginationInnerInterceptor(DbType.POSTGRE_SQL)

Parent topic: Primary/Standby Instances

Previous topic: Using Hibernate to Connect to a Database

Next topic: Using JayDeBeApi to Connect to a Database