Connect to OceanBase Database by using OceanBase Connector/J

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

To download the project code, click java-oceanbase-jdbc.

Prerequisites

• You have installed OceanBase Database.

• You have installed 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

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

Step 1: Obtain the URL of OceanBase Database

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

   Example:

   obclient -hxxx.xxx.xxx.xxx -P2883 -usys@oracel001#cluster_name -p******
   

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

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

   The URL used to connect to OceanBase Database in Oracle mode is similar to the following example:

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

   Parameters:

   • host: the IP address for connecting to OceanBase Database. For connection through an OceanBase Database Proxy (ODP), this parameter is the IP address of the ODP. For direct connection, this parameter is 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, two account formats are supported: Username@Tenant name#Cluster name and Cluster name:Tenant name:Username. For direct connection, the Username@Tenant name format is supported.
   • password: the account password.

   For more information about URL parameters, see Database URL.

Step 2: Obtain the code of the java-oceanbase-jdbc project

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

Decompress the package to obtain the folder named java-oceanbase-jdbc. The directory is structured as follows:

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

File and directory description:

• README-CN.md: This file is the documentation of the project, mainly for Chinese users.
• README.md: This file is also the documentation of the project, but it is written in English, mainly for English users.
• pom.xml: This file is the configuration file of the Maven project and defines the dependencies, plug-ins, and build rules of the project.
• run.sh: This file is a shell script for automatically compiling and running Java applications.
• src: This directory contains the source code and resource files of the project and is the main directory of the project.
• main: This directory contains main Java source 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 sub-directory under com, indicating that the project is related to OceanBase Database.
• example: This directory is a sub-directory under oceanbase, indicating that this project is a sample program for demonstrating how to connect to and operate OceanBase Database by using the OceanBase JDBC driver.
• InsertAndSelectExample.java: This file is a part of the sample program and contains a sample class InsertAndSelectExample for demonstrating how to insert and query data.
• OceanBaseClientTest.java: This file is a part of the sample program and contains a sample class OceanBaseClientTest for demonstrating how to connect to a database, execute SQL statements, and query data.

Code in the pom.xml file

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

Code in the pom.xml file contains the following parts:

1. Declare the file.

   Declare that this file is an XML file, using XML version 1.0, and the character encoding is UTF-8.

   The 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, 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 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 ID of the project, which is set to oceanbase-client.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.
   4. <name>: the project name, which is set to ob-example-oceanbase-client.

   The 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 by using <properties>.

   1. <project.build.sourceEncoding>: the encoding used by 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>: specifies to execute the sample program com.oceanbase.example.InsertAndSelectExample in the project.

   The 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. Define the components on which the project depends by using <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>: defines a dependency.
   2. <groupId>: the ID of the group to which the dependency belongs, which is set to com.oceanbase.
   3. <artifactId>: the ID of the dependency, which is set to oceanbase-client.
   4. <version>: the version of the dependency, which is set to 2.4.2.

   The code is as follows:

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

Code in the InsertAndSelectExample.java file

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

Code in the InsertAndSelectExample.java file contains the following parts:

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

   Declare the name of the parent package of the file to be com.oceanbase.example.

   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 the JDBC driver 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 JDBC to connect to a database, such as connection failure and SQL statement execution failure.
   • Statement interface: indicates a statement in a database.

   The 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 the class name and 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, a public static method that accepts a string array args as parameters. 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 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 Database. In the URL:

   • jdbc:oceanbase: specifies to use the OceanBase JDBC driver to connect to the database.
   • host:port: the IP address and port number of OceanBase Database.
   • opt1=val1&opt2=val2...: additional connection properties, or URL parameters.
   • schema_name: the name of the schema to be accessed.
   • String user = "user_name": the username required for logging on to the database.
   • String password = "******": the password required 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 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 with 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 a program 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 with the name and age columns. The data type of the name column is varchar(50), and that of the age column is int.

   The 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 ? is a placeholder that indicates a value to be inserted.
   2. Call the setString() method to set the first placeholder to Adam.
   3. Call the setInt() method to set the second placeholder 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 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 data 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 indicates to query 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 results 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 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. This avoids resource consumption and performance issues.

   The code is as follows:

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

Complete code for your reference

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

}

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

1. Open Eclipse and select the Import option from the File menu.

   

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

   

3. On the next page that appears, click the Browse button, 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 panel of Eclipse. If the icon is not displayed, select the Window menu and choose 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 4: 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 1: Obtain the URL of OceanBase Database.

For example:

• The IP address of the OBServer node is 10.10.10.1.
• The access port is 2881.
• The name of the schema to be accessed is sys.
• The connection account of the tenant is sys@oracle001. Here, oracle001 is a user tenant created in the Oracle mode of OceanBase Database, and sys is the username of the oracle001 tenant.
• The password is ******.

The sample code is as follows:

String url = "jdbc:oceanbase://10.10.10.1:2881/sys?socketTimeout=7200000&useUnicode=true&characterEncoding=UTF-8&useCursorFetch=true&defaultFetchSize=100&useSSL=false&rewriteBatchedStatements=true&cachePrepStmts=true&useServerPrepStmts=true&usePieceData=true&allowSendParamTypes=true";
String user = "root@oracle001";
String password = "******";

Step 5: 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 page.

   

3. On the Run Configurations page, 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 Eclipse console.

   

References

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