This topic describes how to access Tablestore by using MyBatis to use the Java Database Connectivity (JDBC) driver for Tablestore.

Background information

MyBatis is a persistence framework for Java that supports custom SQL statements, stored procedures, and advanced mappings. MyBatis eliminates the need to use JDBC code, manually configure parameters, and retrieve result sets. For more information, see MyBatis documentation.

Usage notes

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen),China (Hong Kong), Germany (Frankfurt), US (Virginia), India (Mumbai),and Singapore (Singapore) regions.

Prerequisites

  • If you want to use a RAM user to perform operations, a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in the custom policy to grant all SQL operation permissions to the RAM user. For more information, see Grant permissions to a RAM user.
  • An AccessKey pair that consists of an AccessKey ID and an AccessKey secret is obtained. For more information, see Obtain an AccessKey pair.
  • A data table is created, and a mapping table is created for the data table. For more information, see Create a data table and Create mapping tables for tables.

Step 1: Install the JDBC driver

You can install the JDBC driver by using one of the following methods:

  • Download the JDBC driver for Tablestore and import the JDBC driver to the project. For more information about the download path, see JDBC driver for Tablestore.
  • Add dependencies to a Maven project.
    To use the JDBC driver for Tablestore in Maven, you need only to add the corresponding dependencies to the pom.xml file. In this example, JDBC driver 5.13.5 is used. Add the following content to <dependencies>:
    <dependency>
      <groupId>com.aliyun.openservices</groupId>
      <artifactId>tablestore-jdbc</artifactId>
      <version>5.13.5</version>
    </dependency>                

Step 2: Install MyBatis

You can install MyBatis by using one of the following methods:
  • Download the MyBatis installation package mybatis-x.x.x.jar and import the package to the project. For more information about the download path, see MyBatis installation package.

    In mybatis-x.x.x.jar, x.x.x indicates the version number of MyBatis. Select a MyBatis installation package based on your business requirements.

  • Add dependencies to a Maven project.

    To use MyBatis in Maven, you need only to add the corresponding dependencies to the pom.xml file. The following sample code shows how to add content to <dependencies>. In this example, the 3.6.3.Final version is used.

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

Step 3: Map SQL fields

  1. Create a Java Bean that contains member variables whose names are the same as the names of fields in the data table. In this example, trip_id is the only primary key column in the data table.
    Important When you create a Java Bean, make sure that the names of the member variables in the Java Bean are the same as the names of the fields in the data table.
    package mybatis;
    
    public class Trip {
    
        private long trip_id;
        private long duration;
        private String start_date;
        private String end_date;
        private long start_station_number;
        private long end_station_number;
        private String start_station;
        private String end_station;
        private String bike_number;
        private String member_type;
        // ...
    }
  2. Create a mapping configuration file in which the query conditions are defined. The following sample code shows how to create a mapping configuration file named TripMapper.xml in the mybatis directory.

    For more information about the supported SQL features, see SQL features.

    <?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="mybatis.TripMapper">
        <select id="selectTrip" resultType="mybatis.Trip">
            select * from trips where trip_id = #{id}
        </select>
    </mapper>

Step 4: Build the SqlSessionFactory

The SqlSessionFactory is used to create a MyBatis session. You can use a MyBatis session to connect a client to Tablestore.

  1. Add the following content to the MyBatis configuration file named mybatis-config.xml. Modify the configuration items in the configuration file based on your business requirements.
    <?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>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <!-- Specify the type of the data source. To exit the process, you must shut down the JDBC driver for Tablestore. Select a type of the data source that matches your needs. -->
                <!-- If you want to keep the program running after a data query, you can set the data source type to POOLED to maintain a connection pool. If you want the program to exit after a data query, you can set the data source type only to UNPOOLED. -->    
                <dataSource type="UNPOOLED">
                    <property name="driver" value="com.alicloud.openservices.tablestore.jdbc.OTSDriver"/>
                    <property name="url" value="jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance"/>
                    <property name="username" value="***********************"/>
                    <property name="password" value="********************************"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <!-- Specify the path of the mapping configuration file. -->
            <mapper resource="mybatis/TripMapper.xml"/>
        </mappers>
    </configuration>
    The following table describes the items that you must configure in the MyBatis configuration file.
    Configuration item Type Required Example Description
    driver class Yes com.alicloud.openservices.tablestore.jdbc.OTSDriver The name of the class for the JDBC driver for Tablestore. Set this configuration item to com.alicloud.openservices.tablestore.jdbc.OTSDriver.
    url string Yes jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance The endpoint of the instance. The value must be in the following format: jdbc:ots:endpoint/instanceName. endpoint indicates the endpoint of the instance. For more information, see Endpoint. instanceName indicates the name of the instance. Modify the value of instanceName based on your business requirements.

    When you specify a value for this configuration item, the jdbc:ots: prefix must be included in the value.

    username string Yes ************************ The AccessKey ID of your Alibaba Cloud account or a RAM user.
    password string Yes ******************************** The AccessKey secret of your Alibaba Cloud account or a RAM user.
  2. Load the MyBatis configuration file to build the SqlSessionFactory.
    String resource = "mybatis/mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

Step 5: Create a SqlSession to query data

SqlSession session = sqlSessionFactory.openSession(true);

Trip trip = (Trip) session.selectOne("mybatis.TripMapper.selectTrip", 99L);
System.out.println("trip id: " + trip.getTrip_id());
System.out.println("start date: " + trip.getStart_date());
System.out.println("end date: " + trip.getEnd_date());
System.out.println("duration: " + trip.getDuration());
session.close();

Complete sample code

The following sample code shows how to query the row in which the value of the primary key is 99 and return the specified columns of the row:

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 mybatis.Trip;

import java.io.IOException;
import java.io.InputStream;

public class MyBatisDemo {
    public static void main(String[] args) throws IOException {
        // Specify the full path of the MyBatis configuration file.
        String resource = "mybatis/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // Tablestore does not support transactions. Therefore, you must set the parameter that specifies whether configurations are automatically committed to true. 
        SqlSession session = sqlSessionFactory.openSession(true);
        // Enter the identifier of the SELECT statement that you want to execute and set the value of the primary key to 99. 
        // The identifier of the SELECT statement must be in the following format: Path of the mapping configuration file.ID of the SELECT statement that you want to execute. In this example, mybatis.TripMapper.selectTrip indicates that the SELECT statement whose ID is selectTrip in the TripMapper.xml file on the mybatis node is executed. 
        Trip trip = (Trip) session.selectOne("mybatis.TripMapper.selectTrip", 99L);
        // Display the column values that you want to obtain. 
        System.out.println("trip id: " + trip.getTrip_id());
        System.out.println("start date: " + trip.getStart_date());
        System.out.println("end date: " + trip.getEnd_date());
        System.out.println("duration: " + trip.getDuration());
        session.close();
    }
}