Connect to OceanBase Cloud by using Hibernate|OceanBase Cloud| docs|Distributed Database

This topic describes how to use the Hibernate framework and OceanBase Cloud to build an application for basic database operations, such as table creation, data insertion, and data query.

Download the java-oceanbase-hibernate sample project

Connect to OceanBase Database by using Hibernate (Oracle compatible mode)

Prerequisites

You have registered an OceanBase Cloud account, and created a cluster instance and an Oracle-compatible tenant in OceanBase Cloud. For more information, see Create a cluster instance and Create a tenant.
You have obtained the connection string of the Oracle-compatible tenant. For more information, see Obtain the connection string.
You have installed Java Development Kit (JDK) 1.8 and Maven.
You have installed IntelliJ IDEA.

Note

This topic uses IntelliJ IDEA Community Edition 2021.3.2 to run the sample code. You can also choose a suitable tool as needed.

Procedure

Note

The following procedure applies to Windows. If you use another operating system or compiler, the procedure can be slightly different.

Step 1: Import the `java-oceanbase-hibernate` project to IntelliJ IDEA

Start IntelliJ IDEA and choose File > Open....
In the Open File or Project window, select the project files and click OK to import the files.
IntelliJ IDEA automatically identifies various files in the project. You can view project information such as the directory structure, file list, module list, and dependencies in the Project window. Generally, the Project window is at the leftmost of the UI of IntelliJ IDEA and is opened by default. If the Project window is closed, you can choose View > Tool Windows > Project in the menu bar or press Alt + 1 to open it.

Note

When you use IntelliJ IDEA to import a project, IntelliJ IDEA automatically detects the `pom.xml` file in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.
View the project.

Step 2: Modify the database connection information in the `java-oceanbase-hibernate` project

Modify the database connection information in the hibernate.cfg.xml file based on the obtained connection string mentioned in the "Prerequisites" section.

Note

If you need to add additional properties to the JDBC connection string, see Database URL.

Here is an example:

<property name="hibernate.connection.driver_class">com.oceanbase.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:oceanbase://t5******.********.oceanbase.cloud:3306/sys</property>
<property name="hibernate.connection.username">test_user</property>
<property name="hibernate.connection.password">******</property>

The name of the database driver is com.oceanbase.jdbc.Driver.
The endpoint is t5******.********.oceanbase.cloud.
The access port is 3306.
The name of the database to be accessed is sys.
The tenant account is test_user.
The password is ******.

Step 3: Run the `java-oceanbase-hibernate` project

Run path

Find the TestHibernate.java file under src > test > java in the project package.
Choose Run > Run... > TestHibernate in the menu bar or click the green triangle in the upper-right corner to run the project.
View the logs and output of the project in the Console window of IDEA.

Running result

User{id=2, name='update'}
User{id=3, name='user_insert3'}
User{id=4, name='user_insert4'}
User{id=5, name='user_insert5'}

Project code

Click here to download the project code, which is a package named java-oceanbase-hibernate.zip.

Decompress the package to obtain a folder named java-oceanbase-hibernate. The directory structure is as follows:

│--pom.xml
│
├─.idea
├─src
│  ├─main
│  │  ├─java
│  │  │  └─com
│  │  │      └─oceanbase
│  │  │          ├─dao
│  │  │          │   └─--UserDao.java
│  │  │          │
│  │  │          └─pojo
│  │  │              └─--User.java
│  │  │
│  │  └─resources
│  │      │--hibernate.cfg.xml
│  │      │
│  │      └─com
│  │          └─oceanbase
│  │              └─pojo
│  │                  └─--User.hbm.xml
│  │
│  └─test
│      └─java
│          └─--TestHibernate.java
│
└─target

The files and directories are described as follows:

pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build rules of the project.
.idea: a directory used in an Integrated Development Environment (IDE) to store configuration information related to the project.
src: a directory that stores the source code in the project.
main: a directory that stores the main source code and resource files.
java: a directory that stores the Java source code.
com: the root directory of the Java package.
oceanbase: the root directory of the project.
dao: stores the data access object (DAO) package for accessing databases or other data storage services.
UserDao.java: the user DAO that is used to add, delete, modify, and query user data.
User.java: the user persistent object that is mapped to fields in a user data table.
pojo: stores the persistent Java class, which follows the Plain Old Java Object (POJO) programming model, mapped to a database table. It is used for the mapping to a database table or another database storage structure.
resources: a directory that stores resource files, such as configuration files and SQL files.
hibernate.cfg.xml: the Hibernate configuration file, which is used to configure the basic parameters of Hibernate and other information such as the data source.
User.hbm.xml: defines the mapping between the user persistent object and the user data table.
test: a directory that stores the test code and resource files.
TestHibernate.java: the directory that stores the Java class for testing Hibernate.
target: a directory that stores compiled class files and .jar packages.

Code in pom.xml

Note

You can retain the default code in this file for verification purposes or modify the code in the file as needed.

Perform the following steps to configure the pom.xml file:

Declare the file.

Declare the file to be an XML file that uses XML standard 1.0 and the character encoding UTF-8.

The sample code is as follows:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure namespaces and the POM model version.
1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.
The sample code is as follows:
```
<project xmlns="http://maven.apache.org/POM/4.0.0"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
</project>
```
Configure basic project information.
1. <groupId>: the ID of the project group, which is set to com.oceanbase.
2. <artifactId>: the name of the project, which is set to java-oceanbase-hibernate.
3. <version>: the project version, which is set to 1.0-SNAPSHOT.
The sample code is as follows:
```
 <groupId>com.oceanbase</groupId>
 <artifactId>java-oceanbase-hibernate</artifactId>
 <version>1.0-SNAPSHOT</version>
```
Use <build> to define the build process for the project.
1. <plugins>: the plug-ins in the project.
2. <plugin> a plug-in in the project.
3. <groupId>: the ID of the project group, which is set to org.apache.maven.plugins.
4. <artifactId>: the name of the project, which is set to maven-compiler-plugin.
5. <configuration>: the parameters of the plug-in.
6. <source>: the source code version for the compiler, which is set to 8.
7. <target>: the target code version for the compiler, which is set to 8.
The sample code is as follows:
```
 <build>
     <plugins>
         <plugin>
             <groupId>org.apache.maven.plugins</groupId>
             <artifactId>maven-compiler-plugin</artifactId>
             <configuration>
                 <source>8</source>
                 <target>8</target>
             </configuration>
         </plugin>
     </plugins>
 </build>
```

Configure project dependencies in <dependencies>.

Define a dependency named oceanbase-client that belongs to the com.oceanbase group and whose version is 2.4.2.

The sample code is as follows:

    <dependencies>
    <dependency>
        <groupId>com.oceanbase</groupId>
        <artifactId>oceanbase-client</artifactId>
        <version>2.4.2</version>
    </dependency>
    </dependencies>

Define a dependency named junit that belongs to the junit group and whose version is 4.13.

The sample code is as follows:

    <dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
    </dependency>
    </dependencies>

Define a dependency named hibernate-core that belongs to the org.hibernate group and whose version is 5.2.17.Final.

The sample code is as follows:

<dependencies>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.2.17.Final</version>
  </dependency>
</dependencies>

Define a dependency named hibernate-c3p0 that belongs to the org.hibernategroup and whose version is 5.2.17.Final.

The sample code is as follows:

<dependencies>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>hibernate-c3p0</artifactId>
      <version>5.2.17.Final</version>
  </dependency>
</dependencies>

Code in User.hbm.xml

The User.hbm.xml file is a Hibernate mapping file that maps Java objects to database tables.

Perform the following steps to configure the User.hbm.xml file:

Declare the file.

Declare the file to be an XML file that uses XML standard 1.0 and the character encoding UTF-8. Specify the Document Type Definition (DTD) of the Hibernate mapping file by setting the type of the DTD file to hibernate-mapping, the version to 3.0, the language to EN, and the URL to http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd.

The sample code is as follows:
```
<?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">
```
Configure the mapping file.

Define a Hibernate mapping file that maps an entity class named User to a database table named test_hibernate_oracle.
1. Use the package attribute to specify com.oceanbase.pojo as the Java package in the mapping file.
2. Use the class tag to map a Java class to a database table. In the tag, use the name attribute to specify User as the name of the Java class, and use the table attribute to specify test_hibernate_oracle as the mapped-to database table.
3. Use the id tag to define a member variable for the Java class User as the primary key. In the tag, use the name attribute to specify id as the member variable name, and use the column attribute to specify USER_ID as the name of the mapped-to database field.
4. Use the generator tag to define the strategy for primary key generation. In the tag, use the class attribute to specify sequence as the type of the primary key generator, and use the param element to specify SQ_USER as the sequence name. Use the property tag to define another member variable for the Java class. In the tag, use the name attribute to specify USER_NAME as the name of the mapped-to database field, and use the type attribute to specify string as the data type of the member variable.
The sample code is as follows:
```
<hibernate-mapping package="com.oceanbase.pojo">
    <class name="User" table="test_hibernate_oracle">
        <id name="id" column="USER_ID">
            <generator class="sequence">
                <param name="sequence">SQ_USER</param>
            </generator>
        </id>
        <property name="name" column="USER_NAME" type="string"/>
    </class>
</hibernate-mapping>
```

Code in hibernate.cfg.xml

The hibernate.cfg.xml file is a Hibernate configuration file that configures the operating environment of Hibernate and the parameters for connecting to the database.

Perform the following steps to configure the hibernate.cfg.xml file:

Declare the file.

Declare the file to be an XML file that uses XML standard 1.0 and the character encoding UTF-8. Specify the DTD of the Hibernate configuration file by setting the type of the DTD file to hibernate-configuration, the version to 3.0, the language to EN, and the URL to http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd.

The sample code 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">
```
Configure the configuration parameter.

In the <hibernate-configuration> root element, use the <session-factory> element to configure the Hibernate session factory for creating and managing session objects. The session factory contains database connection configurations (such as the driver class name, URL, username, and password), connection pool configurations, as well as SQL interaction configurations (such as the database dialect, SQL printing and formatting, automatic creation of database tables, current session context, batch size, and use of secondary cache).
1. Configure database connection properties.
  
  Configure the properties that Hibernate uses to connect to the database, including the driver class name, URL, username, and password.
  - hibernate.connection.driver_class: the database driver used to establish a connection with OceanBase Cloud, which is set to com.oceanbase.jdbc.Driver.
  - hibernate.connection.url: the URL of the database, which contains the IP address, port number, and name of the database.
  - hibernate.connection.username: the username for connecting to the database.
  - hibernate.connection.password: the password for connecting to the database.
  The sample code is as follows:
```
    <property name="hibernate.connection.driver_class">com.oceanbase.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:oceanbase://host:port/schema_name</property>
    <property name="hibernate.connection.username">user_name</property>
    <property name="hibernate.connection.password">******</property>
```
2. Configure connection pool properties.
  
  Configure the properties of the connection pool used by Hibernate. Specify C3P0 as the connection pool, and set the maximum and minimum number of connections in the connection pool, timeout for obtaining a connection, timeout for an idle connection, maximum number of cached statements, interval for testing idle connections, number of connections to acquire at a time, and connection validation method.
  
  Note
  
  The following properties are not a complete list and are for reference only. If you need to configure other properties, refer to the relevant documentation for more detailed property information.
  - hibernate.connection.provider_class: the connection pool used to manage the creation and release of database connections, which is set to C3P0.
  - hibernate.c3p0.max_size: the maximum number of connections in the connection pool, which is set to 60.
  - hibernate.c3p0.min_size: the minimum number of connections in the connection pool, which is set to 30.
  - hibernate.c3p0.checkoutTimeout: the timeout period for obtaining a connection from the connection pool, which is set to 30,000 milliseconds.
  - hibernate.c3p0.timeout: the timeout period after which an idle connection in the connection pool is closed, which is set to 2,000 milliseconds.
  - hibernate.c3p0.max_statements: the maximum number of SQL statements that can be cached in the connection pool, which is set to 100.
  - hibernate.c3p0.idle_test_period: the interval at which idle connections in the connection pool are tested, which set to 3,000 milliseconds.
  - hibernate.c3p0.acquire_increment: the number of connections to acquire at a time by the connection pool when the pool is exhausted, which is set to 3.
  - hibernate.c3p0.validate: specifies whether to perform verification when the connection pool is used for connection, which is set to true.
  The sample code is as follows:
```
<property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.max_size">60</property>
<property name="hibernate.c3p0.min_size">30</property>
<property name="hibernate.c3p0.checkoutTimeout">30000</property>
<property name="hibernate.c3p0.timeout">20000</property>
<property name="hibernate.c3p0.max_statements">100</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.validate">true</property>
```
3. Configure SQL interaction properties. Configure the properties used by Hibernate to map the database, including the database dialect, SQL printing and formatting, automatic creation of tables, current session context, batch size, cache usage, and mapping file to load.
  - dialect: the database dialect for ensuring compatibility with OceanBase Cloud in the Oracle compatible mode, which is set to Oracle 12c.
  - hibernate.show_sql: specifies whether to print SQL statements generated by Hibernate in the console.
  - hibernate.format_sql: specifies whether to format the SQL statements to print.
  - hbm2ddl.auto: specifies to automatically create tables.
  - current_session_context_class: specifies to use thread-level current session context.
  - hibernate.jdbc.batch_size: the batch size.
  - hibernate.cache.use_second_level_cache: specifies whether to use a second-level cache.
  - Specify User.hbm.xml in the com/oceanbase/pojo/ directory as the mapping file to load.
  The sample code is as follows:
```
  <property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>
  <property name="hibernate.show_sql">true</property>
  <property name="hibernate.format_sql">true</property>
  <property name="hbm2ddl.auto">create</property>
  <property name="current_session_context_class">thread</property>
  <property name="hibernate.jdbc.batch_size">10</property>
  <property name="hibernate.cache.use_second_level_cache">false</property>
  <mapping resource="com/oceanbase/pojo/User.hbm.xml"/>
```

Code in UserDao.java

The UserDao.java file defines a user DAO class and creates a Session object to add, delete, modify, and query user data.

Perform the following steps to configure the UserDao.java file:

Reference other classes and APIs.

Declare this file to contain the following APIs and classes:
- User class: operates user objects.
- Session class: interacts with the database.
- SessionFactory class: creates Session instances.
- Transaction class: manages database transactions.
- Configuration class: loads the configuration file of Hibernate.
- Query class: performs query operations.
- List interface: operates the query result set.
The sample code is as follows:
```
import com.oceanbase.pojo.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
```
Define the UserDao class.

The Java class UserDao is a DAO class for persisting user objects. This class encapsulates a series of methods related to database interaction, such as adding, deleting, modifying, and querying user objects. By calling methods in the UserDao class, the application can easily interact with the database to implement persistence operations.
1. Initialize a UserDao instance.
  
  Use an object initialization block to initialize the Session object during the instantiation of the class.
  
  First, create a Configuration object and call the configure method to read the Hibernate configuration file (hibernate.cfg.xml). Then, call the buildSessionFactory method to create a SessionFactory object. Finally, call the openSession method to create a Session object and assign the object to the session variable.
  
  The sample code is as follows:
```
private Session session;
{
    Configuration cfg = new Configuration().configure();
    // Create SessionFactory
    SessionFactory sessionFactory = cfg.buildSessionFactory();
    session = sessionFactory.openSession();
} //Read the hibernate.cfg.xml file
```
2. Query user data by user ID.
  
  Call the selectUserById method to query user data by user ID.
  
  Use the get method of session to obtain the user record of the specified ID according to the input User.class and ID parameters, and store the record in the user variable.
  
  The sample code is as follows:
```
public User selectUserById(int ID) {
    User user = session.get(User.class, ID);
    return user;
}
```
3. Query user data by username.
  
  Call the selectUserbyName method to query user data by username. First, define a Hibernate Query Language (HQL) statement to query user data with a specified username. The table name is test_hibernate_oracle, the table alias is u, and the query field name is name. Call the createQuery method of the Session object to create a Query object and pass in the HQL statement and the entity class as parameters. Call the setParameter method of the Query object to set query parameters, where 0 represents the parameter position and name represents the parameter value. Call the list method of the Query object to execute the query and convert the query results into a list of User objects.
  
  The sample code is as follows:
```
public List<User> selectUserbyName(String name) {
    String sql = "FROM test_hibernate_oracle u WHERE u.name =?";
    Query<User> query = session.createQuery(sql, User.class);
    query.setParameter(0, name);
    List<User> users = query.list();
    return users;
}
```
4. Query all user data.
  
  Call the selectUser method to query all user data. Define an SQL statement to query all user data. Call the createNativeQuery method of the Session object to create a Query object and pass in the SQL statement as a parameter. Then, call the addEntity method to add the entity class User to the query, so that the query results can be converted into a list of User objects. Call the getResultList method of the Query object to execute the query and convert the query results into a list of User objects.
  
  The sample code is as follows:
```
public List<User> selectUser() {
    String sql = "SELECT * FROM test_hibernate_oracle";
    Query<User> query = session.createNativeQuery(sql).addEntity(User.class);
    List<User> users = query.getResultList();
    return users;
}
```
5. Insert user data.
  
  Call the insertUser method to insert user data. Specify the user parameter of the User type, which is passed in as the user object to be inserted. Call the beginTransaction method of the Session object to create a transaction object and assign the object to the beginTransaction variable. Call the save method to save the user object to the database. Call the getTransaction method of the Session object to obtain the current transaction, call the commit method to commit the transaction to persist the previous operation to the database, and return the insertion result.
  
  The sample code is as follows:
```
public int insertUser(User user) {
    // open transaction
    Transaction beginTransaction = session.beginTransaction();
    session.save(user);
    session.getTransaction().commit();
    return 1;
}
```
6. Delete user data.
  
  Call the deleteUserById method to delete the user record of the corresponding ID from the database. Specify the id parameter of the integer type to pass in the ID of the user to be deleted. Call the beginTransaction method of the Session object to start a new transaction. Call the get method to get the user object that matches the specified user ID and the entity class type User.class. Call the delete method to delete the user object. Call the getTransaction method of Session to obtain the current transaction, call the commit method to commit the transaction to persist the user delete operation to the database, and return the result of the delete operation.
  
  The sample code is as follows:
```
public int deleteUserById(int id) {
    // open transaction
    session.beginTransaction();
    User user = session.get(User.class, id);
    session.delete(user);
    session.getTransaction().commit();
    return 1;
}
```
7. Modify user data.
  
  Call the updateUserById method to update the corresponding user data. Specify the user parameter of the User type. Call the beginTransaction method of the Session object to start a transaction. Call the get method of the Session object to obtain the corresponding User object by user ID. Call the merge method of the Session object to merge the input User object with the obtained User object and update the input user data to the database. Call the getTransaction method of the Session object to obtain the current transaction, and call the commit method to commit the transaction. Return the result of the modify operation.
  
  The sample code is as follows:
```
public int updateUserById(User user) {
    // open transaction
    session.beginTransaction();
    User user1 = session.get(User.class, user.getId());
    session.merge(user);
    session.getTransaction().commit();
    return 1;
}
```

Code in User.java

The User.java file is used for the mapping to a database table.

Perform the following steps to configure the User.java file:

Reference other classes.

Declare that the current file uses JPA annotations to configure the mapping between a class and a database table.
- Column annotation: specifies the mapping between an attribute in the entity class and a column in the database table.
- Entity annotation: maps to a table in the database.
- GeneratedValue annotation: specifies that values of the attribute are automatically generated.
- GenerationType annotation: specifies the primary key generation strategy.
- Id annotation: marks an attribute as the unique identifier attribute.
- Table annotation: specifies the name of the table that the entity class is mapped to.
The sample code is as follows:
```
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
```
Map to a table in the database.

Map the entity class to a table named test_hibernate_oracle in the database.

The sample code is as follows:
```
@Entity
@Table(name = "test_hibernate_oracle")
```
Define the User class.

This class is mapped to a table named test_hibernate_oracle in the database.
1. Define the id and name attributes. Use annotations to mark the mappings between the id and name attributes of the User class and columns in the database table. Specifically, use the @Id annotation to mark the id attribute as the primary key, use the @GeneratedValue annotation to specify sequence as the primary key generation strategy, and use the @Column annotation to specify mappings between attributes in the class and columns in the database table. The id attribute stores the user ID, and the name attribute stores the username.
  
  The sample code is as follows:
```
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "user_id")
private int id;

@Column(name = "user_name")
private String name;
```
2. Create a User object.
  
  Define two constructors of the User class for creating a User object. Define a parameterless constructor for Hibernate query operations. Define a constructor with parameters for initializing the id and name attributes.
  
  The sample code is as follows:
```
public User() {
}

public User(int id, String name) {
    this.id = id;
    this.name = name;
}
```
3. Get and set the id and name values.
  
  Define four methods in the User class to get and set the values of the id and name attributes. The getId method gets the id value. The setId method sets the id value. The getName method gets the name value. The setName method sets the name value.
  
  The sample code is as follows:
```
public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}
```
4. Return a string that represents the User object.
  
  Override the toString method in the User class to return a string that represents the User object. Use the @Override annotation to override the method of the same name in the parent class. Define the toString method that returns a string representation of the User object. Concatenate the id and name values to generate a string and return it to the caller.
  
  The sample code is as follows:
```
@Override
public String toString() {
    return "User{" +
            "id=" + id +
            ", name='" + name + '\'' +
            '}';
}
```

Code in TestHibernate.java

The TestHibernate.java file uses the UserDao object to demonstrate basic database operations of Hibernate, such as the insert, delete, update, and query operations.

Perform the following steps to configure the TestHibernate.java file:

Reference other classes and APIs.

Declare the classes and interfaces related to the current file, such as UserDao, User, Session, SessionFactory, and Configuration.
- UserDao class: performs user-related database operations.
- User class: operates user objects.
- Session class: performs database session operations.
- SessionFactory class: creates session objects.
- Configuration class: configures Hibernate.
- SessionImpl class: obtains underlying JDBC connections.
- Test annotation: marks a test method.
- IOException class: handles I/O exceptions.
- Connection class: obtains JDBC connections.
- SQLException class: handles SQL exceptions.
- List class: stores query result sets.
- UUID class: generates unique identifiers.
The sample code is as follows:
```
import com.oceanbase.dao.UserDao;
import com.oceanbase.pojo.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.internal.SessionImpl;
import org.junit.Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.UUID;
```
Define the testHibernate method. The testHibernate method provides basic database operations such as inserting, deleting, updating, and querying data. Create a UserDao object and insert five user data records in a loop. Call the insertUser method to insert user data. Call the deleteUserById method to delete the data of the user with the ID 1. Call the updateUserById method to update the data of the user with the ID 2. Call the selectUser method to query all user data. Traverse the query results and print them to the console.

The sample code is as follows:
```
public class TestHibernate {
    @Test
    public void testHibernate() throws SQLException, IOException {
        UserDao userDao = new UserDao();
        for (int i = 1; i <= 5; i++) {
            userDao.insertUser(new User(i, "user_insert" + i));
        }
        int deleteUserById = userDao.deleteUserById(1);
        int update = userDao.updateUserById(new User(2, "update"));
        List<User> users = userDao.selectUser();
        users.forEach(System.out::println);
    }
}
```

Complete code

pom.xml

User.hbm.xml

hibernate.cfg.xml

UserDao.java

User.java

TestHibernate.java

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.oceanbase</groupId>
    <artifactId>java-oceanbase-hibernate</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>
        <dependency>
            <groupId>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.17.Final</version>
        </dependency>
        <!-- Hibernate c3p0 connection pool-->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-c3p0</artifactId>
            <version>5.2.17.Final</version>
        </dependency>

    </dependencies>

</project>

<?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 package="com.oceanbase.pojo">
    <class name="User" table="test_hibernate_oracle">
        <!-- Configure primary key generation strategy -->
        <id name="id" column="USER_ID">
            <generator class="sequence">
                <param name="sequence">SQ_USER</param>
            </generator>
        </id>
        <!-- Configuration Tables and Properties -->
        <property name="name" column="USER_NAME" type="string"/>
    </class>
</hibernate-mapping>

<?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.oceanbase.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:oceanbase://host:port/schema_name</property>
        <property name="hibernate.connection.username">user_name</property>
        <property name="hibernate.connection.password">******</property>
        <property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.max_size">60</property>
        <property name="hibernate.c3p0.min_size">30</property>

        <property name="hibernate.c3p0.checkoutTimeout">30000</property>
        <property name="hibernate.c3p0.timeout">20000</property>

        <property name="hibernate.c3p0.max_statements">100</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>
        <property name="hibernate.c3p0.validate">true</property>

        <property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
        <property name="hbm2ddl.auto">create</property>
        <property name="current_session_context_class">thread</property>
        <property name="hibernate.jdbc.batch_size">10</property>
        <property name="hibernate.cache.use_second_level_cache">false</property>
        <mapping resource="com/oceanbase/pojo/User.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

package com.oceanbase.dao;

import com.oceanbase.pojo.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

import java.util.List;


public class UserDao {
    private Session session;
    {
        Configuration cfg = new Configuration().configure();
        // Create SessionFactory
        SessionFactory sessionFactory = cfg.buildSessionFactory();
        session = sessionFactory.openSession();
    } //Read the hibernate.cfg.xml file
//    private Session session = HibernateUtil.getSession();

    public User selectUserById(int ID) {
        User user = session.get(User.class, ID);
        return user;
    }

    public List<User> selectUserbyName(String name) {
        String sql = "FROM test_hibernate_oracle u WHERE u.name =?";
        Query<User> query = session.createQuery(sql, User.class);
        query.setParameter(0, name);
        List<User> users = query.list();
        return users;
    }

    public List<User> selectUser() {
        String sql = "SELECT * FROM test_hibernate_oracle";
        Query<User> query = session.createNativeQuery(sql).addEntity(User.class);
        List<User> users = query.getResultList();
        return users;
    }

    public int insertUser(User user) {
        // open transaction
        Transaction beginTransaction = session.beginTransaction();
        session.save(user);
        session.getTransaction().commit();
        return 1;
    }

    public int deleteUserById(int id) {
        // open transaction
        session.beginTransaction();
        User user = session.get(User.class, id);
        session.delete(user);
        session.getTransaction().commit();
        return 1;
    }

    public int updateUserById(User user) {
        // open transaction
        session.beginTransaction();
        User user1 = session.get(User.class, user.getId());
        session.merge(user);
        session.getTransaction().commit();
        return 1;
    }

}

package com.oceanbase.pojo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name = "test_hibernate_oracle")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Column(name = "user_id")
    private int id;

    @Column(name = "user_name")
    private String name;

    public User() {
    }

    public User(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

import com.oceanbase.dao.UserDao;
import com.oceanbase.pojo.User;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.internal.SessionImpl;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.UUID;


public class TestHibernate {
    @Test
    public void testHibernate() throws SQLException, IOException {
        UserDao userDao = new UserDao();
        for (int i = 1; i <= 5; i++) {
            userDao.insertUser(new User(i, "user_insert" + i));
        }
        int deleteUserById = userDao.deleteUserById(1);
        int update = userDao.updateUserById(new User(2, "update"));
        List<User> users = userDao.selectUser();
        users.forEach(System.out::println);
    }
}

References

For more information about OceanBase Connector/J, see OceanBase Connector/J.

Connect to OceanBase Cloud by using Hibernate

Prerequisites

Note

Procedure

Note

Step 1: Import the java-oceanbase-hibernate project to IntelliJ IDEA

Note

Step 2: Modify the database connection information in the java-oceanbase-hibernate project

Note

Step 3: Run the java-oceanbase-hibernate project

Run path

Running result

Project code

Code in pom.xml

Note

Code in User.hbm.xml

Code in hibernate.cfg.xml

Note

Code in UserDao.java

Code in User.java

Code in TestHibernate.java

Complete code

References

Step 1: Import the `java-oceanbase-hibernate` project to IntelliJ IDEA

Step 2: Modify the database connection information in the `java-oceanbase-hibernate` project

Step 3: Run the `java-oceanbase-hibernate` project