Connect to OceanBase Database by using a c3p0 connection pool

This topic introduces how to build an application by using a c3p0 connection pool, MySQL Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data deletion, data updating, and data query.

Download the c3p0-mysql-jdbc sample project

Prerequisites

• You have installed OceanBase Database and created a MySQL tenant.

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

• You have installed Eclipse.

  Note

  The tool used to run the sample code in this topic is Eclipse IDE for Java Developers (2022-03), but you can also choose a tool that suits your personal preference to run the code.

Procedure

1. Import the c3p0-mysql-jdbc project into Eclipse.
2. Obtain the URL of OceanBase Database.
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. Start Eclipse and choose File > Open Projects from File System.

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

   Note

   When you import a Maven project using Eclipse, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

1. Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest
   

   For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the URL below based on the OceanBase Database connection string.

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

   where:

   • $host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

   • $port specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

   • $database_name specifies the name of the database to be accessed.

     Notice

     The user used to connect to the tenant must have the CREATE, INSERT, DELETE, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

   • $password specifies the password of the account.

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

   Here is an example:

   jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=test_user001@mysql001&password=******
   

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

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

Here is an example:

• The IP address of the OBServer node is xxx.xxx.xxx.xxx.
• The port is 2881.
• The name of the database to be accessed is test.
• The tenant account is test_user001@mysql001, where mysql001 is a MySQL user tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

The sample code is as follows:

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

Step 4: Run the c3p0-mysql-jdbc project

1. In the project navigation view, locate 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 introduction

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

Here is a breakdown of the files and directories:

• src: the root directory for storing the source code.
• main: the directory for storing the main code, including the major logic of the application.
• java: the directory for storing the Java source code.
• com: the directory for storing the Java package.
• example: the directory for storing the packages of the sample project.
• Main.java: the main class that contains logic such as table creation and data insertion.
• resources: the directory for storing 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 the pom.xml file

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

To configure the pom.xml file, perform the following steps:

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. xmlns: the default XML namespace, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the mapping between the XML namespace and its corresponding XML schema definition (XSD) file. The value typically consists of paired strings separated by spaces. Each pair consists of a default XML namespace (http://maven.apache.org/POM/4.0.0) in the first part, and the URI of the corresponding XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd) in the second part.
   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 properties of the project's source file.

   Specify the Maven compiler plug-in 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, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

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

   The sample code is as follows:

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

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:
   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:
   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 the c3p0-config.xml file

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

To configure the c3p0-config.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and 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 properties 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 Database 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>
   

   where:

   • $host specifies the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
   • $port specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • $database_name specifies the name of the database to be accessed.
   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • $password specifies the password of the account.

5. Configure other parameters of the c3p0 connection pool.

   1. Configure the connection pool to increase the number of connections by 20 at a time when needed. This means that when there are insufficient connections in the pool, 20 connections will be added each time.
   2. Set the initial size of the connection pool to 10. This means that 10 connections are created when the connection pool is started.
   3. Set the minimum number of connections in the connection pool to 5. This means that the connection pool must have at least five connections.
   4. Set the maximum number of connections in the connection pool to 30. This means that 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.
   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 properties to 3 seconds. This means that connection properties 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 time for connections exceeding the maximum connection limit in the connection pool to 5 seconds. That is, if a connection exceeds the maximum connection limit and remains idle for more than 5 seconds, it will be closed.
   13. Set the retry interval for requesting a connection to 1,000 ms. That is, 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>
   

The following table describes the basic parameters of the c3p0 connection pool.

Code in the Main.java file

The Main.java file is a part of the sample application, demonstrating how to obtain a database connection through the c3p0 connection pool and perform a series of database operations within a transaction. These operations include creating a table, inserting data, deleting data, updating data, querying data, and printing the query results. This showcases how to use the c3p0 connection pool to manage database connections and execute transactional operations, ultimately improving the efficiency and performance of database operations.

To configure the Main.java file, perform the following steps:

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 Main public class, which is used 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. Specifically, you can start a transaction, create a table, insert data, delete data, update data, query data, and commit a transaction.
   
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       private static Connection getConnection() throws Exception {
           ComboPooledDataSource cpds = new ComboPooledDataSource("oceanbase");
           return cpds.getConnection();
       }
   }
   

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.