Use Hibernate to query data with SQL

Step 1: Install the JDBC driver

Install the JDBC driver in one of the following ways.

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

Step 2: Install Hibernate

Install Hibernate in one of the following ways.

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>3.6.3.Final</version>
</dependency>

Step 3: Map SQL columns

Create a Java Bean and a mapping configuration file to map the bean properties to the table columns.

1. Create a Java Bean with properties that correspond to the table columns.

   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;
       
       // Use tripId as an example. Other fields are similar.
       public void setTripId(Long tripId){
           this.tripId = tripId;
       }
       public Long getTripId() {
           return tripId;
       }
   }

2. Create a Trip.hbm.xml file in the hibernate directory with the following content. This file maps the bean properties to the table columns.

   Important

   For read-only queries, set the insert and update attributes of attribute columns to false to prevent Hibernate from generating write statements. For SQL data type mapping rules, see Data type mappings in SQL.

   <?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>
       <!--The class name must match the actual class name.-->
       <class name="hibernate.Trip" table="trips">
           <!-- The field configured in the id element is the primary key column of the table.-->
           <id name="tripId" column="trip_id" type="long"/>
           <!-- For read-only queries, set insert and update to false 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 a SessionFactory

After you create the configuration files, load the Hibernate configuration to build a SessionFactory.

1. Create a Hibernate configuration file named hibernate.cfg.xml with the following content. Replace the configuration values with your actual values.

   <?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>
           <!-- Set this to the path of the mapping configuration file.-->
           <mapping resource="hibernate/Trip.hbm.xml"/>
       </session-factory>
   </hibernate-configuration>

   Configuration properties:

   Property	Type	Required	Description
   hibernate.connection.driver_class	class	Yes	The class name of the Tablestore JDBC driver. Set this to com.alicloud.openservices.tablestore.jdbc.OTSDriver.
   hibernate.connection.url	string	Yes	The URL of the Tablestore instance in the format jdbc:ots:endpoint/instanceName. Replace endpoint with the service endpoint and instanceName with the name of your instance.
   hibernate.connection.username	string	Yes	The AccessKey ID of your Alibaba Cloud account or RAM user.
   hibernate.connection.password	string	Yes	The AccessKey Secret of your Alibaba Cloud account or RAM user.
   hibernate.connection.autocommit	boolean	Yes	Specifies whether to enable auto-commit. Important Tablestore does not support transactions. Set autocommit to true.
   hibernate.dialect	string	Yes	The SQL dialect. Because Tablestore SQL is compatible with MySQL syntax, set this to org.hibernate.dialect.MySQLDialect.

2. Load the Hibernate configuration file to build a SessionFactory.

   SessionFactory factory = new Configuration().
     configure("hibernate/hibernate.cfg.xml").
     buildSessionFactory();

Step 5: Create a Session and query data

Create a Session from the SessionFactory, and call the get method to query data by primary key.

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

Unable to instantiate default tuplizer

Error message:

org.hibernate.HibernateException: Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer]

Cause: The javassist dependency is missing.

Solution:

Add the following dependency to pom.xml:

<dependency>
    <groupId>org.javassist</groupId>
    <artifactId>javassist</artifactId>
    <version>3.15.0-GA</version>
</dependency>

Unknown column '{columnName}' in 'field list'

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

Solution:

• Add the column as a predefined column. The column is automatically synchronized to the SQL mapping table.

• Add the column to the mapping table with a CREATE TABLE or ALTER TABLE statement. See DDL operations.