C3P0 connection pool dependencies and sample code-ApsaraDB for OceanBase (Deprecated)(OceanBase)-阿里云帮助中心

This topic describes how to use a C3P0 connection pool, MySQL Connector/J, and an OceanBase database to build an application. The application performs basic database operations, such as creating tables, and inserting, deleting, updating, and querying data.

Click to download the c3p0-mysql-jdbc sample project

Prerequisites

You have installed OceanBase Database and created a MySQL-mode tenant.
You have installed JDK 1.8 and Maven.
You have installed Eclipse.

Note
The sample code in this topic was run using Eclipse IDE for Java Developers 2022-03. You can also use another tool to run the sample code.

Procedure

Note

The steps in this topic describe how to compile and run the project in a Windows environment using Eclipse IDE for Java Developers 2022-03. The steps might differ slightly if you use a different operating system or compiler.

Import the c3p0-mysql-jdbc project into Eclipse.
Obtain the OceanBase database URL.
Modify the database connection information in the c3p0-mysql-jdbc project.
Run the c3p0-mysql-jdbc project.

Step 1: Import the c3p0-mysql-jdbc project into Eclipse

Open Eclipse. From the menu bar, choose File > Open Projects from File System.
In the dialog box that appears, click Directory to select the project directory, and then click Finish to import the project.

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

Step 2: Obtain the OceanBase database URL

Contact the OceanBase database administrator to obtain the database connection string.
Example:
```
obclient -hxxx.xxx.xxx.xxx -P3306 -utest_user001 -p****** -Dtest
```
For more information about connection strings, see Obtain connection parameters.
Use the information from the OceanBase database connection string to complete the following URL.
```
jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password
```
Parameter description:
- $host: The domain name for the OceanBase database connection.
- $port: The OceanBase database connection port. The default port for a MySQL-mode tenant is 3306.
- $database_name: The name of the database to access.
- $user_name: The connection account for the tenant.
- $password: The account password.
For more information about MySQL Connector/J connection properties, see Configuration Properties.
Example:
```
jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=test_user001&password=******
```

Step 3: Modify the database connection information in the c3p0-mysql-jdbc project

Modify the database connection information in the c3p0-mysql-jdbc/src/main/resources/c3p0-config.xml file with the information that you obtained in Step 2.

Example:

The IP address of the OBServer node is xxx.xxx.xxx.xxx.
The access port is 3306.
The name of the database to access is test.
The connection account for the tenant is test_user001.
The password is ******.

Code:

...
        <property name="jdbcUrl">jdbc:mysql://xxx.xxx.xxx.xxx:3306/test</property>
        <property name="user">test_user001</property>
        <property name="password">******</property>
...

Step 4: Run the c3p0-mysql-jdbc project

In the Project Explorer view, find and expand the src/main/java directory.
Right-click the Main.java file, and then choose Run As > Java Application.
View the project logs and output results in the Eclipse console window.

You can also run the following SQL statement in the OceanBase client (OBClient) to view the result.

obclient [test]> SELECT * FROM test_c3p0;

The following result is returned:

+------+--------------+
| id   | name         |
+------+--------------+
|    5 | test_update  |
|    6 | test_insert6 |
|    7 | test_insert7 |
|    8 | test_insert8 |
|    9 | test_insert9 |
+------+--------------+
5 rows in set

Project code overview

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

After decompression, a folder named c3p0-mysql-jdbc is created. The directory structure is as follows:

c3p0-mysql-jdbc
├── src
│   └── main
│       ├── java
│       │   └── com
│       │        └── example
│       │           └── Main.java
│       └── resources
│           └── c3p0-config.xml 
└── pom.xml

File description:

src: The root directory for the source code.
main: The main code directory, which contains the primary logic of the application.
java: The Java source code directory.
com: The Java package directory.
example: The package directory for the sample project.
Main.java: The main class, which contains the logic for creating tables and inserting data.
resources: The resource file directory, which contains configuration files.
c3p0-config.xml: The configuration file for the C3P0 connection pool.
pom.xml: The configuration file for the Maven project. It is used to manage project dependencies and build settings.

pom.xml code overview

pom.xml is the configuration file for a Maven project. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that automatically downloads dependencies, and compiles and packages projects.

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

File declaration statement.
Declares that this file is an XML file, the XML version is 1.0, and the character encoding is UTF-8.
Code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure the POM namespace and POM model version.
1. The xmlns attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0.
2. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
3. The xsi:schemaLocation attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM's XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
4. The <modelVersion> element specifies that the POM model version used by this POM file is 4.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>

 

</project>
```
Configure basic information.
1. The <groupId> element specifies the project's organization as com.example.
2. The <artifactId> element specifies the project name as testc3p0.
3. The <version> element specifies the project version number as 1.0-SNAPSHOT.
Code:
```
    <groupId>com.example</groupId>
    <artifactId>testc3p0</artifactId>
    <version>1.0-SNAPSHOT</version>
```
Configure project source file properties.
This configuration sets the Maven compiler plugin to maven-compiler-plugin and the source and target Java versions to 8. This ensures the project's source code, which uses Java 8 features, is compiled into bytecode that is compatible with the Java 8 runtime environment.

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

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

Note
This section of code defines the project's dependency on MySQL Connector/J version 8.0.25. For information about other versions, see MySQL Connector/J.

Dependencies are defined using the <dependency> element:
- Add the mysql-connector-java dependency library:
1. The <groupId> element specifies the dependency's organization as mysql.
2. The <artifactId> element specifies the dependency name as mysql-connector-java.
3. The <version> element specifies the dependency version number as 8.0.25.
- Add the c3p0 dependency library:
1. The <groupId> element specifies the dependency's organization as com.mchange.
2. The <artifactId> element specifies the dependency name as c3p0.
3. The <version> element specifies the dependency version number as 0.9.5.5.
Code:
```
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.5</version>
        </dependency>
    </dependencies>
```

c3p0-config.xml code overview

c3p0-config.xml is the configuration file for the C3P0 connection pool. It is used to configure properties related to the database connection. By setting the values of the <property> elements, you can configure the database driver, connection URL, username, password, and connection pool size.

The code in the c3p0-config.xml file in this topic includes the following main parts:

File declaration statement.
Declares that this file is an XML file, the XML version is 1.0, and the character encoding is UTF-8.
Code:
```
<?xml version="1.0" encoding="UTF-8"?>
```
Configure basic information.
1. The <c3p0-config> element contains the configuration information for the C3P0 connection pool.
2. The <named-config name="oceanbase"> element defines a named configuration called oceanbase. In the code, you can use this name to reference the configuration and retrieve connection information and connection pool properties related to the oceanbase database.
Code:
```
<c3p0-config>
    <named-config name="oceanbase">

        // Configure the values of the <property> elements

    </named-config>
</c3p0-config>
```
Configure the database driver.
The <property> element specifies the class name of the MySQL JDBC driver used to connect to the OceanBase database as com.mysql.cj.jdbc.Driver.

Note
For more information about the names of MySQL Connector/J implementation classes, see Driver/Datasource Class Name.

Code:
```
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
```
Configure database connection information.
1. Set the URL for the database connection, including the host IP address, port number, database to access, and URL parameters.
2. Configure the database username.
3. Configure the database password.
Code:
```
        <property name="jdbcUrl">jdbc:mysql://$host:$port/$database_name</property>
        <property name="user">$user_name</property>
        <property name="password">$password</property>
```
Parameter description:
- $host: The domain name for the OceanBase database connection.
- $port: The OceanBase database connection port. The default port for a MySQL-mode tenant is 3306.
- $database_name: The name of the database to access.
- $user_name: The connection account for the tenant.
- $password: The account password.
Configure other C3P0 connection pool configuration items.
1. Sets the number of connections to acquire at a time to 20. When the connection pool runs out of connections, it acquires 20 new connections.
2. Sets the initial size of the connection pool to 10. The pool creates 10 connections at startup.
3. Sets the minimum number of connections in the pool to 5. The pool maintains at least 5 connections.
4. Sets the maximum number of connections in the pool to 30. The pool allows a maximum of 30 connections.
5. You can set the maximum number of cached statements for each connection to 0 to disable statement caching.
6. Sets the maximum number of cached statements for each connection to 0. This setting disables statement caching for each connection.
7. Sets the number of auxiliary threads that C3P0 uses to 3. These threads perform slow JDBC operations.
8. Set the property check period for c3p0 connections to 3 seconds. This ensures that connection properties are checked at 3-second intervals.
9. Sets the timeout for acquiring a connection to 1,000 milliseconds. If a connection cannot be acquired within this time, a timeout exception is thrown.
10. Sets the interval for testing idle connections to 3 seconds. The pool tests the validity of idle connections every 3 seconds.
11. Sets the maximum idle time for connections to 10 seconds. If a connection is idle for more than 10 seconds, it is closed.
12. Set the maximum idle time to 5 seconds for connections that exceed the maximum connections limit of the connection pool. These connections are closed if they remain idle for more than 5 seconds.
13. Sets the delay for retrying a failed connection acquisition to 1,000 milliseconds. If acquiring a connection fails, the pool waits 1,000 milliseconds before retrying.
14. Sets the automatic test table for C3P0 to Test. This table is used to test if a connection is valid.
15. Sets whether to test a connection's validity when it is returned to the pool. If set to true, a validity test is performed when the connection is returned.
Code:
```
       <property name="acquireIncrement">20</property>
       <property name="initialPoolSize">10</property>
       <property name="minPoolSize">5</property>
       <property name="maxPoolSize">30</property>
       <property name="maxStatements">0</property>
       <property name="maxStatementsPerConnection">0</property>
       <property name="numHelperThreads">3</property>
       <property name="propertyCycle">3</property>
       <property name="checkoutTimeout">1000</property>
       <property name="idleConnectionTestPeriod">3</property>
       <property name="maxIdleTime">10</property>
       <property name="maxIdleTimeExcessConnections">5</property>
       <property name="acquireRetryDelay">1000</property>
       <property name="automaticTestTable">Test</property>
       <property name="testConnectionOnCheckin">true</property>
```

Important

The specific property (parameter) configuration depends on project requirements and database characteristics. Adjust and configure them as needed. For more information about C3P0 connection pool configuration parameters, see C3P0.

Common C3P0 connection pool configuration items:

Category	Property	Default value	Description
Required	driverClass	N/A	The driver class name.
	jdbcUrl	N/A	The connection URL for the database.
	user	N/A	The username for connecting to the database.
	password	N/A	The password for connecting to the database.
Basic configuration	acquireIncrement	3	Sets the number of connections to acquire at one time when the pool runs out. For example, if `acquireIncrement` is set to 20 and the pool has only 5 idle connections, the pool creates 20 new connections at once to meet the application's demand.
	acquireRetryAttempts	30	Sets the number of retries after a new connection fails to be acquired from the database. If this value is less than or equal to zero, C3P0 continues to try to acquire a connection indefinitely.
	maxIdleTime	0	Sets the maximum idle time for a connection in the pool. A value of 0 means idle connections never expire. For example, if `maxIdleTime` is set to 10 seconds, a connection that is idle for more than 10 seconds is closed and removed from the pool. The next time the application requests a connection, the pool creates a new one.
	maxPoolSize	15	Sets the maximum number of connections in the pool. When the number of connections reaches the value of `maxPoolSize`, new connection requests are blocked until a connection is released back to the pool.
	minPoolSize	3	Sets the minimum number of connections in the pool. The pool maintains at least the number of connections specified by `minPoolSize`, even when they are not in use.
	initialPoolSize	3	Sets the number of connections to create when the pool starts. The value should be between `minPoolSize` and `maxPoolSize`. When the pool is initialized, it creates the number of connections specified by `initialPoolSize`.
Optional configuration	acquireRetryDelay	1000	Sets the delay in milliseconds between connection acquisition retries. When an application requests a connection and none are available, the pool retries based on the `acquireRetryDelay` setting.
	autoCommitOnClose	false	Sets whether to automatically commit transactions when a connection is closed. The default value is `false`, which means transactions are not automatically committed when a connection is closed. If the application needs to explicitly commit transactions before closing a connection, set `autoCommitOnClose` to `true`. Important Autocommitting transactions can lead to data inconsistency or loss. Therefore, use `autoCommitOnClose` with caution and only when transaction integrity is ensured. In most cases, manage transactions manually to ensure that commits or rollbacks occur at the appropriate time.
	automaticTestTable	null	Sets the automatic test table for the connection pool. C3P0 creates an empty table with the specified name and uses queries against it to test connections. The default value is `null`, which means no test statement is executed. For example, if `automaticTestTable` is set to `Test`, C3P0 creates an empty table named `Test` and uses its own query statement for testing. Note If both `automaticTestTable` and `preferredTestQuery` are configured, C3P0 prioritizes `preferredTestQuery` for test queries and ignores the `automaticTestTable` setting.
	idleConnectionTestPeriod	0	Sets the time interval in milliseconds for the pool to perform idle connection testing. The pool tests idle connections at this interval. The default value is 0, which means no idle connection testing is performed.
	maxStatements	0	Sets the maximum number of prepared statements for the connection pool. Note If both `maxStatements` and `maxStatementsPerConnection` are 0, statement caching is disabled. If `maxStatements` is 0 but `maxStatementsPerConnection` is non-zero, statement caching is enabled, but no global limit is enforced. Only the per-connection limit is effective. `maxStatements` controls the total number of cached statements for all connections in the pool. If `maxStatements` is set, it should be a fairly large number, because each connection in the pool requires its own independent set of cached statements.
	maxStatementsPerConnection	0	Sets the maximum number of prepared statements allowed for each connection. Note If both `maxStatements` and `maxStatementsPerConnection` are 0, statement caching is disabled. If `maxStatementsPerConnection` is 0 but `maxStatements` is non-zero, statement caching is enabled, and the global limit is enforced, but there is no limit on the number of cached statements for a single connection.
	numHelperThreads	3	Specifies the number of auxiliary threads for asynchronous task processing. Note The more auxiliary threads, the more tasks can be processed in parallel, which improves the processing capacity and response speed of the connection pool. Setting too many auxiliary threads can lead to excessive consumption of system resources. Therefore, set the value of `numHelperThreads` reasonably based on the system's hardware configuration and performance tests.
	preferredTestQuery	null	Defines a test statement that is executed for all connection tests. Using this can significantly improve testing speed. Important The test table must exist when the data source is initialized.
	checkoutTimeout	0	Specifies the timeout in milliseconds for acquiring a connection from the pool. The default value is 0, which means no timeout. When the pool is exhausted, a client calling `getConnection()` waits for this amount of time to get a new connection. After the timeout, an `SQLException` is thrown.
Not recommended	breakAfterAcquireFailure	false	Controls whether to interrupt the pool's acquisition operation when a connection acquisition fails. A connection acquisition failure will cause all threads waiting for a connection to throw an exception. However, the data source remains valid and will try to acquire a connection again on the next call to `getConnection()`. If set to `true`, after multiple failed connection acquisition attempts, the pool will no longer try to acquire connections and will instead fail fast and throw an exception. If set to `false`, the pool will continue to try to acquire a connection until the acquisition timeout is reached.
	testConnectionOnCheckout	false	Specifies whether to test a connection when it is checked out from the pool. If set to `true`, a connection test is performed when a connection is checked out. Use this feature with caution, as it will at least double the number of database calls. If set to `false`, no connection test is performed. Note Although connection testing ensures that connections are valid, it also adds extra overhead. Therefore, decide whether to enable connection testing based on specific application requirements and performance needs. If the application has high requirements for connection availability, you can enable connection testing. However, if connections are frequently checked out and released, testing can become too frequent and affect performance.
	testConnectionOnCheckin	false	Specifies whether to test a connection when it is returned to the pool. If set to `true`, a connection test is performed when a connection is returned to the pool. Use this feature with caution, as it will also at least double the number of database calls. If set to `false`, no connection test is performed. Note Although connection testing ensures that connections are valid, it also adds extra overhead. Therefore, decide whether to enable connection testing based on specific application requirements and performance needs. If the application has high requirements for connection availability, you can enable connection testing. However, if connections are frequently checked out and returned, testing can become too frequent and affect performance.

Main.java code overview

Main.java is part of the sample program. It demonstrates how to retrieve a database connection from a C3P0 connection pool and perform a series of database operations within a transaction. These operations include creating a table, inserting data, deleting data, updating data, and querying data. The program then prints the query results. This process shows how to use a C3P0 connection pool to manage database connections and execute transaction operations, which improves the efficiency and performance of database operations.

The code in the Main.java file in this topic includes the following main parts:

Define the package and import java.sql interfaces.
1. Declare the current code's package name as com.example.
2. Import the java.sql.Connection class, which represents a database connection.
3. Import the java.sql.PreparedStatement class, which is used to execute precompiled database operations.
4. Import the java.sql.ResultSet class, which represents a database query result set.
5. Import the com.mchange.v2.c3p0.ComboPooledDataSource class, which is used for the C3P0 connection pool.
Code:
```
package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
```
Define the class name and methods.
1. Define a public class named Main as the program's entry point. The class name must match the file name.
2. Define a public static method main as the program's starting execution point.
3. Use a try-with-resources statement to retrieve a database connection and create precompiled SQL statements.
4. Perform database transaction operations.
5. Catch any potential exceptions and print the exception stack trace.
6. Define a private static method getConnection to retrieve a database connection from the C3P0 connection pool. Inside the method, first create a ComboPooledDataSource object cpds, which specifies the connection pool configuration through the oceanbase parameter. Then, use the cpds.getConnection() method to retrieve a database connection from the pool and return it.
Code:
```
public class Main {

    public static void main(String[] args) {
        try (
            // Get a database connection
            // Create precompiled SQL statements
            ) {

            // Database transaction operations: start transaction, create table, insert data, delete data, update data, query data, and commit transaction

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase");
        return cpds.getConnection();
    }
}
```
Obtain a database connection.
Retrieve a database connection and assign it to the conn variable.
Code:
```
             Connection conn = getConnection();
```

Create precompiled SQL statements.

Create a precompiled SQL statement to create a database table named test_c3p0.
Create a precompiled SQL statement to insert data into the test_c3p0 table.
Create a precompiled SQL statement to delete data from the test_c3p0 table.
Create a precompiled SQL statement to update data in the test_c3p0 table.
Create a precompiled SQL statement to query data from the test_c3p0 table.

Code:

             PreparedStatement stmtCreate = conn.prepareStatement("CREATE TABLE test_c3p0 (id INT, name VARCHAR(32))");
             PreparedStatement stmtInsert = conn.prepareStatement("INSERT INTO test_c3p0 VALUES (?, ?)");
             PreparedStatement stmtDelete = conn.prepareStatement("DELETE FROM test_c3p0 WHERE id < ?");
             PreparedStatement stmtUpdate = conn.prepareStatement("UPDATE test_c3p0 SET name = ? WHERE id = ?");
             PreparedStatement stmtSelect = conn.prepareStatement("SELECT * FROM test_c3p0")

Start the transaction.
Set the database connection's autocommit to false to enable the transaction mechanism.
Code:
```
            conn.setAutoCommit(false); 
```
Create the table.
Execute the SQL statement to create the table.
Code:
```
            stmtCreate.execute();
```
Insert data.
Use a for loop to insert 10 rows of data into the test_c3p0 table. The value of the first column is the value of the i variable, and the value of the second column is the string test_insert followed by the value of the i variable.
Code:
```
            for (int i = 0; i < 10; i++) {
                stmtInsert.setInt(1, i);
                stmtInsert.setString(2, "test_insert" + i);
                stmtInsert.executeUpdate();
            }
```
Delete data.
Set the parameter of the delete statement to 5 and execute the delete operation.
Code:
```
            stmtDelete.setInt(1, 5);
            stmtDelete.executeUpdate();
```
Update data.
Set the first parameter of the update statement to test_update and the second parameter to 5, and then execute the update operation.
Code:
```
            stmtUpdate.setString(1, "test_update");
            stmtUpdate.setInt(2, 5);
            stmtUpdate.executeUpdate();
```
Query data.
1. Execute the query statement and save the result in the ResultSet object rs.
2. Use a while loop and `rs.next()` to check if there is another row in the result set. If so, execute the code inside the loop.
3. The code inside the loop prints the values of the id and name columns for each row.
4. Close the result set to release related resources.
Code:
```
            ResultSet rs = stmtSelect.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "   " + rs.getString("name"));
            }
            rs.close();
```
Commit the transaction.
Code:
```
            conn.commit(); 
```

Complete code sample

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.oceanbase</groupId>
    <artifactId>testc3p0</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>8.0.25</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.5</version>
        </dependency>
    </dependencies>
</project>

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="oceanbase">
        <!-- Configure the database driver -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <!-- Configure the database connection URL -->
        <property name="jdbcUrl">jdbc:mysql://$host:$port/$database_name</property>
        <!-- Configure the database username -->
        <property name="user">$user_name</property>
        <!-- Configure the database password -->
        <property name="password">$password</property>
        <!-- The number of connections the pool acquires at one time when it runs out of connections. -->
        <property name="acquireIncrement">20</property>
        <!-- The number of connections to create when the pool is initialized. -->
        <property name="initialPoolSize">10</property>
        <!-- The minimum number of connections to keep in the pool. -->
        <property name="minPoolSize">5</property>
        <!-- The maximum number of connections in the pool. Default: 15. -->
        <property name="maxPoolSize">30</property>
        <!-- The number of PreparedStatement objects that the data source will cache. If both maxStatements and maxStatementsPerConnection are 0, statement caching is disabled. Default: 0. -->
        <property name="maxStatements">0</property>
        <!-- The number of cached statements for a single connection in the pool. Default: 0. -->
        <property name="maxStatementsPerConnection">0</property>
        <!-- C3P0 is asynchronous. Slow JDBC operations are performed by helper threads. Increasing the number of threads can improve performance. Default: 3. -->
        <property name="numHelperThreads">3</property>
        <!-- The interval in seconds for the background thread to perform maintenance tasks. Default: 300. -->
        <property name="propertyCycle">3</property>
        <!-- The time in milliseconds to wait for a connection from the pool before timing out. -->
        <property name="checkoutTimeout">1000</property>
        <!-- The interval in seconds to test idle connections. Default: 0. -->
        <property name="idleConnectionTestPeriod">3</property>
        <!-- The time in seconds that a connection can remain idle before being discarded. A value of 0 means never discard. Default: 0. -->
        <property name="maxIdleTime">10</property>
        <!-- The time in seconds that an excess connection can remain idle before being discarded. -->
        <property name="maxIdleTimeExcessConnections">5</property>
        <!-- The delay in milliseconds between connection acquisition retries. Default: 1000. -->
        <property name="acquireRetryDelay">1000</property>
        <!-- The name of a table that C3P0 creates to test connections. If this property is set, preferredTestQuery is ignored. Do not use this table for any other purpose. Default: null. -->
        <property name="automaticTestTable">Test</property>
        <!-- If true, tests the connection's validity when it is returned to the pool. -->
        <property name="testConnectionOnCheckin">true</property>
    </named-config>
</c3p0-config>

Main.java

package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Main {

    public static void main(String[] args) {
        try (Connection conn = getConnection();

             PreparedStatement stmtCreate = conn.prepareStatement("CREATE TABLE test_c3p0 (id INT, name VARCHAR(32))");
             PreparedStatement stmtInsert = conn.prepareStatement("INSERT INTO test_c3p0 VALUES (?, ?)");
             PreparedStatement stmtDelete = conn.prepareStatement("DELETE FROM test_c3p0 WHERE id < ?");
             PreparedStatement stmtUpdate = conn.prepareStatement("UPDATE test_c3p0 SET name = ? WHERE id = ?");
             PreparedStatement stmtSelect = conn.prepareStatement("SELECT * FROM test_c3p0")) {
            
            // Begin transaction
            conn.setAutoCommit(false); 

            // Create table
            stmtCreate.execute();

            // Insert data
            for (int i = 0; i < 10; i++) {
                stmtInsert.setInt(1, i);
                stmtInsert.setString(2, "test_insert" + i);
                stmtInsert.executeUpdate();
            }

            // Delete data
            stmtDelete.setInt(1, 5);
            stmtDelete.executeUpdate();

            // Update data
            stmtUpdate.setString(1, "test_update");
            stmtUpdate.setInt(2, 5);
            stmtUpdate.executeUpdate();

            // Query data
            ResultSet rs = stmtSelect.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "   " + rs.getString("name"));
            }
            rs.close();
            
            // Commit transaction
            conn.commit(); 
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Connection getConnection() throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase");
        return cpds.getConnection();
    }
}

References

For more information about MySQL Connector/J, see Overview of MySQL Connector/J.