Build a Java application

This topic describes how to build a Java application with OceanBase Database and MySQL Connector/J. MySQL Connector/J is an official Java Database Connectivity (JDBC) driver of MySQL.

Prerequisites

• You have installed Java JDK 8 or later for the Java environment.

• You have installed MySQL Connector/J and configured the operating 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 Connector/J Downloads and Connector/J Installation.

Procedure

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. For example:

obclient  -h100.88.xx.xx -uroot@test -p****** -P2881 -Doceanbase

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log on to OceanBase Database by using the database connection string, to verify that the parameters are correct.

The parameters are described as follows:

• -h: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an OceanBase Database Proxy (ODP).
• -u: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The default tenant of a cluster is sys, and the default administrator of a tenant is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.
• -p: the user password.
• -P: the port for connecting to OceanBase Database, which is also the listening port of the ODP.
• -D: the name of the database to be accessed.

Step 2: Write an application

The following example shows how to use Connector/J 5.1.47 to connect to an OceanBase database in Linux.

After you install MySQL Connector/J 5.1.47 and configure the operating environment, you can connect to and use the database based on the sample code in the Test.java file.

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://127.0.0.1:2881/test?user=r***&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. The parameters are concatenated, and the values of the parameters are from the database connection string obtained in Step 1.

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

//Example
jdbc:mysql://100.88.xx.xx:2881/test?user=r***&password=******`

• host: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an ODP. The value of this parameter is obtained from the -h parameter.

• port: the port for connecting to OceanBase Database, which is also the listening port of the ODP. The value of this parameter is obtained from the -P parameter.

• dbname: the name of the database to be accessed. The value of this parameter is obtained from the -D parameter.

• username: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The value of this parameter is obtained from the -u parameter. The default tenant of a cluster is sys, and the default administrator of a tenant is root. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.

• password: the user password. The value of this parameter is obtained from the -p parameter.

Step 3: Run the application

After you edit the code, run the following command:

# Configure a temporary environment based on the actual installation path of mysql-connector-java-5.1.47.jar.
export CLASSPATH=/usr/share/java/mysql-connector-java-5.1.47.jar:$CLASSPATH
# Compile the code.
javac Test.java

After the compilation is completed, execute the Test script:

java Test
# If the following results are returned, you have connected to OceanBase Database, and the sample script is correctly executed.
true
an,1