This topic introduces how to build an application by using a c3p0 connection 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
c3p0-mysql-jdbcproject into Eclipse. - Obtain the URL of OceanBase AI Database.
- Modify the database connection information in the
c3p0-mysql-jdbcproject. - Run the
c3p0-mysql-jdbcproject.
Step 1: Import the c3p0-mysql-jdbc 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=$passwordParameter description:
$host: 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.$port: 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_name: The name of the database to be accessed.Notice
The user used to connect to the tenant must have the
CREATE,INSERT,DELETE,UPDATE, andSELECTprivileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.$user_name: 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.$password: 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=******
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 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:
...
<property name="jdbcUrl">jdbc:mysql://xxx.xxx.xxx.xxx:2881/test</property>
<property name="user">test_user001@mysql001</property>
<property name="password">******</property>
...
Step 4: Run the c3p0-mysql-jdbc 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.

In the console window of Eclipse, view the project's log information and output results.

You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results.
obclient [test]> SELECT * FROM test_c3p0;The return result is as follows:
+------+--------------+ | id | name | +------+--------------+ | 5 | test_update | | 6 | test_insert6 | | 7 | test_insert7 | | 8 | test_insert8 | | 9 | test_insert9 | +------+--------------+ 5 rows in set
Project code
Click c3p0-mysql-jdbc to download the project code, which is a compressed file named c3p0-mysql-jdbc.zip.
After decompressing it, you will get a folder named c3p0-mysql-jdbc. The directory structure is as follows:
c3p0-mysql-jdbc
├── src
│ └── main
│ ├── java
│ │ └── com
│ │ └── example
│ │ └── Main.java
│ └── resources
│ └── c3p0-config.xml
└── 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 primary 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, containing logic such as creating tables and inserting data.resources: The resource file directory, containing configuration files.c3p0-config.xml: The configuration file for the C3P0 connection pool.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 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.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and 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 astestc3p0. - Use
<version>to specify the project version as1.0-SNAPSHOT.
The sample code is as follows:
<groupId>com.example</groupId> <artifactId>testc3p0</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.
Note
The following code defines that the project depends on MySQL Connector/J version 8.0.25. For information about other versions, see MySQL Connector/J.
Define dependencies using
<dependency>:- Add the
mysql-connector-javadependency library:
- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency's name asmysql-connector-java. - Use
<version>to specify the dependency's version as8.0.25.
- Add the
c3p0dependency library:
- Use
<groupId>to specify the dependency's organization ascom.mchange. - Use
<artifactId>to specify the dependency's name asc3p0. - Use
<version>to specify the dependency's version as0.9.5.5.
The sample code is as follows:
<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>- Add the
Code in the c3p0-config.xml file
The c3p0-config.xml file is the configuration file for the C3P0 connection pool, used to configure properties related to database connections. By setting the values of various <property> elements, you can configure properties such as the database driver, connection URL, username, password, and connection pool size.
The code in this c3p0-config.xml file mainly includes the following parts:
The file declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure basic information.
- Use
<c3p0-config>to include the configuration information for the c3p0 connection pool. - Use
<named-config name="oceanbase">to define a named configuration with the nameoceanbase. In the code, you can use this name to reference the named configuration and obtain connection information and connection pool properties related to theoceanbasedatabase.
The sample code is as follows:
<c3p0-config> <named-config name="oceanbase"> // Configure the values for each <property> element. </named-config> </c3p0-config>- Use
Configure the database driver.
Use
<property>to specify the class namecom.mysql.cj.jdbc.Driverfor the MySQL JDBC driver used when connecting to OceanBase AI Database.Note
For information about the MySQL Connector/J implementation class name, see Driver/Datasource Class Name.
The sample code is as follows:
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>Configure database connection information.
- Set the database connection URL, including the host IP, port number, database to access, and URL parameters.
- Configure the database username.
- Configure the database password.
The sample code is as follows:
<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 IP address for connecting to OceanBase AI Database. For ODP connections, use an ODP address; for direct connections, use the IP address of an OBServer node.$port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment. For direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.$database_name: The name of the database to access.$user_name: The connection account for the tenant. For ODP connection, the format isusername@tenant_name#cluster_nameorcluster_name:tenant_name:username. For direct connection, the format isusername@tenant_name.$password: The password for the account.
Configure other parameters of the c3p0 database connection pool.
- Set the number of connections added at once when needed to 20. That is, when the number of connections in the pool is insufficient, 20 connections are added each time.
- Set the initial size of the connection pool to 10. That is, the connection pool pre-creates 10 connections upon startup.
- Set the minimum number of connections in the pool to 5. That is, the pool maintains a minimum of 5 connections.
- Set the maximum number of connections in the pool to 30. That is, the pool allows no more than 30 connections.
- Set the maximum number of cached statements per connection to 0. That is, no statements are cached.
- Set the maximum number of cached statements per connection in the pool to 0. That is, no statements are cached per connection.
- Set the number of auxiliary threads used by c3p0 to 3. These auxiliary threads execute slow JDBC operations.
- Set the property check cycle for c3p0 connections to 3 seconds. That is, connection properties are checked every 3 seconds.
- Set the timeout for obtaining a connection to 1000 milliseconds. That is, if a connection cannot be obtained within 1000 milliseconds, a timeout exception is thrown.
- Set the idle connection check cycle in the pool to 3 seconds. That is, the status of idle connections is checked every 3 seconds.
- Set the maximum idle time for a connection in the pool to 10 seconds. That is, if a connection is not used for 10 seconds, it is closed.
- Set the maximum idle time for a connection exceeding the maximum number in the pool to 5 seconds. That is, if a connection exceeds the maximum number and remains idle for more than 5 seconds, it is closed.
- Set the retry delay when attempting to obtain a connection to 1000 milliseconds. That is, if obtaining a connection fails, the attempt is retried after 1000 milliseconds.
- Set the automatic test table for c3p0 to
Test. This is a special table used to test whether a connection is valid. - Set whether to test the validity of a connection when returning it to the pool. If set to true, a connection validity test is performed when the connection is returned to the pool.
The sample code is as follows:
<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>
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. For more information about c3p0 parameters, see c3p0.
The following table describes the basic parameters of the c3p0 connection pool.
Category |
Parameter |
Default value |
Description |
|---|---|---|---|
| Required parameters | driverClass | N/A | The name of the driver class. |
| jdbcUrl | N/A | The URL for connecting to the database. | |
| user | N/A | The username used to connect to the database. | |
| password | N/A | The password used to connect to the database. | |
| Basic settings | acquireIncrement | 3 | The number of connections acquired at once from the connection pool when needed. For example, if the value of acquireIncrement is 20 and the connection pool currently has only 5 free connections, the connection pool will create 20 new connections at once to meet the application's demand when a connection is requested. |
| acquireRetryAttempts | 30 | The number of retries after which C3P0 stops trying to obtain a new connection from the database. If this value is less than or equal to zero, C3P0 will continue to try indefinitely to obtain a connection. | |
| maxIdleTime | 0 | The maximum idle time of a connection in the connection pool. A value of 0 indicates that an idle connection never expires. For example, if you set maxIdleTime to 10 seconds, a connection in the connection pool that remains idle for more than 10 seconds will be closed and removed by the connection pool. The connection pool will create a new connection when the application requests a connection next time. |
|
| maxPoolSize | 15 | The maximum number of connections in the connection pool. When the number of connections in the connection pool reaches the value specified by maxPoolSize, new connection requests are blocked until a connection is released back to the connection pool. |
|
| MinPoolSize | 3 | Specifies the minimum number of connections in the connection pool. Even when a connection is not in use, the pool maintains at least minPoolSize connections. |
|
| initialPoolSize | 3 | Specifies the number of connections pre-created when the connection pool starts. The value must be between minPoolSiz and maxPoolSize. That is, initialPoolSize connections are created during pool initialization. |
|
| Optional parameters | acquireRetryDelay | 1000 | The retry delay in milliseconds when acquiring a connection. If no connection is available in the pool when an application requests one, the request may fail. In this case, the pool retries according to the configuration of acquireRetryDelay. |
| autoCommitOnClose | false | Specifies whether to automatically commit transactions when a connection is closed. The default value is false, meaning transactions are not automatically committed upon connection closure. If an application needs to explicitly commit transactions before closing a connection, set autoCommitOnClose to true.
NoticeAutomatic transaction commit may lead to data inconsistency or loss. Therefore, use |
|
| automaticTestTable | null | The automatic test table for the connection pool. C3P0 creates an empty table with the specified name and uses queries against this table to test the Connection. The default value is null, indicating no test statements are executed. For example, if automaticTestTable is set to Test, C3P0 creates an empty table named Test and uses its built-in query statements for testing.
NoteIf both |
|
| idleConnectionTestPeriod | 0 | The interval in milliseconds for detecting idle connections in the pool. The pool tests idle connections at regular intervals. The default value is 0, indicating no idle connection detection. | |
| maxStatements | 0 | The maximum number of prepared statements allowed in the connection pool.
Note
|
|
| maxStatementsPerConnection | 0 | The maximum number of prepared statements allowed per connection.
Note
|
|
| numHelperThreads | 3 | The number of helper threads used for asynchronous task processing.
Note
|
|
| preferredTestQuery | null | Defines the test statement that is executed in all connection tests. When connection testing is used, this significantly improves test speed.
NoticeThe test table must already exist when the data source is initialized. |
|
| checkoutTimeout | 0 | Specifies the timeout period, in milliseconds, for obtaining a connection from the connection pool. The default value is 0, indicating no timeout limit. This is the duration the client waits to obtain a new connection after calling getConnection() when the connection pool is exhausted. If the timeout occurs, a SQLException is thrown. |
|
| Unrecommended parameters | breakAfterAcquireFailure | false | Controls whether to interrupt the connection pool acquisition operation when a connection acquisition fails. A failed connection acquisition causes all threads waiting to obtain a connection from the pool to throw an exception. However, the data source is retained and the next call to getConnection() will continue the connection acquisition attempt.
|
| testConnectionOnCheckout | false | Specifies whether to test the connection when obtaining it from the connection pool.
NoteAlthough performing a connection test ensures the connection is valid, it also introduces some additional overhead. Therefore, decide whether to enable connection testing based on your specific application requirements and performance needs. If the application has high requirements for connection availability, you can enable connection testing. However, if connections in the pool are frequently acquired and released, it may lead to overly frequent connection tests, which can impact performance. |
|
| testConnectionOnCheckin | false | Specifies whether to test the connection when returning it to the connection pool.
NoteAlthough performing a connection test ensures the connection is valid, it also introduces some additional overhead. Therefore, decide whether to enable connection testing based on your specific application requirements and performance needs. If the application has high requirements for connection availability, you can enable connection testing. However, if connections in the pool are frequently acquired and returned, it may lead to overly frequent connection tests, which can impact performance. |
Code in the Main.java file
The Main.java file is part of the sample program, demonstrating how to obtain a database connection through the c3p0 connection pool and execute a series of database operations within a transaction. These operations include creating a table, inserting data, deleting data, updating data, querying data, and printing the query results. It shows how to use the c3p0 connection pool to manage database connections and perform transactional operations, thereby improving the efficiency and performance of database operations.
The code in the Main.java file mainly consists of the following parts:
Define packages and import interfaces from
java.sql.- Declare that the current code belongs to the package named
com.example. - Import the
java.sql.Connectionclass, which represents a database connection. - Import the
java.sql.PreparedStatementclass, which is used to execute precompiled database operations. - Import the
java.sql.ResultSetclass, which represents a database query result set. - Import the
com.mchange.v2.c3p0.ComboPooledDataSourceclass, which is used with the c3p0 connection pool.
The sample code is as follows:
package com.example; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import com.mchange.v2.c3p0.ComboPooledDataSource;- Declare that the current code belongs to the package named
Define the class name and methods.
- 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. - Use a
try-with-resourcesstatement to obtain a database connection and create a precompiled SQL statement. - Perform database transaction operations.
- Catch any possible exceptions and print the exception stack trace.
- Define a private static method
getConnectionto obtain a database connection from the c3p0 connection pool. Inside the method, first create aComboPooledDataSourceobject namedcpds, which specifies the connection pool configuration using theoceanbaseparameter. Then, obtain a database connection from the connection pool using thecpds.getConnection()method and return it.
The sample code is as follows:
public class Main { public static void main(String[] args) { try ( //Obtain a database connection // Create a precompiled SQL statement ) { // Database transaction operations: start a transaction, create a table, insert data, delete data, update data, query data, and commit a transaction. } catch (Exception e) { e.printStackTrace(); } } private static Connection getConnection() throws Exception { ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase"); return cpds.getConnection(); } }- Define a public class named
Obtain a database connection.
Obtain a database connection and assign it to the
connvariable.The sample code is as follows:
Connection conn = getConnection();Create precompiled SQL statements.
- Create a precompiled SQL statement for creating a database table named
test_c3p0. - Create a precompiled SQL statement for inserting data into the
test_c3p0table. - Create a precompiled SQL statement for deleting data from the
test_c3p0table. - Create a precompiled SQL statement for updating data in the
test_c3p0table. - Create a precompiled SQL statement for querying data from the
test_c3p0table.
The sample code is as follows:
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")- Create a precompiled SQL statement for creating a database table named
Begin a transaction.
Set the database connection's auto-commit to
falseto enable the transaction mechanism.The sample code is as follows:
conn.setAutoCommit(false);Create a table.
Execute the SQL statement to create the table.
The sample code is as follows:
stmtCreate.execute();Insert data.
Use a
forloop to insert 10 rows of data into thetest_c3p0table. The value in the first column is the value of variablei, and the value in the second column is the stringtest_insertconcatenated with the value of variablei.The sample code is as follows:
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.
The sample code is as follows:
stmtDelete.setInt(1, 5); stmtDelete.executeUpdate();Update data.
Set the first parameter of the update statement to
test_updateand the second parameter to 5, then execute the update operation.The sample code is as follows:
stmtUpdate.setString(1, "test_update"); stmtUpdate.setInt(2, 5); stmtUpdate.executeUpdate();Query data.
- Execute the query statement and save the query result in the
ResultSetobjectrs. - Use a while loop to check if there is another row of data in the result set by using rs.next(). If so, execute the code inside the loop.
- The code inside the loop prints the values of the
idandnamecolumns for each row. - Close the result set to release related resources.
The sample code is as follows:
ResultSet rs = stmtSelect.executeQuery(); while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } rs.close();- Execute the query statement and save the query result in the
Commit the transaction.
The sample code is as follows:
conn.commit();
Complete 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.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>
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="oceanbase">
<!-- Configure Database Driver -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<!-- Configure Database Link Address -->
<property name="jdbcUrl">jdbc:mysql://$host:$port/$database_name</property>
<!-- Configure database username -->
<property name="user">$user_name</property>
<!-- Configure database password -->
<property name="password">$password</property>
<!-- How many connection objects does the database Connection pool want from the database at one time -->
<property name="acquireIncrement">20</property>
<!-- Initialize connections -->
<property name="initialPoolSize">10</property>
<!-- Minimum number of connections -->
<property name="minPoolSize">5</property>
<!-- The maximum number of connections reserved in the Connection pool. Default: 15 -->
<property name="maxPoolSize">30</property>
<!-- JDBC standard parameter used to control the number of PreparedStatements loaded within the data source. However, the pre cached statements belong to a single connection rather than the entire Connection pool. So setting this parameter requires considering multiple factors. If both maxStatements and maxStatementsPerConnection are 0, the cache is turned off. Default:0 -->
<property name="maxStatements">0</property>
<!-- MaxStatementsPerConnection defines the maximum number of cached statements owned by a single connection in the Connection pool. Default: 0 -->
<property name="maxStatementsPerConnection">0</property>
<!-- C3p0 is an asynchronous operation, and slow JDBC operations are completed by the helper process. Expanding these operations can effectively improve performance by enabling multiple operations to be executed simultaneously through multithreading. Default:3 -->
<property name="numHelperThreads">3</property>
<!-- The user can wait up to 300 seconds before modifying the system configuration parameters. Default: 300 -->
<property name="propertyCycle">3</property>
<!-- The default setting for obtaining the connection timeout is to wait for a unit of milliseconds -->
<property name="checkoutTimeout">1000</property>
<!-- Check all free connections in the Connection pool every few seconds. Default: 0 -->
<property name="idleConnectionTestPeriod">3</property>
<!-- The maximum idle time, within seconds, if not used, the connection will be discarded. If it is 0, it will never be discarded. Default: 0 -->
<property name="maxIdleTime">10</property>
<!-- Configure the lifetime of the connection. Connections beyond this time will be automatically disconnected and discarded by the Connection pool. Of course, the connection being used will not be immediately disconnected, but will wait for it to close before disconnecting. When configured to 0, there is no restriction on the lifetime of the connection. -->
<property name="maxIdleTimeExcessConnections">5</property>
<!-- The interval time between two connections, in milliseconds. Default: 1000 -->
<property name="acquireRetryDelay">1000</property>
<!-- C3p0 will create an empty table called Test and use its built-in query statement for testing. If this parameter is defined, the property preferredTestQuery will be ignored. You cannot perform any operations on this Test table, it will only be used for c3p0 testing. Default: null -->
<property name="automaticTestTable">Test</property>
<!-- Test if the connection is valid when obtaining it -->
<property name="testConnectionOnCheckin">true</property>
</named-config>
</c3p0-config>
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.
Download the c3p0-mysql-jdbc sample project