Connect to OceanBase Database by using a Proxool connection pool

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

Click to download the proxool-mysql-client sample project Sample program for connecting to OceanBase Database by using a Proxool connection pool (MySQL mode)

Prerequisites

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

• You have installed JDK 1.8 and Maven.

• You have installed IntelliJ IDEA.

  Note

  The tool used to run the sample code in this topic is IntelliJ IDEA Community Edition 2021.3.2. You can also choose a tool of your preference to run the sample code.

Procedure

1. Import the proxool-mysql-client project into IntelliJ IDEA.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the proxool-mysql-client project.
4. Run the proxool-mysql-client project.

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

1. Start IntelliJ IDEA.

2. On the welcome page, click Open, navigate to the directory where the project is located, 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 using IntelliJ IDEA, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

   

4. (Optional) Manually import the unresolvable dependencies.

   If the dependencies in the pom.xml file are automatically imported to the project, ignore this step.

   The Sync pane of IntelliJ IDEA indicates that the proxool-cglib and proxool dependencies are unresolvable. The .jar files of the proxool-cglib and proxool dependencies are located in the lib folder of the root directory of the proxool-mysql-client project. Perform the following steps to add these files to the project:

   1. In IntelliJ IDEA, choose File > Project Structure.
   2. In the left-side pane, click Modules.
   3. In the right-side pane, click the Dependencies tab. On this tab, click + and choose JARs or directories.
   4. In the dialog box that appears, navigate to the lib directory where the .jar files are stored, select the .jar files, and click OK.
   5. The added .jar files appear in the list on the Dependencies tab.
   6. Click Apply or OK to save the changes.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

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

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

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

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

   Parameter description:

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

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

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

     Notice

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

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

   • password: the password of the account.

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

   Here is an example:

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

Step 3: Modify the database connection information in the proxool-mysql-client project

Modify the database connection information in the proxool-mysql-client/src/main/resources/db.properties file based on the information obtained in Step 2: Obtain the URL of OceanBase Database.

Here is an example:

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

Sample code:

...
jdbc-1.proxool.driver-url=jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?useSSL=false
jdbc-1.user=test_user@mysql001
jdbc-1.password=******
...

Step 4: Run the proxool-mysql-client project

1. In the project navigation bar, locate 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 results in the run panel.

   

4. You can also execute the following SQL statement in OBClient to view the results.

   obclient [(none)]> SELECT * FROM test.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-mysql-client to download the project code, which is a compressed package named proxool-mysql-client.zip.

After decompressing it, you will find 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

File description:

• lib: the directory for storing the dependency library files required by the project.
• proxool-0.9.1.jar: library files of the Proxool connection pool.
• proxool-cglib.jar: CGLib library files that 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 directory for storing the Java source code.
• com: the directory for storing the Java package.
• example: the directory for storing the packages of the sample project.
• Main.java: a sample main class file that includes logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the directory for storing resource files, including configuration files.
• db.properties: the configuration file of the connection pool, containing relevant database connection parameters.
• pom.xml: the configuration file of the Maven project, used for managing project dependencies and build settings.

Introduction to the pom.xml file

The pom.xml file is the configuration file of the Maven project, which 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 the projects.

The pom.xml file in this topic mainly includes the following sections:

1. Declaration statement.

   The XML file is declared as an XML file that uses XML standard 1.0 and character encoding UTF-8.

   Sample code:

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

2. Configure namespaces and the 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 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 group as com.example.
   2. Use <artifactId> to specify the project name as proxool-mysql-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample code:

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

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

   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 configuration ensures that the project can correctly handle Java 8 syntax and features during compilation and execution.

   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 on which the project depends.

   Define the dependencies using <dependency>:

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

      1. Use <groupId> to specify the dependency group as mysql.
      2. Use <artifactId> to specify the dependency name as mysql-connector-java.
      3. Use <version> to specify the dependency version as 5.1.47.

      Sample code:

              <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:

      1. Use <groupId> to specify the dependency group as proxool.
      2. Use <artifactId> to specify the dependency name as proxool-cglib.
      3. Use <version> to specify the dependency version as 0.9.1.

      Sample code:

              <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:

      1. Use <groupId> to specify the dependency group as proxool.
      2. Use <artifactId> to specify the dependency name as proxool.
      3. Use <version> to specify the dependency version as 0.9.1.

      Sample code:

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

   4. Add the commons-logging dependency library, which is a general logging library for recording logs in applications:

      1. Use <groupId> to specify the dependency group as commons-logging.
      2. Use <artifactId> to specify the dependency name as commons-logging.
      3. Use <version> to specify the dependency version as 1.2.

      Sample code:

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

db.properties code

The db.properties file is a connection pool configuration file used in the examples in this topic.

The db.properties file in this topic is an example of a properties file that configures the connection pool for a data source named jdbc-1. The file contains the following sections:

1. Set an alias for the data source, TEST.

   Sample code:

   jdbc-1.proxool.alias=TEST
   

2. Configure the database connection parameters.

   1. Set the driver class name. Here, the class name of the MySQL JDBC driver, com.mysql.jdbc.Driver, is used.
   2. Set the database connection URL, which includes the host IP address, port number, database name, and additional connection properties.
   3. Set the database username.
   4. Set the database password.

   Sample code:

   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
   

   Parameter description:

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

3. Configure other parameters of the Proxool connection pool.

   1. Set the maximum connection count to 8.
   2. Set the minimum connection count to 5.
   3. Set the available connection count to 4.
   4. Set the verbose mode to on, that is, enable more log output.
   5. Set the statistics recording cycle to 10 seconds, 1 minute, and 1 day.
   6. Set the statistics log level 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
   

Commonly used attribute descriptions:

Introduction to Main.java

The Main.java file is part of the sample application and demonstrates how to obtain a database connection from a Proxool connection pool and perform a series of database operations, including table creation, data insertion, data deletion, data updating, data query, and result printing.

The code of the Main.java file in this topic is mainly divided into the following sections:

1. Import the required classes and interfaces.

   Define the package where the code is located and import the classes related to Proxool and JDBC. These classes are used to configure and manage the database connection pool and execute SQL statements. By using the Proxool connection pool, the performance and reliability of database operations are improved. The specific steps are as follows:

   1. Define the com.example package, which stores the current Java class.
   2. Import the org.logicalcobwebs.proxool.configuration.PropertyConfigurator class of Proxool.
   3. Import the java.io.InputStream class for reading configuration files.
   4. Import the java.sql.Connection class of JDBC.
   5. Import the java.sql.DriverManager class of JDBC.
   6. Import the java.sql.ResultSet class of JDBC.
   7. Import the java.sql.Statement class of JDBC.
   8. Import the java.util.Properties class for loading configuration files.

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

   Define the main method of a Java program, which obtains database connection information by reading the configuration file. After establishing a database connection using the Proxool driver, it calls predefined methods to execute DDL, DML, and query statements in sequence. It captures and prints the exception information that may occur. The purpose of this code is to perform database-related operations and record logs by using a log recorder. 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 (attribute) file. This constant can be referenced in the code to load and read the attribute file.

      2. Define a public static method named main, which serves as the starting execution point of the program.

         1. A code block that captures possible exceptions.

            1. Create a Properties object to read attributes from 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 attributes into the Properties object.
            4. Configure the connection pool by using the loaded attributes.
            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 predefined method executeDDLStatements() to execute DDL statements for creating a table. In this example, the table name is test_proxool.
            9. Call the predefined method executeDMLStatements() to execute DML statements for inserting, updating, and deleting data.
            10. Call the predefined method executeQueryStatements() to execute query statements for querying data.

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

   2. Define a method for creating a table.

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

3. Define a method for creating a table.

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

   1. Define a private static method named executeDDLStatements(), which accepts a Statement object as the parameter and may throw an Exception.
   2. Use the execute() method to execute SQL statements to create a table named test_proxool. The table contains two columns: c1 of the INT type and c2 of the VARCHAR(32) type.

   Sample code:

       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 named executeDMLStatements() to execute DML statements, which include statements for inserting data, updating data, and deleting data. The specific steps are as follows:

   1. Define a private static method named executeDMLStatements(), which accepts a Statement object as the parameter and may throw an Exception when 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 an SQL insert statement to insert the variable i and the related string value into the test_proxool table.
   3. Execute an SQL delete statement to delete rows where the value of column c1 is less than or equal to 5 from the test_proxool table.
   4. Execute an SQL update statement to update the value of column c2 to test_update for the row where the value of column c1 is 6 in the test_proxool table.

   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 named executeQueryStatements() to execute SELECT statements and process the query results. The specific steps are as follows:

   1. Define a private static method named executeQueryStatements(), which accepts a Statement object as the parameter and may throw an Exception when an exception occurs during execution.
   2. Use the executeQuery() method to execute a SELECT statement and store the query result in the ResultSet object rs. In this example, all data in the test_proxool table is queried. Use the try-with-resources statement to ensure that the ResultSet is automatically closed after it is used. In this example, the ResultSet is the rs object.
   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 available, the method returns true; otherwise, it returns false. In the while loop, as long as rs.next() returns true, there are more rows available. The code in the loop body is executed, and the data of the current row is processed. After all rows of data are processed, rs.next() returns false, and the loop ends.
   4. Use the getInt() and getString() methods to obtain the values of specified columns in the current row and print them to the console. In this example, the values of the c1 and c2 columns are printed. The getInt() method is used to obtain an integer value, and the getString() method is used to obtain a string value.

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

Related information

• For more information about MySQL Connector/J, see Overview of MySQL Connector/J.
• For more information about the Proxool connection pool, see Introduction for Users.