Skip to main content

Hibernate connection to seekdb

This topic describes how to use the Hibernate framework and seekdb to build an application that performs basic operations such as creating a table, inserting data, and querying data.

Download the java-oceanbase-hibernate sample project

Prerequisites​

  • You have installed seekdb.

  • You have installed JDK 1.8 and Maven.

  • You have installed IntelliJ IDEA.

    info

    The tool used to run the code in this topic is IntelliJ IDEA 2021.3.2 (Community Edition). You can also use your preferred tool to run the sample code.

Procedure​

info

The following procedure is based on the Windows environment. If you use other operating systems or compilers, the procedure may be slightly different.

  1. Obtain the connection string of seekdb.
  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 seekdb connection string​

  1. Contact the seekdb deployment personnel or administrator to obtain the corresponding database connection string.

    mysql -hxx.xx.xx.xx -P2881 -uroot -p**** -A

  2. Fill in the following URL based on the deployed seekdb.

    info

    The URL information is needed 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 seekdb. Replace it with the actual IP address. You can also use the local IP address or 127.0.0.1.
    • port: the port for connecting to seekdb. Replace it with the actual port. The default port is 2881, which can be customized when deploying seekdb.
    • schema_name: the name of the schema to be accessed.
    • user_name: the username, specified by the -u parameter. The default username is root.
    • password: the password for the account.

For more information about URL parameters, see Database URL.

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

  1. Open IntelliJ IDEA and select File > Open....

    file

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

  3. IntelliJ IDEA will automatically recognize various files in the project and display the project structure, file list, module list, and dependencies in the Project tool window. The Project tool window is usually located on the left side of the IntelliJ IDEA interface and is typically open by default. If it is closed, you can reopen it by selecting View > Tool Windows > Project from the menu bar or by using the shortcut key Alt + 1.

    info

    When importing a project using IntelliJ IDEA, it will automatically detect the pom.xml file in the project and download the required dependency libraries based on the described dependencies in the file, and add them to the project.

  4. View the project.

hibernate

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 seekdb connection string.

tip

For more information about additional properties of the JDBC connection string, see Database URL.

Example:

  • The name of the database driver is: com.mysql.cj.jdbc.Driver
  • The IP address of seekdb is 172.30.xxx.xxx.
  • The access port is 2881.
  • The name of the schema to be accessed is test.
  • The connection account is root.
  • The password is ******.

Example 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</property>
<property name="hibernate.connection.password">******</property>

Step 4: Run the java-oceanbase-hibernate project​

Running path​

  1. Find the TestHibernate.java file in src > test > java in the project structure.
  2. In the tool menu bar, select Run(U) > Run > TestHibernate, or click the green triangle in the upper right corner to run.
  3. View the project's log information and output results in the IDEA console.

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

FAQ​

1. Connection timeout​

If you encounter a connection timeout issue, you can configure the connection timeout parameter in the JDBC URL:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. Character set issues​

To ensure correct character encoding, set the appropriate character set parameter in the JDBC URL:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection​

To enable SSL connections with seekdb, add the following parameter to the JDBC URL:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in username or password​

If the username or password contains special characters (such as #), you need to URL-encode them:

String encodedPassword = URLEncoder.encode(password, "UTF-8");
tip

When using MySQL Connector/J 8.x, ensure that the username and password do not contain the # character. Otherwise, you may encounter a connection error.

Project code​

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

After decompressing the file, 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 information about the project's dependencies, plugins, and build settings.
  • .idea: A directory used by the IDE (Integrated Development Environment) to store project-related configuration information.
  • src: A directory typically used to store the source code of the project.
  • main: A directory for storing the main source code and resource files.
  • java: A directory for storing Java source code.
  • com: The root directory for storing Java packages.
  • oceanbase: The root directory for storing the project.
  • dao: A directory for storing Data Access Object (DAO) packages, which are used to access databases or other data storage services.
  • UserDao.java: A user DAO, which is used to perform operations such as adding, deleting, modifying, and querying user data.
  • User.java: A user persistence object, which is used to map the fields of the user data table.
  • pojo: Plain Old Java Object (POJO), which is used to store Java classes corresponding to database tables. It is used to map database tables or other data storage structures.
  • resources: A directory for storing resource files, such as configuration files and SQL files.
  • hibernate.cfg.xml: A configuration file for Hibernate, which is used to configure basic parameters and set up the data source.
  • User.hbm.xml: A mapping file for the user persistence object, which defines the mapping relationship between the user persistence object and the user data table.
  • test: A directory for storing test code and resource files.
  • TestHibernate.java: A Java class for testing Hibernate.
  • target: A directory for storing compiled Class files, Jar packages, and other files.

Introduction to the pom.xml file​

tip

If you just want to verify the example, you can use the default code without any modifications. You can also modify the pom.xml file based on your specific requirements, as explained below.

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

  1. File declaration statement.

    This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

    Code:

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

  2. Configure the namespaces and 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 location of the POM 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.

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

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

    Code:

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

  4. Use <build> to define the project's build process.

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

    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 organization of the dependency as com.oceanbase, the name as oceanbase-client, and the version as 2.4.2.

      Code:

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

    2. Specify the test architecture of the dependency as junit, the name as junit, and the version as 4.13.

      Code:

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

    3. Specify the architecture of the dependency as org.hibernate, the core library as hibernate-core, and the version as 5.2.17.Final.

      Code:

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

    4. Specify the architecture of the dependency as org.hibernate, the data source library as hibernate-c3p0, and the version as 5.2.17.Final.

      Code:

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

Introduction to the User.hbm.xml file​

The User.hbm.xml file is a Hibernate mapping file used to map Java objects to database tables.

The code of the User.hbm.xml file mainly includes the following parts:

  1. File declaration statement.

    This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8. It also specifies the DTD version and location of the Hibernate mapping file, setting the DTD document type to hibernate-mapping, the version to 3.0, the language to EN, and the URL of the file to http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd.

    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. Configure the mapping file.

    Define a Hibernate mapping file to map the entity class named User to the database table named test_hibernate_mysql.

    1. Use the package attribute to set the Java package in the mapping file to com.oceanbase.pojo.
    2. Use the class tag to map data from the data table, setting the name attribute to the Java class name User and the table attribute to the database table name test_hibernate_mysql.
    3. Use id to define the primary key attribute, setting the name attribute to the member variable id of the Java class User and the column attribute to the database field name USER_ID.
    4. Use generator to define the primary key generation strategy, setting the class attribute to the type of the primary key generator sequence, the param element to the sequence name in the database SQ_USER, the name attribute to the column USER_NAME in the database table corresponding to this attribute, and specifying the data type of this attribute as a string.

    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 the hibernate.cfg.xml file​

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

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

  1. File declaration statement.

    This statement declares the file as an XML file using XML version 1.0 and character encoding UTF-8. It also specifies the DTD version and location for the Hibernate configuration file, setting the DTD document type to hibernate-configuration, version 3.0, language EN, and the URL to 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. Configure the configuration parameter.

    The <hibernate-configuration> root element contains the <session-factory> element, which sets up the Hibernate session factory for creating and managing session objects. The session factory includes parameters such as the database driver class name, URL, username, password, connection pool settings, database dialect, SQL output and formatting, automatic table creation, current session context, batch processing, and second-level caching.

    1. Configure database information.

      This section configures the parameters for connecting to the database, including the driver class name, URL, username, and password.

      • Use hibernate.connection.driver_class to specify the database driver class as com.mysql.cj.jdbc.Driver for establishing a connection with seekdb.
      • Use hibernate.connection.url to specify the database URL, including 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. Configure connection pool information.

      This section configures the parameters for the connection pool used by Hibernate. It specifies the connection pool as C3P0, with parameters including the maximum and minimum number of connections, timeout, maximum idle time, number of cached Statement objects, idle connection test time, number of connections acquired at once, and connection validation method.

      info

      The following parameters are for reference only and do not represent the complete list of supported parameters. For more detailed parameter information, please refer to the relevant documentation.

      • Use hibernate.connection.provider_class to specify the connection pool as C3P0 for managing database connection creation and release.
      • Use hibernate.c3p0.max_size to set the maximum number of connections in the connection pool to 60.
      • Use hibernate.c3p0.min_size to set the minimum number of connections in the connection pool to 30.
      • Use hibernate.c3p0.checkoutTimeout to set the timeout for obtaining a connection in the connection pool to 30000 milliseconds.
      • Use hibernate.c3p0.timeout to set the maximum idle time in the connection pool to 2000 milliseconds, after which the connection will be closed.
      • Use hibernate.c3p0.max_statements to set the maximum number of cached SQL statements in the connection pool to 100.
      • Use hibernate.c3p0.idle_test_period to set the idle connection detection period in the connection pool to 3000 milliseconds.
      • Use hibernate.c3p0.acquire_increment to set the connection increment when the connection pool dynamically grows to 3.
      • Use hibernate.c3p0.validate to set the connection validation to 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. Configure SQL interaction information. This section configures the parameters for connecting Hibernate to the database, including the database dialect, SQL printing and formatting, automatic table creation, current session context, batch size, cache usage, and loading of mapping files.

      • Use dialect to specify the database dialect as MySQL 5.7 to ensure compatibility with the specific database.
      • Use hibernate.show_sql to set whether to print the SQL statements generated by Hibernate on the console.
      • Use hibernate.format_sql to set whether to format the printed SQL statements.
      • Use hbm2ddl.auto to set whether to automatically create tables.
      • Use current_session_context_class to specify the current session context at the thread level.
      • Use hibernate.jdbc.batch_size to set the batch size.
      • Use hibernate.cache.use_second_level_cache to set whether to enable second-level caching.
      • Specify the mapping file to load as com/oceanbase/pojo/User.hbm.xml.

      Sample code:

        <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"/>

Introduction to UserDao.java​

The UserDao.java file implements the operations of adding, deleting, modifying, and querying user data by using the Session object in the user data access object (DAO) class.

The code in the UserDao.java file mainly includes the following parts:

  1. Import other classes and interfaces.

    Declare the following interfaces and classes in the current file:

    • User class: used to operate on user objects.
    • Session class: used to interact with the database.
    • SessionFactory class: used to create Session instances.
    • Transaction class: used to manage database transactions.
    • Configuration class: used to load the Hibernate configuration file.
    • Query class: used to execute query operations.
    • List interface: used to operate on query result sets.

    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 is used to perform persistence operations on user objects. This class encapsulates a series of methods for interacting with the database, enabling operations such as adding, deleting, modifying, and querying user objects. By calling the methods in the UserDao class, you can easily interact with the database and perform persistence operations.

  1. Initialize the UserDao class instance.

    Use an object initialization block to initialize the Session object during the class instance creation.

    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.

    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. Query user information based on the ID.

    The selectUserById method queries user information based on the provided ID parameter.

    Use the get method of the session object to retrieve the User record corresponding to the provided User.class and ID parameters from the database and store it in the user variable.

    Code:

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

  3. Query user information based on the username.

    The selectUserbyName method queries user information based on the username. First, define a Hibernate Query Language (HQL) statement to query 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. Use the createQuery method of the Session object to create a Query object and pass the HQL statement and 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 results to a list of User objects.

    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.

    The selectUser method implements the method for querying all user data. Define an SQL statement to query all user data. Use the createNativeQuery method of the Session object to create a Query object and pass the SQL statement as a parameter. Then, use the addEntity method to add the entity class User to the query so that the query results can be converted to a list of User objects. Use the getResultList method of the Query object to execute the query and convert the query results to a list of User objects.

    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.

    The insertUser method inserts user information. Specify a User type parameter user to pass in 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 object to obtain the current transaction and call the commit method to commit the transaction, persisting the previous operations to the database and returning the result of the insert operation.

    Code:

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

  6. Delete user data.

    The deleteUserById method deletes the user record corresponding to the provided ID from the database. Specify an integer parameter id to pass in the user ID to be deleted. Call the beginTransaction method of the Hibernate Session to start a new transaction. Use the get method to obtain the corresponding user object based on the specified user ID and entity class type (User.class). Use the delete method to delete the user object. Call the getTransaction method of the Session object to obtain the current transaction and call the commit method to commit the transaction, persisting the user information to the database and returning the result of the delete operation.

    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.

    The updateUserById method updates the corresponding user information. Specify a User type parameter user to specify 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 corresponding User object based on the user ID. Use the merge method of the Session object to merge the provided User object with the obtained User object, updating the provided 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, returning the result of the update operation.

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

User.java​

The User.java file implements the add, delete, modify, and query operations of user data through the Session object and the user data access object (DAO) class.

The User.java file contains the following code:

  1. Import other classes.

    Declare the classes that are related to the current file and are configured by using the Java Persistence API (JPA) annotations to map the classes to database tables:

    • Column annotation: specifies the mapping between the attributes of an entity class and the columns of a database table.
    • Entity annotation: maps a database table.
    • GeneratedValue annotation: specifies that the value of the attribute is automatically generated.
    • GenerationType annotation: specifies the primary key generation strategy.
    • Id annotation: specifies the unique identifier attribute.
    • Table annotation: specifies 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. Map the database table.

    Create an entity class to map the database table, and specify the table name as test_hibernate_mysql.

    Sample code:

    @Entity
    @Table(name = "test_hibernate_mysql")

  3. Define the User class.

    The User class is used to map the test_hibernate_mysql table in the database.

    1. Define two attributes, id and name. Use annotations to specify the mapping between the id and name attributes of the User class and the columns of the database table. Use the @Id annotation to specify the id attribute as the primary key. Use the @GeneratedValue annotation to specify the sequence as the primary key generation strategy. Use the @Column annotation to specify the mapping between the attributes and the columns of 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. Create a User object.

      Define two constructors for the User class to create a User object. Define an empty constructor for Hibernate query operations. Define 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. Obtain and set the id and name values.

      Define four methods for the User class to obtain and set the id and name attribute values. The getId method is used to obtain the id value. The setId method is used to set the id value. The getName method is used to obtain the username name value. The setName method is used to set the username name value.

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

      Override the toString method of the User class to return the string representation of the User object. Define @Override to override the method with the same name in the parent class. Define the toString method to return the string representation of the User object. Concatenate strings to format the values of the id and name attributes into a string and return the string to the caller User.

      Sample code:

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

TestHibernate.java​

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

The TestHibernate.java file contains the following code:

  1. Import other classes and interfaces.

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

    • UserDao class: executes database operations related to users.
    • User class: operates on user objects.
    • Session class: executes session operations on the database.
    • SessionFactory class: creates a Session object.
    • Configuration class: configures Hibernate.
    • SessionImpl class: obtains the underlying JDBC connection.
    • Test annotation: marks a test method.
    • IOException class: handles I/O exceptions.
    • Connection class: obtains the JDBC connection.
    • SQLException class: handles SQL exceptions.
    • List class: stores query result sets.
    • UUID class: generates 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 the basic operations, including insert, delete, update, and query. Create a UserDao object and insert five user records in a loop. Call the insertUser method to insert user data. Call the deleteUserById method to delete user data whose ID is 1. Call the updateUserById method to update user data whose ID is 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​

pom.xml​

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

User.hbm.xml​

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

hibernate.cfg.xml​

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

UserDao.java​

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

}

User.java​

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 + '\'' +
                '}';
    }
}

TestHibernate.java​

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 JDBC driver.

Contents

Edit this page