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 deletion, data updating, and data query.

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

Prerequisites

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

• You have installed Java Development Kit (JDK) 1.8 and Maven.

• You have installed Eclipse.

  Note

  The tool used to run the sample code in this topic is Eclipse IDE for Java Developers (2022-03), but you can also choose a tool that suits your personal preference to run the 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.

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

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

   where

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

   • $port specifies 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 specifies the name of the database to be accessed.

     Notice

     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-compatible mode.

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

   • $password specifies the password of the account.

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

   Here is an example:

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

Step 3: Modify the database connection information in the 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.

Here is an example:

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

The sample code is as follows:

...
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. In the console window of Eclipse, view the output results.

   

Project code introduction

Click commonpool-mysql-client to download the project code, which is a compressed file 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

Here is a breakdown of the files and directories:

• src: the root directory for storing the source code.
• main: the directory for storing the main code, including the major 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 file of the main class that contains logic for table creation, data insertion, data deletion, data modification, and data query.
• 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.

Code in the pom.xml file

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

To configure the pom.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. xmlns: the default XML namespace, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the mapping between the XML namespace and its corresponding XML schema definition (XSD) file. The value typically consists of paired strings separated by spaces. Each pair consists of a default XML namespace (http://maven.apache.org/POM/4.0.0) in the first part, and the URI of the corresponding XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd) in the second part.
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.

   The sample code is as follows:

   <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. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to commonpool-mysql-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

       <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 plug-in 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, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

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

   The sample code is as follows:

       <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. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
      2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
      3. <version>: the version of the dependency, which is set to 5.1.40.

      The sample code is as follows:

              <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. <groupId>: the ID of the group to which the dependency belongs, which is set to org.apache.commons.
      2. <artifactId>: the name of the dependency, which is set to commons-pool2.
      3. <version>: the version of the dependency, which is set to 2.7.0.

      The sample code is as follows:

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

Code in the db.properties file

db.properties is a sample configuration file of the connection pool. The configuration file contains the URL, username, and password for connecting to the database, and other optional parameters of the connection pool.

To configure the db.properties file, perform the following steps:

1. Configure database connection parameters.

   1. Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
   2. Specify the username for connecting to the database.
   3. Specify the password for connecting to the database.

   The sample code is as follows:

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

   where

   • $host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • $port specifies 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 specifies the name of the database to be accessed.
   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • $password specifies the password of the account.

2. Configure other connection pool parameters.

   1. Set the maximum number of connections allowed in the connection pool to 10.
   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 number of idle connections, excess connections will be closed.
   3. Set the maximum number of idle connections in the connection pool to 2. The connection pool will retain at least two idle connections even if no connection is in use.
   4. Set the maximum waiting time for requesting a connection from the connection pool to 5,000 ms. If no connection is available in the connection pool, the application has to wait until the maximum waiting time elapses.

   The sample code is as follows:

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

The following table describes the general parameters of Commons Pool 2.

Code in the Main.java file

The Main.java file is a part of the sample application, demonstrating how to use Commons Pool 2 for database operations. To configure the Main.java file, perform the following steps:

1. Define the package and import required 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 connections with the database. You can use this object to execute an SQL statement and obtain the result.
   4. Import the java.sql.DriverManager class for managing the loading of the database driver and the establishment of database connections. You can use this class to request a database connection.
   5. Import the java.sql.ResultSet class for representing result sets of SQL queries. You can use this object to traverse and operate the query result set.
   6. Import the java.sql.SQLException class for handling exceptions related to SQL statements.
   7. Import the java.sql.Statement class for executing SQL statement objects. You can call the createStatement method of the Connection object to create a statement.
   8. Import the java.util.Properties class, which is a collection of key-value pairs for loading and saving configuration information. You can load the database connection information from the configuration file.
   9. Import the org.apache.commons.pool2.ObjectPool class, which is an object pool interface that defines basic operations on pooled objects, such as requesting and returning objects.
   10. Import the org.apache.commons.pool2.PoolUtils class, which provides some tool methods for operating the object pool.
   11. Import the org.apache.commons.pool2.PooledObject class, which is a wrapper object for object pool management. You can implement this interface to manage the lifetime of pooled objects.
   12. Import the org.apache.commons.pool2.impl.GenericObjectPool class, which is the default implementation class of the ObjectPool interface and implements basic features of the connection pool.
   13. Import the org.apache.commons.pool2.impl.GenericObjectPoolConfig class for setting properties of the connection pool. It is the configuration class of GenericObjectPool.
   14. Import the org.apache.commons.pool2.impl.DefaultPooledObject class for managing the wrapping of pooled objects. It is the default implementation class of the PooledObject interface. It can contain actual connection objects and some management information.

   The sample code is as follows:

   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 a main method.

   Define a Main class and a main method. The main method demonstrates how to use the connection pool to execute a series of operations in the database. The specific steps are as follows:

   1. Define a Main public class, which is used as the entry to the application. The class name must be the same as the file name.

   2. Define a public static method named main, which is used as the execution start point of the application.

      1. Load the database configuration file.

         1. Create a Properties object for storing the database configuration information.
         2. Use the class loader of the Main class to obtain the input stream of the db.properties configuration file. Call the load() method of the Properties object to load this input stream so as to load the key-value pairs in the configuration file to the props object.
         3. Capture possible IOExceptions and record their stack information.

      2. Create database connection pool configurations.

         1. Create a generic object pool configuration object for configuring a 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 waiting time for requesting a connection.

      3. Create a thread-safe connection pool object connectionPool. Use the ConnectionFactory and poolConfig objects to create a generic object pool and wrap it in a thread-safe object pool. Wrapping the connection pool to be thread-safe can ensure the security when connections are requested and released in a multi-thread environment.

      4. Call the borrowObject() method of the connection pool to obtain a database connection and use this connection in the try block for 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 data after the deletion.
         8. Call the dropTable() method to drop the table.
         9. Capture and record exceptions.

   3. Define other database operation methods.

   The sample code is as follows:

   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 a 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 the 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. The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method createTable() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the table creation statement.
   4. Call the executeUpdate() method of the Statement object to execute the SQL statement to create a table.
   5. Return a message in the console indicating that the table is created.

   The sample code is as follows:

       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(). The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method insertData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the data insertion statement.
   4. Call the executeUpdate() method of the Statement object to execute the SQL statement to insert data.
   5. Return a message in the console indicating that the data is inserted.

   The sample code is as follows:

       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(). The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method updateData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the data update statement.
   4. Call the executeUpdate() method of the Statement object to execute the SQL statement to update data.
   5. Return a message in the console indicating that the data is updated.

   The sample code is as follows:

       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(). The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method deleteData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the data deletion statement.
   4. Call the executeUpdate() method of the Statement object to execute the SQL statement to delete data.
   5. Return a message in the console indicating that the data is deleted.

   The sample code is as follows:

       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(). The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method selectData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the data query statement.
   4. Call the executeQuery() method of the Statement object to execute the SQL statement and store the result in the ResultSet object.
   5. Use the resultSet.next() method to check whether a next row exists. If yes, a loop begins.
   6. Use the resultSet.getInt() method to obtain the integer values of the current row. The parameters of this method are the names of the columns corresponding to the integer values.
   7. Use the resultSet.getString() method to obtain the string values of the current row. The parameters of this method are the names of the columns corresponding to the string values.
   8. Return the current row in the console.

   The sample code is as follows:

       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(). The method receives a Connection object as a parameter. The specific steps are as follows:

   1. Define a private static method dropTable() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method to create a Statement object and use this object in the try-with-resources block to perform database operations.
   3. Define a string variable sql for storing the table dropping statement.
   4. Call the executeUpdate() method of the Statement object to execute the SQL statement to drop the table.
   5. Return a message in the console indicating that the table is dropped.

   The sample code is as follows:

       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 internal class ConnectionFactory that inherits the BasePooledObjectFactory class and implements methods for creating and managing connection objects. The specific steps are as follows:

   Note

   @Override is an annotation indicating that the method overrides the corresponding method in the parent class.

   1. Define a static internal class named ConnectionFactory. It inherits the org.apache.commons.pool2.BasePooledObjectFactory<Connection> class and is a factory class used to create and manage connection objects.
   2. Define a private immutable string variable for storing the database URL.
   3. Define a private immutable string variable for storing the username for connecting to the database.
   4. Define a private immutable string variable for storing the password for connecting to the database.
   5. Define a constructor function of the ConnectionFactory class for initializing the member variables such as url, username, and password of the class. The constructor function receives the database URL and the username and password for connecting to the database as its parameters, and assign the values of these parameters to the corresponding member variables. This way, database connection information can be passed in by using the constructor function when a ConnectionFactory object is created.
   6. Rewrite the create() method in the BasePooledObjectFactory class to create a new connection object.
   7. Call the getConnection() method of the DriverManager class to create and return a connection object.
   8. Rewrite the destroyObject() method in the BasePooledObjectFactory class to destroy the connection object.
   9. Call the close() method of the connection object to close the connection.
   10. Rewrite the validateObject() method in the BasePooledObjectFactory class to verify the connection object.
   11. Call the isValid() method of the connection object to check whether the connection is valid. Set the timeout value to 5,000 ms.
   12. Rewrite the wrap() method in the BasePooledObjectFactory class to wrap the connection object as a PooledObject object.
   13. Call the constructor function of the DefaultPooledObject class to create a PooledObject object and pass in the connection object as a parameter.

   The sample code is as follows:

   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 examples

<?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));

        // Obtain a database connection.
        try (Connection connection = connectionPool.borrowObject()) {

            // Create a 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 the 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.