This topic describes how to build an application using a Druid connection pool, OceanBase Connector/J, and OceanBase Cloud 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 tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
You have obtained the connection string of the target tenant in Oracle 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-oceanbase-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-oceanbase-client project
Modify the database connection information in the druid-oceanbase-client/src/main/resources/db.properties file based on the connection string obtained in the prerequisites.
Sample code:
...
url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_schema001
username=test_user
password=******
...
- The connection address is
t5******.********.oceanbase.cloud. - The access port is 1521.
- The name of the database to access is
test_schema001. - The tenant connection account is
test_user. - The password is
******.
Step 3: Run the druid-oceanbase-client project
In the project navigation view, locate and expand the druid-oceanbase-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-oceanbase-client to download the project code, which is a compressed file named druid-oceanbase-client.zip.
After decompressing it, you will get a folder named druid-oceanbase-client. The directory structure is as follows:
druid-oceanbase-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-oceanbase-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>druid-oceanbase-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
oceanbase-clientdependency library to interact with the database:- Use
<groupId>to specify the dependency's organization ascom.oceanbase. - Use
<artifactId>to specify the dependency name asoceanbase-client. - Use
<version>to specify the dependency version as2.4.2.
Note
The following code defines that the project depends on OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC driver
Sample code:
<dependency> <groupId>com.oceanbase</groupId> <artifactId>oceanbase-client</artifactId> <version>2.4.2</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 the db.properties file mainly consists of the following parts:
Configure database connection parameters.
- Specify the database driver class name as
com.oceanbase.jdbc.Driver. - Specify the database connection URL, including the host IP, port number, and schema to access.
- Specify the database username.
- Specify the database password.
Sample code:
driverClassName=com.oceanbase.jdbc.Driver url=jdbc:oceanbase://$host:$port/$schema_name username=$user_name password=$passwordParameter description:
$host: The connection address of OceanBase Cloud, which is obtained from the-hparameter in the connection string.$port: The connection port of OceanBase Cloud, which is obtained from the-Pparameter in the connection string.$schema_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 password of the account, which is obtained from the-pparameter in the connection string.
- Specify the database driver class name as
Configure parameters for other connection pools.
- Specify the SQL statement
select 1 from dualto validate connections. - Set the initial number of connections in the connection pool to 3. This means that when the connection pool starts, it creates three initial connections.
- Set the maximum number of active connections in the connection pool to 30. This means that at most 30 connections can exist in the connection pool simultaneously.
- Set
trueto specify whether to log abandoned connections. When set totrue, information is output to the error log when abandoned connections are reclaimed. 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 connection pool to 5. This means that if the number of idle connections in the connection pool drops below 5, the connection pool automatically creates new connections.
- Set the maximum wait time for obtaining a connection to 1000 milliseconds. If all connections in the connection pool are occupied and the wait time exceeds 1000 milliseconds, the operation to obtain a connection throws a timeout exception.
- Set the minimum idle time before a connection is reclaimed to 300000 milliseconds (5 minutes). If a connection remains idle for more than 300000 milliseconds, it is reclaimed.
- Set
trueto specify whether to reclaim abandoned connections. Abandoned connections are reclaimed after they exceed the time defined byremoveAbandonedTimeout. - Set the timeout period for abandoned connections to 300 seconds. Connections that remain unused for more than 300 seconds (5 minutes) are reclaimed.
- Set the interval at which the idle connection recycling thread runs to 10000 milliseconds. The idle connection recycling thread runs every 10000 milliseconds (10 seconds) to reclaim idle connections.
- Set
falseto specify whether to validate the availability of a connection when obtaining it. Setting this tofalseimproves performance but may result in the obtained connection being unavailable. - Set
falseto specify whether to validate the availability of a connection when returning it. Setting this tofalseimproves performance but may result in the returned connection being unavailable. - Set
trueto specify whether to validate idle connections. When set totrue, the connection pool periodically executes thevalidationQueryto validate the availability of connections. - Set
falseto specify whether to enable the keep-alive feature for persistent connections. Setting this tofalsedisables the keep-alive feature. - Set the idle time threshold for a connection to 60000 milliseconds. If a connection remains idle for more than the specified threshold of 60000 milliseconds (1 minute), the connection keep-alive mechanism detects the connection to ensure its availability. If any operation is performed on the connection within the threshold period, the connection's idle time is recalculated.
Sample code:
validationQuery=select 1 from dual 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
Notice
The specific attribute (parameter) configuration depends on project requirements and database characteristics. We recommend that you adjust and configure them based on your actual situation.
Common configuration parameters for the Druid connection pool:
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 code
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 includes the following parts:
Import necessary classes and interfaces.
- Declare the package name of the current code as
com.example. - Import the Java
IOExceptionclass to handle input/output exceptions. - Import the Java
InputStreamclass to obtain an input stream from a file or other source. - Import the Java
Connectioninterface to represent a connection to a database. - Import the Java
ResultSetinterface to represent a dataset of database query results. - Import the Java
SQLExceptionclass to handle SQL exceptions. - Import the Java
Statementinterface to execute SQL statements. - Import the Java
PreparedStatementinterface for precompiled SQL statements. - Import the Java
Propertiesclass to process property files. - Import the Java
DataSourceinterface to manage database connections. - Import the Alibaba Druid connection pool's
DruidDataSourceFactoryclass to create 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 of the current code 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.
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 the 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 the propertiesproperties, and return aDataSourceobject.
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.
Code:
private static void createTable(Connection conn) throws SQLException { try (Statement stmt = conn.createStatement()) { String sql = "CREATE TABLE test_druid (id NUMBER, name VARCHAR2(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 pieces 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 accumulate 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 aPreparedStatementobjectpstmtby 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 aPreparedStatementobjectpstmtby 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 aStatementobjectstmtby calling thecreateStatement()method on the connection objectconn.Define a string variable
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
dropTable()to drop a table from the database. The specific steps are as follows:- Define a private static method
dropTable()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 dropping the table. - Use the
executeUpdate()method to execute the SQL statement for dropping the table. - Print a message indicating that the table was dropped successfully.
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
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-oceanbase-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>com.oceanbase</groupId>
<artifactId>oceanbase-client</artifactId>
<version>2.4.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
</dependencies>
</project>
# Database Configuration
driverClassName=com.oceanbase.jdbc.Driver
url=jdbc:oceanbase://$host:$port/$schema_name
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 from dual
#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 NUMBER, name VARCHAR2(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 OceanBase Connector/J, see OceanBase JDBC Driver.