This topic provides a Hibernate connection example and tests several general features.
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>
hibernate.cfg.xml
The file 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 Database connection information-->
<property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://t5******.********.oceanbase.cloud:3306/test</property>
<property name="connection.username">admin</property>
<property name="connection.password">******</property>
<!--Optional configuration-->
<!--Specify whether to support dialects. Configure the following settings based on whether the database is in the MySQL compatible mode or the Oracle compatible mode.-->
<!--MySQL compatible mode-->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!--Oracle compatible mode-->
<property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>
<!--Specify whether to print SQL statements when create, read, update, and delete (CRUD) operations are performed.-->
<property name="show_sql">true</property>
<!--Automatic table creation (modification)-->
<!--<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 first to verify that the table is automatically modified based on the new attributes of the entity class after the table is created based on the framework.
//private String tel;
//The constructor and the get and set methods are omitted here.
}
Entity class mapping file in the XML format
<?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>
<!-- The mapping between a class and a table is printed on the class element. -->
<!-- name: the full path class name. -->
<!-- table: the name of the table. -->
<class name="com.alibaba.ob.pojo.User" table="user">
<!-- The primary key mapping is printed on the id element. -->
<!-- name: the name of the attribute that serves as the primary key in the object. -->
<!-- colomn: the name of the primary key field in the table. -->
<!-- If the values of name and column are the same, the value of column can be ignored. -->
<id name="id" column="id">
<!--Set the class attribute of the generator element to "assigned", which indicates that an ID must be manually assigned.-->
<generator class="identity" />
<!--**Pay attention to the strategy for generating the Hibernate primary key.**-->
</id>
<!-- The mapping between attributes and fields is printed on the property element. -->
<!-- name: the attribute name in the class. -->
<!-- column: the field name in the table. -->
<!-- If the values of name and column are the same, the value of column can be ignored. -->
<property name="username" />
<property name="birthday" />
<property name="sex" />
<property name="address"/>
<!--Same as the entity class-->
<!--<property name="tel"/>-->
</class>
</hibernate-mapping>
Sample code
Establish a 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);
}
If the following information is displayed, a connection is successfully established:
Automatically create a table
Open the row comment for the configuration file:
<--Automatic table creation (modification)-->
<!--<property name="hbm2ddl.auto">update</property>-->
<!--<property name="hbm2ddl.auto">create</property>-->
After you drop the user table in the database, perform the following test operations:
@Test
public void testCreateTableAndInsertData(){
// Create the test object.
User user = new User();
user.setUsername("hibernateTest");
user.setSex("Female");
user.setBirthday(new Date());
user.setAddress("Beijing");
// Start the transaction, where the statement data is obtained based on the session.
Transaction transaction = session.beginTransaction();
// Save data based on the session.
session.save(user);
// Commit the transaction.
transaction.commit();
// Close the session connection object after the operation is completed.
session.close();
}
Check the executed SQL statements in the console and the statement execution results in OceanBase Cloud:
Hibernate: drop table if exists user
Hibernate: create table user (id integer not null auto_increment, username varchar (255), birthday datetime, sex varchar (255), address varchar (255), primary key (id))
Hibernate: insert into user (username, birthday, sex, address) values (?, ?, ?, ?)
Process finished with exit code
obclient> drop table user;
Query OK, 0 rows affected
obclient> select * from user;
+----+---------------+-----------------------+------+---------+
| id | username | birthday | sex | address |
+----+---------------+-----------------------+------+---------+
| 1 | hibernateTest | 2020-09-17 17:22:32 | Female | Beijing |
+----+---------------+-----------------------+------+---------+
The test result shows that OceanBase Cloud supports the Create Table feature of Hibernate.
Modify a table (add fields)
Modify the configuration file as follows:
<--Automatic table creation (modification)-->
<!--<property name="hbm2ddl.auto">update</property>--> // Uncomment this line.
<!--<property name="hbm2ddl.auto">create</property>--> // Comment out this line.
Uncomment the tel attribute in the entity class and the XML file and execute the following method:
@Test
public void testAlterTable(){
// Create the test object.
User user = new User();
user.setUsername("hibernateTest");
user.setSex("Female");
user.setBirthday(new Date());
user.setAddress("Beijing");
user.setTel("12345678911");
// Start the transaction, where the statement data is obtained based on the session.
Transaction transaction = session.beginTransaction();
// Save data based on the session.
session.save(user);
// Commit the transaction.
transaction.commit();
// Close the session connection object after the operation is completed.
session.close();
}
The executed statements and statement execution results in the database are displayed as follows:
Hibernate: insert into user (username, birthday, sex, address, tel) values (?, ?, ?, ?, ?)
Process finished with exit code
obclient> select * from user;
+----+---------------+-----------------------+------+---------+
| id | username | birthday | sex | address |
+----+---------------+-----------------------+------+---------+
| 1 | hibernateTest | 2020-09-17 17:30:39 | Female | Beijing |
+----+---------------+-----------------------+------+---------+
1 row in set
obclient> select * from user;
+----+---------------+-----------------------+------+---------+---------------+
| id | username | birthday | sex | address | tel |
+----+---------------+-----------------------+------+---------+---------------+
| 1 | hibernateTest | 2020-09-17 17:30:39 | Female | Beijing | NULL |
| 2 | hibernateTest | 2020-09-17 17:31:27 | Female | Beijing | 123456789111 |
+----+---------------+-----------------------+------+---------+---------------+
2 row in set
Based on the original table, the framework automatically adds fields to the table based on the entity class and the XML file.
The test result shows that OceanBase Cloud supports the Alter Table feature of Hibernate.
Persist data
The test method is as follows:
@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 the transaction, where the statement data is obtained based on the session.
Transaction transaction = session.beginTransaction();
// Save data based on the session.
for (User user : users) {
session.save(user);
}
// Commit the transaction.
transaction.commit();
// Close the session connection object after the operation is completed.
session.close();
}
The execution result is as follows:
obclient> select * from user;
+----+---------------+-----------------------+------+---------+---------------+
| id | username | birthday | sex | address | tel |
+----+---------------+-----------------------+------+---------+---------------+
| 1 | hibernateTest | 2020-09-17 17:30:39 | Female | Beijing | NULL |
| 2 | hibernateTest | 2020-09-17 17:31:27 | Female | Beijing | 123456789111 |
| 3 | asd | 2020-09-17 18:05:17 | Male | Zhangzhou | NULL |
| 4 | qwe | 2020-09-17 18:05:17 | Female | Hangzhou | NULL |
| 5 | zxc | 2020-09-17 18:05:17 | Male | Shanghai | NULL |
| 6 | xcv | 2020-09-17 18:05:17 | Female | Hangzhou | NULL |
| 7 | sdf | 2020-09-17 18:05:17 | Male | Hangzhou | NULL |
| 8 | wer | 2020-09-17 18:05:17 | Female | Hangzhou | NULL |
| 9 | ert | 2020-09-17 18:05:17 | Male | Zhangzhou | NULL |
| 10 | rty | 2020-09-17 18:05:17 | Female | Shanghai | NULL |
| 11 | tyu | 2020-09-17 18:05:17 | Male | Hangzhou | NULL |
+----+---------------+-----------------------+------+---------+---------------+
The test result shows that OceanBase Cloud supports the Insert feature and the primary key auto-increment feature of Hibernate.
Queries in HQL mode and SQL mode
The test method is as follows:
@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 execution result is as follows:
SQL mode:
HQL mode:
The test result shows that OceanBase Cloud supports the Query feature of Hibernate.
Modify data
The test method is as follows:
@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 modification:" +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 modification:" +user);
}
The execution result is as follows:
The test result shows that OceanBase Cloud supports the Update feature of Hibernate.
Delete data
The test method is as follows:
@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();
// Close the session connection object after the operation is completed.
session.close();
}
The execution result is as follows:
The test result shows that OceanBase Cloud supports the Delete feature of Hibernate.