Connect to OceanBase Cloud using OceanBase Connector/J

This topic describes how to use OceanBase Connector/J to build an application that connects to OceanBase Cloud and performs basic operations such as creating tables, inserting data, and querying data.

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

Prerequisites

• You have registered an account with OceanBase Cloud, created an instance and an Oracle-compatible tenant. For more information, see Create an instance and Create a 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 other tools that you prefer.

Procedure

1. Import the java-oceanbase-jdbc project into Eclipse.
2. Obtain the URL of the OceanBase Cloud 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 Import from the File menu.

   

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

   

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

   

4. Eclipse automatically detects the pom.xml file in the project and displays all Maven projects in the Package Explorer. On the left side of the Eclipse window, you can see the Package Explorer icon. If it is not displayed, choose Window > Show View > Package Explorer.

   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 in the file, and adds them to the project.

   

5. View the project.

   

Step 2: Obtain the URL of the OceanBase Cloud database

1. Log in to the OceanBase Cloud console. In the instance list, expand the information of the target instance and select Connect > Get Connection String under the target tenant.

   For more information, see Get connection string.

2. Fill in the URL with the information of the created OceanBase Cloud database.

   The URL for connecting to the Oracle compatible mode of the OceanBase Cloud database is as follows:

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

   Parameter description:

   • $host: the URL of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.
   • $port: the port of the OceanBase Cloud database. The default value is 1521.
   • $schema_name: the name of the schema to be accessed.
   • $user_name: the account for accessing the database.
   • $password: the password of the account.

   For more information about the 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 the OceanBase Cloud database.

Example:

• The URL of the OceanBase Cloud database is t5******.********.oceanbase.cloud.
• The port is 1521.
• The name of the schema to be accessed is sys.
• The tenant account is oracle001.
• The password is ******.

Sample code:

String url = "jdbc:oceanbase://t5******.********.oceanbase.cloud:1521/sys";
String user = "oracle001";
String password = "******";

Step 4: Run the java-oceanbase-jdbc project

1. View the project structure in Package Explorer.

   

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

   

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

   

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

   

Project code

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

After decompressing the file, 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: This file is the project description document, mainly for Chinese users.
• README.md: This file is also the project description document, but it is written in English and mainly for English users.
• pom.xml: This file is the configuration file of the Maven project, which defines the project's dependencies, plugins, and build rules.
• run.sh: This file is a Shell script used to automatically compile and run Java applications.
• src: This directory contains the source code and resource files of the project, which is the main directory of the project.
• main: This directory is a Java source code directory, containing the main code of the project.
• java: This directory is the root directory of Java source code.
• com: This directory is the root directory of Java packages.
• oceanbase: This directory is a subpackage under the com package, indicating that the project is related to OceanBase Cloud.
• example: This directory is a subpackage under the oceanbase package, indicating that the project is an example program that demonstrates how to connect to and operate on OceanBase Cloud using the OceanBase JDBC driver.
• InsertAndSelectExample.java: This file is part of the example program and contains an example class InsertAndSelectExample that demonstrates how to insert and query data.
• OceanBaseClientTest.java: This file is also part of the example program and contains an example class OceanBaseClientTest that demonstrates how to connect to the database, execute SQL statements, and query data.

Introduction to the pom.xml file

The pom.xml file is the configuration file of a Maven project. It 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 projects.

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

1. The file declaration statement.

   This statement 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. The POM namespace and model version.

   1. The xmlns attribute specifies the POM namespace as http://maven.apache.org/POM/4.0.0.
   2. The xmlns:xsi attribute specifies the XML namespace as http://www.w3.org/2001/XMLSchema-instance.
   3. The xsi:schemaLocation attribute specifies 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. The <modelVersion> element specifies the POM model version used by the POM file 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. Basic information configuration.

   1. The <groupId> element specifies the project identifier as com.oceanbase.example.
   2. The <artifactId> element specifies the project dependency as oceanbase-client.
   3. The <version> element specifies the project version as 1.0-SNAPSHOT.
   4. The <name> element specifies 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. The <properties> element defines the properties of the project source file.

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

   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. The <dependencies> element defines the components that the project depends on.

   Note

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

   1. The <dependency> element defines a dependency.
   2. The <groupId> element specifies the organization of the dependency as com.oceanbase.
   3. The <artifactId> element specifies the name of the dependency as oceanbase-client.
   4. The <version> element specifies the version of the dependency 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 the code in InsertAndSelectExample.java

The InsertAndSelectExample.java file is part of the sample program and contains the InsertAndSelectExample class, which demonstrates how to insert and query data.

The code in the InsertAndSelectExample.java file mainly includes the following parts:

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

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

   Import the following interfaces and classes from the java.sql package:

   • Connection interface: represents a connection to the database.
   • DriverManager class: provides a set of static methods for managing JDBC driver registration and obtaining database connections. 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 occur when using JDBC to connect to the database, such as connection failure or failed SQL command execution.
   • Statement interface: represents a statement in the database.

   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 must be consistent with 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 this method may throw ClassNotFoundException and SQLException exceptions, which need to be handled at the method call site.

   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 the database connection object.

   Define the URL string and additional connection properties required to connect to OceanBase Cloud:

   • jdbc:oceanbase: indicates that the OceanBase JDBC driver is used to connect to the database.
   • host:port: indicates the address and port number of the OceanBase Cloud database.
   • opt1=val1&opt2=val2...: additional connection properties, i.e., URL parameters. For more information about URL parameters, see Database URL.
   • schema_name: indicates the name of the schema to connect to.
   • String user = "user_name": defines the username required to log in to the database.
   • String password = "******": defines the password required to log in to the database.

   Obtain the database connection object:

   • Load the driver program class named com.oceanbase.jdbc.Driver by calling the forName method of the Class class.
   • Obtain the Connection object conn by calling the getConnection method of the DriverManager class.

   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, drop it before creating it. The steps are as follows:

   1. Create a Statement object named stmt to send SQL statements to the database.
   2. Call the execute method of the stmt object and pass the SQL statement drop table person to drop the person table. Since it is uncertain whether the person table exists, use the try-catch structure to capture exceptions to prevent the program from crashing when the table does not exist.
   3. Call the execute method of the stmt object again and pass the SQL statement create table person (name varchar(50), age int) to create the person table with two fields, name and age, where the name field is of type varchar(50) and the age field is of type int.

   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 steps are as follows:

   1. Create a PreparedStatement object named ps to execute SQL statements. The SQL statement is insert into person values(?, ?), where ? is a placeholder indicating that specific values need to be filled in.
   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. Execute 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. Execute an update operation again to insert the second data record into the database.

   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 steps are as follows:

   1. Create a PreparedStatement object named ps to execute SQL statements. The SQL statement is select * from person, which queries all data from the person table. Set the cursor of the result set to move forward by calling the setFetchDirection() method and set the result set to read-only by calling the setConcurrency() method.
   2. Execute a query operation and save the query results in the ResultSet object, which is named rs.
   3. Traverse the ResultSet object by using a while loop. Call the rs.next() method to move the cursor to the next data row. If data exists, it returns true. In the loop, call the rs.getString(1) method to obtain the value of the first column (i.e., the name column in the person table), call the rs.getInt(2) method to obtain the value of the second column (i.e., the age column), and output these values to the console in the format name is age years old..
   4. After the loop ends, close the ResultSet and PreparedStatement objects.

   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 and avoid resource consumption and performance issues.

   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 JDBC driver.