Connect to OceanBase Cloud by using a Druid connection pool

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

Download the druid-oceanbase-client sample project Connect to OceanBase Cloud by using a Druid connection pool (Oracle compatible mode)

Prerequisites

• You have registered an Alibaba Cloud account and created an instance and an Oracle-compatible tenant. For more information, see Create an instance and Create a tenant.

• You have obtained the connection string of the target Oracle-compatible tenant. For more information, see Obtain the connection string.

• 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 other tools that you prefer to run the code examples.

Procedure

Step 1: Import the druid-oceanbase-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 select the project directory and then click Finish.

   Note

   When you import a Maven project into 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: Modify the database connection information in the druid-oceanbase-client project

Modify the database connection information in the druid-oceanbase-client/src/main/resources/db.properties file based on the connection string obtained from the prerequisites.

Sample code:

...
url=jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/test_schema001
username=test_user
password=******
...

• The connection address is t5******.********.oceanbase.cloud.
• The access port is 1521.
• The name of the database to be accessed is test_schema001.
• The tenant account is test_user.
• The password is ******.

Step 3: Run the druid-oceanbase-client project

1. In the Project Explorer view, locate and expand the druid-oceanbase-client/src/main/java directory.

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

   

3. View the output results in the Eclipse console window.

   

Project code

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

After decompressing the file, you will find a folder named druid-oceanbase-client. The directory structure is as follows:

druid-oceanbase-client
├── src
│   └── main
│       ├── java
│       │   └── com
│       │       └── example
│       │           └── Main.java
│       └── resources
│           └── db.properties
└── pom.xml

File description:

• src: the root directory 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 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 relevant database connection parameters.
• pom.xml: the configuration file for the Maven project, used to manage project dependencies and build settings.

Introduction to the pom.xml file

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

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

1. File declaration statements.

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

   Sample code:

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

2. Configure the namespaces and POM model version.

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

   Sample 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 specify the project's organization as com.example.
   2. Use <artifactId> to specify the project name as druid-oceanbase-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample code:

       <groupId>com.example</groupId>
       <artifactId>druid-oceanbase-client</artifactId>
       <version>1.0-SNAPSHOT</version>
   

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

   Specify the Maven compiler plugin as maven-compiler-plugin and set both the source and target Java versions to 8. This means that the project's source code uses 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.

   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>
   

5. Configure the components that the project depends on.

   1. Add the oceanbase-client dependency library for interacting with the database:

      1. Use <groupId> to specify the dependency's organization as com.oceanbase.
      2. Use <artifactId> to specify the dependency name as oceanbase-client.
      3. Use <version> to specify the dependency version as 2.4.2.

      Note

      This section defines the project's dependency as OceanBase Connector/J V2.4.2. For information about other versions, see OceanBase JDBC driver

      Sample code:

              <dependency>
                  <groupId>com.oceanbase</groupId>
                  <artifactId>oceanbase-client</artifactId>
                  <version>2.4.2</version>
              </dependency>
      

   2. Add the druid dependency library:

      1. Use <groupId> to specify the dependency's organization as com.alibaba.
      2. Use <artifactId> to specify the dependency name as druid.
      3. Use <version> to specify the dependency version as 1.2.8.

      Sample code:

              <dependency>
                  <groupId>com.alibaba</groupId>
                  <artifactId>druid</artifactId>
                  <version>1.2.8</version>
              </dependency>
      

Introduction to db.properties

db.properties is the connection pool configuration file used in this example. It contains the configuration parameters for the connection pool, including the database URL, username, password, and other optional settings.

The db.properties file in this example primarily includes the following sections:

1. Configure the database connection parameters.

   1. Specify the class name of the database driver program as com.oceanbase.jdbc.Driver.
   2. Specify the database connection URL, including the host IP, port number, and the schema to be accessed.
   3. Specify the username for the database.
   4. Specify the password for the database.

   Sample code:

   driverClassName=com.oceanbase.jdbc.Driver
   url=jdbc:oceanbase://$host:$port/$schema_name
   username=$user_name
   password=$password
   

   Parameter description:

   • $host: The connection address of the OceanBase Cloud database, obtained from the -h parameter in the connection string.
   • $port: The connection port of the OceanBase Cloud database, obtained from the -P parameter in the connection string.
   • $schema_name: The name of the database to be accessed, obtained from the -D parameter in the connection string.
   • $user_name: The account name, obtained from the -u parameter in the connection string.
   • $password: The account password, obtained from the -p parameter in the connection string.

2. Configure other connection pool parameters.

   1. Specify the SQL statement for validating connections as select 1 from dual.
   2. Specify the initial number of connections in the connection pool as 3. This means that 3 initial connections will be created when the connection pool is started.
   3. Specify the maximum number of active connections in the connection pool as 30. This means that the connection pool can have a maximum of 30 active connections at the same time.
   4. Specify whether to print logs for abandoned connections as true. This means that when abandoned connections are recycled, information will be output to the error log. In a test environment, this can be set to true, while in a production environment, it should be set to false to avoid performance issues.
   5. Specify the minimum number of idle connections in the connection pool as 5. This means that when the number of idle connections in the connection pool is less than 5, the connection pool will automatically create new connections.
   6. Specify the maximum wait time for obtaining a connection as 1000 milliseconds. This means that if all connections in the connection pool are occupied and the wait time exceeds 1000 milliseconds, an exception will be thrown when attempting to obtain a connection.
   7. Specify the minimum idle time for a connection as 300000 milliseconds. This means that if a connection is idle for 300000 milliseconds (5 minutes) and has not been used, it will be recycled.
   8. Specify whether to recycle abandoned connections as true. This means that when a connection exceeds the time defined by removeAbandonedTimeout, it will be recycled.
   9. Specify the timeout time for abandoned connections as 300 seconds. This means that connections that have not been used for more than 300 seconds (5 minutes) will be recycled.
   10. Specify the interval time for the idle connection recycling thread as 10000 milliseconds. This means that the idle connection recycling thread will execute the idle connection recycling operation every 10000 milliseconds (10 seconds).
   11. Specify whether to validate the availability of a connection when obtaining it as false. Setting this to false can improve performance, but may result in obtaining an unavailable connection.
   12. Specify whether to validate the availability of a connection when returning it as false. Setting this to false can improve performance, but may result in returning an unavailable connection.
   13. Specify whether to validate a connection when it is idle as true. When set to true, the connection pool will periodically execute validationQuery to validate the availability of the connection.
   14. Specify whether to enable the keep-alive feature for long connections as false. Setting this to false means that the keep-alive feature for long connections is disabled.
   15. Specify the idle time threshold for a connection as 60000 milliseconds. This means that if the idle time of a connection exceeds the threshold of 60000 milliseconds (1 minute), the keep-alive mechanism will detect the connection to ensure its availability. If there is any operation on the connection within the threshold time, the idle time will be recalculated.

   Sample code:

   validationQuery=select 1 from dual
   initialSize=3
   maxActive=30
   logAbandoned=true
   minIdle=5
   maxWait=1000
   minEvictableIdleTimeMillis=300000
   removeAbandoned=true
   removeAbandonedTimeout=300
   timeBetweenEvictionRunsMillis=10000
   testOnBorrow=false
   testOnReturn=false
   testWhileIdle=true
   keepAlive=false
   keepAliveBetweenTimeMillis=60000
   

Common configuration parameters for the Druid connection pool:

Main.java code introduction

The Main.java file is the main program of the sample program in this topic. This sample program shows how to interact with a database by using a data source, a connection object, and various database operation methods.

The Main.java file in this topic contains the following parts:

1. Import the required classes and interfaces.

   1. Declare the package name of the current code as com.example.
   2. Import the IOException class of Java, which is used to handle input and output exceptions.
   3. Import the InputStream class of Java, which is used to obtain an input stream from a file or other source.
   4. Import the Connection interface of Java, which is used to represent a connection to a database.
   5. Import the ResultSet interface of Java, which is used to represent a dataset of database query results.
   6. Import the SQLException class of Java, which is used to handle SQL exceptions.
   7. Import the Statement interface of Java, which is used to execute SQL statements.
   8. Import the PreparedStatement interface of Java, which is used to execute precompiled SQL statements.
   9. Import the Properties class of Java, which is used to handle property files.
   10. Import the DataSource interface of Java, which is used to manage database connections.
   11. Import the DruidDataSourceFactory class of Alibaba Druid connection pool, which is used to create a Druid data source.

   Sample code:

   package com.example;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.PreparedStatement;
   import java.util.Properties;
   import javax.sql.DataSource;
   import com.alibaba.druid.pool.DruidDataSourceFactory;
   

2. Create a Main class and define the main method.

   Define a Main class and a main method. The main method is used to demonstrate how to use a connection pool to perform a series of operations on a database. The steps are as follows:

   1. Define a public class named Main as the entry point of the program. The class name must be consistent with the file name.

   2. Define a public static method main as the entry point of the program, which receives command-line parameters.

   3. Use the exception handling mechanism to capture and handle exceptions that may occur.

   4. Call the loadPropertiesFile method to load a property file and return a Properties object.

   5. Call the createDataSource() method to create a data source object based on the configuration in the property file.

   6. Use the try-with-resources statement to obtain a database connection and automatically close the connection after it is used.

      1. Call the createTable() method to create a table.
      2. Call the insertData() method to insert data.
      3. Call the selectData() method to query data.
      4. Call the updateData() method to update data.
      5. Call the selectData() method again to query the updated data.
      6. Call the deleteData() method to delete data.
      7. Call the selectData() method again to query the data after deletion.
      8. Call the dropTable() method to delete the table.

   Sample code:

   public class Main {
   
       public static void main(String[] args) {
           try {
               Properties properties = loadPropertiesFile();
               DataSource dataSource = createDataSource(properties);
               try (Connection conn = dataSource.getConnection()) {
                   // Create table
                   createTable(conn);
                   // Insert data
                   insertData(conn);
                   // Query data
                   selectData(conn);
   
                   // Update data
                   updateData(conn);
                   // Query the updated data
                   selectData(conn);
   
                   // Delete data
                   deleteData(conn);
                   // Query the data after deletion
                   selectData(conn);
   
                   // Drop table
                   dropTable(conn);
               }
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       // Define a method for obtaining and using configuration information from the property file
       // Define a method for obtaining a data source object
       // Define a method for creating a table
       // Define a method for inserting data
       // Define a method for updating data
       // Define a method for deleting data
       // Define a method for querying data
       // Define a method for deleting a table
   }
   

3. Define a method for obtaining and using configuration information from the property file.

   Define a private static method loadPropertiesFile() that is used to load a property file and return a Properties object. The steps are as follows:

   1. Define a private static method loadPropertiesFile() that returns a Properties object and declares that it may throw an IOException exception.
   2. Create a Properties object to store key-value pairs in the property file.
   3. Use the try-with-resources statement to obtain an input stream is of the property file db.properties by using the class loader.
   4. Use the load method to load the properties in the input stream to the properties object.
   5. Return the loaded properties object.

   Sample code:

       private static Properties loadPropertiesFile() throws IOException {
           Properties properties = new Properties();
           try (InputStream is = Main.class.getClassLoader().getResourceAsStream("db.properties")) {
               properties.load(is);
           }
           return properties;
       }
   

4. Define a method for obtaining a data source object.

   Define a private static method createDataSource() that is used to create a DataSource object based on the configuration in the property file. The DataSource object is used to manage and obtain database connections. The steps are as follows:

   1. Define a private static method createDataSource() that receives a Properties object as a parameter and declares that it may throw an Exception exception.
   2. Call the createDataSource() method of the DruidDataSourceFactory class and pass the properties parameter to return a DataSource object.

   Sample code:

       private static DataSource createDataSource(Properties properties) throws Exception {
           return DruidDataSourceFactory.createDataSource(properties);
       }
   

5. Define a method for creating a table.

   Define a private static method createTable() that is used to create a data table in a database. The steps are as follows:

   1. Define a private static method createTable() that takes a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the connection object conn.
   3. Define a string variable sql to store the SQL statement for creating the table.
   4. Use the executeUpdate() method to execute the SQL statement and create the data table.
   5. Print a message indicating that the table was created successfully.

   Sample code:

       private static void createTable(Connection conn) throws SQLException {
           try (Statement stmt = conn.createStatement()) {
               String sql = "CREATE TABLE test_druid (id NUMBER, name VARCHAR2(20))";
               stmt.executeUpdate(sql);
               System.out.println("Table created successfully.");
           }
       }
   

6. Define a method for inserting data.

   Define a private static method insertData() for inserting data into the database. The steps are as follows:

   1. Define a private static method insertData() that takes a Connection object as a parameter and declares that it may throw an SQLException.

   2. Define a string variable insertDataSql to store the SQL statement for inserting data.

   3. Define an integer variable insertedRows initialized to 0 to record the number of rows inserted.

   4. Use the try-with-resources statement to create a PreparedStatement object insertDataStmt by calling the prepareStatement() method of the connection object conn with the SQL statement for inserting data.

   5. Use a for loop to iterate 5 times, representing the insertion of 5 data records.

      1. Use the setInt() method to set the value of the first parameter to the loop variable i.
      2. Use the setString() method to set the value of the second parameter to the string test_insert concatenated with the value of the loop variable i.
      3. Use the executeUpdate() method to execute the SQL statement for inserting data and accumulate the number of affected rows to the insertedRows variable.

   6. Print a message indicating that the data was inserted successfully, along with the total number of rows inserted.

   7. Return the total number of rows inserted.

   Sample code:

       private static int insertData(Connection conn) throws SQLException {
           String insertDataSql = "INSERT INTO test_druid (id, name) VALUES (?, ?)";
           int insertedRows = 0;
           try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
               for (int i = 1; i < 6; i++) {
                   insertDataStmt.setInt(1, i);
                   insertDataStmt.setString(2, "test_insert" + i);
                   insertedRows += insertDataStmt.executeUpdate();
               }
               System.out.println("Data inserted successfully. Inserted rows: " + insertedRows);
           }
           return insertedRows;
       }
   

7. Define a method for updating data.

   Define a private static method updateData() for updating data in the database. The steps are as follows:

   1. Define a private static method updateData() that takes a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the try-with-resources statement to create a PreparedStatement object pstmt by calling the prepareStatement() method of the connection object conn with the SQL statement for updating data.
   3. Use the setString() method to set the value of the first parameter to the string test_update.
   4. Use the setInt() method to set the value of the second parameter to the integer value 3.
   5. Use the executeUpdate() method to execute the SQL statement for updating data and assign the number of affected rows to the updatedRows variable.
   6. Print a message indicating that the data was updated successfully, along with the total number of rows updated.

   Sample code:

       private static void updateData(Connection conn) throws SQLException {
           try (PreparedStatement pstmt = conn.prepareStatement("UPDATE test_druid SET name = ? WHERE id = ?")) {
               pstmt.setString(1, "test_update");
               pstmt.setInt(2, 3);
               int updatedRows = pstmt.executeUpdate();
               System.out.println("Data updated successfully. Updated rows: " + updatedRows);
           }
       }
   

8. Define a method for deleting data.

   Define a private static method deleteData() for deleting data from the database. The steps are as follows:

   1. Define a private static method deleteData() that takes a Connection object as a parameter and declares that it may throw an SQLException.
   2. Use the try-with-resources statement to create a PreparedStatement object pstmt by calling the prepareStatement() method of the connection object conn with the SQL statement for deleting data.
   3. Use the setInt() method to set the value of the first parameter to the integer value 3.
   4. Use the executeUpdate() method to execute the SQL statement for deleting data and assign the number of affected rows to the deletedRows variable.
   5. Print a message indicating that the data was deleted successfully, along with the total number of rows deleted.

   Sample code:

       private static void deleteData(Connection conn) throws SQLException {
           try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM test_druid WHERE id < ?")) {
               pstmt.setInt(1, 3);
               int deletedRows = pstmt.executeUpdate();
               System.out.println("Data deleted successfully. Deleted rows: " + deletedRows);
           }
       }
   

9. Define a method for querying data.

   Define a private static method selectData() for querying data from the database. The steps are as follows:

   1. Define a private static method selectData() that takes a Connection object as a parameter and declares that it may throw an SQLException.

   2. Use the try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the connection object conn.

   3. Define a string variable sql to store the SQL statement for querying data.

   4. Use the executeQuery() method to execute the SQL statement for querying data and assign the result set to the resultSet variable.

   5. Use a while loop to traverse each row in the result set.

      1. Use the getInt() method to retrieve the integer value of the id field in the current row and assign it to the id variable.
      2. Use the getString() method to retrieve the string value of the name field in the current row and assign it to the name variable.
      3. Print the values of the id and name fields in the current row.

   Sample code:

       private static void selectData(Connection conn) throws SQLException {
           try (Statement stmt = conn.createStatement()) {
               String sql = "SELECT * FROM test_druid";
               ResultSet resultSet = stmt.executeQuery(sql);
               while (resultSet.next()) {
                   int id = resultSet.getInt("id");
                   String name = resultSet.getString("name");
                   System.out.println("id: " + id + ", name: " + name);
               }
           }
       }
   

10. Define a method for dropping the table.

    Define a private static method dropTable() for dropping the table in the database. The steps are as follows:

    1. Define a private static method dropTable() that takes a Connection object as a parameter and declares that it may throw an SQLException.
    2. Use the try-with-resources statement to create a Statement object stmt by calling the createStatement() method of the connection object conn.
    3. Define a string variable sql to store the SQL statement for dropping the table.
    4. Use the executeUpdate() method to execute the SQL statement for dropping the table.
    5. Print a message indicating that the table was dropped successfully.

    Sample code:

        private static void dropTable(Connection conn) throws SQLException {
            try (Statement stmt = conn.createStatement()) {
                String sql = "DROP TABLE test_druid";
                stmt.executeUpdate(sql);
                System.out.println("Table dropped successfully.");
            }
        }
    

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>druid-oceanbase-client</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
    </dependencies>
</project>

# Database Configuration
driverClassName=com.oceanbase.jdbc.Driver
url=jdbc:oceanbase://$host:$port/$schema_name
username=$user_name
password=$password

# Connection Pool Configuration
#To check whether the database link is valid, MySQL must be configured to select 1; Oracle is select 1 from dual
validationQuery=select 1 from dual
#Initial number of connections
initialSize=3
#Maximum number of activations, that is, the maximum number of Connection pool
maxActive=30
#When closing the Abandoned connection, the error log is output. When the link is recycled, the console prints information. The test environment can add true, while the online environment is false. Will affect performance.
logAbandoned=true
#Minimum number of activations during idle time
minIdle=5
#The maximum waiting time for a connection, in milliseconds
maxWait=1000
#The maximum time to start the eviction thread is the survival time of a link (previous value: 25200000, the converted result of this time is: 2520000/1000/60/60=7 hours)
minEvictableIdleTimeMillis=300000
#Whether to recycle after exceeding the time limit
removeAbandoned=true
#Exceeding the time limit (in seconds), currently 5 minutes. If any business processing time exceeds 5 minutes, it can be adjusted appropriately.
removeAbandonedTimeout=300
# Run the idle connection collector Destroy thread every 10 seconds to detect the interval time between connections, based on the judgment of testWhileIdle
timeBetweenEvictionRunsMillis=10000
#When obtaining a link, not verifying its availability can affect performance.
testOnBorrow=false
#Check whether the link is available when returning the link to the Connection pool.
testOnReturn=false
#This configuration can be set to true, without affecting performance and ensuring security. The meaning is: Detect when applying for a connection. If the idle time is greater than timeBetweenEviceRunsMillis, execute validationQuery to check if the connection is valid.
testWhileIdle=true
#Default false, if configured as true, connection detection will be performed in the DestroyConnectionThread thread (timeBetweenEvaluation once)
keepAlive=false
#If keepAlive rule takes effect and the idle time of the connection exceeds it, the connection will only be detected
keepAliveBetweenTimeMillis=60000

package com.example;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class Main {

    public static void main(String[] args) {
        try {
            Properties properties = loadPropertiesFile();
            DataSource dataSource = createDataSource(properties);
            try (Connection conn = dataSource.getConnection()) {
                // Create table
                createTable(conn);
                // Insert data
                insertData(conn);
                // Query data
                selectData(conn);

                // Update data
                updateData(conn);
                // Query the updated data
                selectData(conn);

                // Delete data
                deleteData(conn);
                // Query the data after deletion
                selectData(conn);

                // Drop table
                dropTable(conn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Properties loadPropertiesFile() throws IOException {
        Properties properties = new Properties();
        try (InputStream is = Main.class.getClassLoader().getResourceAsStream("db.properties")) {
            properties.load(is);
        }
        return properties;
    }

    private static DataSource createDataSource(Properties properties) throws Exception {
        return DruidDataSourceFactory.createDataSource(properties);
    }

    private static void createTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "CREATE TABLE test_druid (id NUMBER, name VARCHAR2(20))";
            stmt.executeUpdate(sql);
            System.out.println("Table created successfully.");
        }
    }

    private static int insertData(Connection conn) throws SQLException {
        String insertDataSql = "INSERT INTO test_druid (id, name) VALUES (?, ?)";
        int insertedRows = 0;
        try (PreparedStatement insertDataStmt = conn.prepareStatement(insertDataSql)) {
            for (int i = 1; i < 6; i++) {
                insertDataStmt.setInt(1, i);
                insertDataStmt.setString(2, "test_insert" + i);
                insertedRows += insertDataStmt.executeUpdate();
            }
            System.out.println("Data inserted successfully. Inserted rows: " + insertedRows);
        }
        return insertedRows;
    }

    private static void updateData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("UPDATE test_druid SET name = ? WHERE id = ?")) {
            pstmt.setString(1, "test_update");
            pstmt.setInt(2, 3);
            int updatedRows = pstmt.executeUpdate();
            System.out.println("Data updated successfully. Updated rows: " + updatedRows);
        }
    }

    private static void deleteData(Connection conn) throws SQLException {
        try (PreparedStatement pstmt = conn.prepareStatement("DELETE FROM test_druid WHERE id < ?")) {
            pstmt.setInt(1, 3);
            int deletedRows = pstmt.executeUpdate();
            System.out.println("Data deleted successfully. Deleted rows: " + deletedRows);
        }
    }

    private static void selectData(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "SELECT * FROM test_druid";
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id: " + id + ", name: " + name);
            }
        }
    }

    private static void dropTable(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String sql = "DROP TABLE test_druid";
            stmt.executeUpdate(sql);
            System.out.println("Table dropped successfully.");
        }
    }
}

References

For more information about OceanBase Connector/J, see OceanBase JDBC driver.