Connect to OceanBase Database by using Hibernate

This topic introduces how to build an application by using the Hibernate framework and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the java-oceanbase-hibernate sample project

Prerequisites

• You have installed OceanBase Database.
• You have installed JDK 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

1. Obtain the OceanBase Database connection string.
2. Import the java-oceanbase-hibernate project into IDEA.
3. Modify the database connection information in the java-oceanbase-hibernate project.
4. Run the java-oceanbase-hibernate project.

Step 1: Obtain the OceanBase Database connection string

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection string.

   obclient -hxx.xx.xx.xx -P2883 -uroot@sys#cluster -p**** -A
   

2. Fill in the URL below based on the deployed OceanBase database.

   Note

   The URL here is required in the hibernate.cfg.xml file.

   jdbc:oceanbase://host:port/schema_name?user=$user_name&password=$password
   

   Parameters in the URL are described as follows:

   • host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • schema_name: the name of the schema to access.
   • user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • password: the account password.

   For more information about URL parameters, see Database URL.

Step 2: Import the java-oceanbase-hibernate project into IDEA

1. Start IntelliJ IDEA and choose File > Open....

   

2. In the Open File or Project window that appears, select the corresponding project file and click OK to import the project file.

3. IntelliJ IDEA automatically identifies all types of files in the project. In the Project window, you can view the directory structure, list of files, list of modules, and dependencies of the project. The Project window is usually on the far left side in IntelliJ IDEA and is displayed by default. If the Project window is closed, you can choose View > Tool Windows > Project from the menu or use the Alt + 1 shortcut to open the window.

   Note

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

4. View the project.

   

Step 3: 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 information obtained in Step 1: Obtain the OceanBase Database connection string.

Here is an example:

• The name of the database driver is com.oceanbase.jdbc.Driver.
• The IP address of the OBServer node is 10.10.10.1.
• The port is 2881.
• The name of the schema to access is sys.
• The tenant account is sys@xyoracle, where xyoracle is a user tenant created in the Oracle-compatible mode of OceanBase Database, and sys is a username in the xyoracle tenant.
• The password is ******.

The sample code is as follows:

<property name="hibernate.connection.driver_class">com.oceanbase.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:oceanbase://10.10.10.1:2881/sys</property>
<property name="hibernate.connection.username">sys@xyoracle</property>
<property name="hibernate.connection.password">******</property>

Step 4: Run the java-oceanbase-hibernate project

Run path

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

Output

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 introduction

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

After decompressing it, you will find 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

Here is a breakdown of the files and directories:

• pom.xml: the configuration file of the Maven project, which contains the dependencies, plug-ins, and build information of the project.
• .idea: the directory used in the Integrated Development Environment (IDE) for storing project-related configurations.
• src: the directory for storing source code of the project.
• main: the directory for storing main source code and resource files.
• java: the directory for storing Java source code.
• com: the root directory for storing the Java package.
• oceanbase: the root directory for storing the project.
• dao: stores the data access object (DAO) package for accessing databases or other data storage services.
• UserDao.java: the user DAO object 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: the 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 user data table.
• test: the directory that stores the test code and resource files.
• TestHibernate.java: the directory that stores the Java class for testing Hibernate.
• target: the directory that stores compiled class files and JAR packages.

Code in pom.xml

To configure the pom.xml file, perform the following steps:

1. Declare the file.

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

   The sample code is as follows:

   <?xml version="1.0" encoding="UTF-8"?>
   

2. Configure the namespaces and the POM model version.

   1. Use xmlns to specify http://maven.apache.org/POM/4.0.0 as the default XML namespace for the POM.
   2. Use xmlns:xsi to specify http://www.w3.org/2001/XMLSchema-instance as the XML namespace for xsi-prefixed elements.
   3. Use xsi:schemaLocation to provide a mapping from the default XML namespace for the POM (http://maven.apache.org/POM/4.0.0) to the location of the POM's XML schema definition (XSD) file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. Use <modelVersion> to specify 4.0.0 as the model version used by the POM.

   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>
   

3. Configure basic project information.

   1. Use <groupId> to specify com.oceanbase as the ID of the project group.
   2. Use <artifactId> to specify java-oceanbase-hibernate as the ID of the project.
   3. Use <version> to specify 1.0-SNAPSHOT as the project version.

   The sample code is as follows:

    <groupId>com.oceanbase</groupId>
    <artifactId>java-oceanbase-hibernate</artifactId>
    <version>1.0-SNAPSHOT</version>
   

4. Use <build> to define the build process for the project.

   1. Use <plugins> to list plug-ins in the project.
   2. Use <plugin> to specify a plug-in for the project.
   3. Use <groupId> to specify org.apache.maven.plugins as the ID of the plug-in group.
   4. Use <artifactId> to specify maven-compiler-plugin as the ID of the plug-in.
   5. Use <configuration> to configure parameters of the plug-in.
   6. Use <source> to specify 8 as the source code version for the compiler.
   7. Use <target> to specify 8 as the version of the code generated by the compiler.

   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>
   

5. Configure project dependencies in <dependencies>.

   1. Specify com.oceanbase as the ID of the dependency group, oceanbase-client as the dependency ID, and 2.4.2 as the dependency version.

      The sample code is as follows:

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

   2. Specify junit as the ID of the dependency group, junit as the dependency ID, and 4.13 as the dependency version.

      The sample code is as follows:

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

   3. Specify org.hibernate as the ID of the dependency group, hibernate-core as the dependency ID, which specifies the core library, and 5.2.17.Final as the dependency version.

      The sample code is as follows:

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

   4. Specify org.hibernate as the ID of the dependency group, hibernate-c3p0 as the dependency ID, which specifies the data source library, and 5.2.17.Final as the dependency version.

      The sample code is as follows:

      <dependencies>
        <dependency>
            <groupId>org.hibernate</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.

To configure the User.hbm.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and 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">
   

2. 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 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 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 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.

To configure the hibernate.cfg.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and 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">
   

2. Configure properties in <hibernate-configuration>.

   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.

      • Use hibernate.connection.driver_class to specify com.oceanbase.jdbc.Driver as the database driver, which is used to establish a connection with OceanBase Database.
      • Use hibernate.connection.url to specify the URL of the database, which contains the IP address, port number, and name of the database.
      • Use hibernate.connection.username to specify the username for connecting to the database.
      • Use hibernate.connection.password to specify 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 connection pool properties 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 for reference only. If you need to configure other properties, refer to the relevant documentation for more detailed property information.

      • Use hibernate.connection.provider_class to specify c3p0 as the connection pool, which is used to manage the creation and release of database connections.
      • Use hibernate.c3p0.max_size to specify 60 as the maximum number of connections in the connection pool.
      • Use hibernate.c3p0.min_size to specify 30 as the minimum number of connections in the connection pool.
      • Use hibernate.c3p0.checkoutTimeout to specify 30,000 milliseconds as the timeout for obtaining a connection from the connection pool.
      • Use hibernate.c3p0.timeout to specify 2,000 milliseconds as the timeout after which an idle connection in the connection pool is closed.
      • Use hibernate.c3p0.max_statements to specify 100 as the maximum number of SQL statements that can be cached in the connection pool.
      • Use hibernate.c3p0.idle_test_period to specify 3,000 milliseconds as the interval at which idle connections in the connection pool are tested.
      • Use hibernate.c3p0.acquire_increment to specify 3 as the number of connections to acquire at a time by the connection pool when the pool is exhausted.
      • Use hibernate.c3p0.validate to specify true as the connection validation method.

      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.

      • Use dialect to specify Oracle 12c as the database dialect, which ensures compatibility with OceanBase Database in Oracle-compatible mode.
      • Use hibernate.show_sql to specify whether to print SQL statements generated by Hibernate in the console.
      • Use hibernate.format_sql to specify whether to format the SQL statements to print.
      • Use hbm2ddl.auto to specify to automatically create tables.
      • Use current_session_context_class to specify to use thread-level current session context.
      • Use hibernate.jdbc.batch_size to specify the batch size.
      • Use hibernate.cache.use_second_level_cache to specify whether to use second-level cache.
      • Specify com/oceanbase/pojo/User.hbm.xml 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.

To configure the UserDao.java file, perform the following steps:

1. Reference other classes and interfaces.

   Declare that the current file contains the following interfaces and classes:

   • User class: used for operating 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: handles query result sets.

   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;
   

2. 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 user name.

      Call the selectUserbyName method to query user data by user name. First, define a Hibernate Query Language (HQL) statement to query user data with a specified user name. 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 a 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, and call the commit method to commit the transaction. 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, and call the commit method to commit the transaction. 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.

To configure the User.java file, perform the following steps:

1. 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: marks the class as an entity class.
   • 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;
   

2. Map 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")
   

3. 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 user name.

      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 no-parameter constructor for Hibernate query operations. Define a constructor with parameters for initializing id and name.

      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 id and name. The getId method gets the id value, and 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.

To configure the TestHibernate.java file, perform the following steps:

1. Reference other classes and interfaces.

   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: used for operating 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 interface: 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;
   

2. 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 user data with ID 1. Call the updateUserById method to update user data with 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 examples

<?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.