This topic describes how to build an application using Commons Pool, MySQL Connector/J, and OceanBase Database to perform basic database operations, including creating tables, inserting data, updating data, deleting data, querying data, and dropping tables.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud MySQL-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
You have obtained the connection string of the target tenant in MySQL mode. For instructions, see Get a connection string.
You have installed JDK 1.8 and Maven.
You have installed Eclipse.
Note
The tool used to run the sample code in this topic is Eclipse IDE for Java Developers 2022-03. You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on using Eclipse IDE for Java Developers 2022-03 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
Step 1: Import the commonpool-mysql-client project into Eclipse
Open Eclipse, and select File > Open Projects from File System from the menu bar.
In the dialog box that appears, click Directory, select the directory where the project is located, and then click Finish to complete the import.
Note
When you import a Maven project in Eclipse, it automatically detects the
pom.xmlfile in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.View the project.
Step 2: 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 connection string obtained in the prerequisites.
Example:
- The connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the database to access is
test. - The tenant connection account is
test_user001. - The password is
******.
Code:
...
db.url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false
db.username=test_user001
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 select Run As > Java Application.
View the output results in the console window of Eclipse.
Sample project overview
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
File description:
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.
Introduction to pom.xml code
The pom.xml file is the configuration file for a Maven project, defining information such as the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.
The code in this pom.xml file mainly includes the following parts:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.0.0.
Code:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <!-- Other configurations --> </project>- Use
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.
Code:
<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.
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>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.
Sample code:
<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.
Sample code:
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-pool2</artifactId> <version>2.7.0</version> </dependency>- Use
Introduction to db.properties
db.properties is the connection pool configuration file in this example, containing the configuration properties of the connection pool. These properties include the database URL, username, password, and other optional settings for the connection pool.
The code in this db.properties file mainly consists of the following parts:
Configure database connection parameters.
- Configure the database connection URL, including the host IP, port number, and the database to access.
- Configure the database username.
- Configure the database password.
Sample code:
db.url=jdbc:mysql://$host:$port/$database_name?useSSL=false db.username=$user_name db.password=$passwordParameter description:
$host: Obtained from the-hparameter in the connection string, which is the connection address for OceanBase Cloud.$port: Obtained from the-Pparameter in the connection string, which is the connection port for OceanBase Cloud.$database_name: Obtained from the-Dparameter in the connection string, which is the name of the database to access.$user_name: The account name, which is obtained from the-uparameter in the connection string.$password: The account password, which is obtained from the-pparameter in the connection string.
Configure parameters for other connection pools.
- Set the maximum number of connections in the connection pool to 10. This means the connection pool can have at most 10 connections simultaneously.
- Set the maximum number of idle connections in the connection pool to 5. When the number of connections in the pool exceeds this value, excess connections will be closed.
- Set the minimum number of idle connections in the connection pool to 2. Even if no connections are in use, the pool maintains at least 2 idle connections.
- Set the maximum wait time for obtaining a connection from the pool to 5000 milliseconds. If no connection is available, the operation waits until the maximum wait time is exceeded.
Sample code:
pool.maxTotal=10 pool.maxIdle=5 pool.minIdle=2 pool.maxWaitMillis=5000
Note
The specific configuration of properties (parameters) depends on project requirements and database characteristics. It is recommended to adjust and configure them according to your actual situation.
Common configuration parameters for Commons Pool 2:
Parameter |
Description |
|---|---|
| url | Specify the URL for connecting to the database, including information such as the database type, hostname, port number, and database name. |
| username | The username required to connect to the database. |
| password | The password required to connect to the database. |
| maxTotal | Specifies the maximum number of objects allowed to be created in the object pool. |
| maxIdle | Specifies the maximum number of idle objects allowed to remain in the object pool. |
| minIdle | Specifies the minimum number of free objects to be retained in the object pool. |
| blockWhenExhausted | Controls how borrowObject behaves when the pool is exhausted.
|
| maxWaitMillis | The maximum time, in milliseconds, that borrowObject waits when the pool is exhausted. |
| testOnBorrow | Specifies whether to validate an object when borrowObject is called.
|
| testOnReturn | Specifies whether to validate an object when returnObject is called.
|
| testWhileIdle | Specifies whether to validate idle objects.
|
| timeBetweenEvictionRunsMillis | Specifies the time interval (in milliseconds) for scheduling the eviction thread of idle objects. |
| numTestsPerEvictionRun | Specifies the number of idle objects to be checked during each eviction thread scheduling. |
Introduction to Main.java code
The Main.java file is part of the sample program, and its code demonstrates how to use Commons Pool 2 to perform database operations. The code in this Main.java file mainly includes the following parts:
Define packages and import necessary classes.
- Declare the package name as
com.example. - Import the
java.io.IOExceptionclass to handle input/output exceptions. - Import the
java.sql.Connectioninterface, which represents a connection to the database. You can use this object to execute SQL statements and retrieve results. - Import the
java.sql.DriverManagerclass to manage driver loading and database connection establishment. You can use this class to obtain a database connection. - Import the
java.sql.ResultSetinterface, which represents the result set of an SQL query. You can use this object to traverse and manipulate query results. - Import the
java.sql.SQLExceptionclass to handle SQL statement-related exceptions. - Import the
java.sql.Statementinterface, which is an object for executing SQL statements. It can be created using thecreateStatementmethod of aConnectionobject. - Import the
java.util.Propertiesclass, which is a collection of key-value pairs used to load and save configuration information. You can load database connection information from a configuration file. - Import the
org.apache.commons.pool2.ObjectPoolclass, which is an object pool interface that defines basic operations for pooling objects, such as acquiring and returning objects. - Import the
org.apache.commons.pool2.PoolUtilsclass, which provides utility methods for convenient operation of object pools. - Import the
org.apache.commons.pool2.PooledObjectclass, which is a wrapper object that implements object pool management. You can implement this interface to manage the lifecycle of pooled objects. - Import the
org.apache.commons.pool2.impl.GenericObjectPoolclass, which is the default implementation class for theObjectPoolinterface and implements the basic functionality of a connection pool. - Import the
org.apache.commons.pool2.impl.GenericObjectPoolConfigclass, which is the configuration class forGenericObjectPooland is used to set connection pool properties. - Import the
org.apache.commons.pool2.impl.DefaultPooledObjectclass, which is the default implementation class for thePooledObjectinterface and is used to manage the pooling of wrapped objects. It can contain the actual connection object and other management information.
The 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 package name 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 program's entry point. The class name must match the file name.Define a public static method
mainas the program's starting execution point.Load the database configuration file:
- Create a
Propertiesobject to store database configuration information. - Obtain the input stream for 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
IOExceptionexception 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 remaining data. - Call the
dropTable()method to drop the table. - Catch and print any exceptions.
- Call the
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 method for creating a table. // Define the method for inserting data. // Define the method for updating data. // Define the method for deleting data. // Define the method for querying data. // Define a method for dropping a table. // 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.
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."); } }- 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.
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."); } }- 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.
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."); } }- 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.
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."); } }- 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 takes the column name as a parameter. - Use the
resultSet.getString()method to retrieve the string data from the current row. The method takes the column name as a parameter. - Output the data from the current row to the console.
Code:
private static void selectData(Connection connection) throws SQLException { try (Statement statement = connection.createStatement()) { String sql = "SELECT * FROM test_commonpool"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println("id: " + id + ", name: " + name); } } }- Define a private static method
Define a method for dropping a table.
Define a method named
dropTable()that takes aConnectionobject as a parameter. The specific steps are as follows:- Define a private static method
dropTable()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 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.
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."); } }- Define a private static method
Define a
ConnectionFactoryclass.Define a static inner class named
ConnectionFactorythat 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
ConnectionFactorythat 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.
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); } }- Define a static inner class named
Complete sample code
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>commonpool-mysql-client</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.40</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.7.0</version>
</dependency>
</dependencies>
</project>
# Database Configuration
db.url=jdbc:mysql://$host:$port/$database_name?useSSL=false
db.username=$user_name
db.password=$password
# Connection Pool Configuration
pool.maxTotal=10
pool.maxIdle=5
pool.minIdle=2
pool.maxWaitMillis=5000
package com.example;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.PoolUtils;
import org.apache.commons.pool2.PooledObject;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import org.apache.commons.pool2.impl.DefaultPooledObject;
public class Main {
public static void main(String[] args) {
// Load the database configuration file
Properties props = new Properties();
try {
props.load(Main.class.getClassLoader().getResourceAsStream("db.properties"));
} catch (IOException e) {
e.printStackTrace();
}
// Create the database connection pool configuration
GenericObjectPoolConfig<Connection> poolConfig = new GenericObjectPoolConfig<>();
poolConfig.setMaxTotal(Integer.parseInt(props.getProperty("pool.maxTotal")));
poolConfig.setMaxIdle(Integer.parseInt(props.getProperty("pool.maxIdle")));
poolConfig.setMinIdle(Integer.parseInt(props.getProperty("pool.minIdle")));
poolConfig.setMaxWaitMillis(Long.parseLong(props.getProperty("pool.maxWaitMillis")));
// Create the database connection pool
ObjectPool<Connection> connectionPool = PoolUtils.synchronizedPool(new GenericObjectPool<>(new ConnectionFactory(
props.getProperty("db.url"), props.getProperty("db.username"), props.getProperty("db.password")), poolConfig));
// Get a database connection
try (Connection connection = connectionPool.borrowObject()) {
// Create table
createTable(connection);
// Insert data
insertData(connection);
// Query data
selectData(connection);
// Update data
updateData(connection);
// Query the updated data
selectData(connection);
// Delete data
deleteData(connection);
// Query the data after deletion
selectData(connection);
// Drop table
dropTable(connection);
} catch (Exception e) {
e.printStackTrace();
}
}
private static void createTable(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "CREATE TABLE test_commonpool (id INT,name VARCHAR(20))";
statement.executeUpdate(sql);
System.out.println("Table created successfully.");
}
}
private static void insertData(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "INSERT INTO test_commonpool (id, name) VALUES (1,'A1'), (2,'A2'), (3,'A3')";
statement.executeUpdate(sql);
System.out.println("Data inserted successfully.");
}
}
private static void updateData(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "UPDATE test_commonpool SET name = 'A11' WHERE id = 1";
statement.executeUpdate(sql);
System.out.println("Data updated successfully.");
}
}
private static void deleteData(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "DELETE FROM test_commonpool WHERE id = 2";
statement.executeUpdate(sql);
System.out.println("Data deleted successfully.");
}
}
private static void selectData(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "SELECT * FROM test_commonpool";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println("id: " + id + ", name: " + name);
}
}
}
private static void dropTable(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
String sql = "DROP TABLE test_commonpool";
statement.executeUpdate(sql);
System.out.println("Table dropped successfully.");
}
}
static class ConnectionFactory extends org.apache.commons.pool2.BasePooledObjectFactory<Connection> {
private final String url;
private final String username;
private final String password;
public ConnectionFactory(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}
@Override
public Connection create() throws Exception {
return DriverManager.getConnection(url, username, password);
}
@Override
public void destroyObject(org.apache.commons.pool2.PooledObject<Connection> p) throws Exception {
p.getObject().close();
}
@Override
public boolean validateObject(org.apache.commons.pool2.PooledObject<Connection> p) {
try {
return p.getObject().isValid(5000);
} catch (SQLException e) {
return false;
}
}
@Override
public PooledObject<Connection> wrap(Connection connection) {
return new DefaultPooledObject<>(connection);
}
}
}
References
For more information about MySQL Connector/J, see Overview of MySQL Connector/J.