This topic provides a Hibernate connection example and tests several general features.
Configuration 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 following example shows the content of the file:
<?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://10.100.xxx.xxx:18815/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 MySQL or Oracle mode.-->
<!--MySQL mode-->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!--Oracle 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 column first to verify that the table is automatically modified based on the new properties 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 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 specified using the class element. -->
<!-- name: the full path class name. -->
<!-- table: the table name. -->
<class name="com.alibaba.ob.pojo.User" table="user">
<!-- The primary key mapping is specified using the id element. -->
<!-- name: the name of the attribute that serves as the primary key in the object. -->
<!-- column: 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 specified using 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
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);
}
The connection is established:
Automatic table creation
Uncomment this line in the configuration file:
<—-Automatic table creation (modify table)-->
<!--<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 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 Database:
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 Database supports the Create Table feature of Hibernate.
Modify a table (add columns)
Modify the configuration file as follows:
<—-Automatic table creation (modify table)-->
<!--<property name="hbm2ddl.auto">update</property>--> //Uncomment this line.
<!--<property name="hbm2ddl.auto">create‹/property>--> //Comment out this line.
Uncomment the comments on the tel attribute in the entity class and the XML file, then execute the following method:
@Test
public void testAlterTable(){
// Create test objects.
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 a column to the table based on the entity class and the XML file.
The test result shows that OceanBase Database 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 Database 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 Database supports the Query feature of Hibernate.
Update
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 Database supports the Update feature of Hibernate.
Delete
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 Database supports the Delete feature of Hibernate.