Connect to OceanBase Cloud by using a HikariCP connection pool

This topic describes how to use a HikariCP connection pool, MySQL Connector/J, and OceanBase Cloud to build an application for basic database operations, such as table creation, data insertion, data deletion, data modification, and data query.

Download the hikaricp-mysql-client sample project Connect to OceanBase Database by using a HikariCP connection pool (MySQL compatible mode)

Prerequisites

• You have registered an OceanBase Cloud account, and created a cluster instance and a MySQL-compatible tenant in OceanBase Cloud. For more information, see Create a cluster instance and Create a tenant.

• You have obtained the connection string of the MySQL-compatible tenant. For more information, see Obtain the connection string.

• You have installed Java Development Kit (JDK) 1.8 and Maven.

• You have installed Eclipse.

  Note

  This topic uses Eclipse IDE for Java Developers 2022-03 to run the sample code. You can also choose a suitable tool as needed.

Procedure

Step 1: Import the hikaricp-mysql-client project to Eclipse

1. Start Eclipse and choose File > Open Projects from File System.

2. In the dialog box that appears, click Directory, navigate to the directory where the project is located, and click Finish to import the project.

   Note

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

   

3. View the project.

   

Step 2: Modify the database connection information in the hikaricp-mysql-client project

Modify the database connection information in the db.properties file in the hikaricp-mysql-client/src/main/resources/ directory based on the obtained connection string mentioned in the "Prerequisites" section.

Here is an example:

• The endpoint is t5******.********.oceanbase.cloud.
• The access port is 3306.
• The name of the database to be accessed is test.
• The tenant account is test_user001.
• The password is ******.

The sample code is as follows:

...
jdbcUrl=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false
username=test_user001
password=******
...

Step 3: Run the hikaricp-mysql-client project

1. In the project navigation view, find and expand the src/main/java directory.

2. Right-click the Main.java file and choose Run As > Java Application.

   

3. In the Console window of Eclipse, view the project logs and output results.

   

4. You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results:

   obclient [(none)]> SELECT * FROM test.test_hikaricp;
   

   The return result is as follows:

   +------+-------------+
   | id   | name        |
   +------+-------------+
   |    1 | test_update |
   +------+-------------+
   1 row in set
   

Project code

Click here to download the project code, which is a package named hikaricp-mysql-client.zip.

Decompress the package to obtain a folder named hikaricp-mysql-client. The directory structure is as follows:

hikaricp-mysql-client
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── example
│       │           └── Main.java
│       └── resources
│           └── db.properties
└── pom.xml

The files and directories are described as follows:

• src: the root directory that stores the source code.
• main: a directory that stores the main code, including the major logic of the application.
• java: a directory that stores the Java source code.
• com: a directory that stores the Java package.
• example: a directory that stores the packages of the sample project.
• Main.java: a sample file of the main class that contains logic such as the table creation, data insertion, data deletion, data modification, and data query logic.
• resources: a directory that stores resource files, including configuration files.
• db.properties: the configuration file of the connection pool, which contains relevant database connection parameters.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in pom.xml

pom.xml is the configuration file of the Maven project, which defines the dependencies, plug-ins, and build rules of the project. Maven is a Java project management tool that can automatically download dependencies and compile and package projects.

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

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure namespaces and the POM model version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.

   The sample code is as follows:

   <project xmlns="http://maven.apache.org/POM/4.0.0"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
   
    <!-- Other configurations -->
   
   </project>
   

3. Configure basic information.

   1. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to proxool-mysql-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

       <groupId>com.example</groupId>
       <artifactId>hikaricp-mysql-client</artifactId>
       <version>1.0-SNAPSHOT</version>
   

4. Configure the attributes of the project source file.

   Specify maven-compiler-plugin as the compiler plug-in of Maven, and set the source code version and target code version of the compiler to Java 8. This means that the project source code is compiled by using Java 8 and the compiled bytecode is compatible with the Java 8 runtime environment. This ensures that Java 8 syntax and characteristics can be correctly processed during the compilation and running of the project.

   Note

   Java 1.8 and Java 8 are different names for the same version.

   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 the components on which the project depends.

   1. Add the mysql-connector-java dependency library for interactions with the database, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
      2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
      3. <version>: the version of the dependency, which is set to 5.1.40.

      The sample code is as follows:

              <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>5.1.40</version>
              </dependency>
      

   2. Add the HikariCP dependency library for implementing a high-performance JDBC connection pool, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to com.zaxxer.
      2. <artifactId>: the name of the dependency, which is set to HikariCP.
      3. <version>: the version of the dependency, which is set to 3.3.1.

      The sample code is as follows:

              <dependency>
                  <groupId>com.zaxxer</groupId>
                  <artifactId>HikariCP</artifactId>
                  <version>3.3.1</version>
              </dependency>
      

   3. Add the logback-classic dependency library for log recording and management, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to ch.qos.logback.
      2. <artifactId>: the name of the dependency, which is set to logback-classic.
      3. <version>: the version of the dependency, which is set to 1.2.5.

      The sample code is as follows:

              <dependency>
                  <groupId>ch.qos.logback</groupId>
                  <artifactId>logback-classic</artifactId>
                  <version>1.2.5</version>
              </dependency>
      

Code in db.properties

db.properties is a sample configuration file of the connection pool. The configuration file contains the URL, username, and password for connecting to the database, and other optional parameters of the connection pool.

Perform the following steps to configure the db.properties file:

1. Configure database connection parameters.

   1. Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
   2. Specify the username for connecting to the database.
   3. Specify the password for connecting to the database.

   The sample code is as follows:

   jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false
   username=$user_name
   password=$password
   

   The parameters are described as follows:

   • $host: the access address of OceanBase Cloud. The value is sourced from the -h parameter in the connection string.
   • $port: the access port of OceanBase Cloud. The value is sourced from the -P parameter in the connection string.
   • $database_name: the name of the database to be accessed. The value is sourced from the -D parameter in the connection string.
   • $user_name: the account name. The value is sourced from the -u parameter in the connection string.
   • $password: the account password. The value is sourced from the -p parameter in the connection string.

2. Configure other connection pool parameters.

   1. Enable caching for precompiled SQL statements.
   2. Set the cache size for precompiled SQL statements to 250.
   3. Set the maximum lifetime of connections to 1,800,000 ms (30 minutes). Connections exceeding the specified lifetime are closed.
   4. Set the timeout value of idle connections to 600,000 ms (10 minutes). Connections that have been idle for the specified period of time are closed.
   5. Set the timeout value for requesting a connection to 30,000 ms (30s). If no connection is obtained when the specified timeout value expires, an exception is thrown.

   The sample code is as follows:

   dataSource.cachePrepStmts=true
   dataSource.prepStmtCacheSize=250
   dataSource.maxLifetime=1800000
   dataSource.idleTimeout=600000
   dataSource.connectionTimeout=30000
   

General parameters of a HikariCP connection pool

Code in Main.java

The Main.java file is a part of the sample application. It demonstrates the process of obtaining a database connection from the HikariCP connection pool, executing a series of database operations, such as table creation, data insertion, data deletion, data modification, and data query, and returning the query result.

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

1. Import the required classes and packages.

   1. Define the name of the Java package as com.example, where the package is used for organizing and managing Java classes.
   2. Import the java.sql.Connection class for establishing and managing connections with the database.
   3. Import the java.sql.PreparedStatement class for executing precompiled SQL statements.
   4. Import the java.sql.ResultSet class for processing query result sets.
   5. Import the java.sql.SQLException class for handling SQL exceptions.
   6. Import the HikariConfig class for configuring HikariCP connection pools.
   7. Import the HikariDataSource class for creating and managing HikariCP connection pools.

   The sample code is as follows:

   package com.example;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import com.zaxxer.hikari.HikariConfig;
   import com.zaxxer.hikari.HikariDataSource;
   

2. Define class names and methods.

   Define a Main class. The main method serves as the entry to the application. Call the main method to read the db.properties file to configure the HikariCP connection pool and request database connections. Then, call a series of methods in sequence to create a table, insert data, query data, update data, and delete data. If an SQLException occurs during the operation, the stack trace of the exception is recorded. Perform the following steps:

   1. Define a public class named Main.
   2. Define an entry method named main of the Main class.
   3. Create a HikariConfig object and configure the object based on the db.properties file.
   4. Create a HikariDataSource object and obtain a database connection by using the getConnection() method in the try-with-resources block.
   5. Call the table creation method and pass in the obtained database connection object to create a table named test_hikaricp.
   6. Call the data insertion method and pass in the obtained database connection object and data parameters to insert the (1,'A1') and (2,'A2') records.
   7. Call the data query method and pass in the obtained database connection object to query the inserted data.
   8. Call the data update method and pass in the obtained database connection object and update parameters to update the name column value of the row whose id value is 1 to test_update.
   9. Call the data query method and pass in the obtained database connection object to query the updated data.
   10. Call the data deletion method and pass in the obtained database connection object and deletion parameters to delete the row whose id value is 2.
   11. Call the data query method and pass in the obtained database connection object to query the data after the deletion.
   12. If an SQLException occurs in the try block, the stack trace of the exception is recorded.
   13. Define methods for creating tables, inserting data, querying data, updating data, and deleting data.

   The sample code is as follows:

   public class Main {
       public static void main(String[] args) {
           try {
               HikariConfig config = new HikariConfig("/db.properties");
               try (HikariDataSource dataSource = new HikariDataSource(config);
                   Connection conn = dataSource.getConnection()) {
                   createTable(conn);
   
                   insertData(conn, 1, "A1");
                   insertData(conn, 2, "A2");
   
                   selectData(conn);
   
                   updateData(conn, "test_update", 1);
                   selectData(conn);
   
                   deleteData(conn, 2);
                   selectData(conn);
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   
       // Define a method for creating tables.
       // Define a method for inserting data.
       // Define a method for querying data.
       // Define a method for updating data.
       // Define a method for deleting data.
   }
   

3. Define a method for creating tables.

   Define a private static method createTable for creating a table named test_hikaricp in the database. The table contains the id and name columns. Perform the following steps:

   1. Define a private static method createTable that receives a Connection object as parameters, and declare that the method can throw an SQLException.
   2. Define an SQL statement string for creating a table named test_hikaricp. The table contains the id column of the INT type and the name column of the VARCHAR(50) type.
   3. Create a precompiled SQL statement object pstmt by using the Connection object conn and use the object in the try-with-resources block.
   4. Execute an SQL statement to create a table named test_hikaricp.
   5. If a success message is displayed in the console, the table is successfully created.

   The sample code is as follows:

       private static void createTable(Connection conn) throws SQLException {
           String sql = "CREATE TABLE IF NOT EXISTS test_hikaricp (id INT, name VARCHAR(50))";
           try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
               pstmt.executeUpdate();
               System.out.println("Table created successfully.");
           }
       }
   

4. Define a method for inserting data.

   Define a private static method insertData for inserting data into the test_hikaricp table in the database. Perform the following steps:

   1. Define a private static method insertData that receives a Connection object, an id parameter of the integer type, and a name parameter of the string type, and declare that the method can throw an SQLException.
   2. Define an SQL statement string for inserting the id and name columns into the test_hikaricp table.
   3. Create a precompiled SQL statement object pstmt by using the Connection object conn and use the object in the try-with-resources block.
   4. Set the value of the first parameter ? in the SQL statement to id.
   5. Set the value of the second parameter ? in the SQL statement to name.
   6. Execute the SQL statement to insert data into the table.
   7. If a success message is displayed in the console, the data is successfully inserted into the table.

   The sample code is as follows:

       private static void insertData(Connection conn, int id, String name) throws SQLException {
           String sql = "INSERT INTO test_hikaricp (id, name) VALUES (?, ?) ";
           try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
               pstmt.setInt(1, id);
               pstmt.setString(2, name);
               pstmt.executeUpdate();
               System.out.println("Data inserted successfully.");
           }
       }
   

5. Define a method for querying data.

   Define a private static method selectData for querying data from the test_hikaricp table in the database. Perform the following steps:

   1. Define a private static method selectData that receives a Connection object as parameters, and declare that the method can throw an SQLException.
   2. Define an SQL statement string for querying all data in the test_hikaricp table.
   3. Create a precompiled SQL statement object pstmt by using the Connection object conn and use the object in the try-with-resources block. Call the executeQuery() method to execute an SQL query and return the query result set object rs.
   4. If a message is displayed in the console, user data is being returned.
   5. Traverse the query result set. Call the next() method to check whether a next row exists in the result set. If yes, a loop begins.
   6. Obtain the values of the id column from the result set and assign them to the id variable.
   7. Obtain the values of the name column from the result set and assign them to the name variable.
   8. Return the id and name values of each row in the console.
   9. Return an empty row in the console.

   The sample code is as follows:

       private static void selectData(Connection conn) throws SQLException {
           String sql = "SELECT * FROM test_hikaricp";
           try (PreparedStatement pstmt = conn.prepareStatement(sql);
               ResultSet rs = pstmt.executeQuery()) {
               System.out.println("User Data:");
               while (rs.next()) {
                   int id = rs.getInt("id");
                   String name = rs.getString("name");
                   System.out.println("ID: " + id + ", Name: " + name);
               }
               System.out.println();
           }
       }
   

6. Define a method for updating data.

   Define a private static method updateData for updating data in the test_hikaricp table in the database. Perform the following steps:

   1. Define a private static method updateData that receives a Connection object, an id parameter of the integer type, and a name parameter of the string type, and declare that the method can throw an SQLException.
   2. Define an SQL statement string for updating the value of the name column in the row whose id column value equals the specified id value in the test_hikaricp table to the specified name value.
   3. Create a precompiled SQL statement object pstmt by using the Connection object conn and use the object in the try-with-resources block.
   4. Set the value of the first parameter ? in the SQL statement to name.
   5. Set the value of the second parameter ? in the SQL statement to id.
   6. Execute the SQL statement to update data in the table.
   7. If a success message is displayed in the console, the data is successfully updated.

   The sample code is as follows:

       private static void updateData(Connection conn, String name, int id) throws SQLException {
           String sql = "UPDATE test_hikaricp SET name = ? WHERE id = ?";
           try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
               pstmt.setString(1, name);
               pstmt.setInt(2, id);
               pstmt.executeUpdate();
               System.out.println("Data updated successfully.");
           }
       }
   

7. Define a method for deleting data.

   Define a private static method deleteData for deleting data that meets the specified condition from the test_hikaricp table in the database. Perform the following steps:

   1. Define a private static method deleteData that receives a Connection object and an id parameter of the integer type, and declare that the method can throw an SQLException.
   2. Define an SQL statement string for deleting data that meets the id = ? condition from the test_hikaricp table.
   3. Create a precompiled SQL statement object pstmt by using the Connection object conn and use the object in the try-with-resources block.
   4. Set the value of the first parameter ? in the SQL statement to id.
   5. Execute the SQL statement to delete data that meets the specified condition from the table.
   6. If a success message is displayed in the console, the data is successfully deleted.

   The sample code is as follows:

       private static void deleteData(Connection conn, int id) throws SQLException {
           String sql = "DELETE FROM test_hikaricp WHERE id = ?";
           try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
               pstmt.setInt(1, id);
               pstmt.executeUpdate();
               System.out.println("Data deleted successfully.");
           }
       }
   

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>hikaricp-mysql-client</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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.5</version>
        </dependency>
    </dependencies>
</project>

jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false
username=$user_name
password=$password

dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.maxLifetime=1800000
dataSource.idleTimeout=600000
dataSource.connectionTimeout=30000

package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class Main {
    public static void main(String[] args) {
        try {
            HikariConfig config = new HikariConfig("/db.properties");
            try (HikariDataSource dataSource = new HikariDataSource(config);
                 Connection conn = dataSource.getConnection()) {
                createTable(conn);

                insertData(conn, 1, "A1");
                insertData(conn, 2, "A2");

                selectData(conn);

                updateData(conn, "test_update", 1);
                selectData(conn);

                deleteData(conn, 2);
                selectData(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS test_hikaricp (id INT, name VARCHAR(50))";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.executeUpdate();
            System.out.println("Table created successfully.");
        }
    }

    private static void insertData(Connection conn, int id, String name) throws SQLException {
        String sql = "INSERT INTO test_hikaricp (id, name) VALUES (?, ?) ";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
            System.out.println("Data inserted successfully.");
        }
    }

    private static void selectData(Connection conn) throws SQLException {
        String sql = "SELECT * FROM test_hikaricp";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("User Data:");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
            System.out.println();
        }
    }

    private static void updateData(Connection conn, String name, int id) throws SQLException {
        String sql = "UPDATE test_hikaricp SET name = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();
            System.out.println("Data updated successfully.");
        }
    }

    private static void deleteData(Connection conn, int id) throws SQLException {
        String sql = "DELETE FROM test_hikaricp WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            System.out.println("Data deleted successfully.");
        }
    }
}

References

• For more information about the HikariCP connection pool, see HikariCP.