Connect to OceanBase Database by using a DBCP connection pool

This topic introduces how to build an application by using a Database Connection Pool (DBCP) connection pool, MySQL Connector/J, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data deletion, data updating, and data query.

Download the dbcp-mysql-client sample project

Prerequisites

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

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

• You have installed Eclipse.

  Note

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

Procedure

1. Import the dbcp-mysql-client project into Eclipse.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the dbcp-mysql-client project.
4. Run the dbcp-mysql-client project.

Step 1: Import the dbcp-mysql-client project into Eclipse

1. Start Eclipse and choose File > Open Projects from File System.

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

   Note

   When you import a Maven project using Eclipse, it will automatically detect the pom.xml file in the project, download the required dependency libraries based on the described dependencies in the file, and add them to the project.

   

3. View the project.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

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

   For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

2. Fill in the URL below based on the OceanBase Database connection string.

   jdbc:mysql://$host:$port/$database_name?user=$user_name&password=$password
   

   where

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

   • $port specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

   • $database_name specifies the name of the database to be accessed.

     Notice

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

   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

   • $password specifies the password of the account.

   For more information about the connection properties of MySQL Connector/J, see Configuration Properties.

   Here is an example:

   jdbc:mysql://xxx.xxx.xxx.xxx:2881/test?user=test_user001@mysql001&password=******
   

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

The sample code is as follows:

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

Step 4: Run the dbcp-mysql-client project

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

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

   

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

   

4. 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 introduction

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

After decompressing it, you will find 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

Here is a breakdown of the files and directories:

• src: the root directory for storing the source code.
• main: the directory for storing the main code, including the major logic of the application.
• java: the directory for storing the Java source code.
• com: the directory for storing the Java package.
• example: the directory for storing the packages of the sample project.
• Main.java: a sample file of the main class that contains logic for table creation, data insertion, data deletion, data modification, and data query.
• resources: the directory for storing resource files, including configuration files.
• db.properties: the configuration file of the connection pool, which contains relevant database connection parameters.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

Code in the pom.xml file

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

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

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

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

   The sample code is as follows:

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

3. Configure basic information.

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

   The sample code is as follows:

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

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

   Specify the Maven compiler plug-in as maven-compiler-plugin, and set both the source code and target Java versions to 8. This means that the project's source code is written using Java 8, and the compiled bytecode will also be compatible with the Java 8 runtime environment. This setup ensures that the project can correctly handle the syntax and features of Java 8 during compilation and runtime.

   Note

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

   The sample code is as follows:

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

5. Configure the components on which the project depends.

   Define the components on which the project depends by using <dependency>.

   • MySQL JDBC dependency:

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

   • dbcp dependency:

     1. <groupId>: the ID of the group to which the dependency belongs, which is set to org.apache.commons.
     2. <artifactId>: the name of the dependency, which is set to commons-dbcp2.
     3. <version>: the version of the dependency, which is set to 2.9.0.

   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:

1. Configure database connection parameters.

   1. Specify the class name of the driver, which is set to the class name com.mysql.jdbc.Driver of MySQL Connector/J.
   2. Specify the URL for connecting to the database, including the host IP address, port number, and database to be accessed.
   3. Specify the username for connecting to the database.
   4. Specify the password for connecting to the database.
   5. Set the connection properties. Note that useSSL=false specifies not to use Secure Sockets Layer (SSL) for connection encryption, and characterEncoding=UTF-8 specifies 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-8
   

   where

   • $host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • $port specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • $database_name specifies the name of the database to be accessed.
   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • $password specifies the password of the account.

2. Configure other parameters of the DBCP connection pool.

   1. Set the initial size of the connection pool to 30, which means that 30 connections are created when the connection pool is started.

   2. Set the maximum number of connections allowed in the connection pool to 30.

   3. Set the maximum number of idle connections allowed in the connection pool to 10.

   4. 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.

   5. 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.

   6. Set the timeout value for deleting an abandoned connection to 1, in seconds.

      Note

      The default value of removeAbandonedTimeout is 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.

   7. Specify whether to recycle unused connections from the application as follows:

      • Set the value to true for the parameter that specifies whether to detect and delete abandoned connections during maintenance.
      • Set the value to true for the parameter that specifies whether to detect and delete abandoned connections when a connection is borrowed from the connection pool.

   The sample code is as follows:

   initialSize=30
   maxTotal=30
   maxIdle=10
   minIdle=5
   maxWaitMillis=1000
   removeAbandonedTimeout=1
   
   removeAbandonedOnMaintenance=true
   removeAbandonedOnBorrow=true
   

The following table describes the basic data source parameters of the DBCP 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:

1. 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.

   1. Declare a package named com.example for storing the current Java classes.
   2. Import the java.io.FileInputStream class for reading files.
   3. Import the java.sql.Connection interface for representing connections with the database.
   4. Import the java.sql.PreparedStatement interface for representing a precompiled SQL statement.
   5. Import the java.sql.ResultSet interface for representing result sets of database queries.
   6. Import the java.sql.SQLException class for indicating SQL operation exceptions.
   7. Import the java.util.Properties class for loading configuration files.
   8. Import the org.apache.commons.dbcp2.BasicDataSource class for representing database connection pools.
   9. Import the org.apache.commons.dbcp2.BasicDataSourceFactory class 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;
   

2. Define class names and methods.

   1. Create a Main class and define a main method as the entry to the application.
   2. In the main method, call the createDataSource() method to create a connection pool object dataSource.
   3. Use the try-with-resources block to automatically close resources when the lifetime of a connection pool object ends.
   4. In the try code block, call the getConnection() method to obtain a database connection object conn from the connection pool.
   5. Call the createTable(), insertData(), deleteData(), updateData(), and queryData() methods in sequence to execute corresponding database operations.
   6. When an exception occurs, use the catch block to record the exception information.

   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 a method for creating tables.
       // Define a method for inserting data.
       // Define a method for deleting data.
       // Define a method for updating data.
       // Define a method for querying data.
   }
   

3. Create a connection pool.

   Provide a method for creating a database connection pool and initialize the pool object by reading parameters from the configuration file. The specific steps are as follows:

   1. Define a private static method createDataSource() whose return type is BasicDataSource. The method can throw an Exception.
   2. Create a Properties object named props for storing the configuration information of database connections.
   3. Create a FileInputStream object named is for reading the db.properties file located in the src/main/resources directory.
   4. Call the load() method to load the key-value pairs in the db.properties file to the props object.
   5. Call the BasicDataSourceFactory.createDataSource(props) method to create and return a BasicDataSource object by using the props object as a parameter.

   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);
       }
   

4. Define a method for creating tables.

   Provide a method for creating a specified table in the database. The method receives a Connection object as a parameter and executes a precompiled SQL statement to create the table. The specific steps are as follows:

   1. Define a private static method createTable() that receives a Connection object as a parameter. The method can throw an SQLException.
   2. Define a string variable createTableSql for storing the table creation statement. The statement specifies the table name test_tbl1 and defines an id column of the INT data type and a name column of the VARCHAR(32) data type.
   3. Call the conn.prepareStatement(createTableSql) method to create a PreparedStatement object createTableStmt for executing precompiled SQL statements.
   4. Call the execute() method to execute the SQL statement to create 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();
           }
       }
   

5. Define a method for inserting data.

   Provide a method for inserting specified data into a table in the database. The method receives a Connection object as a parameter and executes a precompiled SQL statement to insert data. The specific steps are as follows:

   1. Define a private static method insertData() that receives a Connection object as a parameter. The method can throw an SQLException.

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

   3. Call the conn.prepareStatement(insertDataSql) method to create a PreparedStatement object insertDataStmt for executing precompiled SQL statements.

   4. Use the FOR loop to insert data into the table.

      1. Perform 10 rounds of iterations and insert a data record in each iteration.
      2. Call the setInt() method to set the value of the loop variable i to the value of the first parameter in the SQL statement.
      3. Call the setString() method to set the value of the test_insert + i string to the value of the second parameter in the SQL statement.
      4. Call the executeUpdate() method to execute the SQL statement to insert data into the database.

   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();
               }
           }
       }
   

6. Define a method for deleting data.

   Provide a method for deleting data that meets the specified condition from the database. The method receives a Connection object as a parameter and executes a precompiled SQL statement to delete data. The specific steps are as follows:

   1. Define a private static method deleteData() that receives a Connection object as a parameter, and declare that the method may throw an SQLException.
   2. Define a string variable deleteDataSql for storing the data deletion statement.
   3. Call the conn.prepareStatement(deleteDataSql) method to create a PreparedStatement object deleteDataStmt for executing precompiled SQL statements.
   4. Call the setInt() method to set 5 as a parameter value in the SQL statement.
   5. Call the executeUpdate() method to execute the SQL statement to delete data that meets the specified condition 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();
           }
       }
   

7. Define a method for updating data.

   Provide a method for updating data that meets the specified condition in the database. The method receives a Connection object as a parameter and executes a precompiled SQL statement to update data. The specific steps are as follows:

   1. Define a private static method updateData() that receives a Connection object as a parameter. The method can throw an SQLException.
   2. Define a string variable updateDataSql for storing the data update statement.
   3. Call the conn.prepareStatement(updateDataSql) method to create a PreparedStatement object updateDataStmt for executing precompiled SQL statements.
   4. Call the setString() method to set the value of the test_update string to the value of the first parameter in the SQL statement.
   5. Call the setInt() method to set 5 as the value of the second parameter in the SQL statement.
   6. Call the executeUpdate() method to execute the SQL statement to update data that meets the specified condition 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();
           }
       }
   

8. Define a method for querying data.

   Provide a method for querying data in the database and processing the queried data. The method receives a Connection object as a parameter and executes a precompiled SQL statement to query data. The specific steps are as follows:

   1. Define a private static method queryData() that receives a Connection object as a parameter. The method can throw an SQLException.

   2. Define a string variable queryDataSql for storing the data query statement.

   3. Call the conn.prepareStatement(queryDataSql) method to create a PreparedStatement object queryDataStmt for executing precompiled SQL statements.

   4. Call the queryDataStmt.executeQuery() method to execute an SQL query and use the ResultSet object named rs to receive the query result.

   5. Call the rs.next() method to traverse the query result set by using the WHILE loop.

      1. Call the getInt() method to obtain the integer values of the id column in the result set and assign the obtained values to the id variable.
      2. Call the getString() method to obtain the string values of the name column in the result set and assign the obtained values to the name variable.
      3. Return the id and name values in the query result.

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