ApsaraDB for OceanBase:Connect to an OceanBase database by using a JDBC driver

Prerequisites

• The basic database development environment is set.

• Java Development Kit (JDK) 8 is installed.

• Obtain the OceanBase Connector/J driver. Visit the official website of OceanBase and choose Resources > Download > OceanBase Cloud > Middleware. Click a version in OceanBase Connector/J to download.

Connect to an OceanBase database by using a JDBC driver

Step 1: Obtain the database connection parameters

For more information, see Obtain the connection parameters. Example:

$ obclient -hxxx.xxx.xxx.xxx -P3306 -u a**** -p******

The database connection parameters specify the information required to access the database. You can verify the database connection parameters by logging on to the database before using them in the sample code.

Options:

• -h: the domain name of the OceanBase database to be connected.

• -P: the port for connecting to the OceanBase database. By default, the port is 1521 in Oracle mode.

• -u: the tenant account.

• -p: the account password.

Step 2: Install OceanBase Connector/J

Decompress the JAR package of OceanBase Connector/J to the local /usr/share/java path and set the temporary environment variables.

$ mv ./oceanbase-client-{version}.jar /usr/share/java

Set the temporary environment variables.

$ export CLASSPATH=/usr/share/java/oceanbase-client-{version}.jar:$CLASSPATH

Step 3: Write the sample code

Compile the Java file Test.java.

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

public class Test {
    public static void main(String[] args) {
        try {

            Class.forName("com.oceanbase.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:oceanbase://xxx.xxx.xxx.xxx:3306/?pool=false&user=a****&password=******");
            System.out.println(connection.getAutoCommit());
            Statement sm = connection.createStatement();
            // Create a table named t_meta_form.
            sm.executeUpdate("CREATE TABLE t_meta_form (name varchar(36) , id int)");
            // Insert data.
            sm.executeUpdate("insert into t_meta_form values ('an','1')");
            // Query data and output the results.
            ResultSet rs = sm.executeQuery("select * from t_meta_form");
            while (rs.next()) {
                String name = rs.getString("name");
                String id = rs.getString("id");
                System.out.println(name + ','+ id);
            }
            // Drop the table.
            sm.executeUpdate("drop table t_meta_form");
        }catch (SQLException ex) {
            System.out.println("error!");
            ex.printStackTrace() ;
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

Modify the database connection parameters in the code. Refer to the following parameters and format. The parameter values are obtained in Step 1.

• url: the domain name and port number of the OceanBase database to be connected, which correspond to the -h and -P options. Example: jdbc:oceanbase://IP:port/?pool=false. For an Oracle tenant, the default port number is 1521.

• user: the tenant account, which corresponds to the -u option.

• password: the tenant account password, which corresponds to the -p option.

Step 4: Execute the sample code

1. After you edit the code, run the following command:

   $ javac Test.java

2. Run the code.

   $ java Test

3. If the following result is returned, the database is connected and the sample statement is executed correctly.

   true
   an,1