Connect to OceanBase Database by using a DBCP connection pool

This topic describes how to build an application by using a DBCP connection pool, MySQL Connector/J, and OceanBase Database to perform basic database operations, including creating tables, inserting, deleting, updating, and querying data.

Click to download the dbcp-mysql-client sample project Sample program for connecting to OceanBase Database by using a DBCP connection pool (MySQL mode)

Prerequisites

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

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

  The code examples in this topic are run in Eclipse IDE for Java Developers 2022-03. You can also use your preferred tool to run the code examples.

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 select File > Open Projects from File System from the menu bar.

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

   Note

   When you import a Maven project into Eclipse, it automatically detects the pom.xml file in the project, downloads the required dependency libraries based on the described dependencies, and adds them to the project.

   

3. View the project status.

   

Step 2: Obtain the URL of OceanBase Database

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

   Here is an example:

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

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

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

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

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. If you connect through OceanBase Database Proxy (ODP), use the IP address of the ODP. If you connect directly to an OBServer node, use the IP address of the OBServer node.

   • $port: the port for connecting to OceanBase Database. The default port for connecting through ODP is 2883, which can be customized during ODP deployment. The default port for direct connection is 2881, which can be customized during OceanBase Database deployment.

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

     Notice

     The user for connecting 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 mode.

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

   • $password: the account password.

   For more information about MySQL Connector/J connection properties, 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: Obtain the URL of OceanBase Database.

Here is an example:

• The IP address of the OBServer node is xxx.xxx.xxx.xxx.
• The access port is 2881.
• The name of the database to be accessed is test.
• The tenant connection account is test_user001@mysql001. mysql001 is a MySQL tenant created in OceanBase Database, and test_user001 is the username of the mysql001 tenant.
• The password is ******.

Sample code:

...
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 Explorer view, locate and expand the src/main/java directory.

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

   

3. View the project's log information and output results in the console window of Eclipse.

   

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

Click dbcp-mysql-client to download the project code, which is a compressed package 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

File description:

• src: the root directory of the source code.
• main: the main code directory, containing the core logic of the application.
• java: the directory for Java source code.
• com: the directory for Java packages.
• example: the directory for packages of the sample project.
• Main.java: the main class program file, containing logic for creating tables, inserting, deleting, updating, and querying data.
• resources: the directory for resource files, including configuration files.
• db.properties: the configuration file for the connection pool, containing parameters related to database connections.
• pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

Introduction to pom.xml

The pom.xml file is a configuration file for Maven projects, defining project dependencies, plugins, and build rules. Maven is a Java project management tool that can automatically download dependencies, compile, and package projects.

The pom.xml file in this topic includes the following main parts:

1. File declaration statements.

   This declares the file as an XML file using XML version 1.0 and character encoding UTF-8.

   Code:

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

2. Configure the namespace and POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0 and the location of the POM XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to set the POM model version to 4.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>
   

3. Configure basic information.

   1. Use <groupId> to set the project group to com.example.
   2. Use <artifactId> to set the project name to dbcp-mysql-client.
   3. Use <version> to set the project version to 1.0-SNAPSHOT.

   Code:

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

4. Configure project source file properties.

   Set the Maven compiler plugin to maven-compiler-plugin and set both the source code 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 that 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.

   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>
   

5. Configure the components that the project depends on.

   Use <dependency> to define dependencies:

   • MySQL JDBC dependency:

     1. Use <groupId> to set the dependency group to mysql.
     2. Use <artifactId> to set the dependency name to mysql-connector-java.
     3. Use <version> to set the dependency version to 5.1.47.

   • dbcp dependency:

     1. Use <groupId> to set the dependency group to org.apache.commons.
     2. Use <artifactId> to set the dependency name to commons-dbcp2.
     3. Use <version> to set the dependency version to 2.9.0.

   Code:

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

Introduction to the db.properties file

db.properties is the connection pool configuration file used in this example. It contains configuration properties for the connection pool, including the driver class name, database URL, username, password, connection pool size and limits, connection timeout, and options for handling abandoned connections.

The code in the db.properties file in this example mainly includes the following parts:

1. Configure the database connection parameters.

   1. Set the driver class name. Here, it is the class name of the MySQL JDBC driver: com.mysql.jdbc.Driver.
   2. Set the database connection URL, including the host IP address, port number, and the database to be accessed.
   3. Set the database username.
   4. Set the database password.
   5. Set the connection properties. useSSL=false indicates that SSL encryption is not used for connections. characterEncoding=UTF-8 indicates that data transmission uses UTF-8 encoding.

   Sample code:

   driverClassName=com.mysql.jdbc.Driver
   url=jdbc:mysql://$host:$port/$database_name
   username=$user_name
   password=$password
   connectionProperties=useSSL=false;characterEncoding=UTF-8
   

   Parameter description:

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

2. Configure other DBCP connection pool parameters.

   1. Set the initial size of the connection pool to 30, which is the number of connections to be created initially in the connection pool.

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

   3. Set the maximum number of idle connections 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 this value, the connection pool will create new connections.

   5. Set the maximum waiting time (in milliseconds) for obtaining a connection from the connection pool to 1000. If all connections in the connection pool are occupied and no available connections are available, the operation to obtain a connection will wait until an available connection is available or the maximum waiting time is exceeded.

   6. Set the timeout (in seconds) for abandoned connections before they are removed to 1.

      Note

      The default value of removeAbandonedTimeout is 300 seconds. In this example, it is set to 1 second for testing purposes. You can adjust this value as needed to meet the requirements of your application.

   7. Whether the connection pool recycles connections that are no longer used in the program:

      • Set whether to detect and remove abandoned connections during maintenance to true.
      • Set whether to detect and remove abandoned connections when borrowing connections from the connection pool to true.

   Sample code:

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

Basic data source configuration parameters of DBCP connection pool:

Main.java code introduction

The Main.java file is part of the sample program, demonstrating how to obtain a database connection through a DBCP connection pool and perform a series of database operations, including table creation, data insertion, data deletion, data update, data query, and printing the query results.

The Main.java file in this article mainly includes the following parts:

1. Import the required classes and interfaces.

   Import the required classes and interfaces, including those for file reading, database operations, and database connection pools. These classes and interfaces will be used in the subsequent code.

   1. Declare a package named com.example for storing the current Java class.
   2. Import the java.io.FileInputStream class for reading files.
   3. Import the java.sql.Connection interface, representing a connection to the database.
   4. Import the java.sql.PreparedStatement interface, representing a precompiled SQL statement.
   5. Import the java.sql.ResultSet interface, representing the result set of a database query.
   6. Import the java.sql.SQLException exception class, representing an SQL operation exception.
   7. Import the java.util.Properties class for loading configuration files.
   8. Import the org.apache.commons.dbcp2.BasicDataSource class, representing a database connection pool.
   9. Import the org.apache.commons.dbcp2.BasicDataSourceFactory class for creating a database connection pool.

   Code:

   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 the class name and method.

   1. Create the Main class and define a main method as the entry point of the program.
   2. In the main method, first call the createDataSource() method to create a connection pool object dataSource.
   3. Use the try-with-resources statement to automatically close resources when the connection pool object's lifecycle ends.
   4. In the try block, call the getConnection() method to obtain a database connection object conn from the connection pool.
   5. Sequentially call the createTable(), insertData(), deleteData(), updateData(), and queryData() methods to execute the corresponding database operations.
   6. In the event of an exception, print the exception information using the catch block.

   Code:

   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 a table
       // 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 convenient method for creating a database connection pool, initializing the connection pool object by reading parameters from the configuration file. The specific steps are as follows:

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

   Code:

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

   Provide a method for creating a specified table in the database. It accepts a connection object as a parameter and executes the create table SQL statement using a precompiled approach. The specific steps are as follows:

   1. Define a private static method createTable() that accepts a Connection object as a parameter. The method may throw an SQLException exception.
   2. Define a string variable createTableSql for storing the create table SQL statement. The SQL statement specifies the table name as test_tbl1 and defines two columns: one is an INT type column named id, and the other is a VARCHAR(32) type column named name.
   3. Use 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 for creating the table.

   Code:

       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. It accepts a connection object as a parameter and executes the insert data SQL statement using a precompiled approach. The specific steps are as follows:

   1. Define a private static method insertData() that accepts a Connection object as a parameter. The method may throw an SQLException exception.

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

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

   4. Use a for loop to insert data into the table:

      1. Loop 10 times, inserting one piece of data in each iteration.
      2. Use the setInt() method to set the value of the loop variable i as the first parameter value in the SQL statement.
      3. Use the setString() method to set the string test_insert + i as the second parameter value in the SQL statement.
      4. Call the executeUpdate() method to execute the SQL statement for inserting data into the database.

   Code:

       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 that deletes data from the database based on specific conditions. It accepts a connection object as a parameter and uses a precompiled approach to execute the SQL statement for data deletion. The steps are as follows:

   1. Define a private static method deleteData() that accepts a Connection object as a parameter and may throw an SQLException.
   2. Define a string variable deleteDataSql to store the SQL statement for data deletion.
   3. Use the conn.prepareStatement(deleteDataSql) method to create a PreparedStatement object deleteDataStmt for executing the precompiled SQL statement.
   4. Use the setInt() method to set the value of the parameter in the SQL statement to the number 5.
   5. Call the executeUpdate() method to execute the SQL statement and delete data that meets the specified conditions from the database.

   Code:

       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 that updates data in the database based on specific conditions. It accepts a connection object as a parameter and uses a precompiled approach to execute the SQL statement for data updates. The steps are as follows:

   1. Define a private static method updateData() that accepts a Connection object as a parameter and may throw an SQLException.
   2. Define a string variable updateDataSql to store the SQL statement for data updates.
   3. Use the conn.prepareStatement(updateDataSql) method to create a PreparedStatement object updateDataStmt for executing the precompiled SQL statement.
   4. Use the setString() method to set the value of the first parameter in the SQL statement to the string test_update.
   5. Use the setInt() method to set the value of the second parameter in the SQL statement to the number 5.
   6. Call the executeUpdate() method to execute the SQL statement and update data that meets the specified conditions in the database.

   Code:

       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 that queries data from the database and processes it. It accepts a connection object as a parameter and uses a precompiled approach to execute the SQL statement for data queries. The steps are as follows:

   1. Define a private static method queryData() that accepts a Connection object as a parameter and may throw an SQLException.

   2. Define a string variable queryDataSql to store the SQL statement for data queries.

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

   4. Execute the SQL query using the queryDataStmt.executeQuery() method and use the ResultSet object rs to receive the query results.

   5. Use a while loop to iterate through the query result set by calling the rs.next() method:

      1. Use the getInt() method to retrieve the integer value of the column named id in the result set and assign it to the variable id.
      2. Use the getString() method to retrieve the string value of the column named name in the result set and assign it to the variable name.
      3. Print the id and name of the query results.

   Code:

       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

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

See also

For more information about MySQL Connector/J, see Overview of MySQL Connector/J.