Connect to OceanBase Cloud by using a Proxool connection pool

Click here to download the project code, which is a package named proxool-mysql-client.zip.

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

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

The files and directories are described as follows:

• lib: a directory that stores the dependency libraries required by the project.
• proxool-0.9.1.jar: library files of the Proxool connection pool.
• proxool-cglib.jar: CGLib files for the Proxool connection pool.
• 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 proxool-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>proxool-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>.

   1. Add the mysql-connector-java library for connecting to and operating the database and configure the following parameters:

      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.

      The sample code is as follows:

              <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>5.1.47</version>
              </dependency>
      

   2. Add the proxool-cglib dependency library, which is a CGLib library for the Proxool connection pool, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to proxool.
      2. <artifactId>: the name of the dependency, which is set to proxool-cglib.
      3. <version>: the version of the dependency, which is set to 0.9.1.

      The sample code is as follows:

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

   3. Add the proxool dependency library, which is the core library of the Proxool connection pool, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to proxool.
      2. <artifactId>: the name of the dependency, which is set to proxool.
      3. <version>: the version of the dependency, which is set to 0.9.1.

      The sample code is as follows:

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

   4. Add the commons-logging dependency library, which is a general log library for recording logs in the application, and configure the following parameters:

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

      The sample code is as follows:

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

Code in db.properties

db.properties is a sample configuration file of the connection pool.

The db.properties file in this topic is a sample configuration file used to configure the connection pool attributes of a data source named jdbc-1. Perform the following steps to configure the db.properties file:

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

   The sample code is as follows:

   jdbc-1.proxool.alias=TEST
   

2. 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, database to be accessed, and additional connection attributes.
   3. Specify the username for connecting to the database.
   4. Specify the password for connecting to the database.

   The sample code is as follows:

   jdbc-1.proxool.driver-class=com.mysql.jdbc.Driver
   jdbc-1.proxool.driver-url=jdbc:mysql://$host:$port/$database_name?useSSL=false
   jdbc-1.user=$user_name
   jdbc-1.password=$password
   

   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.

3. Configure other parameters of the Proxool connection pool.

   1. Set the maximum number of connections in the connection pool to 8.
   2. Set the minimum number of connections in the connection pool to 5.
   3. Set the number of available connections in the connection pool to 4.
   4. Enable the Verbose mode to show more log information.
   5. Set the statistics collection cycles of the connection pool to 10s, 1 minute, and 1 day.
   6. Set the log level of the statistics to ERROR.

   The sample code is as follows:

   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
   

General parameters

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

   Define the package where the code resides and import relevant Proxool and JDBC classes. These classes are used to configure and manage the database connection pool and execute SQL statements. The Proxool connection pool can improve the database performance and reliability. Perform the following steps:

   1. Define the package where the code resides as com.example. This package stores the current Java classes.
   2. Import the Proxool configuration class org.logicalcobwebs.proxool.configuration.PropertyConfigurator.
   3. Import the input stream class java.io.InputStream for reading configuration files.
   4. Import the JDBC Connection class java.sql.Connection.
   5. Import the JDBC DriverManager class java.sql.DriverManager.
   6. Import the JDBC ResultSet class java.sql.ResultSet.
   7. Import the JDBC Statement class java.sql.Statement.
   8. Import the Properties class java.util.Properties for loading configuration files.

   The sample code is as follows:

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

   Define an entry method for Java applications. Obtain the database connection information from the configuration file. After establishing a database connection by using the Proxool driver, call the defined methods in sequence to execute DDL, DML, and query statements. Capture and return possible exceptions. This code segment aims to execute database operations and record logs by using the log recorder. Perform the following steps:

   1. Define a public class named Main.

      1. Define a private static constant named DB_PROPERTIES_FILE to indicate the path where the database configuration file is located. This constant can be referenced in code to load and read the configuration file.

      2. Define a public static method named main, which is used as the execution start point of the application.

         1. Capture code blocks with possible exceptions.

            1. Create a Properties object for reading attribute values from the configuration file.
            2. Use the class loader of the Main class to obtain the input stream of the configuration file.
            3. Use the input stream to load the attributes in the configuration file to the Properties object.
            4. Configure the connection pool based on the loaded attribute values.
            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 a DDL statement to create a table.
            9. Call the defined method executeDMLStatements() to execute DML statements to insert, update, and delete data.
            10. Call the defined method executeQueryStatements() to execute a query statement to obtain data.

         2. Capture and return possible exceptions.

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

   The sample code is as follows:

   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 a method for creating tables.
       // Define a method for executing DML statements.
       // Define a method for querying data.
   }
   

3. Define a method for creating tables.

   Define a private static method executeDDLStatements() for executing DDL statements, including table creation statements. Perform the following steps:

   1. Define a private static method executeDDLStatements(). The method receives a Statement object as parameters and can throw an exception.
   2. Call the execute() method to execute an SQL statement to create a table named test_proxool. The table has two columns: c1 and c2, which are respectively of the INT and VARCHAR(32) types.

   The sample code is as follows:

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

4. Define a method for executing DML statements.

   Define a private static method executeDMLStatements() for executing DML statements to insert, delete, and update data. Perform the following steps:

   1. Define a private static method executeDMLStatements(). The method receives a Statement object as parameters. If an exception occurs during the execution, the method throws an exception.
   2. Use the FOR loop to perform 10 rounds of iterations. In the loop, call the execute() method to execute an INSERT statement to insert the i variable and relevant string values into the test_proxool table.
   3. Execute a DELETE statement to delete rows whose c1 column values are smaller than or equal to 5 from the test_proxool table.
   4. Execute an UPDATE statement to update the c2 column values of the rows in the test_proxool table whose c1 column values are 6 to test_update.

   The sample code is as follows:

       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() for executing the SELECT statement and processing the result. Perform the following steps:

   1. Define a private static method executeQueryStatements(). The method receives a Statement object as parameters. If an exception occurs during the execution, the method throws an exception.
   2. Call the executeQuery() method to execute a SELECT statement and store the result in a ResultSet object named rs. Here, all data in the test_proxool table is returned for the query. Execute the try-with-resources block and make sure that the ResultSet object is automatically closed after being used.
   3. Use the WHILE loop and the next() method to iterate each row in the ResultSet object named rs. The rs.next() method moves the pointer to the next row in the result set in each iteration. The method returns true if a next row exists, and returns false if otherwise. In a WHILE loop, if the rs.next() method returns true, more rows are available. The code in the loop is executed, and the data in the current row is processed. After the data in all rows is processed, the rs.next() method returns false, and the loop ends.
   4. Call the getInt() and getString() methods to obtain the values of specified columns in the current row and return the values to the console. Here, the values of the c1 and c2 columns are returned. Call the getInt() method to obtain integer values and call the getString() method to obtain string values.

   The sample code is as follows:

       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-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>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.mysql.jdbc.Driver
#driver-url: url connection string, username and password must be determined
jdbc-1.proxool.driver-url=jdbc:mysql://$host:$port/$database_name?useSSL=false
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 INT, c2 VARCHAR(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"));
            }
        }
    }
}