Connect to OceanBase Database by using a HikariCP connection pool

This topic introduces how to build an application by using a HikariCP connection pool, MySQL Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data deletion, data updating, and data query.

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

Prerequisites

• You have installed OceanBase Database and created a MySQL tenant.

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

  The tool used to run the sample code in this topic is Eclipse IDE for Java Developers 2022-03, but you can also choose a tool that suits your personal preference to run the sample code.

Procedure

1. Import the hikaricp-mysql-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the hikaricp-mysql-client project.
4. Run the hikaricp-mysql-client project.

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

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

2. In the dialog box that appears, click Directory to browse and select the project, and then click Finish.

   Note

   When you import a Maven project using Eclipse, 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.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest
   

   For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the URL based on the OceanBase Database connection string, as shown in the following example.

   jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password&useSSL=false
   

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (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.

   • $database_name: the name of the database to be accessed.

     Note

     The user used to connect to the tenant must have the CREATE, INSERT, DELETE, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

   • user_name: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

   • password: the password of the account.

   For more information about the connection properties of MySQL Connector/J, see Configuration Properties.

   Here is an example:

   jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=test_user001@mysql001&password=******&useSSL=false
   

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

Modify the database connection information in the hikaricp-mysql-client/src/main/resources/db.properties file based on the information obtained in Step 2: Obtain the URL of OceanBase Database.

Here is an example:

• The IP address of the OBServer node is xxx.xxx.xxx.xxx.
• The port is 2881.
• The name of the database to be accessed is test.
• The tenant account is test_user001@mysql001. Here, mysql001 is a MySQL user tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

Sample code:

...
jdbcUrl=jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?useSSL=false
username=test_user001@mysql001
password=******
...

Step 4: Run the hikaricp-mysql-client project

1. In the project navigation view, locate 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 OBClient to view the result.

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

   The return result is as follows:

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

Project code introduction

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

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

File description:

• src: the root directory for source code.
• main: the main code directory, containing the core logic of the application.
• java: a directory for storing the Java source code.
• com: a directory for storing the Java package.
• example: a directory for storing the packages of the sample project.
• Main.java: a sample file of the main class that contains logic for table creation, data insertion, data deletion, data modification, and data query.
• resources: a directory for storing 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.

Introduction to pom.xml

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

The pom.xml file in this topic mainly includes the following sections:

1. Declaration statement.

   Declare this 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 specify the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0, and the location of the corresponding XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to specify the POM model version as 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>
   
    <!-- Other configurations -->
   
   </project>
   

3. Configure basic information.

   1. Use <groupId> to specify the project group as com.example.
   2. Use <artifactId> to specify the project name as hikaricp-mysql-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample code:

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

4. Configure the attributes of the project's source file.

   Specify the Maven compiler plugin as maven-compiler-plugin, and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

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

   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. 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. Use <groupId> to specify the dependency group as mysql.
      2. Use <artifactId> to specify the dependency name as mysql-connector-java.
      3. Use <version> to specify the dependency version as 5.1.40.

      Sample code:

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

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

      1. Use <groupId> to specify the dependency group as com.zaxxer.
      2. Use <artifactId> to specify the dependency name as HikariCP.
      3. Use <version> to specify the dependency version as 3.3.1.

      Sample code:

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

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

      1. Use <groupId> to specify the dependency group as ch.qos.logback.
      2. Use <artifactId> to specify the dependency name as logback-classic.
      3. Use <version> to specify the dependency version as 1.2.5.

      Sample code:

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

db.properties code

db.properties is a configuration file for the connection pool in the sample application. It contains the configuration attributes of the connection pool, such as the database URL, username, password, and other options.

The db.properties file in this topic contains the following code:

1. Configure database connection parameters.

   1. Configure the URL of the database connection, which includes the host IP address, port number, and database to be accessed.
   2. Configure the database username.
   3. Configure the database password.

   Sample code:

   jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false
   username=$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.
   • $database_name: the name of the database to be accessed.
   • $user_name: the tenant account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.
   • $password: the password of the account.

2. Configure other connection pool parameters.

   1. Enable the cache for precompiled SQL statements.
   2. Set the cache size for precompiled SQL statements to 250.
   3. Set the maximum lifecycle of a connection to 1,800,000 ms (30 minutes). Connections that exceed this time limit will be closed.
   4. Set the idle timeout of a connection to 600,000 ms (10 minutes). Idle connections that exceed this time limit will be closed.
   5. Set the timeout period for obtaining a connection to 30,000 ms (30 seconds). If a connection is not obtained within this period, an exception will be thrown.

   Sample code:

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

Common basic parameters of HikariCP connection pool:

Introduction to Main.java

The Main.java file is part of the sample application and demonstrates how to obtain a database connection using the HikariCP connection pool and perform a series of database operations, including table creation, data insertion, data deletion, data updating, data query, and result printing.

The code in this topic consists of the following sections:

1. Import the required classes and packages.

   1. Define the package name of the current Java file as com.example to organize and manage Java classes.
   2. Import the java.sql.Connection class to establish and manage connections with the database.
   3. Import the java.sql.PreparedStatement class to execute precompiled SQL statements.
   4. Import the java.sql.ResultSet class to process query result sets.
   5. Import the java.sql.SQLException class to handle SQL exceptions.
   6. Import the HikariConfig class of HikariCP to configure the HikariCP connection pool.
   7. Import the HikariDataSource class of HikariCP to create and manage the HikariCP connection pool.

   Sample code:

   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 the class and methods.

   Define a Main class with the main method as the entry point of the program. In the main method, read the db.properties file to configure the HikariCP connection pool and obtain a database connection. Then, call a series of methods to create a table, insert data, query data, update data, and delete data. If a SQLException exception occurs during the operations, print the stack trace of the exception. The specific steps are as follows:

   1. Define a public class named Main.
   2. Define the main method as the entry method of the Main class.
   3. Create a HikariConfig object and configure it by using the specified db.properties file.
   4. Create a HikariDataSource object and obtain a database connection in the try-with-resources block.
   5. Call the createTable method, pass the obtained database connection object, and create a table named test_hikaricp.
   6. Call the insertData method, pass the obtained database connection object and data parameters, and insert two rows of data: (1,'A1') and (2,'A2').
   7. Call the selectData method, pass the obtained database connection object, and check whether the data is inserted.
   8. Call the updateData method, pass the obtained database connection object and update parameters, and update the value of the name column to test_update in the row where the id column value is 1.
   9. Call the selectData method, pass the obtained database connection object, and check whether the data is updated.
   10. Call the deleteData method, pass the obtained database connection object and delete parameters, and delete the row where the id column value is 2.
   11. Call the selectData method, pass the obtained database connection object, and check whether the data is deleted.
   12. If a SQLException exception occurs in the try block, print the stack trace of the exception.
   13. Define methods for table creation, data insertion, data query, data update, and data deletion.

   Sample code:

   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 table creation.
       // Define a method for data insertion.
       // Define a method for data query.
       // Define a method for data update.
       // Define a method for data deletion.
   }
   

3. Define the method for table creation.

   Define a private static method named createTable to create a table named test_hikaricp in the database. The table contains the id column and the name column. The specific steps are as follows:

   1. Define a private static method named createTable. The method accepts a Connection object as the parameter and declare that the method may throw SQLException.
   2. Define an SQL statement string to create a table named test_hikaricp. The table contains the id column (data type: INT) and the name column (data type: VARCHAR(50).
   3. Use the conn connection object to create a precompiled SQL statement object named pstmt, and use the object in the try-with-resources block.
   4. Execute the SQL statement to create a table named test_hikaricp.
   5. Output a message in the console to indicate that the table is created.

   Sample code:

       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 the method for data insertion.

   Define a private static method named insertData to insert data into the test_hikaricp table in the database. The specific steps are as follows:

   1. Define a private static method named insertData. The method accepts a Connection object, an integer id parameter, and a string name parameter as the parameters and declare that the method may throw SQLException.
   2. Define an SQL statement string to insert data into the test_hikaricp table. The data includes the id and name columns.
   3. Use the conn connection object to create a precompiled SQL statement object named pstmt, 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.
   7. Output a message in the console to indicate that the data is inserted.

   Sample code:

       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 the method for data query.

   Define a private static method named selectData to query data in the test_hikaricp table in the database. The specific steps are as follows:

   1. Define a private static method named selectData. The method accepts a Connection object as the parameter and declare that the method may throw SQLException.
   2. Define an SQL statement string to query all data in the test_hikaricp table.
   3. Use the conn connection object to create a precompiled SQL statement object named pstmt, and use the object in the try-with-resources block. Call the executeQuery() method to execute the SQL query and return the query result set object named rs.
   4. Output a message in the console to indicate that user data is being printed.
   5. Traverse the query result set. Call the next() method to check whether there is another row of data in the result set. If yes, enter the loop.
   6. Obtain the value of the id column from the result set and assign the value to the variable id.
   7. Obtain the value of the name column from the result set and assign the value to the variable name.
   8. Output the id and name values of each row of data in the console.
   9. Output an empty line in the console.

   Sample code:

       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 the method for data update.

   Define a private static method named updateData to update data in the test_hikaricp table in the database. The specific steps are as follows:

   1. Define a private static method named updateData. The method accepts a Connection object, a string name parameter, and an integer id parameter as the parameters and declare that the method may throw SQLException.
   2. Define an SQL statement string to update data in the test_hikaricp table. The value of the name column is updated to the specified name, and the condition is that the value of the id column is equal to the specified id.
   3. Use the conn connection object to create a precompiled SQL statement object named pstmt, 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.
   7. Output a message in the console to indicate that the data is updated.

   Sample code:

       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 the method for data deletion.

   Define a private static method named deleteData to delete data in the test_hikaricp table in the database that meets the specified conditions. The specific steps are as follows:

   1. Define a private static method named deleteData. The method accepts a Connection object and an integer id parameter as the parameters and declare that the method may throw SQLException.
   2. Define an SQL statement string to delete data from the test_hikaricp table that meets the condition id = ?.
   3. Use the conn connection object to create a precompiled SQL statement object named pstmt, 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 condition from the table.
   6. Output a message in the console to indicate that the data is deleted.

   Sample code:

       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 MySQL Connector/J, see Overview of MySQL Connector/J.
• For more information about the HikariCP connection pool, see HikariCP.