This topic describes how to build an application that uses the HikariCP connection pool, MySQL Connector/J, and OceanBase Database to perform basic database operations, including creating tables, and inserting, deleting, updating, and querying data.
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 hikaricp-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 you import 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 hikaricp-mysql-client project
Modify the database connection information in the hikaricp-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:
...
jdbcUrl=jdbc:mysql://t5******.********.oceanbase.cloud:3306/test?useSSL=false
username=test_user001
password=******
...
Step 3: Run the hikaricp-mysql-client project
In the project navigation view, locate and expand the src/main/java directory.
Right-click the Main.java file and select 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 [(none)]> SELECT * FROM test.test_hikaricp;Example output:
+------+-------------+ | id | name | +------+-------------+ | 1 | test_update | +------+-------------+ 1 row in set
Sample project overview
Click hikaricp-mysql-client to download the project code, which is a compressed file named hikaricp-mysql-client.zip.
After decompressing it, you will get a folder named hikaricp-mysql-client. The directory structure is as follows:
hikaricp-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
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 ashikaricp-mysql-client. - Use
<version>to specify the project version as1.0-SNAPSHOT.
Code:
<groupId>com.example</groupId> <artifactId>hikaricp-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
HikariCPdependency library to implement a high-performance JDBC connection pool:- Use
<groupId>to specify the dependency's organization ascom.zaxxer. - Use
<artifactId>to specify the dependency name asHikariCP. - Use
<version>to specify the dependency version as3.3.1.
Sample code:
<dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.3.1</version> </dependency>- Use
Add the
logback-classicdependency library for convenient logging and management:- Use
<groupId>to specify the dependency's organization asch.qos.logback. - Use
<artifactId>to specify the dependency name aslogback-classic. - Use
<version>to specify the dependency version as1.2.5.
Sample code:
<dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.5</version> </dependency>- Use
Introduction to the db.properties Code
db.properties is the connection pool configuration file used in this example, containing the configuration properties for the connection pool. These properties include the database URL, username, password, and other optional connection pool settings.
The code in this db.properties file mainly consists of the following parts:
Configure database connection parameters.
- Configure the database connection URL, including the host IP, port number, and the database to access.
- Configure the database username.
- Configure the database password.
Code:
jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false username=$user_name password=$passwordParameter description:
$host: Obtained from the-hparameter in the connection string, which is the connection address for OceanBase Cloud.$port: Obtained from the-Pparameter in the connection string, which is the connection port for OceanBase Cloud.$database_name: Obtained from the-Dparameter in the connection string, which is the name of the database to access.$user_name: Obtained from the-uparameter in the connection string, which is the account name.$password: Obtained from the-pparameter in the connection string, which is the account password.
Configure other connection pool parameters.
- Enable caching for precompiled SQL statements.
- Set the cache size for precompiled SQL statements to 250.
- Set the maximum lifetime of a connection to 1,800,000 milliseconds (30 minutes). Connections exceeding this time will be closed.
- Set the idle timeout for a connection to 600,000 milliseconds (10 minutes). Connections that exceed this idle time and are idle will be closed.
- Set the connection timeout to 30,000 milliseconds (30 seconds). If a connection is not obtained within this time, an exception will be thrown.
Code:
dataSource.cachePrepStmts=true dataSource.prepStmtCacheSize=250 dataSource.maxLifetime=1800000 dataSource.idleTimeout=600000 dataSource.connectionTimeout=30000
Notice
The specific property (parameter) configuration depends on project requirements and database characteristics. It is recommended that you adjust and configure them according to your actual situation. For more information about HikariCP connection pool parameters, see Configuration.
Common basic parameters of the HikariCP connection pool:
Category |
Parameter |
Default value |
Description |
|---|---|---|---|
| Required parameters | dataSourceClassName | N/A | The name of the DataSource class provided by the JDBC driver.
Notice
|
| jdbcUrl | N/A | The URL for connecting to the database via JDBC. | |
| username | N/A | The username used to connect to the database. | |
| password | N/A | The password used to connect to the database. | |
| Common optional parameters | autoCommit | true | Controls the default auto-commit behavior of connections returned by the connection pool. |
| connectionTimeout | 30000 | Controls the maximum wait time for a client to obtain a connection from the connection pool. Unit: milliseconds. Default value is 30000 (30 seconds). The minimum acceptable connection timeout is 250 milliseconds. | |
| idleTimeout | 600000 | Controls the maximum idle time for a connection in the pool. Unit: milliseconds. Default value is 600000 (10 minutes). This setting has the following limitations:
|
|
| maxLifetime | 1800000 | Controls the maximum lifetime of a connection in the connection pool. Used connections are not automatically reclaimed; they are removed from the pool only when the connection is closed. Unit: milliseconds. Default value is 1800000 (30 minutes). Setting maxLifetime to 0 means there is no maximum lifetime limit for connections in the pool, implying an infinite connection lifetime. |
|
| connectionTestQuery | N/A | The query executed by the connection pool to test connectivity with the database. It runs before obtaining a connection from the pool to verify that the database connection is still valid. | |
| minimumIdle | N/A | Controls the minimum number of idle connections maintained in the pool. If the number of idle connections falls below this value and the total number of connections in the pool is less than maximumPoolSize, HikariCP attempts to quickly and efficiently add additional connections. By default, the value of the minimumIdle attribute is the same as the maximumPoolSize attribute. |
|
| maximumPoolSize | 10 | Controls the maximum size allowed for the connection pool, including both idle and active connections. This value determines the maximum number of actual connections to the database backend. | |
| poolName | N/A | The user-defined name of the connection pool. This name is primarily used for identification and configuration management in logging and the JMX console. By default, a name is automatically generated. |
Introduction to Main.java code
The Main.java file is part of the sample program, demonstrating how to obtain a database connection through the HikariCP connection pool and perform a series of database operations, including creating a table, inserting data, deleting data, updating data, querying data, and printing the query results.
The code in this Main.java file mainly consists of the following parts:
Import the required classes and packages.
- Define the package name for the current Java file as
com.example, used to organize and manage Java classes. - Import the
java.sql.Connectioninterface, used to establish and manage connections to the database. - Import the
java.sql.PreparedStatementinterface, used to execute precompiled SQL statements. - Import the
java.sql.ResultSetinterface, used to process query result sets. - Import the
java.sql.SQLExceptionclass, used to handle SQL exceptions. - Import HikariCP's
HikariConfigclass, used to configure the HikariCP connection pool. - Import HikariCP's
HikariDataSourceclass, used to create and manage the HikariCP connection pool.
Code:
package com.example; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource;- Define the package name for the current Java file as
Define the class and methods.
Define a Main class, with its
mainmethod serving as the program's entry point. In themainmethod, read thedb.propertiesfile to configure the HikariCP connection pool and obtain a database connection. Then, call a series of methods in sequence to create a table, insert data, query data, update data, and delete data. If anSQLExceptionexception occurs during the operations, the stack trace of the exception information will be printed. The specific steps are as follows:- Define a public class named Main.
- Define the entry method
mainfor the Main class. - Create a HikariConfig object and configure it using the specified
db.propertiesfile. - Create a HikariDataSource object and obtain a database connection within a
try-with-resourcesblock. - Call a method to create a table, passing the obtained database connection object, to create the
test_hikaricptable. - Call a method to insert data, passing the obtained database connection object and data parameters, to insert two rows of data:
(1,'A1')and(2,'A2'). - Call a method to query data, passing the obtained database connection object, to check the data insertion.
- Call a method to update data, passing the obtained database connection object and update parameters, to update the value in the
namecolumn of the row withid1totest_update. - Call a method to query data, passing the obtained database connection object, to check the data update.
- Call a method to delete data, passing the obtained database connection object and deletion parameters, to delete the row with
id2. - Call a method to query data, passing the obtained database connection object, to check the data deletion.
- If an
SQLExceptionexception occurs in thetryblock, print the exception's stack trace information. - Define methods for creating a table, inserting data, querying data, updating data, and deleting data.
Code:
public class Main { public static void main(String[] args) { try { HikariConfig config = new HikariConfig("/db.properties"); try (HikariDataSource dataSource = new HikariDataSource(config); Connection conn = dataSource.getConnection()) { createTable(conn); insertData(conn, 1, "A1"); insertData(conn, 2, "A2"); selectData(conn); updateData(conn, "test_update", 1); selectData(conn); deleteData(conn, 2); selectData(conn); } } catch (SQLException e) { e.printStackTrace(); } } // Define the method for creating a table. // Define the method for inserting data. // Define the method for querying data. // Define the method for updating data. // Define the method for deleting data. }Define a method for creating a table.
Define a private static method
createTable, used to create a table namedtest_hikaricpin the database. This table contains anidcolumn and anamecolumn. The specific steps are as follows:- Define a private static method named
createTable. This method takes a Connection object as a parameter and declares that it may throw an SQLException. - Define an SQL statement string to create a table named
test_hikaricp. The table contains anidcolumn (data typeINT) and anamecolumn (data typeVARCHAR(50)). - Use the connection object
connto create a precompiled SQL statement objectpstmt, and use this object within atry-with-resourcesblock. - Execute the SQL statement to create the table named
test_hikaricp. - Output a message to the console indicating that the table was created successfully.
Sample code:
private static void createTable(Connection conn) throws SQLException { String sql = "CREATE TABLE IF NOT EXISTS test_hikaricp (id INT, name VARCHAR(50))"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.executeUpdate(); System.out.println("Table created successfully."); } }- Define a private static method named
Define a method for inserting data.
Define a private static method named
insertDatato insert data into thetest_hikaricptable in the database. The specific steps are as follows:- Define a private static method named
insertData. This method takes a Connection object, an integeridparameter, and a stringnameparameter, and declares that it may throw an SQLException. - Define an SQL statement string to insert data into the
test_hikaricptable, including theidandnamecolumns. - Use the connection object
connto create a precompiled SQL statement objectpstmt, and use this object within atry-with-resourcesblock. - Set the value of the first parameter
?in the SQL statement toid. - Set the value of the second parameter
?in the SQL statement toname. - Execute the SQL statement to insert data into the table.
- Output a message to the console indicating that the data was inserted successfully.
Sample code:
private static void insertData(Connection conn, int id, String name) throws SQLException { String sql = "INSERT INTO test_hikaricp (id, name) VALUES (?, ?)"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt(1, id); pstmt.setString(2, name); pstmt.executeUpdate(); System.out.println("Data inserted successfully."); } }- Define a private static method named
Define a method for querying data.
Define a private static method named
selectDatato query data from thetest_hikaricptable in the database. The specific steps are as follows:- Define a private static method named
selectData. This method takes a Connection object as a parameter and declares that it may throw an SQLException. - Define an SQL statement string to query all data from the
test_hikaricptable. - Use the connection object
connto create a precompiled SQL statement objectpstmt, and use this object within atry-with-resourcesblock. Also, execute the SQL query by calling theexecuteQuery()method and return the query result set objectrs. - Output a message to the console indicating that user data is being printed.
- Traverse the query result set. Use the
next()method to check if there is another row of data in the result set. If so, enter the loop. - Retrieve the value of the
idcolumn from the result set and assign it to the variableid. - Retrieve the value of the
namecolumn from the result set and assign it to the variablename. - Output the
idandnamevalues of each row of data to the console. - Output an empty line to the console.
Sample code:
private static void selectData(Connection conn) throws SQLException { String sql = "SELECT * FROM test_hikaricp"; try (PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery()) { System.out.println("User Data:"); while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println("ID: " + id + ", Name: " + name); } System.out.println(); } }- Define a private static method named
Define a method for updating data.
Define a private static method named
updateDatato update data in thetest_hikaricptable in the database. The specific steps are as follows:- Define a private static method named
updateData. This method accepts a Connection object, a string parameter namedname, and an integer parameter namedid. It declares that it may throw aSQLException. - Define an SQL statement string to update data in the table named
test_hikaricp. The update sets the value of thenamecolumn to the specifiednameif the value of theidcolumn equals the specifiedid. - Use the connection object
connto create a precompiled SQL statement objectpstmt, and use this object within atry-with-resourcesblock. - Set the value of the first parameter
?in the SQL statement toname. - Set the value of the second parameter
?in the SQL statement toid. - Execute the SQL statement to update the data in the table.
- Print a message to the console indicating that the data was updated successfully.
Sample code:
private static void updateData(Connection conn, String name, int id) throws SQLException { String sql = "UPDATE test_hikaricp SET name = ? WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, name); pstmt.setInt(2, id); pstmt.executeUpdate(); System.out.println("Data updated successfully."); } }- Define a private static method named
Define a method for deleting data.
Define a private static method named
deleteDatato delete data from thetest_hikaricptable that meets specific conditions. The steps are as follows:- Define a private static method named
deleteData. This method accepts a Connection object and an integer parameter namedid. It declares that it may throw aSQLException. - Define an SQL statement string to delete data from the table named
test_hikaricpwhere the conditionid = ?is met. - Use the connection object
connto create a precompiled SQL statement objectpstmt, and use this object within atry-with-resourcesblock. - Set the value of the first parameter
?in the SQL statement toid. - Execute the SQL statement to delete data that meets the condition from the table.
- Print a message to the console indicating that the data was deleted successfully.
Sample code:
private static void deleteData(Connection conn, int id) throws SQLException { String sql = "DELETE FROM test_hikaricp WHERE id = ?"; try (PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt(1, id); pstmt.executeUpdate(); System.out.println("Data deleted 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.oceanbase</groupId>
<artifactId>hikaricp-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.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.5</version>
</dependency>
</dependencies>
</project>
jdbcUrl=jdbc:mysql://$host:$port/$database_name?useSSL=false
username=$user_name
password=$password
dataSource.cachePrepStmts=true
dataSource.prepStmtCacheSize=250
dataSource.maxLifetime=1800000
dataSource.idleTimeout=600000
dataSource.connectionTimeout=30000
package com.example;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class Main {
public static void main(String[] args) {
try {
HikariConfig config = new HikariConfig("/db.properties");
try (HikariDataSource dataSource = new HikariDataSource(config);
Connection conn = dataSource.getConnection()) {
createTable(conn);
insertData(conn, 1, "A1");
insertData(conn, 2, "A2");
selectData(conn);
updateData(conn, "test_update", 1);
selectData(conn);
deleteData(conn, 2);
selectData(conn);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void createTable(Connection conn) throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS test_hikaricp (id INT, name VARCHAR(50))";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.executeUpdate();
System.out.println("Table created successfully.");
}
}
private static void insertData(Connection conn, int id, String name) throws SQLException {
String sql = "INSERT INTO test_hikaricp (id, name) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.executeUpdate();
System.out.println("Data inserted successfully.");
}
}
private static void selectData(Connection conn) throws SQLException {
String sql = "SELECT * FROM test_hikaricp";
try (PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
System.out.println("User Data:");
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.println("ID: " + id + ", Name: " + name);
}
System.out.println();
}
}
private static void updateData(Connection conn, String name, int id) throws SQLException {
String sql = "UPDATE test_hikaricp SET name = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setInt(2, id);
pstmt.executeUpdate();
System.out.println("Data updated successfully.");
}
}
private static void deleteData(Connection conn, int id) throws SQLException {
String sql = "DELETE FROM test_hikaricp WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, id);
pstmt.executeUpdate();
System.out.println("Data deleted successfully.");
}
}
}
References
- For more information about MySQL Connector/J, see Overview of MySQL Connector/J.
- For more information about the HikariCP connection pool, see HikariCP.