Connect Hibernate to an OceanBase database

Configure dependencies

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.25</version>
</dependency>
<dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>5.0.7.Final</version>
</dependency>

The hibernate.cfg.xml file

The content is as follows:

<?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>
        <!--OceanBase connection information-->
        <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://xxx.xxx.xxx.xxx:3306/test</property>
        <property name="connection.username">a****</property>
        <property name="connection.password">******</property>

        <!-- Optional configurations -->
        <!--Specifies whether to support a dialect-->
        <!--The following configuration is required in MySQL mode-->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <!--Specifies whether to print SQL statements when executing CURD operations -->
        <property name="show_sql">true</property>
        <!--Automatically create or alter tables-->
        <!--<property name="hbm2ddl.auto">update</property>-->
        <!--<property name="hbm2ddl.auto">create</property>-->

        <!--Resource registration (entity class mapping file)-->
        <mapping resource="./UserModel.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

Test preparations

Entity class

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //Comment out a field to verify that the framework automatically alters the table to add new properties based on the entity class after the table is created.
    //private String tel;
    // ... Constructors, getters, and setters are omitted here ...
}

Entity class mapping XML file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        '-//Hibernate/Hibernate Mapping DTD 3.0//EN'
        'http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd'>
<hibernate-mapping>
    <!-- Map the class to a table on the class element -->
    <!-- name: The fully qualified class name -->
    <!-- table: The table name -->
    <class name="com.alibaba.ob.pojo.User" table="user">
        <!-- Map the primary key on the id element -->
        <!-- name: The property name in the object used as the primary key -->
        <!-- column: The primary key field name in the table -->
        <!-- If the values of name and column are the same, you can omit column -->
        <id name="id" column="id">
            <!-- To generate the ID manually, set the class property of the generator element to "assigned". You must provide an ID. -->
            <generator class="identity"  />

            <!--**Note the Hibernate primary key generation policy**-->

        </id>
        <!-- Map properties to fields on the property element -->
        <!-- name: The property name in the class -->
        <!-- column: The field name in the table -->
        <!-- If the values of name and column are the same, you can omit column -->
        <property name="username" />
        <property name="birthday" />
        <property name="sex" />
        <property name="address"/>
       <!--Same as in the entity class-->
    <!--<property name="tel"/>-->
    </class>
</hibernate-mapping>

Sample code

Connection

@Test
public void testConnection(){
  //Load the configuration information
  Configuration conf = new Configuration().configure();
  //Create a SessionFactory object based on the configuration information
  SessionFactory sessionFactory = conf.buildSessionFactory();
  //Open a session object related to the database
  Session session = sessionFactory.openSession();
  System.out.println(session);
}

A connection is established:

Automatic table creation

Uncomment this line in the configuration file:

Delete the user table from the database, and then run the following test method:

@Test
public void testCreateTableAndInsertData(){
    //Create the object to test
    User user = new User();
    user.setUsername("hibernateTest");
    user.setSex("Female");
    user.setBirthday(new Date());
    user.setAddress("Beijing");

    //Start a transaction based on the session
    Transaction transaction = session.beginTransaction();
    //Save data through the session
    session.save(user);
    //Commit the transaction
    transaction.commit();
    //After the operation is complete, close the session connection object
    session.close();
}

The following figures show the executed SQL statement printed in the console and the result in the OceanBase database.

The result shows that OceanBase is compatible with the Hibernate `create table` feature.

Alter a table (add a field)

First, modify the configuration file as follows:

<!--Automatically create or alter tables-->
<property name="hbm2ddl.auto">update</property>
<!--<property name="hbm2ddl.auto">create</property>-->

Uncomment the `tel` property in the entity class and the XML file, and then run the following method:

@Test
public void testAlterTable(){
    //Create the object to test
    User user = new User();
    user.setUsername("hibernateTest");
    user.setSex("Female");
    user.setBirthday(new Date());
    user.setAddress("Beijing");
    user.setTel("12345678911");

    //Start a transaction based on the session
    Transaction transaction = session.beginTransaction();
    //Save data through the session

    session.save(user);
    //Commit the transaction
    transaction.commit();
    //After the operation is complete, close the session connection object
    session.close();
}

The executed statement and the database result are shown in the following figures.

The framework automatically adds a new field to the existing table based on the entity class and the XML file.

The result shows that OceanBase is compatible with the Hibernate `alter table` feature.

Persistent data

Test method:

@Test
public void testInsert() {
    User u1 = new User("asd", new Date(), "Male", "Zhangzhou");
    User u2 = new User("qwe", new Date(), "Female", "Hangzhou");
    User u3 = new User("zxc", new Date(), "Male", "Shanghai");
    User u4 = new User("xcv", new Date(), "Female", "Hangzhou");
    User u5 = new User("sdf", new Date(), "Male", "Hangzhou");
    User u6 = new User("wer", new Date(), "Female", "Hangzhou");
    User u7 = new User("ert", new Date(), "Male", "Zhangzhou");
    User u8 = new User("rty", new Date(), "Female", "Shanghai");
    User u9 = new User("tyu", new Date(), "Male", "Hangzhou");
    ArrayList<User> users = new ArrayList<>();
    users.add(u1);
    users.add(u2);
    users.add(u3);
    users.add(u4);
    users.add(u5);
    users.add(u6);
    users.add(u7);
    users.add(u8);
    users.add(u9);
    //Start a transaction based on the session
    Transaction transaction = session.beginTransaction();
    //Save data through the session
    for (User user : users) {
        session.save(user);
    }
    //Commit the transaction
    transaction.commit();
    //After the operation is complete, close the session connection object
    session.close();
}

The result is as follows:

The result shows that OceanBase is compatible with the Hibernate `Insert` feature and supports the Hibernate auto-increment primary key generation policy.

Query in HQL mode and SQL mode

Test method:

@Test
public void testQuery() {
    //SQL mode
    //SQLQuery query = sessionFactory.openSession().createSQLQuery("select * from user where sex = 'Male'").addEntity(User.class);
    //HQL mode
    //Query query = sessionFactory.openSession().createQuery("select User from User User where User.sex = 'Male'");
    List<User> list = query.list();
    list.forEach(System.out::println);
}

The result is as follows:

SQL mode:

HQL mode:

The result shows that OceanBase is compatible with the Hibernate `query` feature.

Update operation

Test method:

@Test
public void testChange(){
    User user = (User) session.createSQLQuery("select * from user where id = 1").addEntity(User.class).list().get(0);
    System.out.println("Before update:"+user);
    user.setAddress("Hangzhou");
    session.update(user);
    user = (User) session.createSQLQuery("select * from user where id = 1").addEntity(User.class).list().get(0);
    System.out.println("After update:"+user);
}

The result is as follows:

The result shows that OceanBase is compatible with the Hibernate `update` feature.

Delete operation

Test method:

@Test
public void testDelete(){
    Transaction transaction = session.beginTransaction();
    List<User> list = session.createSQLQuery("select * from user").addEntity(User.class).list();
    System.out.println(list);
    int i = session.createSQLQuery("delete from user where id = " + list.get(0).getId()).executeUpdate();
    list = session.createSQLQuery("select * from user").addEntity(User.class).list();
    System.out.println(list);
    //Commit the transaction
    transaction.commit();
    //After the operation is complete, close the session connection object
    session.close();
}

The result is as follows:

The result shows that OceanBase is compatible with the Hibernate `delete` feature.