DocsICP FilingConsole
Documentation
Home Page ApsaraDB for OceanBase (Deprecated) User Guide Application Development Application Development MySQL mode Use a database connection pool Sample program: Connect to an OceanBase database using a DBCP connection pool

Sample program: Connect to an OceanBase database using a DBCP connection pool

Updated at:
Copy as MD
Product
My Favorites

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

image.pngClick to download the dbcp-mysql-client sample project

Prerequisites

  • You have installed an OceanBase database and created a MySQL mode tenant.

  • You have installed JDK 1.8 and Maven.

  • You have installed Eclipse.

    Note

    The steps in this topic use Eclipse IDE for Java Developers version 2022-03. You can use a different tool to run the sample code.

Procedure

Note

The following procedure describes how to compile and run the project in a Windows environment using Eclipse IDE for Java Developers 2022-03. The steps might differ slightly if you use a different operating system or compiler.

  1. Import the dbcp-mysql-client project into Eclipse.

  2. Obtain the OceanBase database URL.

  3. Modify the database connection information in the dbcp-mysql-client project.

  4. Run the dbcp-mysql-client project.

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

  1. Open Eclipse. From the menu bar, choose File > Open Projects from File System.

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

    Note

    When you import a Maven project into Eclipse, Eclipse automatically detects the pom.xml file. Eclipse then downloads the required dependency libraries based on the dependencies described in the file and adds them to the project.

    image.png

  3. View the project details.

    image.png

Step 2: Get the OceanBase database URL

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

    Example:

    obclient -hxxx.xxx.xxx.xxx -P3306 -utest_user001 -p****** -Dtest

    For more information about connection strings, see Get connection parameters.

  2. Use the information from the OceanBase database connection string to populate the following URL.

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

    Parameter description:

    • $host: The domain name for the OceanBase database connection.

    • $port: The OceanBase database connection port. The default port for a MySQL mode tenant is 3306.

    • $database_name: The name of the database to access.

    • $user_name: The tenant's connection account.

    • $password: The account password.

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

    Example:

    jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=test_user001&password=******

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

Modify the database connection information in the dbcp-mysql-client/src/main/resources/db.properties file using the information from Step 2: Obtain the OceanBase database URL.

Example:

  • The IP address of the OBServer node is xxx.xxx.xxx.xxx.

  • The access port is 3306.

  • The name of the database to access is test.

  • The tenant's connection account is test_user001.

  • The password is ******.

Code:

...
url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/test
username=test_user001
password=******
...

Step 4: Run the dbcp-mysql-client project

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

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

  3. View the project's log information and output results in the Eclipse console window.

    image.png

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

    obclient [test]> SELECT * FROM test_tbl1;

    The following result is returned:

    +------+--------------+
| id   | name         |
+------+--------------+
|    5 | test_update  |
|    6 | test_insert6 |
|    7 | test_insert7 |
|    8 | test_insert8 |
|    9 | test_insert9 |
+------+--------------+
5 rows in set

Project code overview

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

After you decompress the file, a folder named dbcp-mysql-client is created. The directory structure is as follows:

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

File description:

  • src: The source code root directory.

  • main: The main code directory, which contains the primary 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 sample file. It contains the logic for creating tables and for inserting, deleting, updating, and querying data.

  • resources: The resource file directory, which contains configuration files.

  • db.properties: The configuration file for the connection pool. It contains the relevant parameters for the database connection.

  • pom.xml: The configuration file for the Maven project. It is used to manage the project's dependencies and build settings.

pom.xml code overview

The pom.xml file is the configuration file for a Maven project. It defines the project's dependencies, plugins, and build rules. Maven is a Java project management tool that automatically downloads dependencies, and compiles and packages projects.

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

  1. File declaration statement.

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

    Code:

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

  2. Configure 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 that the POM model version used by this POM file is 4.0.0.

    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. The <groupId> element specifies the project's organization as com.example.

    2. The <artifactId> element specifies the project's name as dbcp-mysql-client.

    3. The <version> element specifies the project's version number as 1.0-SNAPSHOT.

    Code:

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

  4. Configure project source file properties.

    This configures the maven-compiler-plugin to use Java 8 for both the source code and the compiled bytecode. This ensures that the project is compiled and runs correctly in a Java 8 environment.

    Note

    Java 1.8 and Java 8 are different names for the same version.

    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 project dependencies.

    Define dependencies using the <dependency> element:

    • MySQL JDBC dependency:

      1. The <groupId> element specifies the dependency's organization as mysql.

      2. The <artifactId> element specifies the dependency's name as mysql-connector-java.

      3. The <version> element specifies the dependency's version number as 5.1.47.

    • dbcp dependency:

      1. The <groupId> element specifies the dependency's organization as org.apache.commons.

      2. The <artifactId> element specifies the dependency's name as commons-dbcp2.

      3. The <version> element specifies the dependency's version number as 2.9.0.

    Code:

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

db.properties code overview

db.properties is the connection pool configuration file for the sample in this topic. It contains the configuration properties for the connection pool. These properties include the driver's class name, database URL, username, password, connection pool size and limits, connection timeout, and options for handling abandoned connections.

The code in this topic's db.properties file includes the following main parts:

  1. Configure database connection parameters.

    1. Set the driver's class name to the MySQL JDBC driver class name, com.mysql.jdbc.Driver.

    2. Set the database connection URL, which includes the host IP, port number, and the database to access.

    3. Set the database username.

    4. Set the database password.

    5. Set the connection properties. useSSL=false indicates that the connection does not use SSL encryption. characterEncoding=UTF-8 indicates that UTF-8 encoding is used for data transmission.

    Code:

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

    Parameter description:

    • $host: The domain name for the OceanBase database connection.

    • $port: The OceanBase database connection port. The default port for a MySQL mode tenant is 3306.

    • $database_name: The name of the database to access.

    • $user_name: The tenant's connection account.

    • $password: The account password.

  2. Configure other DBCP connection pool parameters.

    1. Set the initial size of the connection pool to 30. This is the number of connections to create when the pool starts.

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

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

    4. Set the minimum number of idle connections to maintain in the pool to 5. If the number of idle connections falls below this value, the pool creates new connections.

    5. Set the maximum wait time to obtain a connection from the pool to 1000 milliseconds. If all connections in the pool are in use, the operation to obtain a connection waits until a connection becomes available or the maximum wait time is exceeded.

    6. Set the timeout before an abandoned connection is removed to 1 second.

      Note

      The default value for removeAbandonedTimeout is 300 seconds. In this sample, it is set to 1 second for testing purposes. Adjust this value as needed to meet your application's requirements.

    7. Specifies whether the connection pool revokes idle connections in the program:

      • Set whether to detect and remove abandoned connections during maintenance. Set to true.

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

    Code:

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

removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
Important

The specific property (parameter) configuration depends on your project requirements and database characteristics. Adjust the configuration as needed. For more information about DBCP connection pool parameters, see BasicDataSource Configuration Parameters.

DBCP connection pool basic data source configuration parameters:

Category

Property

Default value

Description

Required parameters

driverClass

N/A

Specifies the class name of the database driver.

url

N/A

Specifies the URL to use when connecting to the database.

username

N/A

Specifies the username to use when connecting to the database.

password

N/A

Specifies the password to use when connecting to the database.

Recommended parameters

initialSize

0

Specifies the initial size of the connection pool, which is the number of initial connections created when the pool starts. If you set this to a value greater than 0, the specified number of connections are created during pool initialization. This can pre-create connections and reduce latency for the client's first connection request.

maxTotal

8

Specifies the maximum number of connections in the connection pool. If you set this to a negative number, there is no limit.

maxIdle

8

Specifies the maximum number of idle connections allowed in the pool without releasing extra connections. If you set this to a negative number, there is no limit.

minIdle

0

Specifies the minimum number of idle connections to maintain in the pool without releasing extra connections. If you set this to a negative number, there is no limit.

maxWaitMillis

indefinitely

Specifies the maximum wait time (in milliseconds) to get a connection from the pool. If set to -1, it means wait indefinitely. If set to a positive number, when all connections in the pool are in use, the operation to get a connection waits for the specified time. An exception is thrown if the time is exceeded.

validationQuery

N/A

Specifies the SQL query statement used to validate whether a connection is valid. If specified, this query must be an SQL SELECT statement that returns at least one row. If not specified, the connection is validated by calling the isValid() method.

testOnBorrow

true

Specifies whether to validate an object when it is borrowed from the pool. If the object fails validation, it is dropped from the pool, and another object will be borrowed.

testWhileIdle

false

Specifies whether the connection pool validates connections when they are idle. If set to true, the pool periodically runs a validation query to check the validity of idle connections. If an object fails validation, it is dropped from the pool.

Optional parameters

connectionProperties

N/A

Specifies additional connection properties in key-value pairs. These properties are passed to the underlying JDBC driver when a database connection is obtained. The string format must be propertyName=property;

Important

The username and password properties are passed explicitly, so they do not need to be included here.

  • removeAbandonedOnMaintenance

  • removeAbandonedOnBorrow

false

These two parameters control the removal behavior for connections that are considered abandoned.

  • removeAbandonedOnMaintenance: If this parameter is set to true, the connection pool removes connections that are considered abandoned during the maintenance cycle (when eviction ends). However, this parameter takes effect only when the maintenance cycle is enabled (by setting timeBetweenEvictionRunsMillis to a positive value).

  • removeAbandonedOnBorrow: If this parameter is set to true, the connection pool checks for and removes abandoned connections each time a connection is borrowed from the pool. This removal also requires the following two conditions to be met:

    • getNumActive() > getMaxTotal() - 3: The number of active connections is greater than the maximum number of connections minus 3.

    • getNumIdle() < 2: The number of idle connections is less than 2.

By setting the above two parameters to true, the connection pool can automatically detect and remove abandoned connections. Abandoned connections are connections that have not been used for a long time, possibly because the application did not close them correctly. Removing these abandoned connections can free up database resources and improve the performance and efficiency of the connection pool.

Main.java code overview

The Main.java file is part of the sample program. It demonstrates how to obtain a database connection from a DBCP connection pool, perform a series of database operations, such as creating a table, inserting, deleting, updating, and querying data, and then printing the query results.

The code in this topic's Main.java file includes the following main parts:

  1. Import required classes and interfaces.

    This section imports the required classes and interfaces for reading files, database operations, and the database connection pool.

    1. Declares the com.example package, which contains the current Java class.

    2. Imports the java.io.FileInputStream class to read files.

    3. Imports the java.sql.Connection interface, which represents a database connection.

    4. Imports the java.sql.PreparedStatement interface, which represents a precompiled SQL statement.

    5. Imports the java.sql.ResultSet interface, which represents the result set of a database query.

    6. Imports the java.sql.SQLException exception class, which represents an SQL operation exception.

    7. Imports the java.util.Properties class to load configuration files.

    8. Imports the org.apache.commons.dbcp2.BasicDataSource class, which represents the database connection pool.

    9. Imports the org.apache.commons.dbcp2.BasicDataSourceFactory class to create the 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 program's entry point.

    2. In the main method, first call the createDataSource() method to create the dataSource connection pool object.

    3. Use a try-with-resources statement to ensure that the connection pool object is automatically closed.

    4. In the try block, call the getConnection() method to obtain a conn database connection object from the connection pool.

    5. Sequentially call the createTable(), insertData(), deleteData(), updateData(), and queryData() methods to perform the corresponding database operations.

    6. If an exception occurs, print the exception information in 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 to create a table
    // Define a method to insert data
    // Define a method to delete data
    // Define a method to update data
    // Define a method to query data
}

  3. Create a connection pool.

    This method creates a database connection pool by reading parameters from a configuration file to initialize the pool object. The steps are as follows:

    1. Define a private static method createDataSource() with a return type of BasicDataSource. The method may throw an Exception.

    2. Create a Properties object named props to store the database connection configuration information.

    3. Create a FileInputStream object named 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, using the props object as a parameter to create and return a BasicDataSource object.

    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 to create a table.

    This method creates a specified table in the database. It accepts a connection object as a parameter and uses a precompiled statement to execute the SQL statement for creating the table. The 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.

    2. Define a string variable createTableSql to store the SQL statement for creating the table. The SQL statement specifies the table name as test_tbl1 and defines two columns: an INT type column named id and a VARCHAR(32) type column named name.

    3. Use the conn.prepareStatement(createTableSql) method to create a PreparedStatement object named createTableStmt. This object is used to execute the precompiled SQL statement.

    4. Call the execute() method to execute the SQL statement to create the table.

    Code:

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

  5. Define a method to insert data.

    This method inserts specified data into a database table. It accepts a connection object as a parameter and uses a precompiled statement to execute the SQL statement for inserting data. The 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.

    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 named insertDataStmt. This object is used to execute the precompiled SQL statement.

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

      1. The loop iterates 10 times to insert 10 records.

      2. Set the first parameter in the SQL statement to the value of the loop variable i.

      3. Set the second parameter to the string test_insert + i.

      4. You can insert data into the database by calling the executeUpdate() method to execute an SQL statement.

    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 to delete data.

    This method deletes data that meets specific conditions from the database. It accepts a connection object as a parameter and uses a precompiled statement to execute the SQL statement for deleting data. The steps are as follows:

    1. Define a private static method deleteData() that accepts a Connection object as a parameter. The method 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 named deleteDataStmt. This object is used to execute the precompiled SQL statement.

    4. Use setInt() to set the parameter value to 5 in the SQL statement.

    5. You can call executeUpdate(). This method executes an SQL statement to delete data that meets specific 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 to update data.

    This method updates data that meets specific conditions in the database. It accepts a connection object as a parameter and uses a precompiled statement to execute the SQL statement for updating data. The steps are as follows:

    1. Define a private static method updateData() that accepts a Connection object as a parameter. The method 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 named updateDataStmt. This object is used to execute the precompiled SQL statement.

    4. Set the first parameter in the SQL statement to the string test_update.

    5. Set the second parameter to 5.

    6. Call the executeUpdate() method to execute an SQL statement that updates data meeting 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 to query data.

    This method queries and processes data from the database. It accepts a connection object as a parameter and uses a precompiled statement to execute the SQL statement for querying data. The steps are as follows:

    1. Define a private static method queryData() that accepts a Connection object as a parameter. The method 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 named queryDataStmt. This object is used to execute the precompiled SQL statement.

    4. Execute the SQL query and use a ResultSet object named rs to store the result.

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

      1. You can use the getInt() method to retrieve the integer value from the id column in the result set and assign the value to the id variable.

      2. Use the getString() method to retrieve the string value from the name column of the result set, and assign it to the name variable.

      3. Print the id and name values.

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

Complete code sample

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>dbcp-mysql-client</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

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

db.properties

jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false
username=$user_name
password=$password

dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.maxLifetime=1800000
dataSource.idleTimeout=600000
dataSource.connectionTimeout=30000

Main.java

package com.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class Main {
    public static void main(String[] args) {
        try {
            HikariConfig config = new HikariConfig("/db.properties");
            try (HikariDataSource dataSource = new HikariDataSource(config);
                 Connection conn = dataSource.getConnection()) {
                createTable(conn);

                insertData(conn, 1, "A1");
                insertData(conn, 2, "A2");

                selectData(conn);

                updateData(conn, "test_update", 1);
                selectData(conn);

                deleteData(conn, 2);
                selectData(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS test_hikaricp (id INT, name VARCHAR(50))";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.executeUpdate();
            System.out.println("Table created successfully.");
        }
    }

    private static void insertData(Connection conn, int id, String name) throws SQLException {
        String sql = "INSERT INTO test_hikaricp (id, name) VALUES (?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
            System.out.println("Data inserted successfully.");
        }
    }

    private static void selectData(Connection conn) throws SQLException {
        String sql = "SELECT * FROM test_hikaricp";
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            System.out.println("User Data:");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
            System.out.println();
        }
    }

    private static void updateData(Connection conn, String name, int id) throws SQLException {
        String sql = "UPDATE test_hikaricp SET name = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();
            System.out.println("Data updated successfully.");
        }
    }

    private static void deleteData(Connection conn, int id) throws SQLException {
        String sql = "DELETE FROM test_hikaricp WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            System.out.println("Data deleted successfully.");
        }
    }
}

References

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

Previous：Sample program for connecting to an OceanBase database with a HikariCP connection poolNext：Sample program for connecting to an OceanBase database using Commons Pool