Tablestore:Use MyBatis to query data with SQL

Step 1: Install the JDBC driver

The JDBC driver is available through Maven or manual installation.

<dependency>
  <groupId>com.aliyun.openservices</groupId>
  <artifactId>tablestore-jdbc</artifactId>
  <version>5.17.0</version>
</dependency>

Step 2: Install MyBatis

MyBatis is available through Maven or manual installation.

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

Step 3: Map SQL fields

1. Create a Java Bean for the data table columns. In this example, trip_id is the primary key of the data table.

   Important

   The member variable names of the Java Bean must match the column names of 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;
   
       // The following example uses trip_id. Other fields follow the same pattern.
       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 and define the query conditions. Create TripMapper.xml in the mybatis directory with the following content:

   <?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 SqlSessionFactory

SqlSessionFactory creates MyBatis sessions that connect to Tablestore.

1. Create the MyBatis configuration file mybatis-config.xml with the following content. Replace the placeholders with your actual values.

   <?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"/>
               <!-- The Tablestore JDBC driver requires explicit closure to exit the process. Use POOLED for long-running applications or UNPOOLED for one-time queries. -->
               <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>
           <!-- Set to the path of your mapping configuration file. -->
           <mapper resource="mybatis/TripMapper.xml"/>
       </mappers>
   </configuration>

   Parameter	Type	Required	Description
   driver	class	Yes	The class name of the Tablestore JDBC driver. Set this to com.alicloud.openservices.tablestore.jdbc.OTSDriver.
   url	string	Yes	The instance endpoint in the format jdbc:ots:endpoint/instanceName. Replace endpoint with your instance's service address and instanceName with your instance name.
   username	string	Yes	The AccessKey ID of your Alibaba Cloud account or RAM user.
   password	string	Yes	The AccessKey secret of your Alibaba Cloud account or RAM user.

2. Load the MyBatis configuration file to build SqlSessionFactory.

   String resource = "mybatis/mybatis-config.xml";
   InputStream inputStream = Resources.getResourceAsStream(resource);
   SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

Step 5: Query data with SqlSession

Tablestore does not support transactions. Set the autoCommit parameter of openSession to true.

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