DocsICP FilingConsole
官方文档
首页

Sample program for connecting to an OceanBase database using a Druid connection pool

更新时间：
复制 MD 格式
我的收藏

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

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

Prerequisites

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

  • You have installed JDK 1.8 and Maven.

  • You have installed Eclipse.

    Note

    The tool used to run the code in this topic is Eclipse IDE for Java Developers version 2022-03. You can also use other tools to run the sample code.

Procedure

Note

The steps provided in this topic are for compiling and running the project in a Windows environment using Eclipse IDE for Java Developers version 2022-03. The steps may vary slightly if you use a different operating system or compiler.

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

  2. Obtain the OceanBase database URL.

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

  4. Run the druid-mysql-client project.

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

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

  2. In the dialog box that appears, click the Directory button to 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 in the project, 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 status.

    image.png

Step 2: Obtain 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 Obtain connection parameters.

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

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

    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 connection account for the tenant.

    • $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=******&useSSL=false

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

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

image.png

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 connection account for the tenant is test_user001.

  • The password is ******.

The code is as follows:

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

Step 4: Run the druid-mysql-client project

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

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

  3. View the output in the Eclipse console window.

    image.png

Project code overview

Click druid-mysql-client to download the project code. The downloaded file is a compressed package named druid-mysql-client.zip.

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

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

File description:

  • src: The root directory for the source code.

  • 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 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 parameters related to the database connection.

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

pom.xml code overview

pom.xml 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 can automatically download dependencies, compile, and package projects.

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

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

    The code is as follows:

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

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

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

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

    The code is as follows:

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

  4. Configure the properties of the project source files.

    This configuration specifies the Maven compiler plugin as maven-compiler-plugin and sets both the source and target Java versions to 8. This means that the project's source code is written using Java 8 features, and the compiled bytecode is 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.

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

    1. Add the mysql-connector-java dependency library, which is used to interact with the database:

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

      The code is as follows:

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

    2. Add the druid dependency library:

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

      2. The <artifactId> element specifies the dependency's name as druid.

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

      The code is as follows:

              <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

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 database URL, username, password, and other optional connection pool settings.

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

  1. Configure database connection parameters.

    1. Set the database driver class name to com.oceanbase.jdbc.Driver.

    2. Specifies the URL for the database connection, including the host IP address, port number, and schema to access.

    3. Specifies the database username.

    4. Specifies the database password.

    The code is as follows:

    driverClassName=com.oceanbase.jdbc.Driver
url=jdbc:oceanbase://$host:$port/$database_name?useSSL=false
username=$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 connection account for the tenant.

    • $password: The account password.

  2. Configure other connection pool parameters.

    1. Specifies the SQL statement to validate the connection as select 1.

    2. Specifies the initial number of connections in the pool as 3. This means that when the connection pool starts, it creates 3 initial connections.

    3. Specifies the maximum number of active connections in the pool as 30. This means that the pool can have a maximum of 30 concurrent connections.

    4. Specifies whether to log abandoned connections as true. This means that when an abandoned connection is recycled, information is written to the error log. You can set this to true in a staging environment and to false in an online environment to prevent performance impact.

    5. Specifies the minimum number of idle connections in the pool as 5. This means that when the number of idle connections drops below 5, the pool automatically creates new connections.

    6. Specifies the maximum wait time for a connection as 1000 milliseconds. This means that if all connections in the pool are in use, a request for a connection throws a timeout exception after waiting for 1000 milliseconds.

    7. Specifies the minimum idle time for a connection as 300000 milliseconds. This means that a connection is recycled if it is idle for 300000 milliseconds (5 minutes).

    8. Specifies whether to recycle abandoned connections as true. This means that a connection is recycled if it exceeds the time defined by removeAbandonedTimeout.

    9. Specifies the timeout for abandoned connections as 300 seconds. This means that a connection that has not been used for 300 seconds (5 minutes) is recycled.

    10. Specifies the run interval for the idle connection recycling thread as 10000 milliseconds. This means that the thread runs every 10000 milliseconds (10 seconds) to recycle idle connections.

    11. Specifies whether to validate a connection's availability when it is borrowed as false. Setting this to false can improve performance, but may result in borrowing an invalid connection.

    12. Specifies whether to validate a connection's availability when it is returned as false. Setting this to false can improve performance, but may result in returning an invalid connection.

    13. Specifies whether to validate a connection while it is idle as true. When set to true, the connection pool periodically runs validationQuery to validate the connection's availability.

    14. Specifies whether to enable the persistent connection keepalive feature as false. Setting this to false disables persistent connection keepalive.

    15. Specifies the idle time threshold for connections as 60000 milliseconds. If a connection's idle time exceeds this threshold of 60000 milliseconds (1 minute), the keepalive mechanism checks the connection to ensure its availability. If any operation occurs on the connection within the threshold, the idle time is recalculated.

    The code is as follows:

    validationQuery=select 1
initialSize=3
maxActive=30
logAbandoned=true
minIdle=5
maxWait=1000
minEvictableIdleTimeMillis=300000
removeAbandoned=true
removeAbandonedTimeout=300
timeBetweenEvictionRunsMillis=10000
testOnBorrow=false
testOnReturn=false
testWhileIdle=true
keepAlive=false
keepAliveBetweenTimeMillis=60000
Important

The specific property (parameter) configuration depends on your project requirements and database characteristics. Adjust the configuration as needed.

Common configuration parameters for the Druid connection pool:

Parameter

Description

url

Specifies the URL for connecting to the database, including the database type, hostname, port number, and database name.

username

Specifies the username required to connect to the database.

password

Specifies the password required to connect to the database.

driverClassName

Specifies the database driver class name. If you do not explicitly configure driverClassName, the Druid connection pool automatically detects the database type (dbType) based on the url and selects the corresponding driverClassName. This automatic detection mechanism reduces configuration effort and simplifies the process. However, if the url cannot be parsed correctly, or to use a non-standard database driver class, you must explicitly configure the driverClassName parameter to ensure the correct driver class is loaded.

initialSize

Specifies the number of connections to create when initializing the connection pool. When the application starts, the connection pool creates the specified number of connections and places them in the pool.

maxActive

Specifies the maximum number of active connections in the connection pool. When the number of active connections reaches the maximum value, subsequent connection requests will wait until a connection is released.

maxIdle

Specifies the maximum number of idle connections in the connection pool (this property is deprecated). When the number of idle connections reaches the maximum value, excess connections are closed.

minIdle

Specifies the minimum number of idle connections in the connection pool. When the number of idle connections drops below the minimum value, the connection pool creates new connections.

maxWait

Specifies the maximum time to wait for a connection. An exception is thrown if this time is exceeded. If set to a positive number, it represents the wait time in milliseconds.

poolPreparedStatements

Specifies whether to cache PreparedStatement. If set to true, PreparedStatement objects are cached to improve performance.

validationQuery

Specifies the SQL query statement for connection validation. When a connection is taken from the pool, this query is executed to verify that the connection is valid.

timeBetweenEvictionRunsMillis

Specifies the interval, in milliseconds, at which the connection pool checks for idle connections. If a connection's idle time exceeds the value of timeBetweenEvictionRunsMillis, the connection is closed.

minEvictableIdleTimeMillis

Specifies the minimum idle time for a connection in the pool, in milliseconds. Connections that are idle longer than this time will be recycled. If set to a negative number, connections are not recycled.

testWhileIdle

Specifies whether to test connections while they are idle. If set to true, the validationQuery is executed on idle connections to verify their validity.

testOnBorrow

Specifies whether to test connections when they are borrowed from the pool. If set to true, the validationQuery is executed to verify the connection's validity.

testOnReturn

Specifies whether to test connections when they are returned to the pool. If set to true, the validationQuery is executed to verify the connection's validity.

filters

Specifies a series of predefined filters in the connection pool. These filters can perform pre-processing and post-processing operations on connections in a specific order to provide additional features and enhance pool performance. Common filters include the following:

  1. stat: Used to collect performance metrics for the connection pool, such as the number of active connections, requests, and faults.

  2. wall: Used for the SQL firewall, which can intercept and disable unsafe SQL statements to improve database security.

  3. log4j: Used to output connection pool logs to log4j for logging and debugging.

  4. slf4j: Used to output connection pool logs to slf4j for logging and debugging.

  5. config: Used to load connection pool configuration information from an external configuration file.

  6. encoding: Used to set the character encoding between the connection pool and the database.

By configuring these filters in the filters property, the connection pool applies them in the specified order. You can separate multiple filter names with commas, for example: filters=stat,wall,log4j.

Main.java code overview

Main.java is the main program for the sample in this topic. This sample shows how to use a data source, connection objects, and various database operation methods to interact with the database.

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

  1. Import necessary classes and interfaces.

    1. Declares the package name for the current code as com.example.

    2. Imports the Java IOException class to handle input/output exceptions.

    3. Imports the Java InputStream class to obtain an input stream from a file or other source.

    4. Imports the Java Connection interface, which represents a connection to the database.

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

    6. Imports the Java SQLException class to handle SQL exceptions.

    7. Imports the Java Statement interface to execute SQL statements.

    8. Imports the Java PreparedStatement interface for precompiled SQL statements.

    9. Imports the Java Properties class to handle properties files.

    10. Imports the Java DataSource interface to manage database connections.

    11. Imports the DruidDataSourceFactory class from the Alibaba Druid connection pool to create a Druid data source.

    The code is as follows:

    package com.example;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

  2. Create a Main class and define the main method.

    This defines a Main class and a main method. The main method demonstrates how to use the connection pool to perform a series of operations on the database. The steps are as follows:

    1. Define a public class named Main as the program's entry point. The class name must match the file name.

    2. Define a public static method main as the program's entry point, which accepts command-line arguments.

    3. Use an exception handling mechanism to catch and handle potential exceptions.

    4. Call the loadPropertiesFile method to load the properties file and return a Properties object.

    5. Call the createDataSource() method to create a data source object based on the configuration in the properties file.

    6. Use a try-with-resources statement to obtain a database connection and automatically close it after use.

      1. Call the createTable() method to create a table.

      2. Call the insertData() method to insert data.

      3. Call the selectData() method to query data.

      4. Call the updateData() method to update data.

      5. Call the selectData() method again to query the updated data.

      6. Call the deleteData() method to delete data.

      7. Call the selectData() method again to query the data after deletion.

      8. Call the dropTable() method to drop the table.

    The code is as follows:

    public class Main {

    public static void main(String[] args) {
        try {
            Properties properties = loadPropertiesFile();
            DataSource dataSource = createDataSource(properties);
            try (Connection conn = dataSource.getConnection()) {
                // Create table
                createTable(conn);
                // Insert data
                insertData(conn);
                // Query data
                selectData(conn);

                // Update data
                updateData(conn);
                // Query the updated data
                selectData(conn);

                // Delete data
                deleteData(conn);
                // Query the data after deletion
                selectData(conn);

                // Drop table
                dropTable(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // Define a method to get and use configuration information from the properties file
    // Define a method to get the data source object
    // Define a method to create a table
    // Define a method to insert data
    // Define a method to update data
    // Define a method to delete data
    // Define a method to query data
    // Define a method to delete the table
}

  3. Define a method to obtain and use configuration information from the properties file.

    Define a private static method loadPropertiesFile() to load a properties file and return a Properties object. The steps are as follows:

    1. The method is defined as private and static. It returns a Properties object and declares that it may throw an IOException.

    2. Create a Properties object to store the key-value pairs from the properties file.

    3. Use a try-with-resources statement to obtain the input stream is for the db.properties file through the class loader.

    4. Use the load method to load the properties from the input stream into the properties object.

    5. Return the loaded properties object.

    The code is as follows:

        private static Properties loadPropertiesFile() throws IOException {
        Properties properties = new Properties();
        try (InputStream is = Main.class.getClassLoader().getResourceAsStream("db.properties")) {
            properties.load(is);
        }
        return properties;
    }

  4. Define a method to obtain the data source object.

    The following steps describe the createDataSource() method, which creates a DataSource object based on the configuration in the properties file. This object is used to manage and obtain database connections.

    1. The method is defined as private and static. It accepts a Properties object as a parameter and declares that it may throw an Exception.

    2. Call the createDataSource() method of the DruidDataSourceFactory class, passing the properties to return a DataSource object.

    The code is as follows:

        private static DataSource createDataSource(Properties properties) throws Exception {
        return DruidDataSourceFactory.createDataSource(properties);
    }

  5. Define a method to create a table.

    Define a private static method createTable() to create a data table in the database:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

    2. Use a try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the conn connection object.

    3. Define a string variable sql to store the SQL statement for creating the table.

    4. Use the executeUpdate() method to execute the SQL statement and create the data table.

    5. Print a success message indicating that the table was created.

    The code is as follows:

        private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE test_druid (id INT, name VARCHAR(20))";
            stmt.executeUpdate(sql);
            System.out.println("Table created successfully.");
        }
    }

  6. Define a method to insert data.

    Define a private static method insertData() to insert data into the database. The method performs the following steps:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

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

    3. Define an integer variable insertedRows with an initial value of 0 to record the number of inserted rows.

    4. Use a try-with-resources statement to create a PreparedStatement object insertDataStmt using the prepareStatement() method of the conn connection object and the insert SQL statement.

    5. Use a for loop to iterate 5 times and insert 5 rows of data.

      1. Use the setInt() method to set the value of the first parameter to the loop variable i.

      2. Use the setString() method to set the value of the second parameter to the string test_insert concatenated with the value of the loop variable i.

      3. Use the executeUpdate() method to execute the insert SQL statement and add the number of affected rows to the insertedRows variable.

    6. Print a success message indicating that data was inserted, along with the total number of inserted rows.

    7. Return the total number of inserted rows.

    The code is as follows:

        private static int insertData(Connection conn) throws SQLException {
        String insertDataSql = "INSERT INTO test_druid (id, name) VALUES (?, ?)";
        int insertedRows = 0; 
        try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
            for (int i = 1; i < 6; i++) {
                insertDataStmt.setInt(1, i);
                insertDataStmt.setString(2, "test_insert" + i);
                insertedRows += insertDataStmt.executeUpdate();
            }
            System.out.println("Data inserted successfully. Inserted rows: " + insertedRows);
        }
        return insertedRows;
    }

  7. Define a method to update data.

    Define a private static method updateData() to update data in the database. The steps are as follows:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

    2. Use a try-with-resources statement to create a PreparedStatement object pstmt using the prepareStatement() method of the conn connection object and the update SQL statement.

    3. Use the setString() method to set the value of the first parameter to the string test_update.

    4. Use the setInt() method to set the value of the second parameter to the integer value 3.

    5. Use the executeUpdate() method to execute the update SQL statement and assign the number of affected rows to the updatedRows variable.

    6. Print a success message indicating that data was updated, along with the total number of updated rows.

    The code is as follows:

        private static void updateData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("UPDATE test_druid SET name = ? WHERE id = ?")) {
            pstmt.setString(1, "test_update");
            pstmt.setInt(2, 3);
            int updatedRows = pstmt.executeUpdate();
            System.out.println("Data updated successfully. Updated rows: " + updatedRows);
        }
    }

  8. Define a method to delete data.

    Define a private static method deleteData() to delete data from the database. The steps are as follows:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

    2. Use a try-with-resources statement to create a PreparedStatement object pstmt using the prepareStatement() method of the conn connection object and the delete SQL statement.

    3. Use the setInt() method to set the value of the first parameter to the integer value 3.

    4. Use the executeUpdate() method to execute the delete SQL statement and assign the number of affected rows to the deletedRows variable.

    5. Print a success message indicating that data was deleted, along with the total number of deleted rows.

    The code is as follows:

        private static void deleteData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM test_druid WHERE id < ?")) {
            pstmt.setInt(1, 3);
            int deletedRows = pstmt.executeUpdate();
            System.out.println("Data deleted successfully. Deleted rows: " + deletedRows);
        }
    }

  9. Define a method to query data.

    Define a private static method selectData() to query data from the database by following these steps:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

    2. Use a try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the conn connection object.

    3. Define a string variable sql to store the SQL statement for querying data.

    4. Use the executeQuery() method to execute the query SQL statement and assign the returned result set to the resultSet variable.

    5. Use a while loop to iterate through each row in the result set.

      1. Use the getInt() method to retrieve the integer value of the id field for the current row and assign it to the id variable.

      2. Use the getString() method to retrieve the string value of the name field for the current row and assign it to the name variable.

      3. Print the values of the id and name fields for the current row.

    The code is as follows:

        private static void selectData(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM test_druid";
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + ", name: " + name);
            }
        }
    }

  10. Define a method to drop the table.

    Define a private static methoddropTable() to drop tables from the database as follows:

    1. The method is defined as private and static. It accepts a Connection object as a parameter and declares that it may throw an SQLException.

    2. Use a try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the conn connection object.

    3. Define a string variable sql to store the SQL statement for dropping the table.

    4. Use the executeUpdate() method to execute the SQL statement to drop the table.

    5. Print a success message indicating that the table was dropped.

    The code is as follows:

        private static void dropTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "DROP TABLE test_druid";
            stmt.executeUpdate(sql);
            System.out.println("Table dropped successfully.");
        }
    }

Complete code

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>druid-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.40</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
    </dependencies>
</project>

db.properties

# Database Configuration
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://$host:$port/$database_name?useSSL=false
username=$user_name
password=$password

# Connection Pool Configuration
# A query to validate connections. For MySQL, you must configure 'select 1'. For Oracle, use 'select 1 from dual'.
validationQuery=select 1
# The initial number of connections.
initialSize=3
# The maximum number of active connections in the connection pool.
maxActive=30
# Specifies whether to log abandoned connections when they are closed. When a connection is recycled, information is printed to the console. Set to true for staging environments and false for online environments to avoid performance impact.
logAbandoned=true
# The minimum number of idle connections.
minIdle=5
# The maximum time to wait for a connection, in milliseconds.
maxWait=1000
# The minimum time a connection can be idle before it is eligible for eviction. The value is in milliseconds.
minEvictableIdleTimeMillis=300000
# Specifies whether to remove abandoned connections that exceed the timeout.
removeAbandoned=true
# The timeout for abandoned connections, in seconds. The current value is 300 seconds (5 minutes). Adjust this value if your business processing time exceeds 5 minutes.
removeAbandonedTimeout=300
# The interval, in milliseconds, between runs of the idle connection eviction thread.
timeBetweenEvictionRunsMillis=10000
# Specifies whether to validate connections when they are borrowed from the pool. Setting to false can improve performance.
testOnBorrow=false
# Specifies whether to validate connections when they are returned to the pool.
testOnReturn=false
# Specifies whether to validate connections while they are idle. If set to true, the pool runs validationQuery on idle connections.
testWhileIdle=true
# Specifies whether to enable the keepalive feature. If set to true, the DestroyConnectionThread checks connections.
keepAlive=false
# The time, in milliseconds, between keepalive checks for a connection.
keepAliveBetweenTimeMillis=60000

Main.java

package com.example;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class Main {

    public static void main(String[] args) {
        try {
            Properties properties = loadPropertiesFile();
            DataSource dataSource = createDataSource(properties);
            try (Connection conn = dataSource.getConnection()) {
                // Create table
                createTable(conn);
                // Insert data
                insertData(conn);
                // Query data
                selectData(conn);

                // Update data
                updateData(conn);
                // Query the updated data
                selectData(conn);

                // Delete data
                deleteData(conn);
                // Query the data after deletion
                selectData(conn);

                // Drop table
                dropTable(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Properties loadPropertiesFile() throws IOException {
        Properties properties = new Properties();
        try (InputStream is = Main.class.getClassLoader().getResourceAsStream("db.properties")) {
            properties.load(is);
        }
        return properties;
    }

    private static DataSource createDataSource(Properties properties) throws Exception {
        return DruidDataSourceFactory.createDataSource(properties);
    }

    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE test_druid (id INT, name VARCHAR(20))";
            stmt.executeUpdate(sql);
            System.out.println("Table created successfully.");
        }
    }

    private static int insertData(Connection conn) throws SQLException {
        String insertDataSql = "INSERT INTO test_druid (id, name) VALUES (?, ?)";
        int insertedRows = 0; 
        try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
            for (int i = 1; i < 6; i++) {
                insertDataStmt.setInt(1, i);
                insertDataStmt.setString(2, "test_insert" + i);
                insertedRows += insertDataStmt.executeUpdate();
            }
            System.out.println("Data inserted successfully. Inserted rows: " + insertedRows);
        }
        return insertedRows;
    }

    private static void updateData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("UPDATE test_druid SET name = ? WHERE id = ?")) {
            pstmt.setString(1, "test_update");
            pstmt.setInt(2, 3);
            int updatedRows = pstmt.executeUpdate();
            System.out.println("Data updated successfully. Updated rows: " + updatedRows);
        }
    }

    private static void deleteData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM test_druid WHERE id < ?")) {
            pstmt.setInt(1, 3);
            int deletedRows = pstmt.executeUpdate();
            System.out.println("Data deleted successfully. Deleted rows: " + deletedRows);
        }
    }

    private static void selectData(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM test_druid";
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + ", name: " + name);
            }
        }
    }

    private static void dropTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "DROP TABLE test_druid";
            stmt.executeUpdate(sql);
            System.out.println("Table dropped successfully.");
        }
    }
}

References

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

Previous：Sample program for connecting to an OceanBase database using Commons PoolNext：Oracle mode