All Products
Search

This Product

    Tablestore:Use MyBatis to query data by executing SQL statements

    Document Center

    Tablestore:Use MyBatis to query data by executing SQL statements

    Last Updated:Dec 25, 2024

    MyBatis is a persistence framework for Java that supports custom SQL statements, stored procedures, and advanced mappings. You can use MyBatis to use the Java Database Connectivity (JDBC) driver for Tablestore to access 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 following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Ulanqab), China (Shenzhen), China (Chengdu), China (Hong Kong), Japan (Tokyo), Singapore, Malaysia (Kuala Lumpur), UK (London), US (Silicon Valley), Indonesia (Jakarta), Germany (Frankfurt), SAU (Riyadh - Partner Region), and US (Virginia).

    • The JDBC driver for Tablestore has a built-in connection pool. Therefore, we recommend you do not use the connection pool of external databases to prevent issues caused by incompatibility or other unknown factors.

    Prerequisites

    Procedure

    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 to only add the corresponding dependencies to the pom.xml file. In this example, JDBC driver 5.17.0 is used. Add the following content to <dependencies>:

      <dependency>  <groupId>com.aliyun.openservices</groupId>  <artifactId>tablestore-jdbc</artifactId>  <version>5.17.0</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 to only add the corresponding dependencies to the pom.xml file. In this example, Tablestore SDK for Java V3.5.9 is used. Add the following content to <dependencies>:

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

    // Add the methods that are used to specify field names and return field values. 
    // In this example, the trip_id field is used. You can add the methods that are used to specify field names and return field values for other fields in a similar manner based on your business requirements. 
    public void setTrip_Id(Long trip_id){
        this.trip_id =trip_id
    }
    public Long getTrip_Id() {
        return trip_id;
    }
}

    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 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 Endpoints. instanceName indicates the name of the instance. Replace instanceName with the actual instance name.

      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 column 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 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 to automatically commit configurations 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();
    }
}

    References