Connect to OceanBase Database by using Commons Pool

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

Click to download the commonpool-mysql-client sample project Connect to OceanBase Database by using Commons Pool (MySQL mode)

Prerequisites

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

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

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

Procedure

1. Import the commonpool-mysql-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
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. Start Eclipse and choose File > Open Projects from File System from the menu bar.

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

   Note

   When you import a Maven project using Eclipse, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

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

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

2. Fill in the URL below based on the OceanBase Database connection string, and specify the corresponding parameters.

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

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

   • $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

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

     Note

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

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

   • password: the account password.

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

   Here is an example:

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

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

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

Here is an example:

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

**Sample code:

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

Step 4: Run the commonpool-mysql-client project

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

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

   

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

   

Project code introduction

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

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

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

File description:

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

Introduction to pom.xml

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

The pom.xml file in this topic is mainly composed of the following parts:

1. Declaration statement.

   Declare this file as an XML file that uses XML standard 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure namespaces and the POM model version.

   1. Use xmlns to specify the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0, and the location of the POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to specify the POM model version as 4.0.0.

   Sample code:

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

3. Configure basic information.

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

   Sample code:

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

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

   Specify the Maven compiler plugin as maven-compiler-plugin, and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures that the project can correctly handle 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 on which the project depends.

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

      1. Use <groupId> to specify the dependency group as mysql.
      2. Use <artifactId> to specify the dependency name as mysql-connector-java.
      3. Use <version> to specify the dependency version as 5.1.40.

      Sample code:

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

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

      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>
      

db.properties code

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

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

1. Configure database connection parameters.

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

   Sample code:

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

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. It is the IP address of an OceanBase Database Proxy (ODP) for ODP connections or the IP address of an OBServer node for direct connections.
   • $port: the port for connecting to OceanBase Database. For ODP connections, the default value is 2883, which can be customized during ODP deployment. For direct connections, the default value is 2881, which can be customized during OceanBase Database deployment.
   • $database_name: the name of the database to be accessed.
   • $user_name: the tenant account. For ODP connections, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connections, the format is username@tenant name.
   • $password: the password of the account.

2. Configure other connection pool parameters.

   1. Set the maximum number of connections in the connection pool to 10. This means that the connection pool can have at most 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 excess connections will be closed.
   3. Set the minimum number of idle connections in the connection pool to 2. Even if there are no connections being used in the connection pool, it will maintain at least 2 idle connections.
   4. Set the maximum waiting time for obtaining a connection from the connection pool to 5000 ms. If there are no available connections in the connection pool, the operation to obtain a connection will wait until it exceeds the maximum waiting time.

   Sample code:

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

Commons Pool 2 common configuration parameters:

Introduction to Main.java

The Main.java file is part of the sample application and demonstrates how to use Commons Pool 2 for database operations. The code in this topic is mainly divided into the following sections:

1. Define packages and import necessary classes.

   1. Declare the name of the package to which the current code belongs as com.example.
   2. Import the java.io.IOException class for handling input/output exceptions.
   3. Import the java.sql.Connection class for representing a database connection. You can execute SQL statements and obtain results using this object.
   4. Import the java.sql.DriverManager class for loading drivers and establishing database connections. You can use this class to obtain database connections.
   5. Import the java.sql.ResultSet class for representing the result set of an SQL query. You can use this object to traverse and operate on query results.
   6. Import the java.sql.SQLException class for handling exceptions related to SQL statements.
   7. Import the java.sql.Statement class for executing SQL statements. You can create this object using the createStatement method of a Connection object.
   8. Import the java.util.Properties class, which is a collection of key-value pairs for loading and saving configurations. You can load database connection information from a configuration file.
   9. Import the org.apache.commons.pool2.ObjectPool class, which is an object pool interface defining basic operations on pooled objects, such as obtaining and returning objects.
   10. Import the org.apache.commons.pool2.PoolUtils class, which provides some utility methods for conveniently operating on object pools.
   11. Import the org.apache.commons.pool2.PooledObject class, which is a wrapper object for managing the lifecycle of pooled objects. 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 and provides basic connection pool functionality.
   13. Import the org.apache.commons.pool2.impl.GenericObjectPoolConfig class, which is the configuration class for GenericObjectPool and allows you to set connection pool properties.
   14. Import the org.apache.commons.pool2.impl.DefaultPooledObject class, which is the default implementation of the PooledObject interface and manages the packaging 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 the main method. The main method is used to demonstrate how to use the connection pool to perform a series of database operations. The specific steps are as follows:

   1. Define a public class named Main, which serves as the entry point of the program. The class name must match the file name.

   2. Define a public static method named main, which serves as the starting execution point of the program.

      1. Load the database configuration file:

         1. Create a Properties object to store database configuration information.
         2. Use the class loader of the Main class to obtain the input stream of the db.properties resource file, and then use the load() method of the Properties object to load this input stream. This will load the key-value pairs from the properties file into the props object.
         3. Capture any possible IOException exceptions and print the stack trace.

      2. Create a 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 a database connection pool: Create a thread-safe connection pool object named connectionPool. Use the ConnectionFactory object and the poolConfig configuration object to create a generic object pool, and then wrap it in a thread-safe object pool.

      4. Obtain a database connection: Use the borrowObject() method of the connection pool to obtain a database connection. In the try block, use the connection 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 drop 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));
   
           // Obtain 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 a method for creating tables
       // Define a method for inserting data
       // Define a method for updating data
       // Define a method for deleting data
       // Define a method for querying data
       // Define a method for dropping tables
       // Define a ConnectionFactory class
   }
   

3. Define a method for creating tables.

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

   1. Define a private static method named createTable that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named 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 indicating that the table has been successfully created.

   Sample 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. Define a method for inserting data.

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

   1. Define a private static method named insertData that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named 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 indicating that the data has been successfully inserted.

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

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

   1. Define a private static method named updateData that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named 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 indicating that the data has been successfully updated.

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

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

   1. Define a private static method named deleteData that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named 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 indicating that the data has been successfully deleted.

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

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

   1. Define a private static method named selectData that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named 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 another row of data and enter a loop if so.
   6. Use the resultSet.getInt() method to obtain the integer data of the current row. The method's parameter is the name of the column.
   7. Use the resultSet.getString() method to obtain the string data of the current row. The method's parameter is the name of the column.
   8. Output the data of the current row.

   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 tables.

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

   1. Define a private static method named dropTable that accepts a Connection object as a parameter. Declare that this method may throw a SQLException.
   2. Use the createStatement() method of the connection to create a Statement object, and use this object to perform database operations within a try-with-resources block.
   3. Define a string variable named sql to store the SQL statement for dropping a 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 has been successfully dropped.

   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 org.apache.commons.pool2.BasePooledObjectFactory<Connection> and implements methods for creating and managing connection objects. The specific steps are as follows:

   Note

   The @Override annotation indicates that the following methods are overrides of the corresponding methods in the parent class.

   1. Define a static inner class named ConnectionFactory that inherits from org.apache.commons.pool2.BasePooledObjectFactory<Connection>. This class 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 the constructor of the ConnectionFactory class to initialize the member variables url, username, and password. The constructor accepts three parameters: the database URL, the username for connecting to the database, and the password. It assigns the values of these parameters to the corresponding member variables. This way, when you create a ConnectionFactory object, you can pass database-related information through the constructor.
   6. Override the create() method in 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 in 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 in the BasePooledObjectFactory class to validate the validity of a connection object.
   11. Call the isValid() method of the connection object to check if the connection is valid. Set the timeout to 5000 ms.
   12. Override the wrap() method in 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 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-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>

# 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

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.