DocsICP FilingConsole
官方文档
首页

Sample program for connecting to an OceanBase database using Commons Pool

更新时间：
复制 MD 格式
我的收藏

This topic describes how to use Commons Pool, MySQL Connector/J, and an OceanBase database to build an application. The application performs basic database operations, including creating tables, inserting data, updating data, deleting data, querying data, and dropping tables.

image.pngClick to download the commonpool-mysql-client sample project

Prerequisites

  • You have installed an OceanBase database and created a tenant in MySQL mode.

  • You have installed JDK 1.8 and Maven.

  • You have installed Eclipse.

    Note

    This topic uses Eclipse IDE for Java Developers 2022-03 to run the code. You can also use other tools to run the sample code.

Procedure

Note

The steps in this topic describe how to compile and run the project in a Windows environment using Eclipse IDE for Java Developers 2022-03. The steps may differ if you use a different operating system or compiler.

  1. Import the commonpool-mysql-client project into Eclipse.

  2. Obtain the OceanBase database URL.

  3. Modify the database connection information in the commonpool-mysql-client project.

  4. Run the commonpool-mysql-client project.

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

  1. Open Eclipse. From the menu bar, select File > Open Projects from File System.

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

    Note

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

    image.png

  3. View the project status.

    image.png

Step 2: Get the OceanBase database URL

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

    Example:

    obclient -hxxx.xxx.xxx.xxx -P3306 -utest_user001 -p****** -Dtest

    For more information about connection strings, see Obtain connection parameters.

  2. Complete the following URL with the information from your OceanBase database connection string.

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

    Parameters:

    • $host: The domain name for the OceanBase database connection.

    • $port: The connection port for the OceanBase database. The default port for a MySQL mode tenant is 3306.

    • $database_name: The name of the database to access.

    • $user_name: The connection account for the tenant.

    • $password: The password for the account.

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

    Example:

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

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

Modify the database connection information in the commonpool-mysql-client/src/main/resources/db.properties file using the information from Step 2: Obtain the OceanBase database URL.

image.png

Example:

  • The IP address of the OBServer node is xxx.xxx.xxx.xxx.

  • The access port is 3306.

  • The name of the database to access is test.

  • The connection account for the tenant is test_user001.

  • The password is ******.

Code:

...
db.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?useSSL=false
db.username=test_user001
db.password=******
...

Step 4: Run the commonpool-mysql-client project

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

  2. Right-click the Main.java file, and then select Run As > Java Application.

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

    image.png

Project code overview

Click commonpool-mysql-client to download the project code as a compressed package named commonpool-mysql-client.zip.

After decompression, a folder named commonpool-mysql-client is created. The directory structure is as follows:

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

File descriptions:

  • src: The root directory for the source code.

  • main: The main code directory, which contains the primary logic of the application.

  • java: The directory for Java source code.

  • com: The Java package directory.

  • example: The package directory for the sample project.

  • Main.java: The main class file for the sample program. It contains the logic for creating tables, and inserting, updating, deleting, and querying data.

  • resources: The directory for resource files, such as configuration files.

  • db.properties: The configuration file for the connection pool. It contains the database connection parameters.

  • pom.xml: The configuration file for the Maven project. It is used to manage project dependencies and build settings.

pom.xml code overview

The pom.xml file is the configuration file for the Maven project. It defines information such as project dependencies, plugins, and build rules. Maven is a Java project management tool that automatically downloads dependencies, compiles, and packages projects.

The pom.xml file includes the following parts:

  1. File declaration.

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

    Code:

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

  2. POM namespace and model version configuration.

    1. The xmlns attribute specifies the Project Object Model (POM) namespace as http://maven.apache.org/POM/4.0.0.

    2. The xmlns:xsi attribute specifies the XML namespace http://www.w3.org/2001/XMLSchema-instance.

    3. The xsi:schemaLocation attribute specifies 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. The <modelVersion> element specifies that the POM version used by this file is 4.0.0.

    Code:

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

 <!-- Other configurations -->

</project>

  3. Basic information configuration.

    1. The <groupId> element specifies the project's organization as com.example.

    2. The <artifactId> element specifies the project's name as commonpool-mysql-client.

    3. The <version> element specifies the project's version number as 1.0-SNAPSHOT.

    Code:

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

  4. Project source file property configuration.

    This part specifies the Maven compiler plugin as maven-compiler-plugin and sets both the source and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode is compatible with a Java 8 runtime environment. This setting ensures that the project can correctly handle Java 8 syntax and features during compilation and at runtime.

    Note

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

    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. Project dependency configuration.

    1. Add the mysql-connector-java dependency library, which is used to interact with the database:

      1. The <groupId> element specifies the dependency's organization as mysql.

      2. The <artifactId> element specifies the dependency's name as mysql-connector-java.

      3. The <version> element specifies the dependency's version number as 5.1.40.

      Code:

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

    2. Add the commons-pool2 dependency library to use its features and classes in the project:

      1. The <groupId> element specifies the dependency's organization as org.apache.commons.

      2. The <artifactId> element specifies the dependency's name as commons-pool2.

      3. The <version> element specifies the dependency's version number as 2.7.0.

      Code:

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

db.properties code overview

db.properties is the connection pool configuration file for this sample. It contains the configuration properties for the connection pool, such as the database URL, username, password, and other optional settings.

The db.properties file includes the following parts:

  1. Database connection parameters.

    1. Configures the database connection URL, which includes the host IP address, port number, and the database to access.

    2. Configures the database username.

    3. Configures the database password.

    Code:

    db.url=jdbc:mysql://$host:$port/$database_name?useSSL=false
db.username=$user_name
db.password=$password

    Parameters:

    • $host: The domain name for the OceanBase database connection.

    • $port: The connection port for the OceanBase database. The default port for a MySQL mode tenant is 3306.

    • $database_name: The name of the database to access.

    • $user_name: The connection account for the tenant.

    • $password: The password for the account.

  2. Other connection pool parameters.

    1. Sets the maximum number of connections in the pool to 10. This means that the pool can have up to 10 active connections at the same time.

    2. Sets the maximum number of idle connections in the pool to 5. When the number of connections in the pool exceeds this value, the extra connections are closed.

    3. Sets the minimum number of idle connections in the pool to 2. The pool maintains at least two idle connections, even if no connections are in use.

    4. Sets the maximum wait time for obtaining a connection from the pool to 5000 milliseconds. If no connections are available in the pool, the operation to obtain a connection waits until the maximum wait time is exceeded.

    Code:

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

The specific property (parameter) configuration depends on your project requirements and database characteristics. Adjust the configuration as needed.

Common configuration parameters for Commons Pool 2:

Parameter

Description

url

Specifies the URL for connecting to the database. The URL includes information such as the database type, hostname, port number, and database name.

username

The username required to connect to the database.

password

The password required to connect to the database.

maxTotal

Specifies the maximum number of objects that can be created in the object pool.

maxIdle

Specifies the maximum number of idle objects that can be kept in the object pool.

minIdle

Specifies the minimum number of idle objects to keep in the object pool.

blockWhenExhausted

Specifies the behavior of the borrowObject operation when the object pool is exhausted.

  • true: The borrowObject method blocks until an object is available.

  • false: The borrowObject method immediately throws a NoSuchElementException.

maxWaitMillis

Specifies the maximum wait time in milliseconds for the borrowObject method when the pool is exhausted.

testOnBorrow

Specifies whether to validate an object when the borrowObject method is called.

  • true: The object's validateObject method is called for validation every time the borrowObject method is called.

  • false: No validation is performed.

testOnReturn

Specifies whether to validate an object when the returnObject method is called.

  • true: The object's validateObject method is called for validation every time the returnObject method is called.

  • false: No validation is performed.

testWhileIdle

Specifies whether to validate objects when they are idle. The details are as follows:

  • If set to true, the validateObject method is periodically called on idle objects in the pool before they are borrowed. This validation ensures that idle objects are still valid and available.

  • If set to false, idle objects are not validated, and the validateObject method is not called.

timeBetweenEvictionRunsMillis

Specifies the time interval in milliseconds for scheduling the idle object eviction thread.

numTestsPerEvictionRun

Specifies the number of idle objects to check each time the eviction thread is scheduled.

Main.java code overview

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

  1. Package definition and import of necessary classes.

    1. Declares the package name for the current code as com.example.

    2. Imports the java.io.IOException class to handle input/output exceptions.

    3. Imports the java.sql.Connection class, which represents a connection to the database. You can use this object to execute SQL statements and retrieve results.

    4. Imports the java.sql.DriverManager class to manage driver loading and database connection establishment. You can use this class to obtain a database connection.

    5. Imports the java.sql.ResultSet class, which represents the result set of an SQL query. You can use this object to traverse and manipulate query results.

    6. Imports the java.sql.SQLException class to handle exceptions related to SQL statements.

    7. Imports the java.sql.Statement class, which is an object used to execute SQL statements. It can be created using the createStatement method of a Connection object.

    8. Imports the java.util.Properties class, which is a collection of key-value pairs used to load and save configuration information. You can load database connection information from a configuration file.

    9. Imports the org.apache.commons.pool2.ObjectPool class, which is an object pool interface that defines basic operations for pooled objects, such as obtaining and returning objects.

    10. Imports the org.apache.commons.pool2.PoolUtils class, which provides utility methods for conveniently operating on object pools.

    11. Imports 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. Imports the org.apache.commons.pool2.impl.GenericObjectPool class, which is the default implementation of the ObjectPool interface and implements the basic functions of a connection pool.

    13. Imports the org.apache.commons.pool2.impl.GenericObjectPoolConfig class, which is the configuration class for GenericObjectPool and is used to set the properties of the connection pool.

    14. Imports the org.apache.commons.pool2.impl.DefaultPooledObject class, which is the default implementation of the PooledObject interface and is used to wrap pooled objects. It can contain the actual connection object and other management information.

    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. Creation of the Main class and definition of the main method.

    This defines a Main class and a main method. The main method demonstrates how to use the connection pool to perform a series of database operations. The steps are as follows:

    1. Define a public class named Main as the entry point of the program. The class name must be the same as the filename.

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

      1. Load the database configuration file:

        1. Create a Properties object to store the database configuration information.

        2. Obtain the input stream of the db.properties resource file through the class loader of the Main class. Use the load() method of the Properties object to load the input stream. This action loads the key-value pairs from the properties file into the props object.

        3. Catch any potential IOException and print the 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 pool.

        3. Set the maximum number of idle connections allowed in the pool.

        4. Set the minimum number of idle connections allowed in the 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 a ConnectionFactory object and a poolConfig configuration object to create a generic object pool, and then wrap it in a thread-safe object pool. Wrapping the connection pool ensures the safety of connection acquisition and release operations in a multi-threaded environment.

      4. Obtain a database connection. Use the borrowObject() method of the connection pool to obtain a database connection. Use this connection to perform database operations within a try block.

        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 drop the table.

        9. Catch and print any exceptions.

    3. Definition of other database operation methods.

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

    // Method to create a table
    // Method to insert data
    // Method to update data
    // Method to delete data
    // Method to query data
    // Method to delete a table
    // Definition of the ConnectionFactory class
}

  3. Method for creating a table.

    This defines 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. Use this object in a try-with-resources statement to perform database operations.

    3. Define a string variable sql to store the SQL statement for creating the table.

    4. Use the executeUpdate() method of the Statement object to execute the sql statement and create the table.

    5. Print a success message to the console.

    Code:

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

  4. Method for inserting data.

    This defines 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. Use this object in a try-with-resources statement to perform 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. Print a success message to the console.

    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. Method for updating data.

    This defines 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. Use this object in a try-with-resources statement to perform 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. Print a success message to the console.

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

    This defines 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. Use this object in a try-with-resources statement to perform 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. Print a success message to the console.

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

    This defines 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 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. Use this object in a try-with-resources statement to perform database operations.

    3. Define a string variable sql to store the SQL statement for querying 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 is more data and enter a loop.

    6. Use the resultSet.getInt() method to retrieve integer data from the current row. The parameter of this method is the column name.

    7. Use the resultSet.getString() method to retrieve string data from the current row. The parameter of this method is the column name.

    8. Print the data of the current row to the console.

    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. Method for dropping a table.

    This defines a method named dropTable that accepts a Connection object as a parameter. The steps are as follows:

    1. Define a private static method dropTable() 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. Use this object in 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. Print a success message to the console.

    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. Definition of the ConnectionFactory class.

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

    Note

    @Override is an annotation that indicates the following method overrides a method in the parent class.

    1. Define a static inner class named ConnectionFactory that inherits from the org.apache.commons.pool2.BasePooledObjectFactory<Connection> class. This class is a factory for creating and managing 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 the database connection.

    4. Define a private, immutable string variable to store the password for the database connection.

    5. Define the constructor for the ConnectionFactory class to initialize the member variables url, username, and password. The constructor accepts three parameters: the database URL, username, and password, and assigns these values to the corresponding member variables. This lets you pass database information when creating a ConnectionFactory object.

    6. Override the create() method from 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 from the BasePooledObjectFactory class to destroy a connection object.

    9. Call the close() method of the connection object to close the connection.

    10. Override the validateObject() method from the BasePooledObjectFactory class to validate a connection object.

    11. Call the isValid() method of the connection object to check if the connection is valid, setting a timeout of 5000 milliseconds.

    12. Override the wrap() method from the BasePooledObjectFactory class to wrap a connection object into a PooledObject object.

    13. Use the constructor of the DefaultPooledObject class to create a PooledObject object, passing the connection object as a parameter.

    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

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

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

db.properties

# Database Configuration
db.url=jdbc:mysql://$host:$port/$database_name?useSSL=false
db.username=$user_name
db.password=$password

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

Main.java

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 INT,name VARCHAR(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 MySQL Connector/J, see Overview of MySQL Connector/J.

Previous：Sample program: Connect to an OceanBase database using a DBCP connection poolNext：Sample program for connecting to an OceanBase database using a Druid connection pool