Use DBCP connection pool to connect to OceanBase Cloud

This topic describes how to use DBCP connection pool, OceanBase Connector/J, and OceanBase Cloud to build an application that performs basic database operations, such as creating tables, inserting, deleting, updating, and querying data.

Download the dbcp-oceanbase-client sample project Connect to OceanBase Cloud by using DBCP connection pool (Oracle compatible mode)

Prerequisites

• You have registered an Alibaba Cloud account and created an instance and an 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 examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also use any other tool that you prefer to run the code examples.

Procedure

Step 1: Import the dbcp-oceanbase-client project into Eclipse

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

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

   Note

   When you import 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 dbcp-oceanbase-client project

Modify the database connection information in the dbcp-oceanbase-client/src/main/resources/db.properties file based on the connection string obtained from the prerequisites.

Here is an example:

...
url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_user001
username=test_user001
password=******
...

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

Step 3: Run the dbcp-oceanbase-client project

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

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

   

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

   

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

   obclient [SYS]> SELECT * FROM test_user001.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 introduction

Click dbcp-oceanbase-client to download the project code, which is a compressed file named dbcp-oceanbase-client.zip.

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

dbcp-oceanbase-client
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── example
│       │           └── Main.java
│       └── resources
│           └── db.properties
└── pom.xml

File description:

• src: the root directory of the source code.
• main: the main code directory, containing the core logic of the application.
• java: the directory for Java source code.
• com: the directory for Java packages.
• example: the directory for packages of the sample project.
• Main.java: the main class file, containing logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the directory for resource files, including configuration files.
• db.properties: the configuration file for the connection pool, containing relevant database connection parameters.
• 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. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.

The pom.xml file in this topic includes the following main parts:

1. File declaration statement.

   This statement declares the file to be 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 POM namespace and POM model version.

   1. Use xmlns to specify the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to specify the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to specify the POM namespace as http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file as http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <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's name as dbcp-oceanbase-client.
   3. Use <version> to specify the project's version as 1.0-SNAPSHOT.

   Sample code:

       <groupId>com.example</groupId>
       <artifactId>dbcp-oceanbase-client</artifactId>
       <version>1.0-SNAPSHOT</version>
   

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

   Specify the Maven compiler plugin as maven-compiler-plugin and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This 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 part of the code defines the components that the project depends on as OceanBase Connector/J V2.4.2. For more information about other versions, see OceanBase JDBC driver.

   Use <dependency> to define the dependencies:

   • oceanbase-client dependency:

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

   • dbcp dependency:

     1. Use <groupId> to specify the organization of the dependency as org.apache.commons.
     2. Use <artifactId> to specify the name of the dependency as commons-dbcp2.
     3. Use <version> to specify the version of the dependency as 2.9.0.

   Sample code:

       <dependencies>
           <dependency>
               <groupId>com.oceanbase</groupId>
               <artifactId>oceanbase-client</artifactId>
               <version>2.4.2</version>
           </dependency>
           <dependency>
               <groupId>org.apache.commons</groupId>
               <artifactId>commons-dbcp2</artifactId>
               <version>2.9.0</version>
           </dependency>
       </dependencies>
   

db.properties code introduction

db.properties is the connection pool configuration file in this example, which contains the configuration attributes of the connection pool. These attributes include the class name of the driver, database URL, username, password, size and limits of the connection pool, connection timeout, and options for handling abandoned connections.

The code in the db.properties file in this example mainly includes the following parts:

1. Configure the database connection parameters.

   1. Set the class name of the driver. Here, it is the class name of the OceanBase JDBC driver, com.oceanbase.jdbc.Driver.

   2. Configure the database connection URL, including the host IP, port number, and the schema to be accessed.

   3. Configure the database username.

   4. Configure the database password.

   Sample code:

   driverClassName=com.oceanbase.jdbc.Driver
   url=jdbc:oceanbase://$host:$port/$schema_name
   username=$user_name
   password=$password
   

   Parameter description:

   • $host: the value of the -h parameter in the connection string, which is the connection address of OceanBase Cloud.
   • $port: the value of the -P parameter in the connection string, which is the connection port of OceanBase Cloud.
   • $schema_name: the value of the -D parameter in the connection string, which is the name of the database to be accessed.
   • $user_name: the value of the -u parameter in the connection string, which is the account name.
   • $password: the value of the -p parameter in the connection string, which is the account password.

2. Configure other parameters of the DBCP connection pool.

   1. Set the initial size of the connection pool to 30, which is the number of connections to be created in the connection pool initially.

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

   3. Set the maximum number of connections that can be idle 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 this value, the connection pool will create new connections.

   5. Set the maximum wait time (in milliseconds) for obtaining a connection from the connection pool to 1000. If all connections in the connection pool are occupied and there are no available connections, the operation to obtain a connection will wait until an available connection is available or the maximum wait time is exceeded.

   6. Set the timeout (in seconds) before abandoned connections are removed to 1.

      Note

      The default value of removeAbandonedTimeout is 300 seconds. In this example, it is set to 1 second for testing purposes. You can adjust this value as needed to meet the requirements of your application.

   7. Configure whether the connection pool recycles connections that are no longer used in the program:

      • Set whether to detect and remove abandoned connections during maintenance to true.
      • Set whether to detect and remove abandoned connections when borrowing a connection from the connection pool to true.

   Sample code:

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

Basic data source configuration parameters of the DBCP connection pool:

Introduction to Main.java

The Main.java file is part of the sample program, demonstrating how to obtain a database connection through a DBCP connection pool and perform a series of database operations, including creating tables, inserting data, deleting data, updating data, querying data, and printing the query results.

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

1. Import the required classes and interfaces.

   Import the required classes and interfaces, including those for reading files, performing database operations, and managing the database connection pool. These classes and interfaces will be used in the subsequent code.

   1. Declare a package named com.example to store the current Java class.
   2. Import the java.io.FileInputStream class to read files.
   3. Import the java.sql.Connection interface to represent a database connection.
   4. Import the java.sql.PreparedStatement interface to represent a precompiled SQL statement.
   5. Import the java.sql.ResultSet interface to represent a result set from a database query.
   6. Import the java.sql.SQLException exception class to represent SQL operation exceptions.
   7. Import the java.util.Properties class to load configuration files.
   8. Import the org.apache.commons.dbcp2.BasicDataSource class to represent a database connection pool.
   9. Import the org.apache.commons.dbcp2.BasicDataSourceFactory class to create a database connection pool.

   Code:

   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 the class name and methods.

   1. Create the Main class and define a main method as the entry point of the program.
   2. In the main method, first call the createDataSource() method to create a connection pool object dataSource.
   3. Use the try-with-resources statement to automatically close resources when the lifecycle of the 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. Successively call the createTable(), insertData(), deleteData(), updateData(), and queryData() methods to execute the corresponding database operations.
   6. In the event of an exception, print the exception information using the catch block.

   Code:

   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 convenient method for creating a database connection pool, initializing the connection pool object by reading parameters from the configuration file. The specific steps are as follows:

   1. Define a private static method createDataSource() that returns a BasicDataSource object. The method may throw an Exception exception.
   2. Create a Properties object props to store the database connection configuration information.
   3. Create a FileInputStream object is to read the db.properties file located in the src/main/resources directory.
   4. Use the load() method to load the key-value pairs from the db.properties file into the props object.
   5. Call the BasicDataSourceFactory.createDataSource(props) method to create and return a BasicDataSource object using the props object as the parameter.

   Code:

       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. It accepts a connection object as a parameter and executes a create-table SQL statement using a precompiled approach. The specific steps are as follows:

   1. Define a private static method createTable() that accepts a Connection object as a parameter. The method may throw an SQLException exception.
   2. Define a string variable createTableSql to store the SQL statement for creating a table. The SQL statement specifies the table name as test_tbl1 and defines two columns: one is a NUMBER type column named id, and the other is a VARCHAR2(32) type column named name.
   3. Use 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 for creating the table.

   Code:

       private static void createTable(Connection conn) throws SQLException {
           String createTableSql = "CREATE TABLE test_tbl1 (id NUMBER, name VARCHAR2(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. It accepts a connection object as a parameter and executes an insert-data SQL statement using a precompiled approach. The specific steps are as follows:

   1. Define a private static method insertData() that accepts a Connection object as a parameter. The method may throw an SQLException exception.

   2. Define a string variable insertDataSql to store the SQL statement for inserting data.

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

   4. Use a for loop to insert data into the table:

      1. Loop 10 times, inserting one piece of data in each iteration.
      2. Use the setInt() method to set the value of the loop variable i as the first parameter value in the SQL statement.
      3. Use the setString() method to set the string test_insert + i as the second parameter value in the SQL statement.
      4. Call the executeUpdate() method to execute the SQL statement for inserting data into the database.

   Code:

       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 from the database that meets specific conditions. It accepts a connection object as a parameter and executes the SQL statement for deleting data using a precompiled statement. The steps are as follows:

   1. Define a private static method deleteData() that accepts a Connection object as a parameter and may throw an SQLException.
   2. Define a string variable deleteDataSql to store the SQL statement for deleting data.
   3. Use the conn.prepareStatement(deleteDataSql) method to create a PreparedStatement object deleteDataStmt for executing the precompiled SQL statement.
   4. Use the setInt() method to set the number 5 as the parameter value in the SQL statement.
   5. Call the executeUpdate() method to execute the SQL statement and delete data that meets the specified conditions from the database.

   Code:

       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 in the database that meets specific conditions. It accepts a connection object as a parameter and executes the SQL statement for updating data using a precompiled statement. The steps are as follows:

   1. Define a private static method updateData() that accepts a Connection object as a parameter and may throw an SQLException.
   2. Define a string variable updateDataSql to store the SQL statement for updating data.
   3. Use the conn.prepareStatement(updateDataSql) method to create a PreparedStatement object updateDataStmt for executing the precompiled SQL statement.
   4. Use the setString() method to set the string test_update as the first parameter value in the SQL statement.
   5. Use the setInt() method to set the number 5 as the second parameter value in the SQL statement.
   6. Call the executeUpdate() method to execute the SQL statement and update data that meets the specified conditions in the database.

   Code:

       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 from the database and processing it. It accepts a connection object as a parameter and executes the SQL statement for querying data using a precompiled statement. The steps are as follows:

   1. Define a private static method queryData() that accepts a Connection object as a parameter and may throw an SQLException.

   2. Define a string variable queryDataSql to store the SQL statement for querying data.

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

   4. Execute the SQL query using the queryDataStmt.executeQuery() method and use the ResultSet object rs to receive the query results.

   5. Use a while loop to traverse the query result set by calling the rs.next() method:

      1. Use the getInt() method to get the integer value of the column named id in the result set and assign it to the variable id.
      2. Use the getString() method to get the string value of the column named name in the result set and assign it to the variable name.
      3. Print the id and name of the query results.

   Code:

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

Full 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-oceanbase-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>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.9.0</version>
        </dependency>
    </dependencies>
</project>

# Database Connect Information
driverClassName=com.oceanbase.jdbc.Driver
url=jdbc:oceanbase://$host:$port/$schema_name
username=$user_name
password=$password

# 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 table
    private static void createTable(Connection conn) throws SQLException {
        String createTableSql = "CREATE TABLE test_tbl1 (id NUMBER, name VARCHAR2(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 OceanBase Connector/J, see OceanBase JDBC driver.