Connect to an OceanBase database by using a JDBC driver

You can use the official MySQL Java Database Connectivity (JDBC) driver to connect to OceanBase Database. This topic describes how to connect to and use an OceanBase database by using MySQL Connector/J.

Prerequisites

Java Development Kit (JDK) 8 or later is installed.
Install MySQL Connector/J and configure the runtime environment.
We recommend that you use MySQL Connector/J 5.1.47. For more information about how to download and install MySQL Connector/J, see Download Connector/J and Install Connector/J.

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****** -Dtest

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 3306 in MySQL mode.
-u: the tenant account.
-p: the account password.
-D: the database name.

Step 2: Write the sample code

The following text takes the MySQL Connector/J 5.1.47 driver for Linux as an example.

After you install MySQL Connector/J 5.1.47 and configure the runtime environment, you can use the sample code in the Test.java file for database connection.

Important

For MySQL Connector/J 8.x, replace com.mysql.jdbc.Driver in Class.forName("com.mysql.jdbc.Driver") with com.mysql.cj.jdbc.Driver.

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

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

            Class.forName("com.mysql.jdbc.Driver").newInstance();

            try{
                
                Connection connection = DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?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 se){
                System.out.println("error!");
                se.printStackTrace() ;
            }

          }catch (Exception ex) {
              ex.printStackTrace();
        }
    }
}

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

connection = DriverManager.getConnection("jdbc:mysql://{host}:{port}/{dbname}?user={username}&password={password}")

host: the domain name of the OceanBase database to be connected, which corresponds to the -h option.
port: the port for connecting to the OceanBase database, which corresponds to the -P option. By default, the port is 3306 in MySQL mode.
dbname: the database name, which corresponds to the -D option.
username: the tenant account, which corresponds to the -u option.
password: the tenant account password, which corresponds to the -p option.

Example:

jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?user=a****&password=******

Step 3: Run the sample code

After you edit the code, take the following steps to edit and execute the code:

Configure temporary environment variables according to the actual installation path of the JAR package.
```
$ export CLASSPATH=/usr/share/java/mysql-connector-java-5.1.47.jar:$CLASSPATH
```
Compile the Test.java file.
```
$ javac Test.java
```
Run the sample statement.
```
$ java Test
```
If the following result is returned, the database is connected and the sample statement is executed correctly.
```
true
an,1
```