Connect to OceanBase Cloud by using Commons Pool

This topic describes how to build an application by using Commons Pool, OceanBase Connector/J, and OceanBase Cloud. The application can perform basic database operations, including creating tables, inserting data, updating data, deleting data, querying data, and deleting tables.

Download the commonpool-oceanbase-client sample project Connect to OceanBase Cloud by using Commons Pool (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 Oracle-compatible tenant. For more information, see Obtain the connection string.

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

  The example code in this topic is run in Eclipse IDE for Java Developers 2022-03. You can also use other tools that you prefer.

Procedure

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

1. Open Eclipse and select File > Open Projects from File System.

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

   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, and adds them to the project.

   

3. View the project.

   

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

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

Here is an example:

...
db.url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_schema001
db.username=test_user001
db.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 connection account is test_user.
• The password is ******.

Step 3: Run the commonpool-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 select Run As > Java Application.

   

3. View the output results in the Eclipse console window.

   

Project code

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

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

commonpool-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 package.
• example: the directory for the package of the sample project.
• Main.java: the main class program 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.

Code in the pom.xml file

The pom.xml file is the configuration file of a Maven project. It 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 code in the pom.xml file in this topic 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 namespaces 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 the basic information.

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

   Sample code:

       <groupId>com.example</groupId>
       <artifactId>commonpool-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 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 setup ensures that the project can correctly handle Java 8 syntax and features 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 that the project depends on.

   1. Add the oceanbase-client dependency library for database interaction:

      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 commons-pool2 dependency library to use its features and classes in the project:

      1. Use <groupId> to specify the dependency group as org.apache.commons.
      2. Use <artifactId> to specify the dependency name as commons-pool2.
      3. Use <version> to specify the dependency version as 2.7.0.

      Sample code:

              <dependency>
                  <groupId>org.apache.commons</groupId>
                  <artifactId>commons-pool2</artifactId>
                  <version>2.7.0</version>
              </dependency>
      

Introduction to db.properties

db.properties is a connection pool configuration file in the example provided in this topic. It contains the configuration parameters 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 provided in this topic mainly includes the following parts:

1. Configure the database connection parameters.

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

   Sample code:

   db.url=jdbc:oceanbase://$host:$port/$schema_name
   db.username=$user_name
   db.password=$password
   

   Parameter description:

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

2. Configure other parameters of the connection pool.

   1. Set the maximum number of connections in the connection pool to 10. This means that the connection pool can have a maximum of 10 connections at the same time.
   2. Set the maximum number of idle connections in the connection pool to 5. When the number of connections in the connection pool exceeds the maximum number of idle connections, the extra connections will be closed.
   3. Set the minimum number of idle connections in the connection pool to 2. At least 2 idle connections will be kept in the connection pool, even if there are no active connections.
   4. Set the maximum wait time for obtaining a connection from the connection pool to 5000 milliseconds. If there are no available connections in the connection pool, the operation to obtain a connection will wait until the maximum wait time is exceeded.

   Sample code:

   pool.maxTotal=10
   pool.maxIdle=5
   pool.minIdle=2
   pool.maxWaitMillis=5000
   

Common connection pool parameters in Commons Pool 2:

Main.java code introduction

The Main.java file is part of the sample program and demonstrates how to use Commons Pool 2 to perform database operations. The code in the Main.java file in this topic includes the following parts:

1. Define the package and import the necessary classes.

   1. Declare the package name of the current code as com.example.
   2. Import the java.io.IOException class to handle input and output exceptions.
   3. Import the java.sql.Connection class to represent a connection to the database. You can use this object to execute SQL statements and obtain results.
   4. Import the java.sql.DriverManager class to manage driver loading and database connection establishment. You can use this class to obtain a database connection.
   5. Import the java.sql.ResultSet class to represent the result set of an SQL query. You can use this object to traverse and manipulate query results.
   6. Import the java.sql.SQLException class to handle exceptions related to SQL statements.
   7. Import the java.sql.Statement class to represent an object for executing SQL statements. You can create this object by calling the createStatement() method of a Connection object.
   8. Import the java.util.Properties class, which is a collection of key-value pairs, to load and save configuration information. You can load database connection information from a configuration file.
   9. Import the org.apache.commons.pool2.ObjectPool class, which is an interface for an object pool. This interface defines basic operations for pooled objects, such as object retrieval and return.
   10. Import the org.apache.commons.pool2.PoolUtils class, which provides some utility methods for conveniently operating an object pool.
   11. Import the org.apache.commons.pool2.PooledObject class, which is a wrapper object that implements object pool management. You can implement this interface to manage the lifecycle of pooled objects.
   12. Import the org.apache.commons.pool2.impl.GenericObjectPool class, which is the default implementation of the ObjectPool interface. This class implements the basic functionality of a connection pool.
   13. Import the org.apache.commons.pool2.impl.GenericObjectPoolConfig class, which is the configuration class of GenericObjectPool. This class is used to set the attributes of a connection pool.
   14. Import the org.apache.commons.pool2.impl.DefaultPooledObject class, which is the default implementation of the PooledObject interface. This class is used to manage the wrapping of pooled objects. It can contain the actual connection object and other management information.

   Sample code:

   package com.example;
   
   import java.io.IOException;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Properties;
   
   import org.apache.commons.pool2.ObjectPool;
   import org.apache.commons.pool2.PoolUtils;
   import org.apache.commons.pool2.PooledObject;
   import org.apache.commons.pool2.impl.GenericObjectPool;
   import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
   import org.apache.commons.pool2.impl.DefaultPooledObject;
   

2. Create a Main class and define the main method.

   Define a Main class and a main method. The main method is used to demonstrate how to use a connection pool to perform a series of operations on a database. The steps are as follows:

   1. Define a public class named Main as the entry point of the program. The class name must be consistent with the file name.

   2. Define a public static method main as the starting point for program execution.

      1. Load the database configuration file:

         1. Create a Properties object to store the database configuration information.
         2. Use the class loader of the Main class to obtain the input stream of the db.properties resource file, and use the load() method of the Properties object to load this input stream. The key-value pairs in the property file are loaded into the props object.
         3. Capture any IOException exceptions that may be thrown and print the exception stack trace.

      2. Create the database connection pool configuration:

         1. Create a generic object pool configuration object to configure the behavior of the connection pool.
         2. Set the maximum number of connections allowed in the connection pool.
         3. Set the maximum number of idle connections allowed in the connection pool.
         4. Set the minimum number of idle connections allowed in the connection pool.
         5. Set the maximum wait time for obtaining a connection.

      3. Create the database connection pool: Create a thread-safe connection pool object connectionPool. Use the ConnectionFactory object and the poolConfig configuration object to create a generic object pool and wrap it in a thread-safe object pool. By wrapping the connection pool in a thread-safe manner, you can ensure the safety of connection acquisition and release operations in a multi-threaded environment.

      4. Obtain a database connection by calling the borrowObject() method of the connection pool. Use this connection in the try code block to perform database operations.

         1. Call the createTable() method to create a table.
         2. Call the insertData() method to insert data.
         3. Call the selectData() method to query data.
         4. Call the updateData() method to update data.
         5. Call the selectData() method again to query the updated data.
         6. Call the deleteData() method to delete data.
         7. Call the selectData() method again to query the data after deletion.
         8. Call the dropTable() method to delete the table.
         9. Capture and print any exceptions.

   3. Define other database operation methods.

   Sample code:

   public class Main {
   
       public static void main(String[] args) {
           // Load the database configuration file
           Properties props = new Properties();
           try {
               props.load(Main.class.getClassLoader().getResourceAsStream("db.properties"));
           } catch (IOException e) {
               e.printStackTrace();
           }
   
           // Create the database connection pool configuration
           GenericObjectPoolConfig<Connection> poolConfig = new GenericObjectPoolConfig<>();
           poolConfig.setMaxTotal(Integer.parseInt(props.getProperty("pool.maxTotal")));
           poolConfig.setMaxIdle(Integer.parseInt(props.getProperty("pool.maxIdle")));
           poolConfig.setMinIdle(Integer.parseInt(props.getProperty("pool.minIdle")));
           poolConfig.setMaxWaitMillis(Long.parseLong(props.getProperty("pool.maxWaitMillis")));
   
           // Create the database connection pool
           ObjectPool<Connection> connectionPool = PoolUtils.synchronizedPool(new GenericObjectPool<>(new ConnectionFactory(
                   props.getProperty("db.url"), props.getProperty("db.username"), props.getProperty("db.password")), poolConfig));
   
           // Get a database connection
           try (Connection connection = connectionPool.borrowObject()) {
   
               // Create table
               createTable(connection);
   
               // Insert data
               insertData(connection);
               // Query data
               selectData(connection);
   
               // Update data
               updateData(connection);
               // Query the updated data
               selectData(connection);
   
               // Delete data
               deleteData(connection);
               // Query the data after deletion
   
               selectData(connection);
   
               // Drop table
               dropTable(connection);
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       // Define the method for creating a table.
       // Define the method for inserting data.
       // Define the method for updating data.
       // Define the method for deleting data.
       // Define the method for querying data.
       // Define the method for dropping a table.
       // Define the ConnectionFactory class.
   }
   

3. Define the method for creating a table.

   Define a method named createTable that accepts a Connection object as a parameter. The 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. Use the createStatement() method of the connection to create a Statement object and use it in a try-with-resources statement to execute database operations.
   3. Define a string variable sql to store the SQL statement for creating a table.
   4. Use the executeUpdate() method of the Statement object to execute the sql statement and create the table.
   5. Output a message to the console indicating that the table was created successfully.

   Sample code:

       private static void createTable(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "CREATE TABLE test_commonpool (id NUMBER,name VARCHAR2(20))";
               statement.executeUpdate(sql);
               System.out.println("Table created successfully.");
           }
       }
   

4. Define the method for inserting data.

   Define a method named insertData that accepts a Connection object as a parameter. The steps are as follows:

   1. Define a private static method insertData() that accepts a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the createStatement() method of the connection to create a Statement object and use it in a try-with-resources statement to execute database operations.
   3. Define a string variable sql to store the SQL statement for inserting data.
   4. Use the executeUpdate() method of the Statement object to execute the sql statement and insert the data.
   5. Output a message to the console indicating that the data was inserted successfully.

   Sample code:

       private static void insertData(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "INSERT INTO test_commonpool (id, name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
               statement.executeUpdate(sql);
               System.out.println("Data inserted successfully.");
           }
       }
   

5. Define the method for updating data.

   Define a method named updateData that accepts a Connection object as a parameter. The steps are as follows:

   1. Define a private static method updateData() that accepts a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the createStatement() method of the connection to create a Statement object and use it in a try-with-resources statement to execute database operations.
   3. Define a string variable sql to store the SQL statement for updating data.
   4. Use the executeUpdate() method of the Statement object to execute the sql statement and update the data.
   5. Output a message to the console indicating that the data was updated successfully.

   Sample code:

       private static void updateData(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "UPDATE test_commonpool SET name = 'A11' WHERE id = 1";
               statement.executeUpdate(sql);
               System.out.println("Data updated successfully.");
           }
       }
   

6. Define the method for deleting data.

   Define a method named deleteData that accepts a Connection object as a parameter. The steps are as follows:

   1. Define a private static method deleteData() that accepts a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the createStatement() method of the connection to create a Statement object and use it in a try-with-resources statement to execute database operations.
   3. Define a string variable sql to store the SQL statement for deleting data.
   4. Use the executeUpdate() method of the Statement object to execute the sql statement and delete the data.
   5. Output a message to the console indicating that the data was deleted successfully.

   Sample code:

       private static void deleteData(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "DELETE FROM test_commonpool WHERE id = 2";
               statement.executeUpdate(sql);
               System.out.println("Data deleted successfully.");
           }
       }
   

7. Define the method for querying data.

   Define a method named selectData that accepts a Connection object as a parameter. The steps are as follows:

   1. Define a private static method selectData() that takes a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the createStatement() method of the connection to create a Statement object and use it within a try-with-resources statement to perform database operations.
   3. Define a string variable sql to store the SQL query statement for retrieving data.
   4. Use the executeQuery() method of the Statement object to execute the sql statement and store the result in a ResultSet object.
   5. Use the resultSet.next() method to check if there are more rows of data and enter a loop.
   6. Use the resultSet.getInt() method to retrieve the integer data from the current row. The parameter for this method is the column name of the data.
   7. Use the resultSet.getString() method to retrieve the string data from the current row. The parameter for this method is the column name of the data.
   8. Output the data from the current row to the console.

   Sample code:

       private static void selectData(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "SELECT * FROM test_commonpool";
               ResultSet resultSet = statement.executeQuery(sql);
               while (resultSet.next()) {
                   int id = resultSet.getInt("id");
                   String name = resultSet.getString("name");
                   System.out.println("id: " + id + ", name: " + name);
               }
           }
       }
   

8. Define a method for dropping a table.

   Define a method named dropTable() that takes a Connection object as a parameter. The steps are as follows:

   1. Define a private static method dropTable() that takes a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the createStatement() method of the connection to create a Statement object and use it within a try-with-resources statement to perform database operations.
   3. Define a string variable sql to store the SQL statement for dropping the table.
   4. Use the executeUpdate() method of the Statement object to execute the sql statement and drop the table.
   5. Output a message indicating that the table was successfully dropped to the console.

   Sample code:

       private static void dropTable(Connection connection) throws SQLException {
           try (Statement statement = connection.createStatement()) {
               String sql = "DROP TABLE test_commonpool";
               statement.executeUpdate(sql);
               System.out.println("Table dropped successfully.");
           }
       }
   

9. Define a ConnectionFactory class.

   Define a static inner class named ConnectionFactory that inherits from the BasePooledObjectFactory class and implements methods for creating and managing connection objects. The steps are as follows:

   Note

   @Override is an annotation indicating that the following method overrides a method from the parent class.

   1. Define a static inner class named ConnectionFactory that inherits from the org.apache.commons.pool2.BasePooledObjectFactory<Connection> class, which is used to create and manage connection objects.
   2. Define a private, immutable string variable to store the database URL.
   3. Define a private, immutable string variable to store the username for connecting to the database.
   4. Define a private, immutable string variable to store the password for connecting to the database.
   5. Define a constructor for the ConnectionFactory class to initialize the class's member variables url, username, and password. The constructor takes three parameters: the database URL, the username for connecting to the database, and the password for connecting to the database. These parameters are assigned to the corresponding member variables. This way, when creating a ConnectionFactory object, you can pass in the database information through the constructor.
   6. Override the create() method of the BasePooledObjectFactory class to create a new connection object.
   7. Use the getConnection() method of the DriverManager class to create and return a connection object.
   8. Override the destroyObject() method of the BasePooledObjectFactory class to destroy the connection object.
   9. Call the close() method of the connection object to close the connection.
   10. Override the validateObject() method of the BasePooledObjectFactory class to validate the connection object.
   11. Call the isValid() method of the connection object to check if the connection is valid and set the timeout to 5000 milliseconds.
   12. Override the wrap() method of the BasePooledObjectFactory class to wrap the connection object into a PooledObject object.
   13. Use the constructor of the DefaultPooledObject class to create a PooledObject object and pass the connection object as a parameter.

   Sample code:

   static class ConnectionFactory extends org.apache.commons.pool2.BasePooledObjectFactory<Connection> {
           private final String url;
           private final String username;
           private final String password;
   
           public ConnectionFactory(String url, String username, String password) {
               this.url = url;
               this.username = username;
               this.password = password;
           }
   
           @Override
           public Connection create() throws Exception {
               return DriverManager.getConnection(url, username, password);
           }
   
           @Override
           public void destroyObject(org.apache.commons.pool2.PooledObject<Connection> p) throws Exception {
               p.getObject().close();
           }
   
           @Override
           public boolean validateObject(org.apache.commons.pool2.PooledObject<Connection> p) {
               try {
                   return p.getObject().isValid(5000);
               } catch (SQLException e) {
                   return false;
               }
           }
   
           @Override
           public PooledObject<Connection> wrap(Connection connection) {
               return new DefaultPooledObject<>(connection);
           }
       }
   

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.example</groupId>
    <artifactId>commonpool-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>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.7.0</version>
        </dependency>
    </dependencies>
</project>

# Database Configuration
db.url=jdbc:oceanbase://$host:$port/$schema_name
db.username=$user_name
db.password=$password

# Connection Pool Configuration
pool.maxTotal=10
pool.maxIdle=5
pool.minIdle=2
pool.maxWaitMillis=5000

package com.example;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.PoolUtils;
import org.apache.commons.pool2.PooledObject;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.apache.commons.pool2.impl.DefaultPooledObject;

public class Main {

    public static void main(String[] args) {
        // Load the database configuration file
        Properties props = new Properties();
        try {
            props.load(Main.class.getClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }

        // Create the database connection pool configuration
        GenericObjectPoolConfig<Connection> poolConfig = new GenericObjectPoolConfig<>();
        poolConfig.setMaxTotal(Integer.parseInt(props.getProperty("pool.maxTotal")));
        poolConfig.setMaxIdle(Integer.parseInt(props.getProperty("pool.maxIdle")));
        poolConfig.setMinIdle(Integer.parseInt(props.getProperty("pool.minIdle")));
        poolConfig.setMaxWaitMillis(Long.parseLong(props.getProperty("pool.maxWaitMillis")));

        // Create the database connection pool
        ObjectPool<Connection> connectionPool = PoolUtils.synchronizedPool(new GenericObjectPool<>(new ConnectionFactory(
                props.getProperty("db.url"), props.getProperty("db.username"), props.getProperty("db.password")), poolConfig));

        // Get a database connection
        try (Connection connection = connectionPool.borrowObject()) {
            
            // Create table
            createTable(connection);

            // Insert data
            insertData(connection);
            // Query data
            selectData(connection);

            // Update data
            updateData(connection);
            // Query the updated data
            selectData(connection);

            // Delete data
            deleteData(connection);
            // Query the data after deletion
            selectData(connection);

            // Drop table
            dropTable(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "CREATE TABLE test_commonpool (id NUMBER,name VARCHAR2(20))";
            statement.executeUpdate(sql);
            System.out.println("Table created successfully.");
        }
    }

    private static void insertData(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "INSERT INTO test_commonpool (id, name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
            statement.executeUpdate(sql);
            System.out.println("Data inserted successfully.");
        }
    }

    private static void updateData(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "UPDATE test_commonpool SET name = 'A11' WHERE id = 1";
            statement.executeUpdate(sql);
            System.out.println("Data updated successfully.");
        }
    }

    private static void deleteData(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "DELETE FROM test_commonpool WHERE id = 2";
            statement.executeUpdate(sql);
            System.out.println("Data deleted successfully.");
        }
    }

    private static void selectData(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "SELECT * FROM test_commonpool";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + ", name: " + name);
            }
        }
    }

    private static void dropTable(Connection connection) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String sql = "DROP TABLE test_commonpool";
            statement.executeUpdate(sql);
            System.out.println("Table dropped successfully.");
        }
    }

    static class ConnectionFactory extends org.apache.commons.pool2.BasePooledObjectFactory<Connection> {
        private final String url;
        private final String username;
        private final String password;

        public ConnectionFactory(String url, String username, String password) {
            this.url = url;
            this.username = username;
            this.password = password;
        }

        @Override
        public Connection create() throws Exception {
            return DriverManager.getConnection(url, username, password);
        }

        @Override
        public void destroyObject(org.apache.commons.pool2.PooledObject<Connection> p) throws Exception {
            p.getObject().close();
        }

        @Override
        public boolean validateObject(org.apache.commons.pool2.PooledObject<Connection> p) {
            try {
                return p.getObject().isValid(5000);
            } catch (SQLException e) {
                return false;
            }
        }

        @Override
        public PooledObject<Connection> wrap(Connection connection) {
            return new DefaultPooledObject<>(connection);
        }
    }
}

References

For more information about OceanBase Connector/J, see OceanBase JDBC driver.