Sample program that uses HikariCP to connect to OceanBase Cloud

This topic describes how to use HikariCP, OceanBase Connector/J, and OceanBase Cloud to build an application that performs basic database operations, including creating tables, inserting, deleting, updating, and querying data.

Download the hikaricp-oceanbase-client sample project Sample program that uses HikariCP to connect to OceanBase Cloud (Oracle compatible mode)

Prerequisites

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

• You have obtained the connection string of the target OceanBase Cloud Oracle compatible tenant. For more information, see Obtain a connection string.

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

  The code examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also choose a tool of your preference to run the sample code.

Procedure

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

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

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

   Note

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

   

3. View the project.

   

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

Modify the database connection information in the hikaricp-oceanbase-client/src/main/resources/db.properties file based on the connection string information obtained in the prerequisites.

Here is an example:

...
jdbcUrl=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_schema001
username=test_user
password=******
...

• The connection address is t5******.********.oceanbase.cloud.
• The access port is 1521.
• The name of the database to be accessed is test_schema001.
• The tenant account is test_user.
• The password is ******.

Step 3: Run the hikaricp-oceanbase-client project

1. In the Project Explorer view, locate and expand the src/main/java directory.

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

   

3. View the project logs and output in the Eclipse console window.

   

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

   obclient [SYS]> SELECT * FROM test_schema001.test_hikaricp;
   

   The returned result is as follows:

   +------+-------------+
   | ID   | NAME        |
   +------+-------------+
   |    1 | test_update |
   +------+-------------+
   1 row in set
   

Project code

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

After decompressing the file, you will find a folder named hikaricp-oceanbase-client. The directory structure is as follows:

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

File description:

• src: the root directory of the source code.
• main: the main code directory, containing the core logic of the application.
• java: the directory for Java source code.
• com: the directory for Java packages.
• example: the directory for packages of the sample project.
• Main.java: the main class file, containing logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the directory for resource files, including configuration files.
• db.properties: the configuration file for the connection pool, containing relevant database connection parameters.
• pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

Introduction to the pom.xml file

The pom.xml file is the configuration file of a 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 projects.

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

1. File declaration statement.

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

   Sample code:

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

2. Configure the namespace and 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 POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use the <modelVersion> element to specify the POM model version used by the POM file 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-oceanbase-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample code:

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

4. Configure the properties of the project source files.

   Specify the Maven compiler plugin as maven-compiler-plugin, and set 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 setting 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 oceanbase-client dependency library for interacting with the database:

      1. Use <groupId> to specify the dependency group as com.oceanbase.
      2. Use <artifactId> to specify the dependency name as oceanbase-client.
      3. Use <version> to specify the dependency version as 2.4.2.

      Note

      This part of the code defines the project's dependency on OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC driver.

      Sample code:

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

   2. Add the HikariCP dependency library for implementing a high-performance JDBC connection pool:

      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:

      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 the connection pool configuration file in the example. It contains the configuration properties of the connection pool, including the database URL, username, password, and other optional parameters of the connection pool.

The code in the db.properties file in this topic mainly includes the following parts:

1. Configure the database connection parameters.

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

   Sample code:

   jdbcUrl=jdbc:oceanbase://$host:$port/$schema_name
   username=$user_name
   password=$password
   

   Parameter description:

   • $host: the value of the -h parameter in the connection string. It specifies the connection address of the cloud database of OceanBase Cloud.
   • $port: the value of the -P parameter in the connection string. It specifies the connection port of the cloud database of OceanBase Cloud.
   • $schema_name: the value of the -D parameter in the connection string. It specifies the name of the database to be accessed.
   • $user_name: the value of the -u parameter in the connection string. It specifies the account name.
   • $password: the value of the -p parameter in the connection string. It specifies the account password.

2. Configure other parameters of the connection pool.

   1. Enable the cache for precompiled SQL statements.
   2. Set the cache size of precompiled SQL statements to 250.
   3. Set the maximum lifecycle of a connection to 1800000 milliseconds (30 minutes). If a connection exceeds this time, it will be closed.
   4. Set the idle timeout of a connection to 600000 milliseconds (10 minutes). If a connection exceeds this time and is idle, it will be closed.
   5. Set the timeout of a connection to 30000 milliseconds (30 seconds). If a connection is not obtained within this time, 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 the HikariCP connection pool:

Main.java code

The Main.java file is part of the sample program and demonstrates how to obtain a database connection using the HikariCP connection pool and perform a series of database operations, including creating a table, inserting data, deleting data, updating data, querying data, and printing the query results.

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

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 a connection to 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 from HikariCP to configure the HikariCP connection pool.
   7. Import the HikariDataSource class from HikariCP to create and manage the HikariCP connection pool.

   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 name and method.

   Define a Main class, where the main method serves 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 an SQLException occurs during the operation, the exception's stack trace is printed. The specific steps are as follows:

   1. Define a public class named Main.
   2. Define the entry method main of the Main class.
   3. Create a HikariConfig object and configure it using the specified db.properties file.
   4. Create a HikariDataSource object and obtain a database connection within the try-with-resources block.
   5. Call the method for creating a table and pass the obtained database connection object to create the test_hikaricp table.
   6. Call the method for inserting data and pass the obtained database connection object and data parameters to insert two rows of data, (1,'A1') and (2,'A2').
   7. Call the method for querying data and pass the obtained database connection object to check the data insertion status.
   8. Call the method for updating data and pass the obtained database connection object and update parameters to update the value of the name column in the row with id equal to 1 to test_update.
   9. Call the method for querying data and pass the obtained database connection object to check the data update status.
   10. Call the method for deleting data and pass the obtained database connection object and deletion parameters to delete the row with id equal to 2.
   11. Call the method for querying data and pass the obtained database connection object to check the data deletion status.
   12. If an SQLException occurs in the try block, the exception's stack trace is printed.
   13. Define methods for creating a table, inserting data, querying data, updating data, and deleting data.

   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 the method for creating a table.
       // Define the method for inserting data.
       // Define the method for querying data.
       // Define the method for updating data.
       // Define the method for deleting data.
   }
   

3. Define the method for creating a table.

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

   1. Define a private static method createTable that accepts a Connection object as a parameter and declares that it may throw an SQLException.
   2. Define an SQL statement string to create a table named test_hikaricp with id (data type NUMBER) and name (data type VARCHAR2(50)) columns.
   3. Use the connection object conn to create a precompiled SQL statement object pstmt and use it within the try-with-resources block.
   4. Execute the SQL statement to create the test_hikaricp table.
   5. Print a message to the console indicating that the table was created successfully.

   Code:

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

4. Define the method for inserting data.

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

   1. Define a private static method insertData that accepts a Connection object, an integer id parameter, and a string name parameter, and declares that it may throw an SQLException.
   2. Define an SQL statement string to insert data into the test_hikaricp table, including id and name columns.
   3. Use the connection object conn to create a precompiled SQL statement object pstmt and use it within 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. Print a message to the console indicating that the data was inserted successfully.

   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 querying data.

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

   1. Define a private static method selectData that accepts a Connection object as a parameter and declares that it may throw a SQLException.
   2. Define a SQL statement string for querying all data from the test_hikaricp table.
   3. Use the conn connection object to create a precompiled SQL statement object pstmt and use it within a try-with-resources block. Additionally, execute the SQL query by calling the executeQuery() method and return the result set object rs.
   4. Print a message to the console indicating that user data is being printed.
   5. Traverse the result set, using the next() method to check if there is another row of data in the result set. If there is, enter the loop.
   6. Retrieve the value of the id column from the result set and assign it to the variable id.
   7. Retrieve the value of the name column from the result set and assign it to the variable name.
   8. Print the id and name values of each row to the console.
   9. Print an empty line to 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 a method for updating data.

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

   1. Define a private static method updateData that accepts a Connection object, a string-type name parameter, and an integer-type id parameter, and declares that it may throw a SQLException.
   2. Define a SQL statement string for updating data in the test_hikaricp table, setting the value of the name column to the specified name where 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 pstmt and use it within a 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 the data in the table.
   7. Print a message to the console indicating that the data update was successful.

   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 a method for deleting data.

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

   1. Define a private static method deleteData that accepts a Connection object and an integer-type id parameter, and declares that it may throw a SQLException.
   2. Define a SQL statement string for deleting data from the test_hikaricp table where 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 pstmt and use it within a 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 conditions from the table.
   6. Print a message to the console indicating that the data deletion was successful.

   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-oceanbase-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>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</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:oceanbase://$host:$port/$schema_name
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 test_hikaricp (id NUMBER, name VARCHAR2(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 OceanBase Connector/J, see OceanBase Connector/J.
• For more information about HikariCP, see HikariCP.