C3P0 connection pool connects to OceanBase Cloud

This topic describes how to use the C3P0 connection pool, OceanBase Connector/J, and OceanBase Cloud to build an application that performs basic database operations, including table creation, data insertion, data deletion, data updating, and data query.

Download the c3p0-oceanbase-jdbc sample project C3P0 connection pool connects to OceanBase Cloud (Oracle compatible mode)

Prerequisites

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

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

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

  The code in this topic is run in Eclipse IDE for Java Developers 2022-03. You can also use any other tool that you prefer to run the sample code.

Procedure

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

1. Open Eclipse and select File > Open Projects from File System.

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

   Note

   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.

   

3. View the project.

   

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

Modify the database connection information in the c3p0-oceanbase-jdbc/src/main/resources/c3p0-config.xml file based on the connection string obtained in the prerequisites.

Here is an example:

...
        <property name="jdbcUrl">jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8</property>
        <property name="user">test_user</property>
        <property name="password">******</property>
...

• The connection address is t5******.********.oceanbase.cloud.
• The access port is 1521.
• The database name to be accessed is test.
• The tenant connection account is test_user.
• The password is ******.

Step 3: Run the c3p0-oceanbase-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.

   

3. View the project logs and output results in the Eclipse console window.

4. You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results.

   obclient [SYS]> SELECT * FROM test_schema001.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-oceanbase-jdbc to download the project code, which is a compressed file named c3p0-oceanbase-jdbc.zip.

After decompressing the file, you will find a folder named c3p0-oceanbase-jdbc. The directory structure is as follows:

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

File description:

• src: the root directory for 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 of the sample project.
• Main.java: the main class, containing logic for creating tables and inserting data.
• 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.

Introduction to the pom.xml file

The pom.xml file is a configuration file for Maven projects, defining 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 article mainly includes the following parts:

1. File declaration statement.

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

   Sample code:

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

2. Configure the 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 <modelVersion> to specify the POM model version used by the POM file as 4.0.0.

   Sample 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 organization as com.example.
   2. Use <artifactId> to specify the project name as testc3p0.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample 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 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 setup ensures that the project can correctly handle Java 8 syntax and features during compilation and runtime.

   Note

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

   Sample code:

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

5. Configure the components that the project depends on.

   Note

   This section defines the project's dependency on OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC driver

   Use <dependency> to define dependencies:

   • oceanbase-client dependency:

     1. Use <groupId> to specify the dependency's organization as com.oceanbase.
     2. Use <artifactId> to specify the dependency name as oceanbase-client.
     3. Use <version> to specify the dependency version as 2.4.2.

   • C3P0 dependency:

     1. Use <groupId> to specify the dependency's organization as com.mchange.
     2. Use <artifactId> to specify the dependency name as c3p0.
     3. Use <version> to specify the dependency version as 0.9.5.5.

   Sample code:

       <dependencies>
           <dependency>
               <groupId>com.oceanbase</groupId>
               <artifactId>oceanbase-client</artifactId>
               <version>2.4.2</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 a C3P0 connection pool configuration file 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 the c3p0-config.xml file in this topic mainly includes the following parts:

1. A file declaration statement.

   This statement declares the file to be an XML file that uses XML version 1.0 and character encoding UTF-8.

   Sample code:

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

2. Basic configuration.

   1. The <c3p0-config> element is used to include the configuration information of the c3p0 connection pool.
   2. The <named-config name="oceanbase"> element defines a named configuration named oceanbase. In the code, you can use this name to reference the named configuration and obtain connection information and connection pool properties related to the oceanbase database.

   Sample code:

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

3. Database driver configuration.

   The <property> element is used to specify the class name of the JDBC driver for connecting to the OceanBase database as com.oceanbase.jdbc.Driver.

   Sample code:

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

4. Database connection information configuration.

   1. The database connection URL is set, including the connection address, port number, schema to be accessed, and URL parameters.
   2. The database username is configured.
   3. The database password is configured.

   Sample code:

           <property name="jdbcUrl">jdbc:oceanbase://$host:$port/$schema_name?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8</property>
           <property name="user">$user_name</property>
           <property name="password">$password</property>
   

   Parameter description:

   • $host: The value of the -h parameter in the connection string, which specifies the connection address of the OceanBase Cloud database.

   • $port: The value of the -P parameter in the connection string, which specifies the connection port of the OceanBase Cloud database.

   • $schema_name: The value of the -D parameter in the connection string, which specifies the name of the database to be accessed.

   • Other parameters are as follows:

     • useSSL=false: Disables SSL encryption, indicating that secure socket layer is not used to protect data transmission.
     • seUnicode=true: Enables Unicode character encoding to ensure that various character sets can be processed correctly.
     • characterEncoding=utf8: Specifies the use of UTF-8 character encoding.
     • &: An XML entity reference used to represent the & character. In XML, & is a special character that must be represented by an entity reference to avoid conflicts with XML syntax.

     For more information about URL parameters, see Database URL.

   • $user_name: The value of the -u parameter in the connection string, which specifies the account name.

   • $password: The value of the -p parameter in the connection string, which specifies the account password.

5. Other c3p0 connection pool configuration items.

   1. The number of connections to be added at a time when the connection pool needs more connections is set to 20. That is, when the number of connections in the connection pool is insufficient, 20 connections are added each time.
   2. The initial size of the connection pool is set to 10. That is, 10 connections are pre-created when the connection pool is started.
   3. The minimum number of connections in the connection pool is set to 5. That is, the number of connections kept in the connection pool is not less than 5.
   4. The maximum number of connections in the connection pool is set to 30. That is, the number of connections allowed in the connection pool does not exceed 30.
   5. The maximum number of cached statements per connection is set to 0. That is, no statements are cached.
   6. The maximum number of cached statements per connection in the connection pool is set to 0. That is, no statements are cached per connection.
   7. The number of auxiliary threads used by c3p0 is set to 3. These auxiliary threads are used to execute slow JDBC operations.
   8. The attribute check cycle for c3p0 connections is set to 3 seconds. That is, the attributes of connections are checked every 3 seconds.
   9. The timeout period for obtaining a connection is set to 1000 milliseconds. That is, if a connection cannot be obtained within 1000 milliseconds, a timeout exception is thrown.
   10. The idle connection check cycle in the connection pool is set to 3 seconds. That is, the status of idle connections is checked every 3 seconds.
   11. The maximum idle time of a connection in the connection pool is set to 10 seconds. That is, if a connection is not used for 10 seconds, it will be closed.
   12. The maximum idle time of a connection exceeding the maximum number of connections in the connection pool is set to 5 seconds. That is, if a connection exceeds the maximum number of connections and is idle for more than 5 seconds, it will be closed.
   13. The retry delay when attempting to obtain a connection is set to 1000 milliseconds. That is, if obtaining a connection fails, it will be retried after 1000 milliseconds.
   14. The automatic test table for c3p0 is set to "test". This is a special table used to test whether a connection is valid.
   15. Whether to test the validity of a connection when returning it to the connection pool is set. If set to true, the validity of a connection is tested when it is returned to the connection pool.

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

Common basic configuration items of the C3P0 connection pool:

Main.java code introduction

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

   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 information.
   6. Define a private static method getConnection to obtain a database connection from the c3p0 connection pool. Inside the method, first create a ComboPooledDataSource object cpds that specifies the connection pool configuration through the parameter oceanbase. Then, obtain a database connection from the connection pool using the cpds.getConnection() method and return it.

   Code:

   public class Main {
   
       public static void main(String[] args) {
           try (
               // Obtain a database connection
               // Create a precompiled SQL statement
               ) {
   
               // Database transaction operations: start transaction, create table, insert data, delete data, update data, query data, and commit transaction
   
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       private static Connection getConnection() throws Exception {
           ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase");
           return cpds.getConnection();
       }
   }
   

3. Obtain a database connection.

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

   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.

   Code:

                PreparedStatement stmtCreate = conn.prepareStatement("CREATE TABLE test_c3p0 (id NUMBER, name VARCHAR2(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.

   Code:

               conn.setAutoCommit(false); 
   

6. Create a table.

   Execute the SQL statement for creating a table.

   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.

   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.

   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.

   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 check if there are more rows of data in the result set by calling rs.next(). If there are, execute the code inside the loop.
    3. The code inside the loop prints the values of the id and name columns for each row.
    4. Close the result set and release the related resources.

    Code:

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

11. Commit the transaction.

    Code:

                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.example</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>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</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.oceanbase.jdbc.Driver</property>
        <!-- Configure Database Link Address -->
        <property name="jdbcUrl">jdbc:oceanbase://$host:$port/$schema_name?useSSL=false&amp;useUnicode=true&amp;characterEncoding=utf8</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 NUMBER, name VARCHAR2(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")) {
            
            conn.setAutoCommit(false); 

            stmtCreate.execute();

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

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

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

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

            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 OceanBase Connector/J, see OceanBase JDBC driver.