This topic introduces how to build an application by using Commons Pool, MySQL Connector/J, and OceanBase AI 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.
Prerequisites
You have installed OceanBase AI Database and created a user 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
Note
The steps outlined in this topic are for the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Import the
commonpool-mysql-clientproject into Eclipse. - Obtain the URL of OceanBase AI Database.
- Modify the database connection information in the
commonpool-mysql-clientproject. - Run the
commonpool-mysql-clientproject.
Step 1: Import the commonpool-mysql-client project into Eclipse
Start Eclipse and choose File > Open Projects from File System.
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.xmlfile in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.
View the project.

Step 2: Obtain the URL of OceanBase AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the connection string.
Here is an example:
obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -DtestFor more information about the connection string, see Connect to OceanBase AI Database by using OBClient.
Fill in the URL below based on the OceanBase AI Database connection string.
jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password&useSSL=falseParameter description:
$hostspecifies the IP address for connecting to OceanBase AI 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.$portspecifies the port for connecting to OceanBase AI Database. For connection through ODP, the default value is2883, which can be customized when ODP is deployed. For direct connection, the default value is2881, which can be customized when OceanBase AI Database is deployed.$database_namespecifies 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, andSELECTprivileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.$user_namespecifies the tenant account. For connection through ODP, the tenant account can be in theusername@tenant_name#cluster_nameorcluster_name:tenant_name:usernameformat. For direct connection, the tenant account is in theusername@tenant_nameformat.$passwordspecifies 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, wheremysql001is a user tenant created in OceanBase AI Database, andtest_user001is the username of themysql001tenant. - 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
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and choose Run As > Java Application.

View the output results 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 get 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
The files and directories are described as follows:
src: The root directory for source code.main: The main code directory, containing the application's core logic.java: The Java source code directory.com: The Java package directory.example: The package directory for the sample project.Main.java: The main class sample file, containing logic for creating tables, and inserting, deleting, updating, and querying data.resources: The resource file directory, containing configuration files.db.properties: The connection pool configuration file, containing relevant database connection parameters.pom.xml: The Maven project configuration file, 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:
Declare the file.
Declare the file to be an XML file that uses XML standard
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure the namespaces and the POM model version.
xmlns: the default XML namespace, which is set tohttp://maven.apache.org/POM/4.0.0.xmlns:xsi: the namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.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.<modelVersion>: the POM model version used by the POM file, which is set to4.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>Configure basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name ascommonpool-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
The sample code is as follows:
<groupId>com.example</groupId> <artifactId>commonpool-mysql-client</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the properties of the project's source files.
Specifies the Maven compiler plugin as
maven-compiler-pluginand sets both the source and target Java versions to 8. This means 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 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.
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>Configure the components on which the project depends.
Add the
mysql-connector-javadependency library to interact with the database:- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency name asmysql-connector-java. - Use
<version>to specify the dependency version as5.1.40.
The sample code is as follows:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.40</version> </dependency>- Use
Add the
commons-pool2dependency library to use its features and classes in the project:- Use
<groupId>to specify the dependency's organization asorg.apache.commons. - Use
<artifactId>to specify the dependency name ascommons-pool2. - Use
<version>to specify the dependency version as2.7.0.
The sample code is as follows:
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-pool2</artifactId> <version>2.7.0</version> </dependency>- Use
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:
Configure database connection parameters.
- Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
- Specify the username for connecting to the database.
- 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=$passwordParameter description:
$hostspecifies the IP address for connecting to OceanBase AI 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.$portspecifies the port for connecting to OceanBase AI Database. For connection through ODP, the default value is2883, which can be customized when ODP is deployed. For direct connection, the default value is2881, which can be customized when OceanBase AI Database is deployed.$database_namespecifies the name of the database to be accessed.$user_namespecifies the tenant account. For connection through ODP, the tenant account can be in theusername@tenant_name#cluster_nameorcluster_name:tenant_name:usernameformat. For direct connection, the tenant account is in theusername@tenant_nameformat.$passwordspecifies the password of the account.
Configure other connection pool parameters.
- Set the maximum number of connections allowed in the connection pool to 10.
- 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.
- Set the minimum 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.
- 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
Notice
The actual parameter configurations depend on the project requirements and database characteristics. We recommend that you adjust and configure the parameters based on the actual situation.
The following table describes the general parameters of Commons Pool 2.
Parameter |
Description |
|---|---|
| url | The URL for connecting to the database. The value contains 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 that can be created in the object pool. |
| maxIdle | The maximum number of idle objects allowed in the object pool. |
| minIdle | The minimum number of idle objects in the object pool. |
| blockWhenExhausted | Specifies whether to block the borrowObject method when all objects in the object pool are occupied. Valid values:
|
| maxWaitMillis | The maximum waiting time for the borrowObject method when all objects in the object pool are occupied, in ms. |
| testOnBorrow | Specifies whether to verify objects when the borrowObject method is called. Valid values:
|
| testOnReturn | Specifies whether to verify objects when the returnObject method is called. Valid values:
|
| testWhileIdle | Specifies whether to verify idle objects in the object pool. Valid values:
|
| timeBetweenEvictionRunsMillis | The interval for scheduling the eviction thread for idle objects, in ms. |
| numTestsPerEvictionRun | The number of idle objects to verify when the eviction thread is scheduled. |
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:
Define the package and import required classes.
- Declare the name of the package to which the current code belongs as
com.example. - Import the
java.io.IOExceptionclass for handling input/output exceptions. - Import the
java.sql.Connectionclass for representing connections with the database. You can use this object to execute an SQL statement and obtain the result. - Import the
java.sql.DriverManagerclass for managing the loading of the database driver and the establishment of database connections. You can use this class to request a database connection. - Import the
java.sql.ResultSetclass for representing result sets of SQL queries. You can use this object to traverse and operate the query result set. - Import the
java.sql.SQLExceptionclass for handling exceptions related to SQL statements. - Import the
java.sql.Statementclass for executing SQL statement objects. You can call thecreateStatementmethod of theConnectionobject to create a statement. - Import the
java.util.Propertiesclass, 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. - Import the
org.apache.commons.pool2.ObjectPoolclass, which is an object pool interface that defines basic operations on pooled objects, such as requesting and returning objects. - Import the
org.apache.commons.pool2.PoolUtilsclass, which provides some tool methods for operating the object pool. - Import the
org.apache.commons.pool2.PooledObjectclass, which is a wrapper object for object pool management. You can implement this interface to manage the lifetime of pooled objects. - Import the
org.apache.commons.pool2.impl.GenericObjectPoolclass, which is the default implementation class of theObjectPoolinterface and implements basic features of the connection pool. - Import the
org.apache.commons.pool2.impl.GenericObjectPoolConfigclass for setting properties of the connection pool. It is the configuration class ofGenericObjectPool. - Import the
org.apache.commons.pool2.impl.DefaultPooledObjectclass for managing the wrapping of pooled objects. It is the default implementation class of thePooledObjectinterface. 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;- Declare the name of the package to which the current code belongs as
Create a
Mainclass and define themainmethod.Define a
Mainclass and amainmethod. Themainmethod demonstrates how to use a connection pool to perform a series of operations on a database. The specific steps are as follows:Define a public class named
Mainas the entry point of the program. The class name must match the file name.Define a public static method
mainas the starting execution point of the program.Load the database configuration file:
- Create a
Propertiesobject to store database configuration information. - Obtain the input stream of the
db.propertiesresource file through the Main class's class loader, and use theload()method of thePropertiesobject to load this input stream, loading the key-value pairs from the property file into thepropsobject. - Catch any potential
IOExceptionexceptions and print the exception stack trace.
- Create a
Configure the database connection pool:
- Create a generic object pool configuration object to configure the behavior of the connection pool.
- Set the maximum number of connections allowed in the connection pool.
- Set the maximum number of idle connections allowed in the connection pool.
- Set the minimum number of idle connections allowed in the connection pool.
- Set the maximum wait time for obtaining a connection.
Create a database connection pool: Create a thread-safe connection pool object
connectionPool. Use theConnectionFactoryobject and thepoolConfigconfiguration object to create a generic object pool, and wrap it in a thread-safe object pool. By thread-safely wrapping the connection pool, you can ensure the safety of connection acquisition and release operations in a multi-threaded environment.Obtain a database connection. Use the connection pool's
borrowObject()method to get a database connection, and use this connection to perform database operations within thetryblock.- Call the
createTable()method to create a table. - Call the
insertData()method to insert data. - Call the
selectData()method to query data. - Call the
updateData()method to update data. - Call the
selectData()method again to query the updated data. - Call the
deleteData()method to delete data. - Call the
selectData()method again to query the deleted data. - Call the
dropTable()method to drop the table. - Catch and print any exceptions.
- Call the
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)); // Get a database connection try (Connection connection = connectionPool.borrowObject()) { // Create table createTable(connection); // Insert data insertData(connection); // Query data selectData(connection); // Update data updateData(connection); // Query the updated data selectData(connection); // Delete data deleteData(connection); // Query the data after deletion selectData(connection); // Drop table dropTable(connection); } catch (Exception e) { e.printStackTrace(); } } // Define the method for creating a table. // Define the method for inserting data. // Define a method for updating data. // Define a method for deleting data. // Define methods for querying data // Define a method for deleting tables. // Define a ConnectionFactory class }Define a method for creating a table.
Define a method named
createTablethat takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
createTable()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for creating the table. - Use the
Statementobject'sexecuteUpdate()method to execute thesqlstatement and create the table. - Output a success message for table creation to the console.
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."); } }- Define a private static method
Define a method for inserting data.
Define a method named
insertData()that takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
insertData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for inserting data. - Use the
Statementobject'sexecuteUpdate()method to execute thesqlstatement and insert data. - Output a success message for data insertion to the console.
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."); } }- Define a private static method
Define a method for updating data.
Define a method named
updateData()that takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
updateData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for updating data. - Use the
executeUpdate()method of theStatementobject to execute thesqlstatement and update the data. - Output a success message for the data update to the console.
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."); } }- Define a private static method
Define a method for deleting data.
Define a method named
deleteData()that takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
deleteData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for deleting data. - Use the
executeUpdate()method of theStatementobject to execute thesqlstatement and delete the data. - Output a success message for the data deletion to the console.
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."); } }- Define a private static method
Define a method for querying data.
Define a method named
selectData()that takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
selectData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for querying data. - Use the
executeQuery()method of theStatementobject to execute thesqlstatement and store the result in aResultSetobject. - Use the
resultSet.next()method to check if there is a next row of data and enter a loop. - Use the
resultSet.getInt()method to retrieve the integer data from the current row. The method's parameter is the column name. - Use the
resultSet.getString()method to retrieve the string data from the current row. The method's parameter is the column name. - Output the data from the current row to 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); } } }- Define a private static method
Define a method for dropping a table.
Define a method named
dropTable(), which takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
dropTable(), which takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use the connection's
createStatement()method to create aStatementobject, and use this object in atry-with-resourcesstatement to perform database operations. - Define a string variable
sqlto store the SQL statement for dropping the table. - Use the
Statementobject'sexecuteUpdate()method to execute thesqlstatement and drop the table. - Output a message indicating the table was dropped successfully to the console.
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."); } }- Define a private static method
Define a
ConnectionFactoryclass.Define a static inner class named
ConnectionFactory, which inherits fromBasePooledObjectFactoryand implements methods for creating and managing connection objects. The specific steps are as follows:Note
@Overrideis an annotation that indicates the following method overrides a method in the parent class.- Define a static inner class named
ConnectionFactory, which inherits fromorg.apache.commons.pool2.BasePooledObjectFactory<Connection>. This class serves as a factory for creating and managing connection objects. - Define a private immutable string variable to store the database URL.
- Define a private immutable string variable to store the username for connecting to the database.
- Define a private immutable string variable to store the password for connecting to the database.
- Define a constructor for the
ConnectionFactoryclass to initialize the class member variablesurl,username, andpassword. The constructor takes three parameters: the database URL, the username for connecting to the database, and the password. These parameters are assigned to their corresponding member variables. This way, when creating aConnectionFactoryobject, you can pass in database-related information through the constructor. - Override the
create()method in theBasePooledObjectFactoryclass to create a new connection object. - Use the
getConnection()method of theDriverManagerclass to create and return a connection object. - Override the
destroyObject()method in theBasePooledObjectFactoryclass to destroy the connection object. - Call the connection object's
close()method to close the connection. - Override the
validateObject()method in theBasePooledObjectFactoryclass to validate the connection object. - Call the connection object's
isValid()method to check if the connection is valid, setting the timeout to 5000 milliseconds. - Override the
wrap()method in theBasePooledObjectFactoryclass to wrap the connection object into aPooledObject. - Use the constructor of the
DefaultPooledObjectclass to create aPooledObjectand pass 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); } }- Define a static inner class named
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));
// 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.
Download the commonpool-mysql-client sample project