This topic introduces how to build an application by using a Database Connection Pool (DBCP) connection pool, MySQL Connector/J, and OceanBase AI 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.
Prerequisites
You have installed OceanBase AI Database and created a user 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
Note
The steps outlined in this topic are for the Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Import the
dbcp-mysql-clientproject into Eclipse. - Obtain the URL of OceanBase AI Database.
- Modify the database connection information in the
dbcp-mysql-clientproject. - Run the
dbcp-mysql-clientproject.
Step 1: Import the dbcp-mysql-client project into Eclipse
Start Eclipse and choose File > Open Projects from File System.
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.xmlfile in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.
View the project.

Step 2: Obtain the URL of OceanBase AI Database
Contact the deployment personnel or administrator of OceanBase AI Database to obtain the connection string.
Here is an example:
obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -DtestFor more information about the connection string, see Connect to OceanBase AI Database by using OBClient.
Fill in the URL below based on the OceanBase AI Database connection string.
jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$passwordParameter description:
$hostspecifies the IP address for connecting to OceanBase AI 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.$portspecifies the port for connecting to OceanBase AI Database. For connection through ODP, the default value is2883, which can be customized when ODP is deployed. For direct connection, the default value is2881, which can be customized when OceanBase AI Database is deployed.$database_namespecifies the name of the database to be accessed.Notice
The user used to connect to the tenant must have the
CREATE,INSERT,DELETE,UPDATE, andSELECTprivileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.$user_namespecifies the tenant account. For connection through ODP, the tenant account can be in theusername@tenant_name#cluster_nameorcluster_name:tenant_name:usernameformat. For direct connection, the tenant account is in theusername@tenant_nameformat.$passwordspecifies 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=******
Step 3: Modify the database connection information in the dbcp-mysql-client project
Modify the database connection information in the dbcp-mysql-client/src/main/resources/db.properties file 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, wheremysql001is a user tenant created in OceanBase AI Database, andtest_user001is the username of themysql001tenant. - The password is
******.
The sample code is as follows:
...
url=jdbc:mysql://xxx.xxx.xxx.xxx:2881/test
username=test_user001@mysql001
password=******
...
Step 4: Run the dbcp-mysql-client project
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and choose Run As > Java Application.

In the console window of Eclipse, view the project's log information and output results.

You can also execute the following SQL statement in OceanBase Client (OBClient) to view the results.
obclient [test]> SELECT * FROM test_tbl1;The return result is as follows:
+------+--------------+ | id | name | +------+--------------+ | 5 | test_update | | 6 | test_insert6 | | 7 | test_insert7 | | 8 | test_insert8 | | 9 | test_insert9 | +------+--------------+ 5 rows in set
Project code
Click dbcp-mysql-client to download the project code, which is a compressed file named dbcp-mysql-client.zip.
After decompressing it, you will get a folder named dbcp-mysql-client. The directory structure is as follows:
dbcp-mysql-client
├── src
│ └── main
│ ├── java
│ │ └── com
│ │ └── example
│ │ └── Main.java
│ └── resources
│ └── db.properties
└── pom.xml
The files and directories are described as follows:
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.
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:
Declare the file.
Declare the file to be an XML file that uses XML standard
1.0and character encodingUTF-8.The sample code is as follows:
<?xml version="1.0" encoding="UTF-8"?>Configure the namespaces and the POM model version.
xmlns: the default XML namespace, which is set tohttp://maven.apache.org/POM/4.0.0.xmlns:xsi: the namespace for XML elements prefixed withxsi, which is set tohttp://www.w3.org/2001/XMLSchema-instance.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.<modelVersion>: the POM model version used by the POM file, which is set to4.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>Configure basic information.
- Use
<groupId>to specify the project's organization ascom.example. - Use
<artifactId>to specify the project name asdbcp-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
The sample code is as follows:
<groupId>com.example</groupId> <artifactId>dbcp-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.
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>Configure the components on which the project depends.
Define dependencies using
<dependency>:MySQL JDBC dependency:
- Use
<groupId>to specify the dependency's organization asmysql. - Use
<artifactId>to specify the dependency's name asmysql-connector-java. - Use
<version>to specify the dependency's version as5.1.47.
- Use
dbcp dependency:
- Use
<groupId>to specify the dependency's organization asorg.apache.commons. - Use
<artifactId>to specify the dependency's name ascommons-dbcp2. - Use
<version>to specify the dependency's version as2.9.0.
- Use
The sample code is as follows:
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.9.0</version> </dependency> </dependencies>
Code in the db.properties file
db.properties is a sample configuration file of the connection pool. The configuration file contains the driver class name, database URL, username, password, connection pool size and limitations, connection timeout values, and options for handling abandoned connections.
To configure the db.properties file, perform the following steps:
Configure database connection parameters.
- Specify the class name of the driver, which is set to the class name
com.mysql.jdbc.Driverof MySQL Connector/J. - Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
- Specify the username for connecting to the database.
- Specify the password for connecting to the database.
- Set the connection properties. Note that
useSSL=falsespecifies not to use Secure Sockets Layer (SSL) for connection encryption, andcharacterEncoding=UTF-8specifies to use UTF-8 for data transmission.
The sample code is as follows:
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://$host:$port/$database_name username=$user_name password=$password connectionProperties=useSSL=false;characterEncoding=UTF-8Parameter description:
$hostspecifies the IP address for connecting to OceanBase AI 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.$portspecifies the port for connecting to OceanBase AI Database. For connection through ODP, the default value is2883, which can be customized when ODP is deployed. For direct connection, the default value is2881, which can be customized when OceanBase AI Database is deployed.$database_namespecifies the name of the database to be accessed.$user_namespecifies the tenant account. For connection through ODP, the tenant account can be in theusername@tenant_name#cluster_nameorcluster_name:tenant_name:usernameformat. For direct connection, the tenant account is in theusername@tenant_nameformat.$passwordspecifies the password of the account.
- Specify the class name of the driver, which is set to the class name
Configure other parameters of the DBCP connection pool.
Set the initial size of the connection pool to
30, which means that 30 connections are created when the connection pool is started.Set the maximum number of connections allowed in the connection pool to
30.Set the maximum number of idle connections allowed in the connection pool to
10.Set the minimum number of idle connections in the connection pool to
5. If the number of idle connections is less than the specified value, new connections are created in the connection pool.Set the maximum waiting time for requesting a connection from the connection pool to
1000 ms. When the application requests a connection from the connection pool, if all connections are occupied, the application has to wait until a connection is available or the maximum waiting time elapses.Set the timeout value for deleting an abandoned connection to
1, in seconds.Note
The default value of
removeAbandonedTimeoutis 300 seconds, but in this example, it is set as 1 second for testing purposes. You can adjust this value as needed to meet the requirements of your application.Specify whether to recycle unused connections from the application as follows:
- Set the value to
truefor the parameter that specifies whether to detect and delete abandoned connections during maintenance. - Set the value to
truefor the parameter that specifies whether to detect and delete abandoned connections when a connection is borrowed from the connection pool.
- Set the value to
The sample code is as follows:
initialSize=30 maxTotal=30 maxIdle=10 minIdle=5 maxWaitMillis=1000 removeAbandonedTimeout=1 removeAbandonedOnMaintenance=true removeAbandonedOnBorrow=true
Notice
The actual parameter configurations depend on the project requirements and database characteristics. We recommend that you adjust and configure the parameters based on the actual situation. For more information about parameters of the DBCP connection pool, see BasicDataSource Configuration Parameters.
The following table describes the basic data source parameters of the DBCP connection pool.
Category |
Parameter |
Default value |
Description |
|---|---|---|---|
| Required parameters | driverClass | N/A | The class name of the database driver. |
| url | N/A | The URL for connecting to the database. | |
| username | N/A | The username for connecting to the database. | |
| password | N/A | The password for connecting to the database. | |
| Recommended parameters | initialSize | 0 | The initial size of the connection pool, namely, the number of initial connections created when the connection is started. If you specify a value greater than 0, the specified number of connections are created during the initialization of the connection pool. Creating connections in advance can help reduce the latency when the client requests a connection for the first time. |
| maxTotal | 8 | The maximum number of connections allowed in the connection pool. A negative value indicates no limit. | |
| maxIdle | 8 | The maximum number of idle connections allowed in the connection pool, without extra connections released. A negative value indicates no limit. | |
| minIdle | 0 | The minimum number of idle connections allowed in the connection pool, without extra connections released. A negative value indicates no limit. | |
| maxWaitMillis | indefinitely | The maximum waiting time for requesting a connection from the connection pool, in ms. The value -1 indicates that the waiting time is unlimited. If you specify a positive value, when all connections in the connection pool are occupied, an exception is thrown after the specified waiting time elapses. |
|
| validationQuery | N/A | The SQL query statement for verifying connections. The value must be an SQL SELECT statement that returns at least one row. If this parameter is not specified, the isValid() method is called to verify connections. |
|
| testOnBorrow | true | Specifies whether to verify a connection object borrowed from the connection pool. If the connection object fails the verification, it is deleted from the connection pool, and an attempt is made to borrow another connection object. | |
| testWhileIdle | false | Specifies whether to verify idle connections in the connection pool. If you set the value to true, the connection pool periodically verifies idle connections. If an object fails the verification, it is deleted from the connection pool. |
|
| Optional parameters | connectionProperties | N/A | Additional connection properties that exist as key-value pairs. These properties are passed to the underlying JDBC driver when a database connection is requested. The string format is propertyName=property;.
NoticeThe |
|
false | Specify whether to remove abandoned connections.
true, the connection pool can automatically detect and delete abandoned connections. A connection is considered abandoned when it has not been used for a long period of time, which may be because it is not correctly closed by the application. Deleting abandoned connections can release database resources and improve the performance and efficiency of the connection pool. |
Code in the Main.java file
The Main.java file is a part of the sample application, demonstrating how to obtain a database connection through the DBCP connection pool and perform a series of database operations. These operations include creating a table, inserting data, deleting data, updating data, querying data, and printing the query results.
To configure the Main.java file, perform the following steps:
Import the required classes and interfaces.
Import classes and interfaces for file reading, database operations, and the database connection pool. These classes and interfaces will be used in subsequent code.
- Declare a package named
com.examplefor storing the current Java classes. - Import the
java.io.FileInputStreamclass for reading files. - Import the
java.sql.Connectioninterface for representing connections with the database. - Import the
java.sql.PreparedStatementinterface for representing a precompiled SQL statement. - Import the
java.sql.ResultSetinterface for representing result sets of database queries. - Import the
java.sql.SQLExceptionclass for indicating SQL operation exceptions. - Import the
java.util.Propertiesclass for loading configuration files. - Import the
org.apache.commons.dbcp2.BasicDataSourceclass for representing database connection pools. - Import the
org.apache.commons.dbcp2.BasicDataSourceFactoryclass for creating database connection pools.
The sample code is as follows:
package com.example; import java.io.FileInputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbcp2.BasicDataSourceFactory;- Declare a package named
Define the class name and methods.
- Create the Main class and define a
mainmethod as the program's entry point. - In the
mainmethod, first call thecreateDataSource()method to create a connection pool objectdataSource. - Use a
try-with-resourcesstatement to automatically close resources when the lifecycle of the connection pool object ends. - In the
tryblock, call thegetConnection()method to obtain a database connection objectconnfrom the connection pool. - Call the
createTable(),insertData(),deleteData(),updateData(), andqueryData()methods in sequence to perform the corresponding database operations. - If an exception occurs, print the exception information in the
catchblock.
The sample code is as follows:
public class Main { public static void main(String[] args) { try (BasicDataSource dataSource = createDataSource()) { try (Connection conn = dataSource.getConnection()) { createTable(conn); insertData(conn); deleteData(conn); updateData(conn); queryData(conn); } } catch (Exception e) { e.printStackTrace(); } } // Create a connection pool // Define the method for creating a table. // Define the method for inserting data. // Define a method for deleting data. // Define a method for updating data. // Define methods for querying data }- Create the Main class and define a
Create a connection pool.
Provide a convenient method for creating a database connection pool by initializing it with parameters read from a configuration file. The specific steps are as follows:
- Define a private static method
createDataSource()with a return type ofBasicDataSource. The method may throw anException. - Create a
Propertiesobjectpropsto store the database connection configuration information. - Create a
FileInputStreamobjectisto read thedb.propertiesfile located in thesrc/main/resourcesdirectory. - Use the
load()method to load the key-value pairs from thedb.propertiesfile into thepropsobject. - Call the
BasicDataSourceFactory.createDataSource(props)method, using thepropsobject as a parameter, to create and return aBasicDataSourceobject.
The sample code is as follows:
private static BasicDataSource createDataSource() throws Exception { Properties props = new Properties(); FileInputStream is = new FileInputStream("src/main/resources/db.properties"); props.load(is); return BasicDataSourceFactory.createDataSource(props); }- Define a private static method
Define a method for creating a table.
Provide a method to create a specified table in the database. It accepts a connection object as a parameter and executes the table creation SQL statement using prepared compilation. The specific steps are as follows:
- Define a private static method
createTable()that accepts aConnectionobject as a parameter. The method may throw anSQLExceptionexception. - Define a string variable
createTableSqlto store the SQL statement for creating the table. The SQL statement specifies the table name astest_tbl1and defines two columns: one namedidof typeINT, and another namednameof typeVARCHAR(32). - Use the
conn.prepareStatement(createTableSql)method to create aPreparedStatementobject namedcreateTableStmt, which is used to execute the precompiled SQL statement. - Call the
execute()method to execute the SQL statement for creating the table.
The sample code is as follows:
private static void createTable(Connection conn) throws SQLException { String createTableSql = "CREATE TABLE test_tbl1 (id INT, name VARCHAR(32))"; try (PreparedStatement createTableStmt = conn.prepareStatement(createTableSql)) { createTableStmt.execute(); } }- Define a private static method
Define a method for inserting data.
Provide a method to insert specified data into a table in the database. It accepts a connection object as a parameter and executes the data insertion SQL statement using prepared compilation. The specific steps are as follows:
Define a private static method
insertData()that accepts aConnectionobject as a parameter. The method may throw anSQLExceptionexception.Define a string variable
insertDataSqlto store the SQL statement for inserting data.Use the
conn.prepareStatement(insertDataSql)method to create aPreparedStatementobject namedinsertDataStmt, which is used to execute the precompiled SQL statement.Use a
forloop to insert data into the table:- Loop
10times, inserting one piece of data in each iteration. - Use the
setInt()method to set the value of the loop variableias the value for the first parameter in the SQL statement. - Use the
setString()method to set the stringtest_insert + ias the value for the second parameter in the SQL statement. - Call the
executeUpdate()method to execute the SQL statement for inserting data into the database.
- Loop
The sample code is as follows:
private static void insertData(Connection conn) throws SQLException { String insertDataSql = "INSERT INTO test_tbl1 (id, name) VALUES (?, ?)"; try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) { for (int i = 0; i < 10; i++) { insertDataStmt.setInt(1, i); insertDataStmt.setString(2, "test_insert" + i); insertDataStmt.executeUpdate(); } } }Define a method for deleting data.
Provide a method to delete data that meets specific conditions from the database. It accepts a connection object as a parameter and executes the data deletion SQL statement using prepared compilation. The specific steps are as follows:
- Define a private static method
deleteData()that accepts aConnectionobject as a parameter. The method may throw anSQLExceptionexception. - Define a string variable
deleteDataSqlto store the SQL statement for deleting data. - Use the
conn.prepareStatement(deleteDataSql)method to create aPreparedStatementobject nameddeleteDataStmt, which is used to execute the precompiled SQL statement. - Use the
setInt()method to set the number 5 as the parameter value in the SQL statement. - Call the
executeUpdate()method to execute the SQL statement for deleting data that meets the conditions from the database.
The sample code is as follows:
private static void deleteData(Connection conn) throws SQLException { String deleteDataSql = "DELETE FROM test_tbl1 WHERE id < ?"; try (PreparedStatement deleteDataStmt = conn.prepareStatement(deleteDataSql)) { deleteDataStmt.setInt(1, 5); deleteDataStmt.executeUpdate(); } }- Define a private static method
Define a method for updating data.
Provide a method to update data that meets specific conditions in the database. It accepts a connection object as a parameter and executes the data update SQL statement using prepared compilation. The specific steps are as follows:
- Define a private static method named
updateData()that accepts aConnectionobject as a parameter. This method may throw anSQLException. - Define a string variable named
updateDataSqlto store the SQL statement for updating data. - Use the
conn.prepareStatement(updateDataSql)method to create aPreparedStatementobject namedupdateDataStmt. This object is used to execute precompiled SQL statements. - Use the
setString()method to set the stringtest_updateas the value for the first parameter in the SQL statement. - Use the
setInt()method to set the number5as the value for the second parameter in the SQL statement. - Call the
executeUpdate()method to execute the SQL statement, which updates data that meets the conditions in the database.
The sample code is as follows:
private static void updateData(Connection conn) throws SQLException { String updateDataSql = "UPDATE test_tbl1 SET name = ? WHERE id = ?"; try (PreparedStatement updateDataStmt = conn.prepareStatement(updateDataSql)) { updateDataStmt.setString(1, "test_update"); updateDataStmt.setInt(2, 5); updateDataStmt.executeUpdate(); } }- Define a private static method named
Define a method for querying data.
Provide a method for querying data from the database and processing it. It accepts a connection object as a parameter and uses prepared statements to execute the SQL query. The specific steps are as follows:
Define a private static method named
queryData()that accepts aConnectionobject as a parameter. This method may throw anSQLException.Define a string variable named
queryDataSqlto store the SQL statement for querying data.Use the
conn.prepareStatement(queryDataSql)method to create aPreparedStatementobject namedqueryDataStmt. This object is used to execute precompiled SQL statements.Execute the SQL query using the
queryDataStmt.executeQuery()method, and use theResultSetobjectrsto receive the query results.Use a
whileloop to traverse the query result set by callingrs.next():- Use the
getInt()method to retrieve the integer value of the column namedidfrom the result set and assign it to the variableid. - Use the
getString()method to retrieve the string value of the column namednamefrom the result set and assign it to the variablename. - Print the
idandnamefrom the query results.
- Use the
The sample code is as follows:
private static void queryData(Connection conn) throws SQLException { String queryDataSql = "SELECT * FROM test_tbl1"; try (PreparedStatement queryDataStmt = conn.prepareStatement(queryDataSql)) { try (ResultSet rs = queryDataStmt.executeQuery()) { while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println("id: " + id + ", name: " + name); } } } }
Complete code examples
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>dbcp-mysql-client</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.9.0</version>
</dependency>
</dependencies>
</project>
# Database Connect Information
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://$host:$port/$database_name
username=$user_name
password=$password
connectionProperties=useSSL=false;characterEncoding=UTF-8
# ConnectionPool Parameters
initialSize=30
maxTotal=30
maxIdle=10
minIdle=5
maxWaitMillis=1000
removeAbandonedTimeout=1
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
package com.example;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
public class Main {
public static void main(String[] args) {
try (BasicDataSource dataSource = createDataSource()) {
try (Connection conn = dataSource.getConnection()) {
createTable(conn);
insertData(conn);
deleteData(conn);
updateData(conn);
queryData(conn);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// Create ConnectionPool
private static BasicDataSource createDataSource() throws Exception {
Properties props = new Properties();
FileInputStream is = new FileInputStream("src/main/resources/db.properties");
props.load(is);
return BasicDataSourceFactory.createDataSource(props);
}
// Create table
private static void createTable(Connection conn) throws SQLException {
String createTableSql = "CREATE TABLE test_tbl1 (id INT, name VARCHAR(32))";
try (PreparedStatement createTableStmt = conn.prepareStatement(createTableSql)) {
createTableStmt.execute();
}
}
// Insert data
private static void insertData(Connection conn) throws SQLException {
String insertDataSql = "INSERT INTO test_tbl1 (id, name) VALUES (?, ?)";
try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
for (int i = 0; i < 10; i++) {
insertDataStmt.setInt(1, i);
insertDataStmt.setString(2, "test_insert" + i);
insertDataStmt.executeUpdate();
}
}
}
// Delete data
private static void deleteData(Connection conn) throws SQLException {
String deleteDataSql = "DELETE FROM test_tbl1 WHERE id < ?";
try (PreparedStatement deleteDataStmt = conn.prepareStatement(deleteDataSql)) {
deleteDataStmt.setInt(1, 5);
deleteDataStmt.executeUpdate();
}
}
// Update data
private static void updateData(Connection conn) throws SQLException {
String updateDataSql = "UPDATE test_tbl1 SET name = ? WHERE id = ?";
try (PreparedStatement updateDataStmt = conn.prepareStatement(updateDataSql)) {
updateDataStmt.setString(1, "test_update");
updateDataStmt.setInt(2, 5);
updateDataStmt.executeUpdate();
}
}
// Query data
private static void queryData(Connection conn) throws SQLException {
String queryDataSql = "SELECT * FROM test_tbl1";
try (PreparedStatement queryDataStmt = conn.prepareStatement(queryDataSql)) {
try (ResultSet rs = queryDataStmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println("id: " + id + ", name: " + name);
}
}
}
}
}
References
For more information about MySQL Connector/J, see Overview of MySQL Connector/J.
Download the dbcp-mysql-client sample project