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

Background information

Hibernate is an Object/Relational Mapping (ORM) solution for Java environments. You can use Hibernate to map Java classes to database tables, map Java data types to SQL data types, and query data. Hibernate can significantly reduce the development time that is spent on manually handling data in SQL and JDBC. For more information, see Hibernate 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 Hibernate

You can install Hibernate by using one of the following methods:

  • Download the Hibernate installation package hibernate-core-x.x.x.jar and import the package to the project. For more information about the download path, see Hibernate installation package.

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

  • Add dependencies to a Maven project.

    To use Hibernate 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.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>3.6.3.Final</version>
</dependency>

Step 3: Map SQL fields

After you create a Java Bean that contains member variables whose names are the same as the names of fields in the data table, create a mapping configuration file to map the member variables in the Java Bean to the fields in the data table.

  1. Create a Java Bean that contains member variables whose names are the same as the names of fields in the data table. 
    package hibernate;

public class Trip {

    private long tripId;
    private long duration;
    private String startDate;
    private String endDate;
    private long startStationNumber;
    private long endStationNumber;
    private String startStation;
    private String endStation;
    private String bikeNumber;
    private String memberType;
    // ...
}
  2. Create a mapping configuration file to map the member variables in the Java Bean to the fields in the data table. The following sample code shows how to create a mapping configuration file named Trip.hbm.xml in the hibernate directory.

    Tablestore SQL does not support data insert and update. Therefore, you must set the insert property and the update property to false. For more information about the mappings between the data types of fields in SQL and Tablestore, see Data type mappings. For more information about the supported SQL features, see SQL features. 

    <?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <!--Specify the actual class name. -->
    <class name="hibernate.Trip" table="trips">
        <!-- The field that is configured in the id element is the primary key column of the data table. -->
        <id name="tripId" column="trip_id" type="long"/>
        <!-- The fields that are configured in the property element are attribute columns of the data table. You must set the insert property and the update property to false because insert and update operations are prohibited for attribute columns. -->
        <property name="duration" column="duration" type="long" insert="false" update="false"/>
        <property name="startDate" column="start_date" type="string" insert="false" update="false"/>
        <property name="endDate" column="end_date" type="string" insert="false" update="false"/>
        <property name="startStationNumber" column="start_station_number" type="long" insert="false" update="false"/>
        <property name="endStationNumber" column="end_station_number" type="long" insert="false" update="false"/>
        <property name="startStation" column="start_station" type="string" insert="false" update="false"/>
        <property name="endStation" column="end_station" type="string" insert="false" update="false"/>
        <property name="bikeNumber" column="bike_number" type="string" insert="false" update="false"/>
        <property name="memberType" column="member_type" type="string" insert="false" update="false"/>
    </class>
</hibernate-mapping>

Step 4: Build the SessionFactory

After you configure the Hibernate configuration file, load the Hibernate configuration file to build the SessionFactory.

  1. Add the following content to the Hibernate configuration file named hibernate.cfg.xml. Modify the configuration items in the configuration file based on your business requirements. 
    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.alicloud.openservices.tablestore.jdbc.OTSDriver</property>
        <property name="hibernate.connection.url">jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance</property>
        <property name="hibernate.connection.username">************************</property>
        <property name="hibernate.connection.password">********************************</property>
        <property name="hibernate.connection.autocommit">true</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <!-- Specify the path of the mapping configuration file. -->
        <mapping resource="hibernate/Trip.hbm.xml"/>
    </session-factory>
</hibernate-configuration>
    The following table describes the items that you must configure in the Hibernate configuration file.
    Configuration item Type Required Example Description
    hibernate.connection.driver_class 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.
    hibernate.connection.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.

    hibernate.connection.username string Yes ************************ The AccessKey ID of your Alibaba Cloud account or a RAM user.
    hibernate.connection.password string Yes ******************************** The AccessKey secret of your Alibaba Cloud account or a RAM user.
    hibernate.connection.autocommit boolean Yes true Specifies whether to automatically commit configurations. Tablestore does not support transactions. Set hibernate.connection.autocommit to true.
    hibernate.dialect string Yes org.hibernate.dialect.MySQLDialect Tablestore SQL inherits the MySQL syntax. Set this configuration item to org.hibernate.dialect.MySQLDialect.
  2. Load the Hibernate configuration file to build the SessionFactory. 
    SessionFactory factory = new Configuration().
  configure("hibernate/hibernate.cfg.xml").
  buildSessionFactory();

Step 5: Create a session to query data

Session session = factory.openSession();
Trip trip = (Trip) session.get(Trip.class, 99L); 
System.out.println("trip id: " + trip.getTripId());
System.out.println("start date: " + trip.getStartDate());
System.out.println("end date: " + trip.getEndDate());
System.out.println("duration: " + trip.getDuration());
session.close();
factory.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.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import hibernate.Trip;

public class HibernateDemo {
    public static void main(String[] args) {
        SessionFactory factory = new Configuration().
                configure("hibernate/hibernate.cfg.xml"). // Specify the full path of the Hibernate configuration file. 
                buildSessionFactory();
        Session session = factory.openSession();
        // Set the value of the primary key to 99. If the row in which the value of the primary key is 99 does not exist, null is returned. 
        Trip trip = (Trip) session.get(Trip.class, 99L);
        // Display the column values that you want to obtain. 
        System.out.println("trip id: " + trip.getTripId());
        System.out.println("start date: " + trip.getStartDate());
        System.out.println("end date: " + trip.getEndDate());
        System.out.println("duration: " + trip.getDuration());
        session.close();
        factory.close();
    }
}

FAQ

  • Problem description: What do I do if the following error message appears when I query data by using Hibernate to use the JDBC driver? 
    Exception in thread "main" org.hibernate.HibernateException: Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer]
  at org.hibernate.tuple.entity.EntityTuplizerFactory.constructTuplizer(EntityTuplizerFactory.java:108)
  at org.hibernate.tuple.entity.EntityTuplizerFactory.constructDefaultTuplizer(EntityTuplizerFactory.java:133)
  at org.hibernate.tuple.entity.EntityEntityModeToTuplizerMapping.<init>(EntityEntityModeToTuplizerMapping.java:80)
  at org.hibernate.tuple.entity.EntityMetamodel.<init>(EntityMetamodel.java:322)
  at org.hibernate.persister.entity.AbstractEntityPersister.<init>(AbstractEntityPersister.java:485)
  at org.hibernate.persister.entity.SingleTableEntityPersister.<init>(SingleTableEntityPersister.java:133)
  at org.hibernate.persister.PersisterFactory.createClassPersister(PersisterFactory.java:84)
  at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:286)
  .....

    Possible cause: The javassist-x.x.x.jar package is missing.

    Solution: Install the javassist-x.x.x.jar package by using one of the following methods:
    • Install the javassist installation package javassist-x.x.x.jar and import the package to the project. For more information about the download path, see javassist installation package.

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

    • Add dependencies to a Maven project.

      Add the corresponding dependencies to the pom.xml file in the Maven project. The following sample code shows how to add content to <dependencies>. In this example, the 3.15.0-GA version is used. 

      <!-- https://mvnrepository.com/artifact/org.javassist/javassist -->
<dependency>
    <groupId>org.javassist</groupId>
    <artifactId>javassist</artifactId>
    <version>3.15.0-GA</version>
</dependency>
  • Problem description: What do I do if the Message: Unknown column '{columnName}' in 'field list' error message appears when I query data by using Hibernate to use the JDBC driver?

    Possible cause: The specified column does not exist in the SQL mapping table.

    Solution: Make sure that the specified column exists in the SQL mapping table. You can use one of the following methods to fix the error:
    • Add the specified column to the pre-defined columns to automatically synchronize the specified column to the SQL mapping table.
    • Specify the column when you execute the CREATE TABLE statement to create a mapping table. For more information, see Create mapping tables for tables.