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, data query, and other basic operations.

Download the java-oceanbase-hibernate sample project

Prerequisites

• You have installed OceanBase Database.
• You have installed Java Development Kit (JDK) 1.8 and Maven.
• You have installed IntelliJ IDEA.

Procedure

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

Step 1: Obtain the connection string of OceanBase Database

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

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

2. Fill in the URL below based on the OceanBase Database that is deployed.

   Note

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

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

   Parameter description:

   • host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use 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 be accessed.
   • user_name: the username specified after the -u parameter, in the format of username@tenant name#cluster name or username@SERVICE:service name. If you use the format username@tenant name#cluster name, the default tenant is sys and the default administrator user is root. The cluster name can be omitted when you connect directly to the database, but it is required when you connect through ODP.
   • password: the password of the account.

For more information about URL parameters, see Database URL.

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

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

   

2. In the Open File or Project window, select the project files and click OK to import the files.

3. IntelliJ IDEA automatically recognizes the files and displays the directory structure, file list, module list, dependency relationships, and other details of the project in the Project window. The Project window is usually positioned on the left side of the IntelliJ IDEA interface and is generally open by default. If the Project window is closed, you can reopen it by choosing View > Tool Windows > Project in the menu bar or by using the shortcut Alt + 1.

   Note

   When you import a project using IntelliJ IDEA, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them 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 connection string of OceanBase Database.

Here is an example:

• The name of the database driver is: com.mysql.cj.jdbc.Driver.
• The IP address of the OBServer node is 172.30.xxx.xxx.
• The port is 2881.
• The name of the database to be accessed is test.
• The tenant account is root@mysql001. Here, mysql001 is a user tenant created in OceanBase Database with the MySQL mode, and root is the username of the mysql001 tenant.
• The password is ******.

Sample code:

<property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://172.30.xxx.xxx:2881/test</property>
<property name="hibernate.connection.username">root@mysql001</property>
<property name="hibernate.connection.password">******</property>

Step 4: Run the java-oceanbase-hibernate project

Running path

1. In the project structure, locate the TestHibernate.java file under src > test > java.
2. In the toolbar, choose Run(U) > Run > TestHibernate or simply click the green triangle in the upper right corner to start the run.
3. View the project logs and output results in the console of IntelliJ 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 introduction

Click java-oceanbase-hibernate to download the project code, which is a compressed package 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

File description:

• pom.xml: the configuration file of the Maven project, which contains the dependencies, plugins, and build details of the project.
• .idea: the directory used by the Integrated Development Environment (IDE) to store project-related configuration information.
• src: a directory that usually indicates the source code of the project.
• main: the directory that stores the main source code and resource files.
• java: the directory that stores the Java source code.
• com: the root directory that stores the Java package.
• oceanbase: the root directory that stores the project.
• dao: the directory that stores the Data Access Object (DAO) package for accessing databases or other data storage services.
• UserDao.java: the user DAO used for CRUD operations on user data.
• User.java: the persistent object that maps to the fields of the user data table.
• pojo: the Plain Old Java Object (POJO) that corresponds to the Java class of the database table. It is used to map database tables or other data storage structures.
• resources: the directory that stores resource files such as configuration files and SQL files.
• hibernate.cfg.xml: the Hibernate configuration file that stores basic parameters of Hibernate and data source information.
• User.hbm.xml: the mapping file that stores the persistent object of users and defines the mapping relationships with the user data table.
• test: the directory that stores the test code and resource files.
• TestHibernate.java: the Java class used for testing Hibernate.
• target: the directory that stores compiled class files and JAR packages.

Introduction to pom.xml

The content of the pom.xml configuration file is as follows:

1. Declaration statement.

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

   Sample code:

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

2. Configure namespaces and the POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0, and the URI of the corresponding XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to set the POM model version used by the POM file to 4.0.0.

   Sample code:

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

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

   Sample code:

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

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

   1. Use <plugins> to specify the plugins configured for the project.
   2. Use <plugin> to specify one plugin configured for the project.
   3. Use <groupId> to specify the project identifier as org.apache.maven.plugins.
   4. Use <artifactId> to specify the project dependency as maven-compiler-plugin.
   5. Use <configuration> to specify the parameters of the configured plugin.
   6. Use <source> to specify the source code version of the compiler as 8.
   7. Use <target> to specify the target code version of the compiler as 8.

   Sample code:

    <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. Use <dependencies> to define the components on which the project depends.

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

      Sample code:

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

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

      Sample code:

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

   3. Specify the group ID as org.hibernate, artifact ID as hibernate-core, and version as 5.2.17.Final for the dependency.

      Sample code:

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

   4. Specify the group ID as org.hibernate, artifact ID as hibernate-c3p0, and version as 5.2.17.Final for the dependency.

      Sample code:

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

Introduction to User.hbm.xml

The User.hbm.xml file is a Hibernate mapping file that maps a Java object to a database table.

The code in the User.hbm.xml file mainly consists of the following sections:

1. Declaration statements.

   The declaration statements indicate that the file is an XML file that uses XML standard 1.0 and character encoding UTF-8. They also specify the DTD version and location of the Hibernate mapping file. The DTD type is hibernate-mapping, version 3.0, language EN, and URL http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd.

   Sample code:

   <?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. Configuration of the mapping file.

   The file defines a Hibernate mapping file that maps the User entity class to the test_hibernate_mysql table in the database.

   1. The package attribute specifies the Java package in the mapping file as com.oceanbase.pojo.
   2. The class tag maps data in the data table. The name attribute specifies the Java class name as User, and the table attribute specifies the database table name as test_hibernate_mysql.
   3. The id tag defines the primary key attribute. The name attribute specifies the member variable of the Java class User as id, and the column attribute specifies the database field name as USER_ID.
   4. The generator tag defines the primary key generation strategy. The class attribute specifies the type of the primary key generator as sequence, the param element specifies the sequence name in the database as SQ_USER, the name attribute specifies the USER_NAME column in the database table, and the attribute specifies the data type of this attribute as string.

   Sample code:

   <hibernate-mapping package="com.oceanbase.pojo">
   <class name="User" table="test_hibernate_mysql">
       <!-- 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>
   

Introduction to hibernate.cfg.xml

The hibernate.cfg.xml file is a Hibernate configuration file used to configure the runtime environment and database connection parameters of Hibernate.

The code in the hibernate.cfg.xml file mainly includes the following sections:

1. Declaration statement.

   The statement declares that this file is an XML file that uses XML standard 1.0 and character encoding UTF-8. It also specifies the DTD version and location of the Hibernate configuration file, setting the DTD document type to hibernate-configuration, version to 3.0, and language to EN, and specifying the URL of the file as http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd.

   Sample code:

   <?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. Configuration of configuration parameters.

   The root element contains the element, which is used to create and manage session objects. The session factory includes the driver class name, URL, username, and password for connecting to the database, connection pool parameters, database dialect, SQL output and formatting, automatic table creation, current session context, batch processing, and secondary cache.

   1. Database information configuration.

      Configure the parameters for connecting Hibernate to the database, including the driver class name, URL, username, and password for connecting to the database.

      • Use hibernate.connection.driver_class to specify the database driver com.mysql.cj.jdbc.Driver for connecting to OceanBase Database.
      • Use hibernate.connection.url to specify the database URL, which includes the database address, port number, and database name.
      • 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.

      Sample code:

          <property name="hibernate.connection.driver_class">com.mysql.cj.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. Connection pool information configuration.

      Configure the parameters for the connection pool used by Hibernate. Specify the connection pool as c3p0, and set parameters such as the maximum and minimum number of connections in the connection pool, the timeout period, the maximum idle time of a connection, the number of cached Statement objects, the idle test period for connections, the number of connections to be obtained at a time, and the connection validation method.

      Note

      The following parameters are provided for reference only. They are not a complete list of supported parameters. If you want to configure other parameters, refer to the relevant documentation for more information.

      • Use hibernate.connection.provider_class to specify c3p0 as the connection pool for managing the creation and release of database connections.
      • Use hibernate.c3p0.max_size to specify the maximum number of connections in the connection pool as 60.
      • Use hibernate.c3p0.min_size to specify the minimum number of connections in the connection pool as 30.
      • Use hibernate.c3p0.checkoutTimeout to specify the connection timeout period in the connection pool as 30000 ms.
      • Use hibernate.c3p0.timeout to specify the maximum idle time of a connection in the connection pool as 2000 ms, after which the connection will be closed.
      • Use hibernate.c3p0.max_statements to specify the maximum number of cached SQL statements in the connection pool as 100.
      • Use hibernate.c3p0.idle_test_period to specify the idle connection detection period in the connection pool as 3000 ms.
      • Use hibernate.c3p0.acquire_increment to specify the number of connection increments when the connection pool dynamically grows as 3.
      • Use hibernate.c3p0.validate to specify the connection validation method as true.

      Sample code:

      <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. SQL interaction information configuration.

      Configure the parameters for interacting Hibernate with the database. The parameters include the database dialect, SQL statement printing and formatting, automatic table creation, current session context, batch processing size, cache usage, and mapping file loading.

      • Use dialect to specify the database dialect, such as MySQL 5.7, to ensure compatibility with the specific database.
      • Use hibernate.show_sql to specify whether to print the SQL statements generated by Hibernate on the console.
      • Use hibernate.format_sql to specify whether to format the printed SQL statements.
      • Use hbm2ddl.auto to specify automatic table creation.
      • Use current_session_context_class to specify the thread-level current session context.
      • Use hibernate.jdbc.batch_size to specify the batch processing size.
      • Use hibernate.cache.use_second_level_cache to specify whether to enable the secondary cache.
      • Load the mapping file com/oceanbase/pojo/User.hbm.xml.

      Sample code:

Introduction to UserDao.java

The UserDao.java file uses a Session object to implement the add, delete, modify, and query operations on user data through the user data access object (DAO) class.

The code in the UserDao.java file is divided into the following sections:

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: used for interacting with the database.
   • SessionFactory class: used for creating Session instances.
   • Transaction class: used for managing database transactions.
   • Configuration class: used for loading the Hibernate configuration file.
   • Query class: used for executing query operations.
   • List interface: used for operating the query result set.

   Sample code:

   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 UserDao class is a data access object that uses the Java class to perform persistent operations on user objects. The class encapsulates a series of methods for interacting with the database and performing add, delete, modify, and query operations on user objects. You can conveniently interact with the database and perform persistent operations by calling methods in the UserDao class.

1. Initialize the UserDao class.

   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 it to the session variable.

   Sample code:

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

2. Retrieve user information corresponding to an ID.

   Call the selectUserById method to query user information corresponding to the provided ID.

   Use the session object's get method to retrieve the User record corresponding to the specified ID from the database and store it in the user variable.

   Sample code:

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

3. Retrieve user information based on the username.

   Call the selectUserbyName method to query user information based on the username. First, define an Hibernate Query Language (HQL) statement for querying user data with the specified name. The table name is test_hibernate_mysql, the table alias is u, and the query field name is name. Create a Query object by calling the createQuery method of the Session object and passing in the HQL statement and the entity class as parameters. Use the setParameter method of the Query object to set the query parameters, where 0 indicates the parameter position and name indicates the parameter value. Use the list method of the Query object to execute the query and convert the query result to a User object list.

   Sample code:

   public List<User> selectUserbyName(String name) {
     String sql = "FROM test_hibernate_mysql 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 for querying all user data. Create a Query object by calling the createNativeQuery method of the Session object and passing in the SQL statement as a parameter. Then, use the addEntity method to add the entity class User to the query so that the query result can be converted to a User object list. Call the getResultList method of the Query object to execute the query and convert the query result to a User object list.

   Sample code:

   public List<User> selectUser() {
       String sql = "SELECT * FROM test_hibernate_mysql";
       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 information. Specify a User-typed parameter user for passing the user object to be inserted. Call the beginTransaction method of the Hibernate Session to create a transaction object and save it in the beginTransaction variable. Use the save method to save the user object to the database. Call the getTransaction method of the Session to obtain the current transaction and call the commit method to commit the transaction, persist the previous operations to the database, and return the result of the insertion operation.

   Sample code:

   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 corresponding to the specified ID from the database. Specify an integer parameter id for passing the ID of the user to be deleted. Call the beginTransaction method of the Hibernate Session to start a new transaction. Use the get method to obtain the User object corresponding to the specified user ID and the entity class type User.class. Use the delete method to delete the user object. Call the getTransaction method of the Session to obtain the current transaction and call the commit method to commit the transaction, delete the user information from the database, and return the result of the deletion operation.

   Sample code:

   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 information. Specify a User-typed parameter user for specifying the user data to be updated. Call the beginTransaction method of the Session object to start a transaction. Use the get method of the Session object to obtain the User object corresponding to the user ID. Use the merge method of the Session object to merge the passed User object with the obtained User object and update the passed 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, persist the modification operation to the database, and return the result of the modification operation.

   Sample code:

   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;
   }
   

Introduction to User.java

The User.java file performs CRUD operations on user data by using the Session object through a Data Access Object (DAO) class.

The code in the User.java file is divided into the following sections:

1. References other classes.

   The current file uses JPA annotations to configure the mappings between classes and database tables.

   • The Column annotation is used to identify the correspondence between attributes in the entity class and columns in the database table.
   • The Entity annotation is used to map the tables in the database.
   • The GeneratedValue annotation is used to indicate that the value of the attribute is automatically generated.
   • The GenerationType annotation is used to specify the primary key generation strategy.
   • The Id annotation is used to specify the unique identifier attribute.
   • The Table annotation is used to indicate the name of the table corresponding to the entity class.

   Sample code:

   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. Maps a database table.

   The entity class maps to a database table, and the value of the name attribute of the entity class is test_hibernate_mysql.

   Sample code:

   @Entity
   @Table(name = "test_hibernate_mysql")
   

3. Defines the User class.

   The class is mapped to a database table named test_hibernate_mysql.

   1. Defines two attributes, id and name. The @Column annotation identifies the correspondence between the id and name attributes in the User class and the columns in the database table. The @Id annotation indicates that the id attribute is the primary key, and the @GeneratedValue annotation specifies that the primary key is automatically generated by using a sequence. The @Column annotation specifies the correspondence between the attributes and the columns in the database table. The id attribute stores the user ID, and the name attribute stores the username.

      Sample code:

      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE)
      @Column(name = "user_id")
      private int id;
      
      @Column(name = "user_name")
      private String name;
      

   2. Creates a User object.

      Defines two constructors of the User class to create a User object. Defines a constructor without parameters to support Hibernate query operations. Defines a constructor with parameters to initialize the id and name attributes.

      Sample code:

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

   3. Gets and sets the values of id and name.

      Defines four methods in the User class to get and set the values of the id and name attributes. The getId method is used to get the id value, and the setId method is used to set the id value. The getName method is used to get the value of the username name, and the setName method is used to set the username name.

      Sample code:

      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. Returns the string representation of a User object.

      Rewrites the toString method in the User class to return the string representation of a User object. Defines the @Override annotation to override the same-named method in the parent class. Defines the toString method to return the string representation of a User object. Concatenates the values of the id and name attributes to generate a string, which is returned to the caller User.

      Sample code:

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

Introduction to TestHibernate.java

The TestHibernate.java file demonstrates the basic database operations of Hibernate by performing insert, delete, update, and query operations on the UserDao object.

The code in the TestHibernate.java file consists of the following sections:

1. Reference other classes and interfaces.

   Declare the classes and interfaces related to the current file, including UserDao, User, Session, SessionFactory, Configuration, etc.

   • UserDao class: used to perform database operations related to users.
   • User class: used to operate user objects.
   • Session class: used to perform session operations with the database.
   • SessionFactory class: used to create Session objects.
   • Configuration class: used to configure Hibernate.
   • SessionImpl class: used to obtain the underlying JDBC connection.
   • Test annotation: used to mark test methods.
   • IOException class: used to handle I/O exceptions.
   • Connection class: used to obtain a JDBC connection.
   • SQLException class: used to handle SQL exceptions.
   • List class: used to store the query result set.
   • UUID class: used to generate a unique identifier.

   Sample code:

   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 contains basic operations such as insertion, deletion, modification, and query. Create a UserDao object. Insert five user data entries in a loop. Call the insertUser method to insert user data. Call the deleteUserById method to delete the user data with an ID of 1. Call the updateUserById method to modify the user data with an ID of 2. Call the selectUser method to query all user data. Traverse the query results and print them to the console.

   Sample code:

   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

<?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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</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_mysql">
        <!-- 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.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://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.MySQL57Dialect</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_mysql 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_mysql";
        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_mysql")
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.