Connect to OceanBase Cloud by using OceanBase Connector/J

This topic describes how to use OceanBase Connector/J and OceanBase Cloud to build an application for basic database operations, such as table creation, data insertion, and data query.

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

Prerequisites

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

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

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

• You have installed Eclipse.

  Note

  This topic uses Eclipse IDE for Java Developers 2022-03 to run the sample code. You can also choose a suitable tool as needed.

Procedure

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

1. Start Eclipse and choose File > Import.

   

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

   

3. In the window that appears, click Browse, select the root directory of the Maven project, and then click Finish.

   

4. Eclipse automatically recognizes the pom.xml file in the project and displays all Maven projects in Package Explorer. The icon of Package Explorer is displayed in the left-side pane of Eclipse. If the icon is not displayed, choose Window > Show View > Package Explorer.

   Note

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

   

5. View the project.

   

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

Modify the database connection information in the InsertAndSelectExample.java file based on the obtained connection string mentioned in the "Prerequisites" section.

Here is an example:

String url = "jdbc:oceanbase://t5******.********.oceanbase.cloud:3306/test";
String user = "test_user";
String password = "******";

• The endpoint is t5******.********.oceanbase.cloud.
• The access port is 3306.
• The name of the database to be accessed is sys.
• The tenant account is test_user.
• The password is ******.

Step 3: Run the java-oceanbase-jdbc project

1. View the project structure in Package Explorer.

   

2. Right-click the main class in the project and choose Run As > Run Configurations... to open the Run Configurations window.

   

3. In the Run Configurations window, choose Java Application > New_configuration and select the com.oceanbase.example.InsertAndSelectExample project for Main class.

   

4. View the logs and output of the project in the Console window.

   

Project code

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

Decompress the package to obtain 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

The files and directories are described as follows:

• README-CN.md: the file that introduces the project in Chinese.
• README.md: the file that introduces the project in English.
• pom.xml: the configuration file of the Maven project, which defines the dependencies, plug-ins, and build rules of the project.
• run.sh: the shell script for automatically compiling and running the Java application.
• src: the directory that contains the source code and resource files of the project. It is the main directory of the project.
• main: the directory that contains main Java source code of the project.
• java: the root directory of Java source code.
• com: the root directory of the Java package.
• oceanbase: a subdirectory under com, indicating that the project is related to OceanBase Cloud.
• example: a subdirectory under oceanbase, indicating that this project is a sample application for demonstrating how to connect to and operate OceanBase Cloud by using OceanBase Connector/J.
• InsertAndSelectExample.java: a part of the sample application that contains a sample class InsertAndSelectExample for demonstrating how to insert and query data.
• OceanBaseClientTest.java: a part of the sample application that contains a sample class OceanBaseClientTest for demonstrating how to connect to a database, execute SQL statements, and query data.

Code in pom.xml

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

Perform the following steps to configure the pom.xml file:

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure namespaces and the POM model version.

   1. xmlns: the default XML namespace for the POM, which is set to http://maven.apache.org/POM/4.0.0.
   2. xmlns:xsi: the XML namespace for XML elements prefixed with xsi, which is set to http://www.w3.org/2001/XMLSchema-instance.
   3. xsi:schemaLocation: the location of an XML schema definition (XSD) file. The value consists of two parts: the default XML namespace (http://maven.apache.org/POM/4.0.0) and the URI of the XSD file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. <modelVersion>: the POM model version used by the POM file, which is set to 4.0.0.

   The sample code is as follows:

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

3. Configure basic project information.

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

   The sample code is as follows:

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

4. Define source file properties of the project in <properties>.

   1. <project.build.sourceEncoding>: the character encoding for the project source file, which is set to UTF-8.
   2. <maven.compiler.source>: the version of the Java source code used by the Maven compiler, which is set to 1.8.
   3. <maven.compiler.target>: the version of the Java bytecode generated by the Maven compiler, which is set to 1.8.
   4. <exec.mainClass>: a sample application in the project, which is set to com.oceanbase.example.InsertAndSelectExample.

   The sample code is as follows:

   <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. Configure project dependencies in <dependencies>.

   Note

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

   1. <dependency>: a dependency.
   2. <groupId>: the ID of the group to which the dependency belongs, which is set to com.oceanbase.
   3. <artifactId>: the name of the dependency, which is set to oceanbase-client.
   4. <version>: the version of the dependency, which is set to 2.4.2.

   The sample code is as follows:

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

Code in InsertAndSelectExample.java

InsertAndSelectExample.java is a part of the sample application and contains a sample class InsertAndSelectExample for demonstrating how to insert and query data.

Perform the following steps to configure the InsertAndSelectExample.java file:

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

   Declare that the current file belongs to the com.oceanbase.example package.

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

   • Connection interface: indicates a connection to a database.
   • DriverManager class: provides a set of static methods for managing registration of OceanBase Connector/J and obtaining a database connection. The most common method is getConnection(), which is used to create a connection to a database.
   • PreparedStatement interface: indicates a precompiled SQL statement.
   • ResultSet interface: indicates a query result set.
   • SQLException class: indicates exceptions that may occur when you use OceanBase Connector/J to connect to a database, such as connection failure and SQL statement execution failure.
   • Statement interface: indicates a statement in a database.

   The sample code is as follows:

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

   Set the class name to InsertAndSelectExample. The name must be the same as the file name.

   Set the main method to public static void, which is a public static method that accepts a string array args as arguments. throws ClassNotFoundException, SQLException is used to declare that the ClassNotFoundException and SQLException exceptions can be thrown from the method. The caller of the method that throws exceptions is required to handle the exceptions.

   The sample code is as follows:

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

   Define the URL with additional connection properties required for connecting to OceanBase Cloud. In the URL:

   • jdbc:oceanbase: specifies to use the OceanBase Connector/J driver to connect to the database.
   • host:port: the IP address and port number of OceanBase Cloud.
   • opt1=val1&opt2=val2...: additional connection properties or parameters of the URL. For more information about the additional connection properties of a URL, see Database URL.
   • schema_name: the name of the schema to access.
   • String user = "user_name": the username for logging on to the database.
   • String password = "******": the password for logging on to the database.

   Obtain the database connection object as follows:

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

   The sample code is as follows:

   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 that contains the name and age columns. If the table already exists, drop the existing one and then create a new one. Perform the following steps:

   1. Create a Statement object named stmt to send SQL statements to the database.
   2. Call the execute method of the stmt object to execute the SQL statement drop table person to drop a table named person. Whether the person table already exists cannot be determined. Therefore, the try-catch structure is used to capture exceptions, to avoid an application crash caused by an attempt to drop a nonexistent table.
   3. Call the execute method of the stmt object again to execute the SQL statement create table person (name varchar(50), age int) to create a table named person that contains the name and age columns. The data type of the name column is varchar(50), and that of the age column is int.

   The sample code is as follows:

   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.

   Call the PreparedStatement interface to insert two data records into the person table in the database. Perform the following steps:

   1. Create a PreparedStatement object named ps to execute an SQL statement. The SQL statement is insert into person values(?, ?·), where each question mark (?`) is a placeholder that indicates a value to be inserted.
   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 to insert the first data record into the table.
   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 again to insert the second data record into the table.

   The sample code is as follows:

   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. Obtain all records.

   Query all data from the person table, and return the results to the console. Perform the following steps:

   1. Create a PreparedStatement object named ps to execute an SQL statement. The SQL statement is select * from person, which queries all data from the person table. Call the setFetchDirection() method to set the cursor in the result set to move forward, and call the setConcurrency() method to set the result set to be read-only.
   2. Perform a query operation, save the query result to the ResultSet object, and name the ResultSet object rs.
   3. Use the while loop to traverse the ResultSet object, and call the rs.next() method to move the cursor to the next row of data. true is returned if there is more data. In the loop, call the rs.getString(1) method to obtain the values of the first column (namely, the name column in the person table), call the rs.getInt(2) method to obtain the values of the second column (namely, the age column), and return the values to the console in the format of name is age years old.
   4. After the loop ends, close the ResultSet and PreparedStatement objects.

   The sample code is as follows:

   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 database resources to reduce resource consumption and avoid performance issues.

   The sample code is as follows:

   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.