Connect to OceanBase Cloud using Proxool connection pool

This topic describes how to build an application by using Proxool connection pool, OceanBase Connector/J, and OceanBase Cloud. The application can perform basic database operations, such as creating tables, inserting, deleting, updating, and querying data.

Download the proxool-oceanbase-client sample project Connect to OceanBase Cloud using Proxool connection pool (Oracle compatible mode)

Prerequisites

• You have registered an OceanBase Cloud account, 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 IntelliJ IDEA.

  Note

  The code examples in this topic are run in IntelliJ IDEA Community Edition 2021.3.2. You can also choose any other tool that you prefer to run the code examples.

Procedure

Step 1: Import the proxool-oceanbase-client project into IntelliJ IDEA

1. Start IntelliJ IDEA.

2. On the welcome page, click Open. Navigate to the project directory, select the root directory of the project, and click OK.

   

3. IntelliJ IDEA automatically detects the project type and loads the project.

   Note

   When you import a Maven project by using IntelliJ IDEA, 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.

   

4. (Optional) Manually import unresolved dependencies.

   If all the dependencies in the pom.xml file are automatically imported to the project, you can skip this step.

   Based on the prompt in the Sync window of IntelliJ IDEA, you can find that the proxool-cglib and proxool dependencies are unresolved. The proxool-cglib and proxool jar files are stored in the lib folder under the root directory of the proxool-oceanbase-client project. Perform the following steps to add the jar files to the project:

   1. In IntelliJ IDEA, choose File > Project Structure.
   2. In the left-side navigation pane, choose Modules.
   3. In the right-side pane, select the Dependencies tab. Click the + icon and choose JARs or directories.
   4. In the dialog box that appears, navigate to the lib directory that stores the jar files, select the jar files, and click OK.
   5. On the Dependencies tab, you can see the newly added jar files in the list.
   6. Click Apply or OK to save the changes.

   

Step 2: Modify the database connection information in the proxool-oceanbase-client project

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

Here is an example:

...
jdbc-1.proxool.driver-url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_schema001
jdbc-1.user=test_user
jdbc-1.password=******
...

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

Step 3: Run the proxool-oceanbase-client project

1. In the project navigation pane, find and expand the src/main/java/com.example directory.

2. Right-click the Main file and choose Run 'Main.main()'.

3. IntelliJ IDEA automatically compiles and runs the project, and displays the output result in the Run panel.

   

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

   obclient [TEST_USER001]> SELECT * FROM test_schema001.test_proxool;
   

   The return result is as follows:

   +------+---------------+
   | C1   | C2            |
   +------+---------------+
   |    6 | test_update   |
   |    7 | test_insert7  |
   |    8 | test_insert8  |
   |    9 | test_insert9  |
   |   10 | test_insert10 |
   +------+---------------+
   5 rows in set
   

Project code introduction

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

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

proxool-oceanbase-client
├── lib
│    ├── proxool-0.9.1.jar
│    └── proxool-cglib.jar
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── example
│       │           └── Main.java
│       └── resources
│           └── db.properties
└── pom.xml

File description:

• lib: stores the dependency library files required by the project.
• proxool-0.9.1.jar: the Proxool connection pool library file.
• proxool-cglib.jar: the CGLib library file used to support the Proxool connection pool.
• src: the root directory for source code.
• main: the main code directory, containing the core logic of the application.
• java: the Java source code directory.
• com: the Java package directory.
• example: the package directory for the sample project.
• Main.java: the main class program file, containing logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the resource file directory, containing 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.

Code of pom.xml

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

The code in this topic's pom.xml file mainly includes the following parts:

1. The file declaration statement.

   This statement declares that the file is an XML file, the XML version is 1.0, and the character encoding is UTF-8.

   Sample code:

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

2. The POM namespace and POM model version.

   1. The xmlns attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. The xsi:schemaLocation attribute specifies 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. The <modelVersion> element specifies 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. Basic information.

   1. The <groupId> element specifies the project's group ID as com.example.
   2. The <artifactId> element specifies the project's name as proxool-oceanbase-client.
   3. The <version> element specifies the project's version as 1.0-SNAPSHOT.

   Sample code:

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

4. Project source file attributes.

   The maven-compiler-plugin compiler plugin is specified for Maven, and the source code and target Java versions are set 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 setting 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. Project dependencies.

   1. The oceanbase-client dependency library is added to connect to and operate on the database:

      1. The <groupId> element specifies the dependency's group ID as com.oceanbase.
      2. The <artifactId> element specifies the dependency's name as oceanbase-client.
      3. The <version> element specifies the dependency's version as 2.4.2.

      Note

      This part of the code defines the project's dependency on OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC Driver.

      Sample code:

              <dependency>
                  <groupId>com.oceanbase</groupId>
                  <artifactId>oceanbase-client</artifactId>
                  <version>2.4.2</version>
              </dependency>
      

   2. The proxool-cglib dependency library is added to support the CGLib library for the Proxool connection pool:

      1. The <groupId> element specifies the dependency's group ID as proxool.
      2. The <artifactId> element specifies the dependency's name as proxool-cglib.
      3. The <version> element specifies the dependency's version as 0.9.1.

      Sample code:

              <dependency>
                  <groupId>proxool</groupId>
                  <artifactId>proxool-cglib</artifactId>
                  <version>0.9.1</version>
              </dependency>
      

   3. The proxool dependency library is added as the core library for the Proxool connection pool:

      1. The <groupId> element specifies the dependency's group ID as proxool.
      2. The <artifactId> element specifies the dependency's name as proxool.
      3. The <version> element specifies the dependency's version as 0.9.1.

      Sample code:

              <dependency>
                  <groupId>proxool</groupId>
                  <artifactId>proxool</artifactId>
                  <version>0.9.1</version>
              </dependency>
      

   4. The commons-logging dependency library is added as a general-purpose logging library for logging in applications:

      1. The <groupId> element specifies the dependency's group ID as commons-logging.
      2. The <artifactId> element specifies the dependency's name as commons-logging.
      3. The <version> element specifies the dependency's version as 1.2.

      Sample code:

              <dependency>
                  <groupId>commons-logging</groupId>
                  <artifactId>commons-logging</artifactId>
                  <version>1.2</version>
              </dependency>
      

db.properties code introduction

db.properties is the connection pool configuration file used in the example in this topic. It contains the configuration attributes of the connection pool.

The db.properties file in this topic is an example of a properties file used to configure the connection pool attributes of a data source named jdbc-1. It mainly contains the following parts:

1. Sets the alias of the data source to TEST.

   Sample code:

   jdbc-1.proxool.alias=TEST
   

2. Configures the database connection parameters.

   1. Sets the class name of the driver to com.oceanbase.jdbc.Driver, which is the class name of the OceanBase Database JDBC driver.
   2. Sets the database connection URL, including the host IP address, port number, and schema to be accessed.
   3. Sets the username of the database.
   4. Sets the password of the database.

   Sample code:

   jdbc-1.proxool.driver-class=com.oceanbase.jdbc.Driver
   jdbc-1.proxool.driver-url=jdbc:oceanbase://$host:$port/$schema_name
   jdbc-1.user=$user_name
   jdbc-1.password=$password
   

   Parameter description:

   • $host: the connection address of the cloud database of OceanBase Database, which is specified by the -h parameter in the connection string.
   • $port: the connection port of the cloud database of OceanBase Database, which is specified by the -P parameter in the connection string.
   • $schema_name: the name of the database to be accessed, which is specified by the -D parameter in the connection string.
   • $user_name: the username, which is specified by the -u parameter in the connection string.
   • $password: the password, which is specified by the -p parameter in the connection string.

3. Configures other Proxool connection pool parameters.

   1. Sets the maximum number of connections in the connection pool to 8.
   2. Sets the minimum number of connections in the connection pool to 5.
   3. Sets the number of available connections in the connection pool to 4.
   4. Enables the detailed mode of the connection pool to display more log information.
   5. Sets the statistics recording cycle of the connection pool to 10 seconds, 1 minute, and 1 day.
   6. Sets the log level of the connection pool statistics recording to the error level.

   Sample code:

   jdbc-1.proxool.maximum-connection-count=8
   jdbc-1.proxool.minimum-connection-count=5
   jdbc-1.proxool.prototype-count=4
   jdbc-1.proxool.verbose=true
   jdbc-1.proxool.statistics=10s,1m,1d
   jdbc-1.proxool.statistics-log-level=error
   

Common configuration parameters:

Introduction to Main.java

The Main.java file is part of the sample program, which demonstrates how to obtain a database connection through a Proxool 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 in this topic mainly includes the following parts:

1. Import the required classes and interfaces.

   Define the package where the code is located and import the classes and interfaces related to Proxool and JDBC. These classes are used to implement the configuration and management of the database connection pool and to execute SQL statements. By using the Proxool connection pool, you can improve the performance and reliability of database operations. The specific steps are as follows:

   1. Define the package where the code is located as com.example, which is used to store the current Java class.
   2. Import the org.logicalcobwebs.proxool.configuration.PropertyConfigurator class, which is used to configure Proxool.
   3. Import the java.io.InputStream class, which is used to read the configuration file.
   4. Import the java.sql.Connection class, which is used to obtain a database connection.
   5. Import the java.sql.DriverManager class, which is used to obtain a database connection.
   6. Import the java.sql.ResultSet class, which is used to obtain query results.
   7. Import the java.sql.Statement class, which is used to execute SQL statements.
   8. Import the java.util.Properties class, which is used to load and read the configuration file.

   Sample code:

   package com.example;
   
   import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   import java.util.Properties;
   

2. Define the class name and method.

   Define the entry method of the Java program. In this method, the database connection information is obtained by reading the configuration file. After the database connection is established by using the Proxool driver, the defined methods are called in sequence to execute DDL statements, DML statements, and query statements. The exceptions that may occur are captured and printed. The purpose of this code is to execute database-related operations and record logs by using a logger. The specific steps are as follows:

   1. Define a public class named Main.

      1. Define a private static constant named DB_PROPERTIES_FILE, which indicates the path of the database configuration (property) file. This constant can be referenced in the code to load and read the property file.

      2. Define a public static method main, which is the starting point of the program.

         1. Define a code block for capturing exceptions that may occur.

            1. Create a Properties object to read the properties in the configuration file.
            2. Use the class loader of the Main class to obtain the input stream of the configuration file.
            3. Load the configuration file by using the loaded input stream and load the properties into the Properties object.
            4. Configure the connection pool by using the loaded properties.
            5. Dynamically load the Proxool database driver.
            6. Establish a database connection by using the Proxool driver.
            7. Create a Statement object.
            8. Call the defined method executeDDLStatements() to execute DDL statements, which are statements for creating tables.
            9. Call the defined method executeDMLStatements() to execute DML statements, which are statements for inserting, updating, and deleting data.
            10. Call the defined method executeQueryStatements() to execute query statements and obtain data.

         2. Capture and print the exception information that may occur.

   2. Define methods for creating tables, executing DML statements, and querying data.

   Sample code:

   public class Main {
       private static final String DB_PROPERTIES_FILE = "/db.properties";
   
       public static void main(String[] args) {
           try {
               Properties properties = new Properties();
               InputStream is = Main.class.getResourceAsStream(DB_PROPERTIES_FILE);
               properties.load(is);
               PropertyConfigurator.configure(properties);
   
               Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
               try (Connection conn = DriverManager.getConnection("proxool.TEST");
                   Statement stmt = conn.createStatement()) {
                   executeDDLStatements(stmt);
                   executeDMLStatements(stmt);
                   executeQueryStatements(stmt);
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       // Define the method for creating tables.
       // Define the method for executing DML statements.
       // Define the method for querying data.
   }
   

3. Define the method for creating tables.

   Define a private static method executeDDLStatements() to execute DDL (data definition language) statements, including statements for creating tables. The specific steps are as follows:

   1. Define a private static method executeDDLStatements() that receives a Statement object as a parameter and may throw an Exception exception.
   2. Use the execute() method to execute SQL statements and create a table named test_proxool, which has two columns, c1 and c2, of the NUMBER and VARCHAR2(32) types, respectively.

   Sample code:

       private static void executeDDLStatements(Statement stmt) throws Exception {
           stmt.execute("CREATE TABLE test_proxool (c1 NUMBER, c2 VARCHAR2(32))");
       }
   

4. Define the method for executing DML statements.

   Define a private static method executeDMLStatements() to execute DML (data manipulation language) statements, including statements for inserting, deleting, and updating data. The specific steps are as follows:

   1. Define a private static method executeDMLStatements() that receives a Statement object as a parameter and throws an Exception exception if an exception occurs during execution.
   2. Use a for loop to iterate from 1 to 10. In the loop, use the execute() method to execute SQL insert statements and insert the variable i and the corresponding string value into the test_proxool table.
   3. Execute an SQL delete statement to delete rows from the test_proxool table where the value of the c1 column is less than or equal to 5.
   4. Execute an SQL update statement to update the value of the c2 column to test_update for rows in the test_proxool table where the value of the c1 column is 6.

   Sample code:

       private static void executeDMLStatements(Statement stmt) throws Exception {
           for (int i = 1; i <= 10; i++) {
               stmt.execute("INSERT INTO test_proxool VALUES (" + i + ",'test_insert" + i + "')");
           }
           stmt.execute("DELETE FROM test_proxool WHERE c1 <= 5");
           stmt.execute("UPDATE test_proxool SET c2 = 'test_update' WHERE c1 = 6");
       }
   

5. Define a method for querying data.

   Define a private static method executeQueryStatements() to execute SELECT queries and process the results. The steps are as follows:

   1. Define a private static method executeQueryStatements() that receives a Statement object as a parameter. If an exception occurs during execution, the method will throw an Exception.
   2. Use the executeQuery() method to execute the SELECT query statement and store the results in a ResultSet object rs. In this case, the query returns all data from the test_proxool table. Use the try-with-resources statement to ensure that the ResultSet is automatically closed after it is no longer needed.
   3. Use a while loop and the next() method to iterate through each row of data in the ResultSet object rs. In each iteration, the rs.next() method moves the pointer to the next row in the result set. If there is another row of data available, this method returns true; otherwise, it returns false. In the while loop, as long as rs.next() returns true, it indicates that there are more rows of data available. The code within the loop will execute and process the data of the current row. Once all rows of data have been processed, rs.next() will return false, and the loop will end.
   4. Use the getInt() and getString() methods to retrieve the values of specified columns in the current row and print them to the console. In this case, the values of the c1 and c2 columns are printed. The getInt() method is used to retrieve integer values, and the getString() method is used to retrieve string values.

   Code:

       private static void executeQueryStatements(Statement stmt) throws Exception {
           try (ResultSet rs = stmt.executeQuery("SELECT * FROM test_proxool")) {
               while (rs.next()) {
                   System.out.println(rs.getInt("c1") + "   " + rs.getString("c2"));
               }
           }
       }
   

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>proxool-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>proxool</groupId>
            <artifactId>proxool-cglib</artifactId>
            <version>0.9.1</version>
        </dependency>
        <dependency>
            <groupId>proxool</groupId>
            <artifactId>proxool</artifactId>
            <version>0.9.1</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>
</project>

#alias: the alias of the data source
jdbc-1.proxool.alias=TEST
#driver-class: driver name
jdbc-1.proxool.driver-class=com.oceanbase.jdbc.Driver
#driver-url: url connection string, username and password must be determined
jdbc-1.proxool.driver-url=jdbc:oceanbase://$host:$port/$schema_name
jdbc-1.user=$user_name
jdbc-1.password=$password
#The maximum number of database connections. The default is 15
jdbc-1.proxool.maximum-connection-count=8
#The minimum number of database connections, defaults to 5
jdbc-1.proxool.minimum-connection-count=5
#The number of available connections in the Connection pool. If the number of connections in the current Connection pool is less than this value, new connections will be established (assuming that the maximum number of available connections is not exceeded). For example, if we have three active connections and two available connections, and our prototype count is 4, the database Connection pool will try to establish another two connections. This is different from the minimum connection count Minimum connection count also counts active connections. Prototype count is the number of spare connections
jdbc-1.proxool.prototype-count=4
#verbose: detailed information settings. Parameter bool value
jdbc-1.proxool.verbose=true
#statistics: connection pool usage statistics. Parameter "10s, 1m, 1d"
jdbc-1.proxool.statistics=10s,1m,1d
#statistics-log-level:  log statistics tracking type. Parameter 'ERROR' or 'INFO'
jdbc-1.proxool.statistics-log-level=error

package com.example;

import org.logicalcobwebs.proxool.configuration.PropertyConfigurator;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class Main {
    private static final String DB_PROPERTIES_FILE = "/db.properties";

    public static void main(String[] args) {
        try {
            Properties properties = new Properties();
            InputStream is = Main.class.getResourceAsStream(DB_PROPERTIES_FILE);
            properties.load(is);
            PropertyConfigurator.configure(properties);

            Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
            try (Connection conn = DriverManager.getConnection("proxool.TEST");
                Statement stmt = conn.createStatement()) {
                executeDDLStatements(stmt);
                executeDMLStatements(stmt);
                executeQueryStatements(stmt);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void executeDDLStatements(Statement stmt) throws Exception {
        stmt.execute("CREATE TABLE test_proxool (c1 NUMBER, c2 VARCHAR2(32))");
    }

    private static void executeDMLStatements(Statement stmt) throws Exception {
        for (int i = 1; i <= 10; i++) {
            stmt.execute("INSERT INTO test_proxool VALUES ("+ i +",'test_insert" + i + "')");
        }
        stmt.execute("DELETE FROM test_proxool WHERE c1 <= 5");
        stmt.execute("UPDATE test_proxool SET c2 = 'test_update' WHERE c1 = 6");
    }

    private static void executeQueryStatements(Statement stmt) throws Exception {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM test_proxool")) {
            while (rs.next()) {
                System.out.println(rs.getInt("c1") + "   " + rs.getString("c2"));
            }
        }
    }
}

References

• For more information about OceanBase Connector/J, see OceanBase JDBC driver.
• For more information about using the Proxool connection pool, see Introduction for Users.