Skip to main content

C3P0 connection pool and seekdb sample program

This topic describes how to build an application by using C3P0 connection pool, MySQL Connector/J, and seekdb. The application implements basic database operations, including table creation, data insertion, data deletion, data update, and data query.

Click here to download the c3p0-mysql-jdbc sample project.

Prerequisites​

  • You have installed seekdb.

  • You have installed JDK 1.8 and Maven.

  • You have installed Eclipse.

    info

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

Procedure​

info

The following steps are for compiling and running the project in Eclipse IDE for Java Developers 2022-03 in the Windows environment. If you use a different operating system or compiler, the steps may vary.

  1. Import the c3p0-mysql-jdbc project into Eclipse.
  2. Obtain the seekdb URL.
  3. Modify the database connection information in the c3p0-mysql-jdbc project.
  4. Run the c3p0-mysql-jdbc project.

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

  1. Open Eclipse and select File > Open Projects from File System from the menu bar.

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

    info

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

    Import

  3. View the project.

    p1

Step 2: Obtain the seekdb URL​

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

    Example:

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

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

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

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

    Parameter description:

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

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

    Example:

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

Example:

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

Code:

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

Step 4: Run the c3p0-mysql-jdbc project​

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

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

    run

  3. View the project's log information and output results in the Eclipse console window.

    log

  4. You can also execute the following SQL statement in seekdb to view the results.

    SELECT * FROM test_c3p0;

    The returned 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 find 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

File description:

  • src: the root directory of the source code.
  • main: the main code directory, containing the core logic of the application.
  • java: the directory for Java source code.
  • com: the directory for Java packages.
  • example: the directory for packages in the sample project.
  • Main.java: the main class, containing logic such as table creation and data insertion.
  • resources: the directory for resource files, including configuration files.
  • c3p0-config.xml: the configuration file for the C3P0 connection pool.
  • pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

pom.xml code introduction​

The pom.xml file is a configuration file for a Maven project. It defines 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 pom.xml file in this topic includes the following main sections:

  1. File declaration statement.

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

    Code:

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

  2. Configure the POM namespace and POM model version.

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

    Code:

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

     <!-- Other configurations -->

    </project>

  3. Configure basic information.

    1. Use <groupId> to specify the project's group ID as com.example.
    2. Use <artifactId> to specify the project's name as testc3p0.
    3. Use <version> to specify the project's version as 1.0-SNAPSHOT.

    Code:

        <groupId>com.example</groupId>
        <artifactId>testc3p0</artifactId>
        <version>1.0-SNAPSHOT</version>

  4. Configure the properties of the project's source files.

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

    info

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

    Code:

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

  5. Configure the components that the project depends on.

    info

    This section defines that the project depends on MySQL Connector/J version 8.0.25. For information about other versions, see MySQL Connector/J.

    Use <dependency> to define the dependencies:

    • Add the mysql-connector-java dependency library:
    1. Use <groupId> to specify the dependency's group ID as mysql.
    2. Use <artifactId> to specify the dependency's name as mysql-connector-java.
    3. Use <version> to specify the dependency's version as 8.0.25.
    • Add the c3p0 dependency library:
    1. Use <groupId> to specify the dependency's group ID as com.mchange.
    2. Use <artifactId> to specify the dependency's name as c3p0.
    3. Use <version> to specify the dependency's version 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>

Introduction to c3p0-config.xml​

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 article's c3p0-config.xml file mainly includes the following parts:

  1. File declaration statement.

Declares this file as an XML file using XML version 1.0 and character encoding UTF-8.

Code:

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

  1. Basic configuration.

  2. The <c3p0-config> element contains the configuration information for the C3P0 connection pool. 2. The <named-config name="seekdb"> element defines a named configuration with the name seekdb. In the code, you can use this name to reference the named configuration and obtain connection information and connection pool properties related to the seekdb database.

Code:

   <c3p0-config>
       <named-config name="seekdb">

// Configure the values of various <property> elements

</named-config>
   </c3p0-config>
  1. Database driver configuration.

The <property> element specifies the MySQL JDBC driver class name as com.mysql.cj.jdbc.Driver for connecting to seekdb.

info

For information about the class name of the MySQL Connector/J implementation, see Driver/Datasource Class Name.

Code:

            <property name="driverClass">com.mysql.cj.jdbc.Driver</property>

  1. Database connection configuration.

  2. Sets the database connection URL, including the host IP, port number, database to be accessed, and URL parameters. 2. Configures the database username. 3. Configures 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 explanation:

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

  1. Other C3P0 database connection pool configurations.

  2. Sets the number of connections to be added at once when the connection pool needs more connections to 20. That is, when the number of connections in the connection pool is insufficient, 20 connections are added each time. 2. Sets the initial size of the connection pool to 10. That is, 10 connections are created in the connection pool when it starts. 3. Sets the minimum number of connections in the connection pool to 5. That is, the number of connections in the connection pool is not less than 5. 4. Sets the maximum number of connections in the connection pool to 30. That is, the number of connections in the connection pool does not exceed 30. 5. Sets the maximum number of cached statements per connection to 0. That is, statements are not cached. 6. Sets the maximum number of cached statements per connection in the connection pool to 0. That is, statements are not cached for each connection. 7. Sets the number of auxiliary threads used by C3P0 to 3. These auxiliary threads are used to execute slow JDBC operations. 8. Sets the attribute check cycle for C3P0 connections to 3 seconds. That is, the attributes of the connections are checked every 3 seconds. 9. Sets 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. 10. Sets the idle connection check cycle in the connection pool to 3 seconds. That is, the status of idle connections is checked every 3 seconds. 11. Sets the maximum idle time for connections in the connection pool to 10 seconds. That is, if a connection is not used within 10 seconds, it will be closed. 12. Sets the maximum idle time for connections exceeding the maximum number of connections in the connection pool to 5 seconds. That is, if a connection exceeds the maximum number of connections and remains idle for more than 5 seconds, it will be closed. 13. Sets the retry delay time when attempting to obtain a connection to 1000 milliseconds. That is, if obtaining a connection fails, it will be retried after 1000 milliseconds. 14. Sets the automatic test table for C3P0 to Test. This is a special table used to test whether a connection is valid. 15. Sets whether to test the validity of a connection when returning it to the connection pool. If set to true, the validity of the connection is tested when it is returned to the connection pool.

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>
tip

The specific property (parameter) configurations depend on project requirements and database characteristics. We recommend that you adjust and configure them based on your actual situation. For more information about C3P0 connection pool configuration parameters, see C3P0.

Common C3P0 connection pool configurations:

ClassificationParameterDefaultDescription
RequireddriverClassN/ADriver class name
RequiredjdbcUrlN/AThe URL of the database to connect to.
RequireduserN/AThe username to use when connecting to the database.
RequiredpasswordN/AThe password to use when connecting to the database.
BasicacquireIncrement3The number of connections to acquire at once from the connection pool when needed. For example, if acquireIncrement is set to 20 and the connection pool currently has only 5 idle connections, the connection pool will create 20 new connections at once to meet the application's needs when a connection is requested.
BasicacquireRetryAttempts30The number of retries to attempt when acquiring a new connection from the database. If this value is less than or equal to zero, C3P0 will continue to attempt to acquire a connection indefinitely.
BasicmaxIdleTime0The maximum idle time for a connection in the connection pool. A value of 0 means that idle connections will never expire. For example, if maxIdleTime is set to 10 seconds, any idle connection in the connection pool that has been idle for more than 10 seconds and has not been used will be closed and removed by the connection pool. When the application requests a connection again, the connection pool will create a new connection.
BasicmaxPoolSize15The 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 will be blocked until a connection is released back to the connection pool.
BasicMinPoolSize3The minimum number of connections in the connection pool. The connection pool will maintain at least the number of connections specified by minPoolSize even when connections are not in use.
BasicinitialPoolSize3The number of connections to pre-create in the connection pool when it starts. The value should be between minPoolSiz and maxPoolSize. That is, when the connection pool is initialized, it will create the number of connections specified by initialPoolSize.
OptionalacquireRetryDelay1000The retry delay time in milliseconds when acquiring a connection. When an application requests a connection from the connection pool and no available connections are available, a connection acquisition failure may occur. In this case, the connection pool will retry based on the configuration of acquireRetryDelay.
OptionalautoCommitOnClosefalseWhether to automatically commit transactions when the connection is closed. The default value is false, which means that transactions are not automatically committed when the connection is closed. If the application needs to explicitly commit transactions before the connection is closed, set autoCommitOnClose to true.
Notice:Automatic transaction commits may lead to data inconsistency or loss. Therefore, autoCommitOnClose should be used with caution to ensure transaction integrity. In most cases, it is recommended to manually manage transactions and ensure that transactions are committed or rolled back at the appropriate times.
OptionalautomaticTestTablenullThe automatic test table for the connection pool. C3P0 will create an empty table with the specified name and use queries on this table to test the Connection. The default value is null, which means no test statements are executed. For example, if automaticTestTable is set to Test, C3P0 will create an empty table named Test and use its built-in query statements for testing.
Note:If both automaticTestTable and preferredTestQuery are configured in the connection pool, C3P0 will prioritize using preferredTestQuery for test queries and ignore the settings of automaticTestTable.
OptionalidleConnectionTestPeriod0The interval in milliseconds at which the connection pool performs idle connection detection. That is, the connection pool will test idle connections at regular intervals. The default value is 0, which means no idle connection detection is performed.
OptionalmaxStatements0The maximum number of prepared statements in the connection pool.
Note:If both maxStatements and maxStatementsPerConnection are set to 0, statement caching is not enabled.
If maxStatements is 0 but maxStatementsPerConnection is a non-zero value, statement caching is enabled, but the global limit is not enforced; only the per-connection limit takes effect.
maxStatements controls the total number of cached statements across all connections in the connection pool. If maxStatements is set, it should be a relatively large number because each connection in the pool requires its own, separate set of cached statements.
OptionalmaxStatementsPerConnection0Used to set the maximum number of prepared statements allowed per connection.
Note:If both maxStatements and maxStatementsPerConnection are set to 0, statement caching is not enabled.
If maxStatementsPerConnection is set to 0 but maxStatements is set to a non-zero value, statement caching is enabled and the global limit is enforced, but no limit is set for individual connections.
OptionalnumHelperThreads3Used to specify the number of helper threads for asynchronous task processing.
Note:The more helper threads you specify, the more tasks can be processed in parallel, thereby improving the processing capability and response speed of the connection pool.
Specifying too many helper threads may lead to excessive consumption of system resources. Therefore, you should set the value of numHelperThreads based on the hardware configuration and performance testing results.
OptionalpreferredTestQuerynullSpecifies the test statement executed for all connection tests. Using connection tests can significantly improve the test speed.
Notice:The test table must exist when the data source is initially created.
OptionalcheckoutTimeout0Specifies the timeout period for obtaining a connection from the connection pool, in milliseconds. The default value is 0, indicating no timeout. If the connection pool is exhausted, the client calls getConnection() and waits for a new connection. If the timeout is reached, a SQLException is thrown.
Not recommendedbreakAfterAcquireFailurefalseControls whether to interrupt the connection pool's acquisition operation when a connection acquisition fails. If a connection acquisition fails, all threads waiting for a connection from the connection pool will throw exceptions. However, the data source remains valid and will continue to attempt to acquire a connection on the next call to getConnection().
If set to true, the connection pool will no longer attempt to acquire a connection after multiple failures and will instead fail quickly and throw an exception.
If set to false, the connection pool will continue to attempt to acquire a connection until the connection acquisition timeout is reached.
Not recommendedtestConnectionOnCheckoutfalseSpecifies whether to test the connection when acquiring it from the connection pool.
If set to true, a connection test will be performed when acquiring a connection. This feature should be used with caution as it will result in at least double the number of database calls.
If set to false, no connection test will be performed.
Note:While executing a connection test ensures the connection is valid, it also incurs additional overhead. Therefore, whether to enable connection testing should be decided based on specific application requirements and performance considerations. If the application has high availability requirements for connections, connection testing can be enabled. However, if connections are frequently acquired and released from the connection pool, it may lead to excessive connection testing, thereby affecting performance. =
Not recommendedtestConnectionOnCheckinfalseSpecifies whether to test the connection when returning it to the connection pool.
If set to true, a connection test will be performed when returning a connection to the connection pool. This feature should be used with caution as it will result in at least double the number of database calls.
If set to false, no connection test will be performed.
Note:While executing a connection test ensures the connection is valid, it also incurs additional overhead. Therefore, whether to enable connection testing should be decided based on specific application requirements and performance considerations. If the application has high availability requirements for connections, connection testing can be enabled. However, if connections are frequently acquired and released from the connection pool, it may lead to excessive connection testing, thereby affecting performance.

Main.java code introduction​

The Main.java file is part of the sample program, demonstrating how to obtain a database connection through the c3p0 connection pool, and perform a series of database operations within a transaction, including creating tables, 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 transaction operations to improve the efficiency and performance of database operations.

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

  1. Define the package and import the java.sql interface.

    1. Declare the package name of the current code as com.example.
    2. Import the java.sql.Connection class, which is used to represent 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 is used to represent a database query result set.
    5. Import the com.mchange.v2.c3p0.ComboPooledDataSource class, which is used to use the c3p0 connection pool.

    Sample code:

    package com.example;

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

  2. Define the class name and method.

    1. Define a public class named Main as the entry point of the program. The class name must be consistent with the file name.
    2. Define a public static method main as the starting point of the program.
    3. Use the try-with-resources statement to obtain a database connection and create a precompiled SQL statement.
    4. Perform database transaction operations.
    5. Capture any exceptions that may occur and print the exception stack trace.
    6. Define a private static method getConnection to obtain a database connection from the c3p0 connection pool. In the method, first create a ComboPooledDataSource object cpds that specifies the connection pool configuration through the oceanbase parameter. Then, obtain a database connection from the connection pool by using the cpds.getConnection() method and return it.

    Sample code:

    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 the transaction.

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

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

  3. Obtain a database connection.

    Obtain a database connection and assign it to the conn variable.

    Sample code:

                 Connection conn = getConnection();

  4. Create a precompiled SQL statement.

    1. Create a precompiled SQL statement for creating a database table named test_c3p0.
    2. Create a precompiled SQL statement for inserting data into the test_c3p0 table.
    3. Create a precompiled SQL statement for deleting data from the test_c3p0 table.
    4. Create a precompiled SQL statement for updating data in the test_c3p0 table.
    5. Create a precompiled SQL statement for querying data from the test_c3p0 table.

    Sample 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")

  5. Start a transaction.

    Set the auto-commit mode of the database connection to false to enable the transaction mechanism.

    Sample code:

                conn.setAutoCommit(false);

  6. Create a table.

    Execute the SQL statement for creating a table.

    Sample code:

                stmtCreate.execute();

  7. 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 variable i, and the value of the second column is the string test_insert concatenated with the value of the variable i.

    Sample code:

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

  8. Delete data.

    Set the parameter of the delete statement to 5 and execute the delete operation.

    Sample code:

                stmtDelete.setInt(1, 5);
                stmtDelete.executeUpdate();

  9. Update data.

    Set the first parameter of the update statement to test_update and the second parameter to 5, and execute the update operation.

    Sample code:

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

  10. Query data.

    1. Execute the query statement and save the query results in the ResultSet object rs.
    2. Use a while loop to determine whether there is a next row of data in the result set by using rs.next(). If yes, execute the code in the loop.
    3. The code in the loop prints the values of the id column and the name column for each row.
    4. Close the result set and release the related resources.

    Sample code:

                ResultSet rs = stmtSelect.executeQuery();
                while (rs.next()) {
                    System.out.println(rs.getInt("id") + "   " + rs.getString("name"));
                }
                rs.close();

  11. Commit the transaction.

    Sample code:

                conn.commit();

Complete code​

pom.xml​

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.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="seekdb">
        <!-- 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>

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.

Contents

Edit this page