Connect to OceanBase Database by using a Druid connection pool

This topic introduces how to build an application by using a Druid 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.

Download the druid-mysql-client sample project

Prerequisites

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

• You have installed Java Development Kit (JDK) 1.8 and Maven.

• You have installed Eclipse.

  Note

  The tool used to run the sample code in this topic is Eclipse IDE for Java Developers (2022-03), but you can also choose a tool that suits your personal preference to run the code.

Procedure

1. Import the druid-mysql-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
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. Start Eclipse and choose File > Open Projects from File System.

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

   Note

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

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

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

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

   where

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

   • $port specifies 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 specifies the name of the database to be accessed.

     Notice

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

   • $user_name specifies 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 specifies 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=******&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.

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 account is test_user001@mysql001, where mysql001 is a MySQL user tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

The sample code is as follows:

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

Step 4: Run the druid-mysql-client project

1. In the project navigation view, locate and expand the druid-mysql-client/src/main/java directory.

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

   

3. In the console window of Eclipse, view the output results.

   

Project code introduction

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

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

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

Here is a breakdown of the files and directories:

• src: the root directory for storing the source code.
• main: the directory for storing the main code, including the major 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 file of the main class that contains logic for table creation, data insertion, data deletion, data modification, and data query.
• resources: the directory for storing resource files, including configuration files.
• db.properties: the configuration file of the connection pool, which contains relevant database connection parameters.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in the pom.xml file

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

To configure the pom.xml file, perform the following steps:

1. Declare the file.

   Declare the file to be an XML file that uses XML standard 1.0 and character encoding UTF-8.

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. xmlns: the default XML namespace, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the mapping between the XML namespace and its corresponding XML schema definition (XSD) file. The value typically consists of paired strings separated by spaces. Each pair consists of a default XML namespace (http://maven.apache.org/POM/4.0.0) in the first part, and the URI of the corresponding XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd) in the second part.
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.

   The sample code is as follows:

   <project xmlns="http://maven.apache.org/POM/4.0.0"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
   
    <! -- Other configurations -->
   
   </project>
   

3. Configure basic information.

   1. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to druid-mysql-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

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

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

   Specify the Maven compiler plug-in 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, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

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

   The sample code is as follows:

       <build>
           <plugins>
               <plugin>
                   <groupId>org.apache.maven.plugins</groupId>
                   <artifactId>maven-compiler-plugin</artifactId>
                   <configuration>
                       <source>8</source>
                       <target>8</target>
                   </configuration>
               </plugin>
           </plugins>
       </build>
   

5. Configure the components on which the project depends.

   1. Add the mysql-connector-java dependency library for interactions with the database, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
      2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
      3. <version>: the version of the dependency, which is set to 5.1.40.

      The sample 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, and configure the following parameters:

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to com.alibaba.
      2. <artifactId>: the name of the dependency, which is set to druid.
      3. <version>: the version of the dependency, which is set to 1.2.8.

      The sample code is as follows:

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

Code in the db.properties file

db.properties is a sample configuration file of the connection pool. The configuration file contains the URL, username, and password for connecting to the database, and other optional parameters of the connection pool.

To configure the db.properties file, perform the following steps:

1. Configure database connection parameters.

   1. Specify the class name of the database driver as com.mysql.jdbc.Driver.
   2. Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
   3. Specify the username for connecting to the database.
   4. Specify the password for connecting to the database.

   The sample code is as follows:

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

   where

   • $host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • $port specifies 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 specifies the name of the database to be accessed.
   • $user_name specifies 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 specifies the password of the account.

2. Configure other connection pool parameters.

   1. Specify select 1 as the SQL statement for connection verification.
   2. Set the number of initial connections in the connection pool to 3. When the connection pool is started, three initial connections are created.
   3. Set the maximum number of active connections allowed in the connection pool to 30.
   4. Set the value of the parameter that specifies whether to record logs for abandoned connections to true. This way, when an abandoned connection is recycled, an error log is recorded. We recommend that you set the value to true in a test environment and to false in an online environment to avoid compromising the performance.
   5. Set the minimum number of idle connections in the connection pool to 5. When the number of idle connections is less than 5, the connection pool automatically creates new connections.
   6. Set the maximum waiting time for requesting a connection to 1,000 ms. When the application requests a connection from the connection pool, if all connections are occupied, a timeout exception is thrown when the specified waiting time expires.
   7. Set the minimum duration for which a connection can remain idle in the connection pool to 300,000 ms. A connection that has been idle for 300,000 ms (5 minutes) will be recycled.
   8. Set the value of the parameter that specifies whether to recycle abandoned connections to true. An abandoned connection is recycled after the time specified by removeAbandonedTimeout elapses.
   9. Set the timeout value of an abandoned connection to 300s. An abandoned connection that has not been used in 300s (5 minutes) will be recycled.
   10. Set the interval for scheduling the idle connection recycling thread to 10,000 ms. In other words, the idle connection recycling thread is scheduled every 10,000 ms (10s) to recycle idle connections.
   11. Set the value of the parameter that specifies whether to verify a connection when it is requested to false. This can improve the performance. However, the obtained connection may be unavailable.
   12. Set the value of the parameter that specifies whether to verify a connection when it is returned to the connection pool to false. This can improve the performance. However, the connection returned to the connection pool may be unavailable.
   13. Set the value of the parameter that specifies whether to verify idle connections to true. When the value is set to true, the connection pool periodically executes the statement specified by validationQuery to verify the availability of connections.
   14. Set the value of the parameter that specifies whether to enable connection keepalive to false. The value false specifies to disable connection keepalive.
   15. Set the maximum idle period of connections to 60,000 ms. If the idle period of a connection exceeds 60,000 ms (1 minute), the connection keepalive mechanism will check the connection to ensure its availability. If any operation is performed in the connection during the idle period, the idle period is recounted.

   The sample 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
   

The following table describes the general Druid parameters.

Code in the Main.java file

The Main.java file is the main program of the sample application in this topic. It demonstrates how to use the data source, connection objects, and various database operation methods to interact with the database.

To configure the Main.java file, perform the following steps:

1. Import the required classes and interfaces.

   1. Declare the name of the package to which the current code belongs as com.example.
   2. Import the Java class IOException for handling input/output exceptions.
   3. Import the Java class InputStream for obtaining input streams from files or other sources.
   4. Import the Java interface Connection for representing connections with the database.
   5. Import the Java interface ResultSet for representing datasets of database query results.
   6. Import the Java class SQLException for handling SQL exceptions.
   7. Import the Java interface Statement for executing SQL statements.
   8. Import the Java interface PreparedStatement for representing precompiled SQL statements.
   9. Import the Java class Properties for processing the configuration file.
   10. Import the Java interface DataSource for managing database connections.
   11. Import the DruidDataSourceFactory class of Alibaba Druid for creating Druid data sources.

   The sample 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 a main method.

   Define a Main class and a main method. The main method demonstrates how to use the connection pool to execute a series of operations in the database. The specific steps are as follows:

   1. Define a Main public class, which is used as the entry to the application. The class name must be the same as the file name.

   2. Define a public static method main as the entry to the application to receive command-line options.

   3. Capture and handle possible exceptions by using the exception handling mechanism.

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

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

   6. Use the try-with-resources block to obtain a database connection and automatically close the connection 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 data after the deletion.
      8. Call the dropTable() method to drop the table.

   The sample 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 a 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 the table.
                   dropTable(conn);
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       // Define a method for obtaining and using configurations in the configuration file.
       // Define a method for obtaining a data source object.
       // Define a method for creating tables.
       // Define a method for inserting data.
       // Define a method for updating data.
       // Define a method for deleting data.
       // Define a method for querying data.
       // Define a method for dropping tables.
   }
   

3. Define a method for obtaining and using configurations in the configuration file.

   Define a private static method loadPropertiesFile() for loading the configuration file and returning a Properties object. The specific steps are as follows:

   1. Define a private static method loadPropertiesFile() that returns a Properties object, and declare that the method may throw an IOException.
   2. Create a Properties object for storing key-value pairs in the configuration file.
   3. Use the try-with-resources block to obtain the input stream is of the db.properties configuration file by using the class loader.
   4. Call the load method to load properties in the input stream to the properties object.
   5. Return the properties object.

   The sample 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 for obtaining a data source object.

   Define a private static method createDataSource() for creating a DataSource object based on the configuration file. The object is used to manage and obtain database connections. The specific steps are as follows:

   1. Define a private static method createDataSource() that receives a Properties object as a parameter, and declare that the method may throw an Exception.
   2. Call the createDataSource() method of the DruidDataSourceFactory class to pass the properties property to the createDataSource method and return a DataSource object.

   The sample code is as follows:

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

5. Define a method for creating tables.

   Define a private static method createTable() for creating tables in the database. The specific steps are as follows:

   1. Define a private static method createTable() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Call the createStatement() method of the Connection object conn in the try-with-resources block to create a Statement object named stmt.
   3. Define a string variable sql for storing the table creation statement.
   4. Call the executeUpdate() method to execute the SQL statement to create a data table.
   5. Return a message indicating that the table is created.

   The sample 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 for inserting data.

   Define a private static method insertData() for inserting data into the database. The specific steps are as follows:

   1. Define a private static method insertData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.

   2. Define a string variable insertDataSql for storing the data insertion statement.

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

   4. Use the prepareStatement() method of the Connection object conn and the data insertion statement in the try-with-resources block to create a PreparedStatement object named insertDataStmt.

   5. Use the FOR loop statement to perform five rounds of iterations to insert five data records.

      1. Call the setInt() method to set the value of the first parameter to the value of the loop variable i.
      2. Call the setString() method to set the value of the second parameter to the value of the test_insert string combined with the value of the loop variable i.
      3. Call the executeUpdate() method to execute the data insertion statement and add the number of operated rows to the value of the insertedRows variable.

   6. Return a message indicating that the data is inserted, with the total number of inserted rows displayed.

   7. Return the total number of inserted rows.

   The sample 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 for updating data.

   Define a private static method updateData() for updating data in the database. The specific steps are as follows:

   1. Define a private static method updateData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Use the prepareStatement() method of the Connection object conn and the data update statement in the try-with-resources block to create a PreparedStatement object named pstmt.
   3. Call the setString() method to set the value of the first parameter to test_update.
   4. Call the setInt() method to set the value of the second parameter to 3.
   5. Call the executeUpdate() method to execute the data update statement and assign the number of operated rows to the updatedRows variable.
   6. Return a message indicating that the data is updated, with the total number of updated rows displayed.

   The sample 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 for deleting data.

   Define a private static method deleteData() for deleting data from the database. The specific steps are as follows:

   1. Define a private static method deleteData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Use the prepareStatement() method of the Connection object conn and the data deletion statement in the try-with-resources block to create a PreparedStatement object named pstmt.
   3. Call the setInt() method to set the value of the first parameter to 3.
   4. Call the executeUpdate() method to execute the data deletion statement and assign the number of operated rows to the deletedRows variable.
   5. Return a message indicating that the data is deleted, with the total number of deleted rows displayed.

   The sample 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 for querying data.

   Define a private static method selectData() for querying data from the database. The specific steps are as follows:

   1. Define a private static method selectData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.

   2. Call the createStatement() method of the Connection object conn in the try-with-resources block to create a Statement object named stmt.

   3. Define a string variable sql for storing the data query statement.

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

   5. Use the WHILE loop statement to traverse each row in the result set.

      1. Call the getInt() method to obtain the integer value of the id field in the current row and assign the obtained value to the id variable.
      2. Call the getString() method to obtain the string value of the name field in the current row and assign the obtained value to the name variable.
      3. Return the values of the id and name fields in the current row.

   The sample 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 for dropping tables.

    Define a private static method dropTable() for dropping tables from the database. The specific steps are as follows:

    1. Define a private static method dropTable() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
    2. Call the createStatement() method of the Connection object conn in the try-with-resources block to create a Statement object named stmt.
    3. Define a string variable sql for storing the table dropping statement.
    4. Call the executeUpdate() method to execute the table dropping statement.
    5. Return a message indicating that the table is dropped.

    The sample 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

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

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

# Connection Pool Configuration
#To check whether the database link is valid, MySQL must be configured to select 1; Oracle is select 1 from dual
validationQuery=select 1
#Initial number of connections
initialSize=3
#Maximum number of activations, that is, the maximum number of Connection pool
maxActive=30
#When closing the Abandoned connection, the error log is output. When the link is recycled, the console prints information. The test environment can add true, while the online environment is false. Will affect performance.
logAbandoned=true
#Minimum number of activations during idle time
minIdle=5
#The maximum waiting time for a connection, in milliseconds
maxWait=1000
#The maximum time to start the eviction thread is the survival time of a link (previous value: 25200000, the converted result of this time is: 2520000/1000/60/60=7 hours)
minEvictableIdleTimeMillis=300000
#Whether to recycle after exceeding the time limit
removeAbandoned=true
#Exceeding the time limit (in seconds), currently 5 minutes. If any business processing time exceeds 5 minutes, it can be adjusted appropriately.
removeAbandonedTimeout=300
# Run the idle connection collector Destroy thread every 10 seconds to detect the interval time between connections, based on the judgment of testWhileIdle
timeBetweenEvictionRunsMillis=10000
#When obtaining a link, not verifying its availability can affect performance.
testOnBorrow=false
#Check whether the link is available when returning the link to the Connection pool.
testOnReturn=false
#This configuration can be set to true, without affecting performance and ensuring security. The meaning is: Detect when applying for a connection. If the idle time is greater than timeBetweenEviceRunsMillis, execute validationQuery to check if the connection is valid.
testWhileIdle=true
#Default false, if configured as true, connection detection will be performed in the DestroyConnectionThread thread (timeBetweenEvaluation once)
keepAlive=false
#If keepAlive rule takes effect and the idle time of the connection exceeds it, the connection will only be detected
keepAliveBetweenTimeMillis=60000

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 a 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 the 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.