Connect to OceanBase Cloud by using a DBCP connection pool

This topic describes how to use a Database Connection Pool (DBCP) 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 dbcp-mysql-client sample project Connect to OceanBase Database by using a DBCP connection pool (MySQL compatible mode)

Prerequisites

• You have registered an OceanBase Cloud account, and created an instance and a MySQL-compatible tenant in OceanBase Cloud. For more information, see Create an 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 dbcp-mysql-client 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 dbcp-mysql-client project

Modify the database connection information in the db.properties file in the dbcp-mysql-client/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:

...
url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test
username=test_user001
password=******
...

Step 3: Run the dbcp-mysql-client 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_tbl1;
   

   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 dbcp-mysql-client.zip.

Decompress the package to obtain a folder named dbcp-mysql-client. The directory structure is as follows:

dbcp-mysql-client
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── example
│       │           └── Main.java
│       └── resources
│           └── db.properties
└── 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: a sample file of the main class that contains logic such as the table creation, data insertion, data deletion, data modification, and data query logic.
• resources: a directory that stores resource files, including configuration files.
• db.properties: the configuration file of the connection pool, which contains relevant database connection parameters.
• 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 dbcp-mysql-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

       <groupId>com.example</groupId>
       <artifactId>dbcp-mysql-client</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.

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

   • MySQL JDBC dependency:

     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 5.1.47.

   • DBCP dependency:

     1. <groupId>: the ID of the group to which the dependency belongs, which is set to org.apache.commons.
     2. <artifactId>: the name of the dependency, which is set to commons-dbcp2.
     3. <version>: the version of the dependency, which is set to 2.9.0.

   The sample code is as follows:

       <dependencies>
           <dependency>
               <groupId>mysql</groupId>
               <artifactId>mysql-connector-java</artifactId>
               <version>5.1.47</version>
           </dependency>
           <dependency>
               <groupId>org.apache.commons</groupId>
               <artifactId>commons-dbcp2</artifactId>
               <version>2.9.0</version>
           </dependency>
       </dependencies>
   

Code in db.properties

db.properties is a sample configuration file of the connection pool. The configuration file contains the driver class name, database URL, username, password, connection pool size and limitations, connection timeout values, and options for handling abandoned connections.

Perform the following steps to configure the db.properties file:

1. Configure database connection parameters.

   1. Specify the class name of the driver, which is set to the class name com.mysql.jdbc.Driver.
   2. Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
   3. Specify the username for connecting to the database.
   4. Specify the password for connecting to the database.
   5. Set the connection attributes. useSSL=false specifies not to use Secure Sockets Layer (SSL) encryption for connections. characterEncoding=UTF-8 specifies to use UTF-8 for data transmission.

   The sample code is as follows:

   driverClassName=com.mysql.jdbc.Driver
   url=jdbc:mysql://$host:$port/$database_name
   username=$user_name
   password=$password
   connectionProperties=useSSL=false;characterEncoding=UTF-8
   

   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.

2. Configure other parameters of the DBCP connection pool.

   1. Set the initial size of the connection pool to 30, which means that 30 connections are created when the connection pool is started.

   2. Set the maximum number of connections allowed in the connection pool to 30.

   3. Set the maximum number of idle connections allowed in the connection pool to 10.

   4. Set the minimum number of idle connections in the connection pool to 5. If the number of idle connections is less than the specified value, new connections are created in the connection pool.

   5. Set the maximum waiting time for requesting a connection from the connection pool to 1000 ms. When the application requests a connection from the connection pool, if all connections are occupied, the application has to wait until a connection is available or the maximum waiting time elapses.

   6. Set the timeout value for deleting an abandoned connection to 1s.

      Note

      The default value of removeAbandonedTimeout is 300s. In this example, the parameter value is set to 1s to test the application. You can adjust the parameter value as required based on the actual situation.

   7. Specify whether to recycle unused connections from the application as follows:

      • Set the value to true for the parameter that specifies whether to detect and delete abandoned connections during maintenance.
      • Set the value to true for the parameter that specifies whether to detect and delete abandoned connections when a connection is borrowed from the connection pool.

   The sample code is as follows:

   initialSize=30
   maxTotal=30
   maxIdle=10
   minIdle=5
   maxWaitMillis=1000
   removeAbandonedTimeout=1
   
   removeAbandonedOnMaintenance=true
   removeAbandonedOnBorrow=true
   

Basic data source parameters of a DBCP 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 DBCP connection pool, executing a series of database operations, such as table creation, data insertion, data deletion, data modification, and data query, and returning the query result.

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

1. Import the required classes and interfaces.

   Import classes and interfaces for file reading, database operations, and the database connection pool. These classes and interfaces will be used in subsequent code.

   1. Declare a package named com.example for storing the current Java classes.
   2. Import the java.io.FileInputStream class for reading files.
   3. Import the java.sql.Connection interface for representing connections with the database.
   4. Import the java.sql.PreparedStatement interface for representing precompiled SQL statements.
   5. Import the java.sql.ResultSet interface for representing result sets of database queries.
   6. Import the java.sql.SQLException class for representing SQL operation exceptions.
   7. Import the java.util.Properties class for loading configuration files.
   8. Import the org.apache.commons.dbcp2.BasicDataSource class for representing database connection pools.
   9. Import the org.apache.commons.dbcp2.BasicDataSourceFactory class for creating database connection pools.

   The sample code is as follows:

   package com.example;
   
   import java.io.FileInputStream;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.Properties;
   
   import org.apache.commons.dbcp2.BasicDataSource;
   import org.apache.commons.dbcp2.BasicDataSourceFactory;
   

2. Define class names and methods.

   1. Create a Main class and define a main method as the entry to the application.
   2. In the main method, call the createDataSource() method to create a connection pool object dataSource.
   3. Use the try-with-resources block to automatically close resources when the lifetime of a connection pool object ends.
   4. In the try code block, call the getConnection() method to obtain a database connection object conn from the connection pool.
   5. Call the createTable(), insertData(), deleteData(), updateData(), and queryData() methods in sequence to execute corresponding database operations.
   6. When an exception occurs, use the catch block to record the exception information.

   The sample code is as follows:

   public class Main {
   
       public static void main(String[] args) {
           try (BasicDataSource dataSource = createDataSource()) {
               try (Connection conn = dataSource.getConnection()) {
                   createTable(conn);
                   insertData(conn);
                   deleteData(conn);
                   updateData(conn);
                   queryData(conn);
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       // Create a connection pool.
       // Define a method for creating tables.
       // Define a method for inserting data.
       // Define a method for deleting data.
       // Define a method for updating data.
       // Define a method for querying data.
   }
   

3. Create a connection pool.

   Provide a method for creating a database connection pool and initialize the connection pool object based on parameters read from the configuration file. Perform the following steps:

   1. Define a private static method createDataSource() whose return type is BasicDataSource. The method can throw an exception.
   2. Create a Properties object named props for storing the configuration information of database connections.
   3. Create a FileInputStream object named is for reading the db.properties file located in the src/main/resources directory.
   4. Call the load() method to load the key-value pairs in the db.properties file to the props object.
   5. Call the BasicDataSourceFactory.createDataSource(props) method to create and return a BasicDataSource object by using the props object as parameters.

   The sample code is as follows:

       private static BasicDataSource createDataSource() throws Exception {
           Properties props = new Properties();
           FileInputStream is = new FileInputStream("src/main/resources/db.properties");
           props.load(is);
   
           return BasicDataSourceFactory.createDataSource(props);
       }
   

4. Define a method for creating tables.

   Provide a method for creating a specified table in the database. The method receives a Connection object as parameters and executes a precompiled SQL statement to create the table. Perform the following steps:

   1. Define a private static method createTable() that receives a Connection object as parameters. The method can throw an SQLException.
   2. Define a string variable createTableSql for storing the table creation statement. The statement creates a table named test_tbl1 that contains an id column of the INT data type and a name column of the VARCHAR(32) data type.
   3. Call the conn.prepareStatement(createTableSql) method to create a PreparedStatement object createTableStmt for executing precompiled SQL statements.
   4. Call the execute() method to execute the SQL statement to create the table.

   The sample code is as follows:

       private static void createTable(Connection conn) throws SQLException {
           String createTableSql = "CREATE TABLE test_tbl1 (id INT, name VARCHAR(32))";
           try (PreparedStatement createTableStmt = conn.prepareStatement(createTableSql)) {
               createTableStmt.execute();
           }
       }
   

5. Define a method for inserting data.

   Provide a method for inserting specified data into a table in the database. The method receives a Connection object as parameters and executes a precompiled SQL statement to insert data. Perform the following steps:

   1. Define a private static method insertData() that receives a Connection object as parameters. The method can throw an SQLException.

   2. Define a string variable insertDataSql for storing the data insertion statement.

   3. Call the conn.prepareStatement(insertDataSql) method to create a PreparedStatement object insertDataStmt for executing precompiled SQL statements.

   4. Use the FOR loop to insert data into the table.

      1. Perform 10 rounds of iterations and insert a data record in each iteration.
      2. Call the setInt() method to set the value of the loop variable i to the value of the first parameter in the SQL statement.
      3. Call the setString() method to set the value of the test_insert + i string to the value of the second parameter in the SQL statement.
      4. Call the executeUpdate() method to execute the SQL statement to insert data into the database.

   The sample code is as follows:

       private static void insertData(Connection conn) throws SQLException {
           String insertDataSql = "INSERT INTO test_tbl1 (id, name) VALUES (?, ?) ";
           try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
               for (int i = 0; i < 10; i++) {
                   insertDataStmt.setInt(1, i);
                   insertDataStmt.setString(2, "test_insert" + i);
                   insertDataStmt.executeUpdate();
               }
           }
       }
   

6. Define a method for deleting data.

   Provide a method for deleting data that meets the specified condition from the database. The method receives a Connection object as parameters and executes a precompiled SQL statement to delete data. Perform the following steps:

   1. Define a private static method deleteData() that receives a Connection object as parameters. The method can throw an SQLException.
   2. Define a string variable deleteDataSql for storing the data deletion statement.
   3. Call the conn.prepareStatement(deleteDataSql) method to create a PreparedStatement object deleteDataStmt for executing precompiled SQL statements.
   4. Call the setInt() method to set 5 as a parameter value in the SQL statement.
   5. Call the executeUpdate() method to execute the SQL statement to delete data that meets the specified condition from the database.

   The sample code is as follows:

       private static void deleteData(Connection conn) throws SQLException {
           String deleteDataSql = "DELETE FROM test_tbl1 WHERE id < ?";
           try (PreparedStatement deleteDataStmt = conn.prepareStatement(deleteDataSql)) {
               deleteDataStmt.setInt(1, 5);
               deleteDataStmt.executeUpdate();
           }
       }
   

7. Define a method for updating data.

   Provide a method for updating data that meets the specified condition in the database. The method receives a Connection object as parameters and executes a precompiled SQL statement to update data. Perform the following steps:

   1. Define a private static method updateData() that receives a Connection object as parameters. The method can throw an SQLException.
   2. Define a string variable updateDataSql for storing the data update statement.
   3. Call the conn.prepareStatement(updateDataSql) method to create a PreparedStatement object updateDataStmt for executing precompiled SQL statements.
   4. Call the setString() method to set the value of the test_update string to the value of the first parameter in the SQL statement.
   5. Call the setInt() method to set 5 as the value of the second parameter in the SQL statement.
   6. Call the executeUpdate() method to execute the SQL statement to update data that meets the specified condition in the database.

   The sample code is as follows:

       private static void updateData(Connection conn) throws SQLException {
           String updateDataSql = "UPDATE test_tbl1 SET name = ? WHERE id = ?";
           try (PreparedStatement updateDataStmt = conn.prepareStatement(updateDataSql)) {
               updateDataStmt.setString(1, "test_update");
               updateDataStmt.setInt(2, 5);
               updateDataStmt.executeUpdate();
           }
       }
   

8. Define a method for querying data.

   Provide a method for querying data in the database and processing the queried data. The method receives a Connection object as parameters and executes a precompiled SQL statement to query data. Perform the following steps:

   1. Define a private static method queryData() that receives a Connection object as parameters. The method can throw an SQLException.

   2. Define a string variable queryDataSql for storing the data query statement.

   3. Call the conn.prepareStatement(queryDataSql) method to create a PreparedStatement object queryDataStmt for executing precompiled SQL statements.

   4. Call the queryDataStmt.executeQuery() method to execute an SQL query and use the ResultSet object named rs to receive the query result.

   5. Call the rs.next() method to traverse the query result set by using the WHILE loop.

      1. Call the getInt() method to obtain the integer values of the id column in the result set and assign the obtained values to the id variable.
      2. Call the getString() method to obtain the string values of the name column in the result set and assign the obtained values to the name variable.
      3. Return the id and name values in the query result.

   The sample code is as follows:

       private static void queryData(Connection conn) throws SQLException {
           String queryDataSql = "SELECT * FROM test_tbl1";
           try (PreparedStatement queryDataStmt = conn.prepareStatement(queryDataSql)) {
               try (ResultSet rs = queryDataStmt.executeQuery()) {
                   while (rs.next()) {
                       int id = rs.getInt("id");
                       String name = rs.getString("name");
                       System.out.println("id: " + id + ", name: " + name);
                   }
               }
           }
       }
   

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>dbcp-mysql-client</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>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.9.0</version>
        </dependency>
    </dependencies>
</project>

# Database Connection Information
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://$host:$port/$database_name
username=$user_name
password=$password
connectionProperties=useSSL=false;characterEncoding=UTF-8

# ConnectionPool Parameters
initialSize=30
maxTotal=30
maxIdle=10
minIdle=5
maxWaitMillis=1000
removeAbandonedTimeout=1

removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true

package com.example;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class Main {

    public static void main(String[] args) {
        try (BasicDataSource dataSource = createDataSource()) {
            try (Connection conn = dataSource.getConnection()) {
                createTable(conn);
                insertData(conn);
                deleteData(conn);
                updateData(conn);
                queryData(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // Create ConnectionPool
    private static BasicDataSource createDataSource() throws Exception {
        Properties props = new Properties();
        FileInputStream is = new FileInputStream("src/main/resources/db.properties");
        props.load(is);
        
        return BasicDataSourceFactory.createDataSource(props);
    }
    
    // Create a table.
    private static void createTable(Connection conn) throws SQLException {
        String createTableSql = "CREATE TABLE test_tbl1 (id INT, name VARCHAR(32))";
        try (PreparedStatement createTableStmt = conn.prepareStatement(createTableSql)) {
            createTableStmt.execute();
        }
    }
    
    // Insert data.
    private static void insertData(Connection conn) throws SQLException {
        String insertDataSql = "INSERT INTO test_tbl1 (id, name) VALUES (?, ?) ";
        try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
            for (int i = 0; i < 10; i++) {
                insertDataStmt.setInt(1, i);
                insertDataStmt.setString(2, "test_insert" + i);
                insertDataStmt.executeUpdate();
            }
        }
    }
    
    // Delete data.
    private static void deleteData(Connection conn) throws SQLException {
        String deleteDataSql = "DELETE FROM test_tbl1 WHERE id < ?";
        try (PreparedStatement deleteDataStmt = conn.prepareStatement(deleteDataSql)) {
            deleteDataStmt.setInt(1, 5);
            deleteDataStmt.executeUpdate();
        }
    }
    
    // Update data.
    private static void updateData(Connection conn) throws SQLException {
        String updateDataSql = "UPDATE test_tbl1 SET name = ? WHERE id = ?";
        try (PreparedStatement updateDataStmt = conn.prepareStatement(updateDataSql)) {
            updateDataStmt.setString(1, "test_update");
            updateDataStmt.setInt(2, 5);
            updateDataStmt.executeUpdate();
        }
    }
    
    // Query data.
    private static void queryData(Connection conn) throws SQLException {
        String queryDataSql = "SELECT * FROM test_tbl1";
        try (PreparedStatement queryDataStmt = conn.prepareStatement(queryDataSql)) {
            try (ResultSet rs = queryDataStmt.executeQuery()) {
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    System.out.println("id: " + id + ", name: " + name);
                }
            }
        }
    }
}

References

For more information about MySQL Connector/J, see Overview of MySQL Connector/J.