Connect to OceanBase Cloud by using a C3P0 connection pool

This topic describes how to use a C3P0 connection pool, MySQL Connector/J, and OceanBase Cloud to build an application for basic database operations, such as table creation, data insertion, data deletion, data modification, and data query.

Download the c3p0-mysql-jdbc sample project Connect to OceanBase Database by using a C3P0 connection pool (MySQL compatible mode)

Prerequisites

• You have registered an OceanBase Cloud account, and created a cluster instance and a MySQL-compatible tenant in OceanBase Cloud. For more information, see Create a cluster instance and Create a tenant.

• You have obtained the connection string of the MySQL-compatible tenant. For more information, see Obtain the connection string.

• You have installed Java Development Kit (JDK) 1.8 and Maven.

• You have installed Eclipse.

  Note

  This topic uses Eclipse IDE for Java Developers 2022-03 to run the sample code. You can also choose a suitable tool as needed.

Procedure

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

1. Start Eclipse and choose File > Open Projects from File System.

2. In the dialog box that appears, click Directory, navigate to the directory where the project is located, and click Finish to import the project.

   Note

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

   

3. View the project.

   

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

Modify the database connection information in the c3p0-config.xml file in the c3p0-mysql-jdbc/src/main/resources/ directory based on the obtained connection string mentioned in the "Prerequisites" section.

Here is an example:

• The endpoint is t5******.********.oceanbase.cloud.
• The access port is 3306.
• The name of the database to be accessed is test.
• The tenant account is test_user001.
• The password is ******.

The sample code is as follows:

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

Step 3: Run the c3p0-mysql-jdbc project

1. In the project navigation view, find and expand the src/main/java directory.

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

   

3. In the Console window of Eclipse, view the project logs and output results.

   

4. 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 here to download the project code, which is a package named c3p0-mysql-jdbc.zip.

Decompress the package to obtain 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 that stores the source code.
• main: a directory that stores the main code, including the major logic of the application.
• java: a directory that stores the Java source code.
• com: a directory that stores the Java package.
• example: a directory that stores the packages of the sample project.
• Main.java: the main class that contains logic such as the table creation and data insertion logic.
• resources: a directory that stores resource files, including configuration files.
• c3p0-config.xml: the configuration file of the C3P0 connection pool.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in pom.xml

pom.xml is the configuration file of the Maven project, which defines the dependencies, plug-ins, and build rules of the project. Maven is a Java project management tool that can automatically download dependencies and compile and package projects.

Perform the following steps to configure the pom.xml file:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and the character encoding UTF-8.

   The sample code is as follows:

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

2. Configure namespaces and the POM model version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.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>
   

3. Configure basic information.

   1. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to testc3p0.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

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

4. Configure the attributes of the project source file.

   Specify maven-compiler-plugin as the compiler plug-in of Maven, and set the source code version and target code version of the compiler to Java 8. This means that the project source code is compiled by using Java 8 and the compiled bytecode is compatible with the Java 8 runtime environment. This ensures that Java 8 syntax and characteristics can be correctly processed during the compilation and running of the project.

   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>
   

5. Configure the components on which the project depends.

   Note

   The following code defines that the project depends on MySQL Connector/J V8.0.25. For more information about other versions, see MySQL Connector/J.

   Define the components on which the project depends by using <dependency>.

   • Add the mysql-connector-java dependency library and configure the following parameters:
   1. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
   2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
   3. <version>: the version of the dependency, which is set to 8.0.25.
   • Add the c3p0 dependency library and configure the following parameters:
   1. <groupId>: the ID of the group to which the dependency belongs, which is set to com.mchange.
   2. <artifactId>: the name of the dependency, which is set to c3p0.
   3. <version>: the version of the dependency, which is set to 0.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>
   

Code in c3p0-config.xml

c3p0-config.xml is the configuration file of the C3P0 connection pool and is used to configure database connection attributes. You can specify values of the <property> elements to configure attributes such as the database driver, connection URL, username, password, and connection pool size.

Perform the following steps to configure the c3p0-config.xml file:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and the character encoding UTF-8.

   The sample code is as follows:

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

2. Configure basic information.

   1. <c3p0-config>: the configuration information of the C3P0 connection pool.
   2. <named-config name="oceanbase">: a configuration named oceanbase. In the code, the name can be used to reference this configuration and obtain connection information and connection pool attributes related to the database named oceanbase.

   The sample code is as follows:

   <c3p0-config>
       <named-config name="oceanbase">
   
           // Specify values of the <property> elements.
   
       </named-config>
   </c3p0-config>
   

3. Configure the database driver.

   Set the class name of the MySQL Java Database Connectivity (JDBC) driver used to connect to OceanBase Cloud to com.mysql.cj.jdbc.Driver by using <property>.

   Note

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

   The sample code is as follows:

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

4. Configure the database connection information.

   1. Specify the URL for connecting to the database, including the host IP address, port number, database to be accessed, and other parameters.
   2. Specify the username for connecting to the database.
   3. Specify the password for connecting to the database.

   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>
   

   The parameters are described as follows:

   • $host: the access address of OceanBase Cloud. The value is sourced from the -h parameter in the connection string.
   • $port: the access port of OceanBase Cloud. The value is sourced from the -P parameter in the connection string.
   • $database_name: the name of the database to be accessed. The value is sourced from the -D parameter in the connection string.
   • $user_name: the account name. The value is sourced from the -u parameter in the connection string.
   • $password: the account password. The value is sourced from the -p parameter in the connection string.

5. Configure other parameters of the C3P0 connection pool.

   1. Set the number of connections created at a time in the connection pool to 20. In other words, when the connection pool has insufficient connections, 20 connections are created each time.
   2. Set the initial size of the connection pool to 10. In other words, 10 connections are created when the connection pool is started.
   3. Set the minimum number of connections in the connection pool to 5. In other words, the connection pool must have at least five connections.
   4. Set the maximum number of connections allowed in the connection pool to 30. In other words, the connection pool allows at most 30 connections.
   5. Set the maximum number of cached statements in each connection to 0. This means that no statement is cached for connections.
   6. Set the maximum number of statements cached for each connection in the connection pool to 0. This means that no statement is cached for connections.
   7. Set the number of auxiliary threads for C3P0 to 3. These auxiliary threads are used to execute slow JDBC operations.
   8. Set the interval for checking C3P0 connection attributes to 3 seconds. In other words, connection attributes are checked every 3 seconds.
   9. Set the timeout value for requesting a connection to 1,000 ms. If a connection fails to be obtained within 1,000 ms, a timeout exception is thrown.
   10. Set the interval for checking idle connections in the connection pool to 3 seconds. The status of idle connections is checked every 3 seconds.
   11. Set the maximum idle duration of connections in the connection pool to 10 seconds. A connection that is not used within 10 seconds will be closed.
   12. Set the maximum idle duration to 5 seconds for connections created in the connection pool after the maximum number of connections is reached. If a connection created after the maximum number of connections is reached remains idle for more than 5 seconds, it will be closed.
   13. Set the retry interval for requesting a connection to 1,000 ms. When a connection failed to be obtained, a retry is performed after 1,000 ms.
   14. Set the name of the table for automatic testing of the C3P0 connection pool to "test". This is a special table used to test whether a connection is valid.
   15. Specify whether to verify a connection when it is returned to the connection pool. If you set the value to true, a connection is verified when it is returned to the connection 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>
   

General parameters of a C3P0 connection pool

Code in Main.java

The Main.java file is a part of the sample application. It demonstrates the process of obtaining a database connection from the C3P0 connection pool, executing a series of database operations in transactions, such as table creation, data insertion, data deletion, data modification, and data query, and returning the query result. It shows how to manage database connections and execute transaction operations by using the C3P0 connection pool to improve the efficiency and performance of database operations.

Perform the following steps to configure the Main.java file:

1. Define the package and import java.sql interfaces and classes.

   1. Declare the name of the package to which the current code belongs as com.example.
   2. Import the java.sql.Connection class for representing database connections.
   3. Import the java.sql.PreparedStatement class for executing precompiled database operations.
   4. Import the java.sql.ResultSet class for representing the result sets of database queries.
   5. Import the com.mchange.v2.c3p0.ComboPooledDataSource class for using 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;
   

2. Define class names and methods.

   1. Define a public class named Main as the entry to the application. The class name must be the same as the file name.
   2. Define a public static method named main, which is used as the execution start point of the application.
   3. Use the try-with-resources block to obtain a database connection and create a precompiled SQL statement.
   4. Perform transaction operations.
   5. Capture possible exceptions and record stack information of the exceptions.
   6. Define a private static method getConnection for requesting database connections from the C3P0 connection pool. Create a ComboPooledDataSource object named cpds in the method. This object specifies the connection pool configurations by using the oceanbase parameter. Call the cpds.getConnection() method to request a database connection from the connection pool and return the connection.

   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.
               ) {
   
               // Perform 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.

   The sample code is as follows:

                Connection conn = getConnection();
   

4. Create precompiled SQL statements.

   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.

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

5. Start a transaction.

   Set conn.setAutoCommit to false to start the transaction mechanism.

   The sample code is as follows:

               conn.setAutoCommit(false); 
   

6. Create a table.

   Execute the SQL statement for creating a table.

   The sample code is as follows:

               stmtCreate.execute();
   

7. Insert data.

   Use the FOR loop to insert 10 records into the test_c3p0 table. The values of the first column are the values of the i variable. The values of the second column are the values of the test_insert string followed by the values of the i variable.

   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();
               }
   

8. Delete data.

   Set the value of the first parameter in the deletion statement to 5 and execute the deletion statement.

   The sample code is as follows:

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

9. Update data.

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

   The sample code is as follows:

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

10. Query data.

    1. Execute a query statement and save the query result in the ResultSet object named rs.
    2. Use rs.next() to check whether the result set contains another row based on a WHILE loop. If yes, execute the code in the loop.
    3. The code in the loop returns the id and name column values of each row.
    4. Close the result set and release relevant 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();
    

11. 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")) {
            
            // Start a transaction.
            conn.setAutoCommit(false); 

            // Create a 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 the 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.