Commons Pool connection seekdb example

This topic describes how to build an application by using Commons Pool, MySQL Connector/J, and seekdb to perform basic database operations, including creating a table, inserting data, updating data, deleting data, querying data, and deleting a table.

Download the commonpool-mysql-client sample project

Prerequisites

• You have installed seekdb.

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  info

  The code examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also use your preferred tool to run the code examples.

Procedure

info

The following procedure describes how to compile and run the project in the Windows environment by using Eclipse IDE for Java Developers 2022-03. If you are using other operating systems or compilers, the procedure may vary.

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

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

   info

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

   

3. View the project.

   

Step 2: Obtain the seekdb URL

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

   Example:

   mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

   For more information about the connection string, see Connect to seekdb by using a MySQL client.

2. Fill in the corresponding information in the URL based on the seekdb connection string.

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

   Parameter description:

   • $host: the IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address or 127.0.0.1.
   • $port: the port for connecting to seekdb. Replace it with the actual port. The default value is 2881, which can be customized when you deploy seekdb.
   • $database_name: the name of the database to be accessed.
   • user_name: the connection account. The format is username.
   • password: the account password.

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

   Example:

   jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=root&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 seekdb URL.

Example:

• The IP address of seekdb is xxx.xxx.xxx.xxx.
• The access port is 2881.
• The name of the database to be accessed is test.
• The connection account is root.
• The password is ******.

Sample code:

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

Step 4: Run the commonpool-mysql-client project

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

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

   

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

   

Project code

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

File description:

• src: the root directory of the source code.
• main: the main code directory, containing the core logic of the application.
• java: the directory for 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: the sample file of the main class program, containing logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the directory for storing resource files, such as configuration files.
• db.properties: the configuration file for the connection pool, which contains parameters related to the database connection.
• pom.xml: the configuration file of the Maven project, used to manage the project's dependencies and build settings.

Code of pom.xml

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

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

1. File declaration statement.

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

   Sample code:

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

2. Configure the namespaces and POM model version.

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

   Sample code:

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

3. Configure basic information.

   1. Use <groupId> to specify the project's organization as com.example.
   2. Use <artifactId> to specify the project's name as commonpool-mysql-client.
   3. Use <version> to specify the project's 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 files.

   Specify the Maven compiler plugin as maven-compiler-plugin and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   info

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

   Sample code:

       <build>
           <plugins>
               <plugin>
                   <groupId>org.apache.maven.plugins</groupId>
                   <artifactId>maven-compiler-plugin</artifactId>
                   <configuration>
                       <source>8</source>
                       <target>8</target>
                   </configuration>
               </plugin>
           </plugins>
       </build>

5. Configure the components that the project depends on.

   1. Add the mysql-connector-java dependency library for interacting with the database:

      1. Use <groupId> to specify the dependency's organization as mysql.
      2. Use <artifactId> to specify the dependency's name as mysql-connector-java.
      3. Use <version> to specify the dependency's 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:

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

      Sample code:

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

Code introduction

db.properties is the connection pool configuration file in the sample code. It contains the configuration attributes of the connection pool, including the database URL, username, password, and other optional parameters.

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

1. Configure the database connection parameters.

   1. Configure the URL for database connections, including the host IP, port number, and the 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 seekdb. Replace it with the actual IP address. You can also use the local IP address or 127.0.0.1.
   • $port: the port for connecting to seekdb. Replace it with the actual port number. The default value is 2881. You can customize the port number when you deploy seekdb.
   • $database_name: the name of the database to be accessed.
   • $user_name: the username for the connection. Format: username.
   • $password: the password for the account.

2. Configure other parameters of the connection pool.

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

   Sample code:

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

tip

The specific parameters depend on the project requirements and the characteristics of the database. We recommend that you adjust and configure them as needed.

Common parameters of Commons Pool 2:

Parameter	Description
url	The URL for connecting to the database, including the database type, host name, port number, and database name.
username	The username for connecting to the database.
password	The password for connecting to the database.
maxTotal	The maximum number of objects allowed in the object pool.
maxIdle	The maximum number of idle objects allowed in the object pool.
minIdle	The minimum number of idle objects allowed in the object pool.
blockWhenExhausted	The behavior of the borrowObject operation when the object pool is exhausted. true: The borrowObject method will block until an available object is available. false: The borrowObject method will immediately throw a NoSuchElementException exception.
maxWaitMillis	The maximum waiting time (in milliseconds) for the borrowObject method when the object pool is exhausted.
testOnBorrow	Whether to validate objects when the borrowObject method is called. true: The validateObject method of the object will be called every time the borrowObject method is called for validation. false: No validation will be performed.
testOnReturn	Whether to validate objects when the returnObject method is called. true: The validateObject method of the object will be called every time the returnObject method is called for validation. false: No validation will be performed.
testWhileIdle	Whether to validate objects when they are idle. The specific meaning is as follows: When set to true, the validateObject method will be periodically called for validation before idle objects in the object pool are borrowed. This validation operation ensures that idle objects are still valid and available. When set to false, no validation will be performed on idle objects, meaning the validateObject method will not be called.
timeBetweenEvictionRunsMillis	The interval (in milliseconds) for scheduling the idle object eviction thread.
numTestsPerEvictionRun	The number of idle objects to check each time the idle object eviction thread is scheduled.

Main.java code introduction

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

1. Define a package and import the necessary classes.

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

Sample code:

    package com.example;

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

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

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

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

1. Define a public class named Main as the entry point of the program. The class name must be consistent with the file name. 2. Define a public static method main as the starting point of the program's execution.

2. Load the database configuration file:

3. Create a Properties object to store the database configuration information. 2. Obtain the input stream of the db.properties resource file by using the class loader of the Main class and use the load() method of the Properties object to load the input stream, thereby loading the key-value pairs in the attribute file into the props object. 3. Catch the IOException exception that may be thrown and print the exception stack information.

4. Create the database connection pool configuration:

5. 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 waiting time for obtaining a connection.

6. Create the database connection pool: Create a thread-safe connection pool object connectionPool, use the ConnectionFactory object and the poolConfig configuration object to create a generic object pool, and wrap it in a thread-safe object pool. By wrapping the connection pool in a thread-safe manner, you can ensure the safety of connection acquisition and release operations in a multithreaded environment. 4. Obtain a database connection by calling the borrowObject() method of the connection pool and use the connection in the try code block to perform database operations.

7. 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 the deletion. 8. Call the dropTable() method to delete the table. 9. Catch and print any exceptions.

8. Define other database operation methods.

Sample code:

    public class Main {

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

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

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

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

// Create table
                createTable(connection);

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

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

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

selectData(connection);

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

// Define the createTable method.
        // Define the insertData method.
        // Define the updateData method.
        // Define the deleteData method.
        // Define the selectData method.
        // Define the dropTable method.
        // Define a ConnectionFactory class.
    }

3. Define the createTable method.

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

1. Define a private static method createTable() that accepts a Connection object as a parameter and declares that it may throw an SQLException. 2. Use the createStatement() method of the connection to create a Statement object, and use this object to execute database operations within a try-with-resources statement. 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. Output a message indicating that the table was successfully created to the console.

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 the insertData method.

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

1. Define a private static method insertData() that accepts a Connection object as a parameter and declares that it may throw an SQLException. 2. Use the createStatement() method of the connection to create a Statement object, and use this object to execute database operations within a try-with-resources statement. 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 data. 5. Output a message indicating that the data was successfully inserted to the console.

Sample code:

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

5. Define the updateData method.

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

1. Define a private static method updateData() that accepts a Connection object as a parameter and declares that it may throw an SQLException. 2. Use the createStatement() method of the connection to create a Statement object, and use this object to execute database operations within a try-with-resources statement. 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 data. 5. Output a message indicating that the data was successfully updated to the console.

Sample code:

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

6. Define the deleteData method.

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

1. Define a private static method deleteData() that accepts a Connection object as a parameter and declares that it may throw an SQLException. 2. Use the createStatement() method of the connection to create a Statement object, and use this object to execute database operations within a try-with-resources statement. 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 data. 5. Output a message indicating that the data was successfully deleted to the console.

Sample code:

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

7. Define the selectData method.

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

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

Sample code:

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

8. Define a method for dropping a table.

Define a method named dropTable() that 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 and use it within a try-with-resources statement to perform database operations. 3. Define a string variable sql to store the SQL statement for dropping the table. 4. Use the executeUpdate() method of the Statement object to execute the sql statement and drop the table. 5. Output a message indicating that the table was successfully dropped to the console.

Sample code:

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

9. Define a ConnectionFactory class.

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

info

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

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

Sample code:

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

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

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

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

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

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

Complete code

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.