Connect to OceanBase Database by using OceanBase Connector/J

This topic introduces how to build an application by using OceanBase Connector/J and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, data query, and other basic operations.

Download the java-oceanbase-jdbc sample project Connect to OceanBase Database by using OceanBase Connector/J (Oracle mode)

Prerequisites

• You have installed OceanBase Database.

• You have installed JDK 1.8 and Maven.

• You have installed Eclipse.

  Note

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

Procedure

1. Import the java-oceanbase-jdbc project into Eclipse.
2. Obtain the URL of OceanBase Database.
3. Modify the database connection information in the java-oceanbase-jdbc project.
4. Run the java-oceanbase-jdbc project.

Step 1: Import the java-oceanbase-jdbc project into Eclipse

1. Start Eclipse and choose File > Import.

   

2. In the import window that appears, choose Maven > Existing Maven Projects and click Next.

   

3. On the next page, click Browse, select the root directory of the Maven project, and then click Finish.

   

4. Eclipse automatically recognizes the pom.xml file and displays all Maven projects in the Package Explorer. To view the Package Explorer view, choose Window > Show View > Package Explorer if it is not displayed on the left side of the window.

   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.

   

5. View the project.

   

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 -P2883 -usys@oracel001#cluster_name -p******
   

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

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

   Here is an example URL for connecting to OceanBase Database in Oracle mode:

   jdbc:oceanbase://$host:$port/$schema_name?user=$user_name&password=$password
   

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
   • $port: 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.
   • $schema_name: the name of the schema to be accessed.
   • $user_name: the tenant account. For connection 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 password of the account.

   For more information about URL parameters, see Database URL.

Step 3: Modify the database connection information in the java-oceanbase-jdbc project

Modify the database connection information in the InsertAndSelectExample.java 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 10.10.10.1.
• The port is 2881.
• The name of the database to be accessed is sys.
• The tenant account is sys@oracle001. Here, oracle001 refers to a user tenant created in OceanBase Database with the Oracle mode, and sys is the username of the oracle001 tenant.
• The password is ******.

Sample code:

String url = "jdbc:oceanbase://10.10.10.1:2881/sys";
String user = "sys@oracle001";
String password = "******";

Step 4: Run the java-oceanbase-jdbc project

1. You can view the project structure in the Package Explorer.

   

2. Find the Main class in the project, right-click it, choose Run As > Run Configurations... to open the Run Configurations page.

   

3. On the Run Configurations page, select Java Application > New_configuration. In the Main class field of the page that appears, select com.oceanbase.example.InsertAndSelectExample.

   

4. View the project logs and output results in the console of Eclipse.

   

Project code introduction

Click java-oceanbase-jdbc to download the project code, which is a compressed package named java-oceanbase-jdbc.zip.

After decompressing it, you will find a folder named java-oceanbase-jdbc. The directory structure is as follows:

.
|-- README-CN.md
|-- README.md
|-- pom.xml
|-- run.sh
`-- src
    `-- main
        `-- java
            `-- com
                `-- oceanbase
                    `-- example
                        |-- InsertAndSelectExample.java
                        `-- OceanBaseClientTest.java

File description:

• README-CN.md: the project documentation in Chinese.
• README.md: the project documentation in English.
• pom.xml: the configuration file of the Maven project, which defines the project dependencies, plugins, and build rules.
• run.sh: a shell script for automatically compiling and running Java applications.
• src: the main directory that contains the project's source code and resource files.
• main: a directory that contains the project's main Java source code.
• java: the root directory for storing the project's Java source code.
• com: the root directory for storing the project's Java package.
• oceanbase: a subpackage under the com package, indicating that the project is related to OceanBase Database.
• example: a subpackage under the oceanbase package, indicating that the project is an example program for demonstrating how to use the OceanBase JDBC driver to connect to and operate OceanBase Database.
• InsertAndSelectExample.java: a file that contains an example class InsertAndSelectExample for demonstrating how to insert and query data.
• OceanBaseClientTest.java: a file that contains an example class OceanBaseClientTest for demonstrating how to connect to a database, execute SQL statements, and query data.

Introduction to pom.xml

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

The pom.xml file in this topic is divided into the following sections:

1. Declaration statement.

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

   Sample code:

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

2. Configure namespaces and the 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 used by the POM file to 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 identifier as com.oceanbase.example.
   2. Use <artifactId> to specify the project dependency as oceanbase-client.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.
   4. Use <name> to specify the project name as ob-example-oceanbase-client.

   Sample code:

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

4. Use <properties> to define the properties of the project's source file.

   1. Use <project.build.sourceEncoding> to specify the encoding of the project's source file as UTF-8.
   2. Use <maven.compiler.source> to specify the Java source code version used by the Maven compiler as 1.8.
   3. Use <maven.compiler.target> to specify the Java bytecode version generated by the Maven compiler as 1.8.
   4. Use <exec.mainClass> to specify an example program in the project, namely, com.oceanbase.example.InsertAndSelectExample.

   Sample code:

   <properties>
     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
     <maven.compiler.source>1.8</maven.compiler.source>
     <maven.compiler.target>1.8</maven.compiler.target>
     <exec.mainClass>com.oceanbase.example.InsertAndSelectExample</exec.mainClass>
   </properties>
   

5. Use <dependencies> to define the components on which the project depends.

   Note

   This section defines that the project depends on OceanBase Connector/J V2.4.2. For more information about other versions, see OceanBase Connector/J.

   1. Use <dependency> to define a dependency.
   2. Use <groupId> to specify the dependency group as com.oceanbase.
   3. Use <artifactId> to specify the dependency name as oceanbase-client.
   4. Use <version> to specify the dependency version as 2.4.2.

   Sample code:

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

Introduction to InsertAndSelectExample.java

The InsertAndSelectExample.java file is part of the sample program and contains a sample class named InsertAndSelectExample for demonstrating how to insert and query data.

The InsertAndSelectExample.java file in this topic includes the following sections:

1. Define the package and import java.sql interfaces.

   Declare the name of the package to which the current file belongs as com.oceanbase.example.

   Introduce the following interfaces and classes in the java.sql package:

   • Connection interface: represents a connection to a database.
   • DriverManager class: provides a set of static methods for managing JDBC driver registration and database connection retrieval. The most commonly used method is getConnection(), which is used to create a connection to the database.
   • PreparedStatement interface: represents a precompiled SQL statement.
   • ResultSet interface: represents a query result set.
   • SQLException exception class: represents various exceptions that may occur when using JDBC to connect to a database, such as connection failures or SQL command execution failures.
   • Statement interface: represents a statement in a database.

   Sample code:

   package com.oceanbase.example;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   

2. Define the class name and main method.

   Define the class name as InsertAndSelectExample, which should be the same as the file name.

   Define the main method as public static void, indicating that it is a public static method that accepts a string array args as a parameter. Use throws ClassNotFoundException, SQLException to indicate that the method may throw ClassNotFoundException and SQLException exceptions, which need to be handled where the method is called.

   Sample code:

   public class InsertAndSelectExample {
   
       public static void main(String[] args) throws ClassNotFoundException, SQLException {
           // to do:
           // 3.connect to your database
           // 4.create a table
           // 5.insert records
           // 6.fetch all records
           // 7.release all resources
       }
   }
   

3. Connect to the database and obtain a database connection object.

   Define the URL string and additional connection properties required for connecting to OceanBase Database:

   • jdbc:oceanbase: indicates the use of the OceanBase JDBC driver to connect to the database.
   • host:port: indicates the IP address and port number of OceanBase Database.
   • opt1=val1&opt2=val2...: additional connection properties, or URL parameters. For more information about URL additional connection properties, see Database URL.
   • schema_name: indicates the name of the schema to connect to.
   • String user = "user_name": defines the username required for logging into the database.
   • String password = "******": defines the password required for logging into the database.

   Obtain a database connection object:

   • Call the forName method of the Class class to load the driver class specified by the class name com.oceanbase.jdbc.Driver.
   • Call the getConnection method of the DriverManager class to obtain the Connection object conn.

   Sample code:

   String url = "jdbc:oceanbase://host:port/schema_name?[opt1=val1&opt2=val2...]";
   String user = "user_name";
   String password = "******";
   Class.forName("com.oceanbase.jdbc.Driver");
   Connection conn = DriverManager.getConnection(url, user, password);
   

4. Create a table.

   Create a table named person with two fields, name and age. If the table already exists, it will be deleted before creation. The specific steps are as follows:

   1. Create a Statement object named stmt for sending SQL statements to the database.
   2. Call the execute method of the stmt object and pass in the SQL statement drop table person to delete the table named person. Since it is uncertain whether the person table already exists, use the try-catch structure to capture exceptions to prevent the program from crashing when deleting a non-existent table.
   3. Call the execute method of the stmt object again and pass in the SQL statement create table person (name varchar(50), age int) to create a table named person. The table contains two fields, name and age. The name field type is varchar(50), and the age field type is int.

   Sample code:

   Statement stmt = conn.createStatement();
       try {stmt.execute("drop table person");} 
       catch (Exception ignore) {}
           stmt.execute("create table person (name varchar(50), age int)");
   

5. Insert data into the table.

   Use the PreparedStatement interface to insert two data records into the person table in the database. The specific steps are as follows:

   1. Create a PreparedStatement object named ps for executing SQL statements. The SQL statement is insert into person values(?, ?), where ? is a placeholder for specific values.
   2. Call the setString() method to set the first placeholder (the first question mark) to Adam.
   3. Call the setInt() method to set the second placeholder (the second question mark) to 28.
   4. Perform an update operation to insert the data into the database.
   5. Call the setString() method again to set the first placeholder to Eve.
   6. Call the setInt() method again to set the second placeholder to 26.
   7. Perform an update operation again to insert the second data record into the database.

   Sample code:

   PreparedStatement ps = conn.prepareStatement("insert into person values(?, ?)");
       ps.setString(1, "Adam");
       ps.setInt(2, 28);
       ps.executeUpdate();
       ps.setString(1, "Eve");
       ps.setInt(2, 26);
       ps.executeUpdate();
   

6. Retrieve all records.

   Query all data from the person table and output the results to the console. The specific steps are as follows:

   1. Create a PreparedStatement object named ps for executing SQL statements. The SQL statement is select * from person, which indicates that all data in the person table is to be queried. Use the setFetchDirection() method to set the cursor of the result set to move forward, and use the setConcurrency() method to set the result set to read-only.
   2. Perform a query operation, and save the query result in a ResultSet object named rs.
   3. Use a while loop to traverse the ResultSet object. Use the rs.next() method to move the cursor to the next row of data. If there is still data, the method returns true. In the loop, use the rs.getString(1) method to obtain the value of the first column (i.e., the name field in the person table), and use the rs.getInt(2) method to obtain the value of the second column (i.e., the age field), and output these values to the console in the format of name is age years old.
   4. After the loop ends, close the ResultSet and PreparedStatement objects.

   Sample code:

   ps = conn.prepareStatement("select * from person", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
   ResultSet rs = ps.executeQuery();
   while (rs.next()) {
       System.out.println(rs.getString(1) + " is " + rs.getInt(2) + " years old.");
       }
   

7. Release database resources.

   Close the PreparedStatement, Statement, and Connection objects to release the associated database resources, thereby avoiding resource consumption and performance issues.

   Sample code:

   ps.close();
   stmt.close();
   conn.close();
   

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.oceanbase.example</groupId>
  <artifactId>oceanbase-client</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>ob-example-oceanbase-client</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <exec.mainClass>com.oceanbase.example.InsertAndSelectExample</exec.mainClass>
  </properties>

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

</project>

package com.oceanbase.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertAndSelectExample {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        String url = "jdbc:oceanbase://host:port/schema_name?[opt1=val1&opt2=val2...]";
        String user = "user_name";
        String password = "******";
        Class.forName("com.oceanbase.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, user, password);

        Statement stmt = conn.createStatement();
        try {
            stmt.execute("drop table person");
        } catch (Exception ignore) {
        }
        stmt.execute("create table person (name varchar2(50), age int)");

        PreparedStatement ps = conn.prepareStatement("insert into person values(?, ?)");
        ps.setString(1, "Adam");
        ps.setInt(2, 28);
        ps.executeUpdate();
        ps.setString(1, "Eve");
        ps.setInt(2, 26);
        ps.executeUpdate();

        ps = conn.prepareStatement("select * from person", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1) + " is " + rs.getInt(2) + " years old.");
        }

        ps.close();
        stmt.close();
        conn.close();
    }

}

References

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