This topic describes how to build an application using a Druid connection pool, MySQL Connector/J, and OceanBase Database to perform basic database operations, including creating tables, inserting data, updating data, deleting data, querying data, and dropping tables.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud MySQL-compatible tenant. For more information, see Create a cluster instance and Create a tenant.
You have obtained the connection string of the target tenant in MySQL mode. For instructions, see Get a connection string.
You have installed 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. You can also choose a tool that suits your personal preference to run the sample code.
Procedure
Note
The procedure described in this topic is based on using Eclipse IDE for Java Developers 2022-03 in a Windows environment to compile and run the project. If you are using a different operating system or compiler, the steps may vary slightly.
Step 1: Import the druid-mysql-client project into Eclipse
Open Eclipse, and select File > Open Projects from File System from the menu bar.
In the dialog box that appears, click Directory, select the directory where the project is located, and then click Finish to complete the import.
Note
When importing a Maven project in Eclipse, it automatically detects the
pom.xmlfile in the project, downloads the required dependency libraries based on the dependencies described in the file, and adds them to the project.View the project.
Step 2: 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 connection string obtained in the prerequisites.
Example:
- The connection address is
t5******.********.oceanbase.cloud. - The access port is 3306.
- The name of the database to access is
test. - The tenant connection account is
test_user001. - The password is
******.
Code:
...
url=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false
username=test_user001
password=******
...
Step 3: Run the druid-mysql-client project
In the project navigation view, locate and expand the druid-mysql-client/src/main/java directory.
Right-click the Main.java file and select Run As > Java Application.
View the output results in the console window of Eclipse.
Sample project overview
Click druid-mysql-client to download the project code, which is a compressed file named druid-mysql-client.zip.
After decompressing it, you will get 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
File description:
src: The root directory for source code.main: The main code directory, containing the application's core logic.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, containing logic for creating tables, and inserting, deleting, updating, and querying data.resources: The resource file directory, containing configuration files.db.properties: The connection pool configuration file, containing relevant database connection parameters.pom.xml: The Maven project configuration file, used to manage project dependencies and build settings.
Introduction to pom.xml code
The pom.xml file is the configuration file for a Maven project, defining information such as 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 this pom.xml file mainly includes the following parts:
File declaration statement.
Declares that this file is an XML file, using XML version
1.0and character encodingUTF-8.Code:
<?xml version="1.0" encoding="UTF-8"?>Configure the POM namespace and POM model version.
- Use
xmlnsto specify the POM namespace ashttp://maven.apache.org/POM/4.0.0. - Use
xmlns:xsito specify the XML namespace ashttp://www.w3.org/2001/XMLSchema-instance. - Use
xsi:schemaLocationto specify the POM namespace,http://maven.apache.org/POM/4.0.0, and the location of its XSD file,http://maven.apache.org/xsd/maven-4.0.0.xsd. - Use the
<modelVersion>element to specify the POM model version used by this POM file as4.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>- Use
Configure basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asdruid-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>druid-mysql-client</artifactId> <version>1.0-SNAPSHOT</version>- Use
Configure the properties of the project's source files.
Specifies the Maven compiler plugin as
maven-compiler-pluginand sets both the source and target Java versions to 8. This means the project's source code is written using Java 8 features, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This configuration ensures 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.
Sample code:
<build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>8</source> <target>8</target> </configuration> </plugin> </plugins> </build>Configure the components on which the project depends.
Add the
mysql-connector-javadependency library to interact with the database:- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency name asmysql-connector-java. - Use
<version>to specify the dependency version as5.1.40.
Sample code:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.40</version> </dependency>- Use
Add the
druiddependency library:- Use
<groupId>to specify the dependency's organization ascom.alibaba. - Use
<artifactId>to specify the dependency name asdruid. - Use
<version>to specify the dependency version as1.2.8.
Sample code:
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>- Use
Introduction to db.properties
db.properties is the connection pool configuration file in this example, containing the configuration properties of the connection pool. These properties include the database URL, username, password, and other optional settings for the connection pool.
The code in this db.properties file mainly consists of the following parts:
Configure the database connection parameters.
- Specify the database driver class name as
com.mysql.jdbc.Driver. - Specify the database connection URL, including the host IP, port number, and the schema to access.
- Specify the database username.
- Specify the database password.
Sample code:
driverClassName=com.mysql.jdbc.Driver url=jdbc:oceanbase://$host:$port/$database_name?useSSL=false username=$user_name password=$passwordParameter description:
$host: Obtained from the-hparameter in the connection string. This is the connection address for OceanBase Cloud.$port: Obtained from the-Pparameter in the connection string. This is the connection port for OceanBase Cloud.$database_name: The name of the database to be accessed, which is obtained from the-Dparameter in the connection string.$user_name: The account name, which is obtained from the-uparameter in the connection string.$password: The account password, which is obtained from the-pparameter in the connection string.
- Specify the database driver class name as
Configure parameters for other connection pool settings.
- Specify the SQL statement
select 1to validate connections. - Set the initial number of connections in the pool to 3. This means that when the connection pool starts, it will create three initial connections.
- Set the maximum number of active connections in the pool to 30. This means the connection pool can have up to 30 connections at the same time.
- Set whether to log abandoned connections to
true. This means information about reclaimed abandoned connections will be output to the error logs. You can set this totruein a test environment andfalsein a production environment to avoid performance impact. - Set the minimum number of idle connections in the pool to 5. This means if the number of idle connections in the pool drops below 5, the pool will automatically create new connections.
- Set the maximum wait time for obtaining a connection to 1000 milliseconds. This means if all connections in the pool are occupied and the wait exceeds 1000 milliseconds, the connection acquisition operation will throw a timeout exception.
- Set the minimum idle connection hold time to 300000 milliseconds. This means if a connection remains idle for 300000 milliseconds (5 minutes), it will be reclaimed.
- Set whether to reclaim abandoned connections to
true. This means connections that exceed the time defined byremoveAbandonedTimeoutwill be reclaimed. - Set the timeout period for abandoned connections to 300 seconds. This means connections that remain unused for more than 300 seconds (5 minutes) will be reclaimed.
- Set the idle connection recycling thread's execution interval to 10000 milliseconds. This means the idle connection recycling thread will perform a recycling operation every 10000 milliseconds (10 seconds).
- Set whether to validate connection availability when obtaining a connection to
false. Setting this tofalsecan improve performance but may result in unavailable connections. - Set whether to validate connection availability when returning a connection to
false. Setting this tofalsecan improve performance but may result in unavailable returned connections. - Set whether to validate idle connections to
true. When set totrue, the connection pool will periodically executevalidationQueryto validate connection availability. - Set whether to enable persistent connection keep-alive to
false. Setting this tofalsemeans persistent connection keep-alive is not enabled. - Set the idle connection threshold to 60000 milliseconds. This means if a connection's idle time exceeds the set threshold of 60000 milliseconds (1 minute), the connection keep-alive mechanism will detect the connection to ensure its availability. If any operation occurs on the connection within the threshold time, the connection's idle time will be recalculated.
Sample code:
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- Specify the SQL statement
Note
The specific attribute (parameter) configuration depends on project requirements and database characteristics. It is recommended to adjust and configure according to your actual situation.
Common configuration parameters for Druid connection pools:
Parameter |
Description |
|---|---|
| url | Specify the URL for connecting to the database, including information such as the database type, hostname, port number, and database name. |
| username | Specifies the username required to connect to the database. |
| password | Specify the password required to connect to the database. |
| driverClassName | The database driver class. If omitted, Druid determines the database type (dbType) from url and selects the driver class automatically. Set driverClassName explicitly if the URL cannot be parsed or you need a nonstandard driver. |
| initialSize | Specifies the number of connections created when initializing the connection pool. When the application starts, the connection pool creates and adds the specified number of connections to itself. |
| maxActive | Specifies the maximum number of active connections in the connection pool. When the number of active connections in the connection pool reaches this maximum, subsequent connection requests will wait until a connection is released. |
| maxIdle | Specifies the maximum number of idle connections in the connection pool (this attribute is deprecated). When the number of idle connections in the connection pool reaches the maximum value, excess connections will be closed. |
| minIdle | Specifies the minimum number of idle connections in the connection pool. When the number of idle connections in the pool drops below this value, the pool creates new connections. |
| maxWait | Specifies the maximum wait time for obtaining a connection. If this period is exceeded, an exception is thrown. If set to a positive value, it indicates the number of milliseconds to wait; if exceeded, an exception is thrown. |
| poolPreparedStatements | Enables prepared statement caching (PSCache) when set to true. Monitor and configure memory usage carefully because OBProxy memory consumption may continue to grow. |
| validationQuery | Specifies the SQL query statement for connection validation. When a connection is retrieved from the connection pool, this query statement is executed to validate whether the connection is valid. |
| timeBetweenEvictionRunsMillis | Specifies the interval for detecting idle connections in the connection pool, in milliseconds. If a connection remains idle for more thantimeBetweenEvictionRunsMillis, the connection will be closed. |
| minEvictableIdleTimeMillis | Specifies the minimum idle time for a connection in the connection pool before it is recycled. The unit is milliseconds. If set to a negative value, the connection will not be recycled. |
| testWhileIdle | If true, runs validationQuery to validate connections when they are idle. |
| testOnBorrow | If true, runs validationQuery to validate connections when they are borrowed from the pool. |
| testOnReturn | If true, runs validationQuery to validate connections when they are returned to the pool. |
| filters | Configures a comma-separated list of filters, for example, filters=stat,wall,log4j. Filters are applied in the specified order:
|
Introduction to Main.java
The Main.java file is the main program of the sample application in this topic. Through this example, you can learn how to use data sources, connection objects, and various database operation methods to interact with a database.
The code in the Main.java file mainly consists of the following parts:
Import necessary classes and interfaces.
- Declare the package name as
com.example. - Import the Java
IOExceptionclass for handling input/output exceptions. - Import the Java
InputStreamclass for obtaining an input stream from a file or other source. - Import the Java
Connectioninterface, which represents a connection to a database. - Import the Java
ResultSetinterface, which represents a dataset of query results from a database. - Import the Java
SQLExceptionclass for handling SQL exceptions. - Import the Java
Statementinterface for executing SQL statements. - Import the Java
PreparedStatementinterface for precompiled SQL statements. - Import the Java
Propertiesclass for processing property files. - Import the Java
DataSourceinterface for managing database connections. - Import the Alibaba Druid connection pool's
DruidDataSourceFactoryclass for creating a Druid data source.
Code:
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;- Declare the package name as
Create a
Mainclass and define themainmethod.Define a
Mainclass and amainmethod. Themainmethod demonstrates how to use a connection pool to perform a series of operations on a database. The specific steps are as follows:Define a public class named
Mainas the program's entry point. The class name must match the file name.Define a public static method
mainas the program's entry point, which accepts command-line arguments.Use exception handling mechanisms to catch and handle potential exceptions.
Call the
loadPropertiesFilemethod to load the property file and return aPropertiesobject.Call the
createDataSource()method to create a data source object based on the configuration in the property file.Use a
try-with-resourcesstatement to obtain a database connection and automatically close it after the connection is no longer needed.- Call the
createTable()method to create a table. - Call the
insertData()method to insert data. - Call the
selectData()method to query data. - Call the
updateData()method to update data. - Call the
selectData()method again to query the updated data. - Call the
deleteData()method to delete data. - Call the
selectData()method again to query the data after deletion. - Call the
dropTable()method to drop the table.
- Call the
Code:
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 methods for retrieving and using configuration information from the property file. // Define a method for retrieving data source objects. // Define the method for creating a table. // Define the method for inserting data. // Define the method for updating data. // Define the method for deleting data. // Define the method for querying data. // Define a method for dropping a table. }Define methods for retrieving and using configuration information from the property file.
Define a private static method
loadPropertiesFile()to load the properties file and return aPropertiesobject. The specific steps are as follows:- Define a private static method
loadPropertiesFile()that returns aPropertiesobject and declares that it may throw anIOException. - Create a
Propertiesobject to store the key-value pairs from the properties file. - Use a
try-with-resourcesstatement to obtain an input streamisfor the properties filedb.propertiesthrough the class loader. - Use the
loadmethod to load the properties from the input stream into thepropertiesobject. - Return the loaded
propertiesobject.
Sample code:
private static Properties loadPropertiesFile() throws IOException { Properties properties = new Properties(); try (InputStream is = Main.class.getClassLoader().getResourceAsStream("db.properties")) { properties.load(is); } return properties; }- Define a private static method
Define a method to obtain a data source object.
Define a private static method
createDataSource()to create aDataSourceobject based on the configuration in the properties file, which is used to manage and obtain database connections. The specific steps are as follows:- Define a private static method
createDataSource()that takes aPropertiesobject as a parameter and declares that it may throw anException. - Call the
createDataSource()method of theDruidDataSourceFactoryclass, pass in thepropertiesobject, and return aDataSourceobject.
Sample code:
private static DataSource createDataSource(Properties properties) throws Exception { return DruidDataSourceFactory.createDataSource(properties); }- Define a private static method
Define a method to create a table.
Define a private static method
createTable()to create a table in the database. The specific steps are as follows:- Define a private static method
createTable()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use a
try-with-resourcesstatement to create aStatementobjectstmtby calling thecreateStatement()method on the connection objectconn. - Define a string variable
sqlto store the SQL statement for creating the table. - Use the
executeUpdate()method to execute the SQL statement and create the table. - Print a success message indicating the table was created.
Sample code:
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."); } }- Define a private static method
Define a method to insert data.
Define a private static method
insertData()to insert data into the database. The specific steps are as follows:Define a private static method
insertData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException.Define a string variable
insertDataSqlto store the SQL statement for inserting data.Define an integer variable
insertedRowswith an initial value of 0 to record the number of rows inserted.Use a
try-with-resourcesstatement to create aPreparedStatementobjectinsertDataStmtby calling theprepareStatement()method on the connection objectconnand passing the SQL statement for inserting data.Use a
forloop to iterate five times, representing the insertion of five rows of data.- Use the
setInt()method to set the value of the first parameter to the loop variablei. - Use the
setString()method to set the value of the second parameter to the stringtest_insertplus the value of the loop variablei. - Use the
executeUpdate()method to execute the SQL statement for inserting data, and increment the returned number of rows affected into theinsertedRowsvariable.
- Use the
Print a success message indicating that the data was inserted, along with the total number of rows inserted.
Return the total number of rows inserted.
Sample code:
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; }Define a method for updating data.
Define a private static method named
updateData()to update data in the database. The specific steps are as follows:- Define a private static method named
updateData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use a
try-with-resourcesstatement to create aPreparedStatementobject namedpstmtby calling theprepareStatement()method on the connection objectconnand passing the SQL statement for updating data. - Use the
setString()method to set the value of the first parameter to the stringtest_update. - Use the
setInt()method to set the value of the second parameter to the integer value 3. - Use the
executeUpdate()method to execute the SQL statement for updating data, and assign the returned number of rows affected to theupdatedRowsvariable. - Print a success message indicating that the data was updated, along with the total number of rows updated.
Sample code:
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); } }- Define a private static method named
Define a method for deleting data.
Define a private static method named
deleteData()to delete data from the database. The specific steps are as follows:- Define a private static method named
deleteData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use a
try-with-resourcesstatement to create aPreparedStatementobject namedpstmtby calling theprepareStatement()method on the connection objectconnand passing the SQL statement for deleting data. - Use the
setInt()method to set the value of the first parameter to the integer value 3. - Use the
executeUpdate()method to execute the SQL statement for deleting data, and assign the returned number of rows affected to thedeletedRowsvariable. - Print a success message indicating that the data was deleted, along with the total number of rows deleted.
Sample code:
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); } }- Define a private static method named
Define a method for querying data.
Define a private static method named
selectData()to query data from the database. The specific steps are as follows:Define a private static method named
selectData()that takes aConnectionobject as a parameter and declares that it may throw anSQLException.Use a
try-with-resourcesstatement to create aStatementobject namedstmtby calling thecreateStatement()method on the connection objectconn.Define a string variable named
sqlto store the SQL statement for querying data.Use the
executeQuery()method to execute the SQL statement for querying data, and assign the returned result set to theresultSetvariable.Use a
whileloop to iterate through each row of data in the result set.- Use the
getInt()method to get the integer value of theidfield in the current row and assign it to theidvariable. - Use the
getString()method to get the string value of thenamefield in the current row and assign it to thenamevariable. - Print the values of the
idandnamefields in the current row.
- Use the
Sample code:
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); } } }Define a method for dropping a table.
Define a private static method named
dropTable()to drop a table from the database. The specific steps are as follows:- Define a private static method named
dropTable(). This method takes aConnectionobject as a parameter and declares that it may throw anSQLException. - Use a
try-with-resourcesstatement to create aStatementobject namedstmtby calling thecreateStatement()method on the connection objectconn. - Define a string variable named
sqlto store the SQL statement for dropping the table. - Use the
executeUpdate()method to execute the SQL statement for dropping the table. - Print a success message indicating that the table was dropped.
Sample code:
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."); } }- Define a private static method named
Complete sample 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 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.