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, and data query.

Download the java-oceanbase-jdbc sample project

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), but you can also choose a tool that suits your personal preference to run the 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, choose Maven > Existing Maven Projects and click Next.

   

3. On the page 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. You can see the icon of Package Explorer 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 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 database connection string.

   Here is an example:

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

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

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

   The following example shows how to connect to the Oracle mode of OceanBase Database by using the URL:

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

   where:

   • $host specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • $port specifies 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 specifies the name of the schema to access.
   • $user_name specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • $password specifies the account password.

   For more information about URL parameters in OceanBase Connector/J, 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 schema to be accessed is sys.
• The tenant account is sys@oracle001, where oracle001 is an Oracle user tenant created in 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";
String user = "sys@oracle001";
String password = "******";

Step 4: Run the java-oceanbase-jdbc project

1. View the project structure in Package Explorer.

   

2. Right-click the main class 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 com.oceanbase.example.InsertAndSelectExample for Main class.

   

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

   

Project code introduction

Click java-oceanbase-jdbc to download the project code, which is a compressed file 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

Here is a breakdown of the files and directories:

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

Code in pom.xml

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

To configure the pom.xml file, perform the following steps:

1. Declare the file.

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

   The sample code is as follows:

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

2. Configure the namespaces and the POM model version.

   1. Use xmlns to specify http://maven.apache.org/POM/4.0.0 as the default XML namespace for the POM.
   2. Use xmlns:xsi to specify http://www.w3.org/2001/XMLSchema-instance as the XML namespace for xsi-prefixed elements.
   3. Use xsi:schemaLocation to provide a mapping from the default XML namespace for the POM (http://maven.apache.org/POM/4.0.0) to the location of the POM's XML schema definition (XSD) file (http://maven.apache.org/xsd/maven-4.0.0.xsd).
   4. Use <modelVersion> to specify 4.0.0 as the model version used by the POM.

   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. Use <groupId> to specify com.oceanbase.example as the ID of the project group.
   2. Use <artifactId> to specify oceanbase-client as the ID of project.
   3. Use <version> to specify 1.0-SNAPSHOT as the project version.
   4. Use <name> to specify ob-example-oceanbase-client as the project name.

   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. Use <project.build.sourceEncoding> to specify UTF-8 as the encoding method used by the project source file.
   2. Use <maven.compiler.source> to specify 1.8 as the version of the Java source code used by the Maven compiler.
   3. Use <maven.compiler.target> to specify 1.8 as the version of the Java bytecode generated by the Maven compiler.
   4. Use <exec.mainClass> to specify to execute the sample program com.oceanbase.example.InsertAndSelectExample in the project.

   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. Use <dependency> to define a dependency.
   2. Use <groupId> to specify com.oceanbase as the ID of the group that the dependency belongs to.
   3. Use <artifactId> to specify oceanbase-client as the dependency ID.
   4. Use <version> to specify 2.4.2 as the dependency version.

   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

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.

To configure the InsertAndSelectExample.java file, perform the following steps:

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 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 the class 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 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 Database, where

   • jdbc:oceanbase specifies to use the OceanBase Connector/J driver to connect to the database.
   • host:port specifies the IP address and port number of OceanBase Database.
   • opt1=val1&opt2=val2... indicates additional connection properties, or URL parameters. For more information about the additional connection properties of a URL, see Database URL.
   • schema_name specifies the name of the schema to access.
   • String user = "user_name" specifies the username required for logging on to the database.
   • String password = "******" specifies 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 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 with the name and age fields. If the table already exists, drop the existing one and then create a new one. 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 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 fields. The data type of the name field is varchar(50), and that of the age field 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. The steps are as follows:

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

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

   The sample code is as follows:

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

Complete code examples

<?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.