Connect to OceanBase Database by using MySQL Connector/J

You can connect to OceanBase Database by using the Java Database Connectivity (JDBC) driver of MySQL. This topic describes how to build a Java application that connects to and uses OceanBase Database by using MySQL Connector/J.

Download the mysql-connector/j sample project.

Prerequisites

• You have downloaded and installed IntelliJ IDEA.
• You have downloaded Java Development Kit (JDK) 1.8.0.
• You have downloaded Maven and configured it in IntelliJ IDEA.
• You have downloaded the sample project code.

Perform check after the installation

1. Check whether JDK has been installed.

   java -version
   

2. Check whether Maven has been installed.

   mvn -version
   

3. (Optional) Check whether Maven has been correctly configured in IntelliJ IDEA.

   This topic provides only a sample project for your reference. If you want to develop a more complex project, you can download Maven of a version suitable for your development environment and configure it in IntelliJ IDEA.

   1. Check whether Maven has been installed.

      mvn -version
      

   2. Open IntelliJ IDEA and choose IntelliJ IDEA > Settings > Build,Execution,Development > Build Tools > Maven. Set Maven home path to the installation path of Maven.

   

Procedure

1. Obtain the connection information of OceanBase Database.
2. Modify the downloaded sample project code.
3. Run the sample project code.

Build a Java application

Step 1: Obtain a database connection string

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

obclient -h$host -P$port -u$user_name -p$password -D$database_name

where

• $host indicates the IP address for connecting to OceanBase Database, which is the IP address of an OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port indicates 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.

• $database_name: the name of the database to be accessed.

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name indicates 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 indicates the password of the account.

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

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Modify the sample project code

1. Decompress the downloaded sample project.

2. Open IntelliJ IDEA and choose File > Open to add the sample project file.

3. Choose JDBCDemo > src > main > java > JDBCTest and modify the database connection parameters in the project code based on the connection string information obtained in Step 1.

   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=******`
   

   where

   • host indicates 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 indicates 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 indicates the name of the database to be accessed. The value of this parameter is obtained from the -D parameter.

   • username indicates 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 indicates the user password. The value of this parameter is obtained from the -p parameter.

   Here is an example of a snippet after modification:

   // Example
   Connection connection = DriverManager.getConnection("jdbc:mysql://100.88.xx.xx:2881/test?user=r***&password=******");
   

   Notice

   If MySQL Connector/J 8.x is used, make sure that the account password does not contain the number sign (#). Otherwise, an error will occur when you run the sample project.

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.

Here is an example of the complete code:

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

public class JDBCTest {

    public static void main(String[] args) {
        try {
            // Load the driver.
            Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
//            Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8

            // Create a connection.
            Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:2881/test?user=r***&password=");
            System.out.println("jdbc version : " + connection.getMetaData().getDriverVersion());
            Statement stmt = connection.createStatement();

            // Create a table.
            stmt.execute("drop table if exists test");
            stmt.execute("create table test (id int, name varchar(25))");
            System.out.println("create table successfully");

            // Insert data.
            stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')");
            System.out.println("insert data successfully");

            // Query data.
            System.out.println("query data : ");
            ResultSet rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            // Update data.
            stmt.execute("update test set name = 'bbb' where id = 1");
            System.out.println("update data successfully");

            // Query data after update.
            System.out.println("query data after update : ");
            rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            //Delete data.
            stmt.execute("delete from test where id = 1");
            System.out.println("delete data successfully");

            // Query data after deletion.
            System.out.println("query data after delete : ");
            rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            // Drop the table.
            stmt.execute("drop table test");
            //close
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            System.out.println("error!");
            e.printStackTrace();
        }
    }
}

Step 3: Run the application

Run the sample project in IntelliJ IDEA. If the result shown in the following figure is returned, the database is connected and the sample project is correctly executed.

Project code

Click mysql-connector/j to download the project code, which is a package named JDBCDemo.zip.

Decompress the package to obtain a folder named JDBCDemo. The directory structure is as follows:

JDBCDemo
├── src
│   └── main
│       └── java
│           └── JDBCTest.java
└── pom.xml

The files and directories are described as follows:

• src: the root directory that stores the source code.
• main: a directory that stores the main code, including the major logic of the application.
• java: a directory that stores the Java source code.
• JDBCTest.java: the main class that contains logic such as the table creation and data insertion logic.
• pom.xml: the configuration file of the Maven project, which is used to manage project dependencies and build settings.

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.

Perform the following steps to configure the pom.xml file:

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

   1. <groupId>: the ID of the group to which the project belongs, which is set to com.example.
   2. <artifactId>: the name of the project, which is set to JDBCDemo.
   3. <version>: the project version, which is set to 1.0-SNAPSHOT.

   The sample code is as follows:

       <groupId>org.example</groupId>
       <artifactId>JDBCDemo</artifactId>
       <version>1.0-SNAPSHOT</version>
   

4. Configure the components on which the project depends.

   1. Add the mysql-connector-java dependency library for interactions with the database, and configure the following parameters:

      Note

      The following code defines that the project depends on MySQL Connector/J V5.1.40. For more information about other versions, see MySQL Connector/J.

      1. <groupId>: the ID of the group to which the dependency belongs, which is set to mysql.
      2. <artifactId>: the name of the dependency, which is set to mysql-connector-java.
      3. <version>: the version of the dependency, which is set to 5.1.40.

      The sample code is as follows:

              <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>5.1.40</version>
              </dependency>
      

Code in the JDBCTest.java file

The JDBCTest.java file is a part of the sample application. Code in this file demonstrates how to perform database operations by using MySQL Connector/J. The code first configures the database connection information, and then creates a JDBCTest object to perform database operations. It also provides examples of creating tables, inserting data, and querying data.

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

1. Import the following Java classes required for using JDBC APIs in a Java application.

   1. Connection class for establishing a connection with the database.
   2. DriverManager class for managing a group of JDBC drivers.
   3. ResultSet class for processing data returned by SQL queries.
   4. Statement class for executing static SQL statements and returning results.

   The sample code is as follows:

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

2. Create a class named JDBCTest and define the main method.

   1. Define a public class named JDBCTest as the entry to the application. The class name must be the same as the file name.
   2. Define a public static method named main, which is used as the execution start point of the application.
   3. Define other database operations.

   The sample code is as follows:

   public class Main {
       public static void main(String[] args) {
           // Database connection information
           // Create a data source.
           // Create a table.
           // Insert data.
           // Update data.
           // Delete data.
       }
   }
   

3. Define the database connection information.

   1. Class.forName(): used for loading and registering the MySQL JDBC driver.
   2. DriverManager.getConnection(): used for defining the URL, username, and password for connecting to the database. You must replace $host, $port, $database_name, $user_name, and $password with the actual database connection information.

   The sample code is as follows:

       Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
       //Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8
       Connection connection = DriverManager.getConnection("jdbc:mysql://xx.xxx.xxx.xxx:2881/test?user=test@tt1&password=test");
   

   The parameters are described as follows:

   • $host: the IP address for connecting to OceanBase Database, which is the IP address of an OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.
   • $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.
   • $database_name: the name of the database to be accessed.
   • $user_name: 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: the password of the account.

4. Create and execute an SQL statement.

       Statement stmt = connection.createStatement();
       stmt.execute("...");
   

5. Create a table.

   The sample code is as follows:

       // Execute an SQL statement to drop the table named "test" if it exists.
       stmt.execute("drop table if exists test");
       // Create a table named "test" that contains two columns: "id" column of the integer type and "name" column of the character type.
       stmt.execute("create table test (id int, name varchar(25))");
               // Output a message indicating that the table is created.
       System.out.println("create table successfully");
   

6. Insert data.

   The sample code is as follows:

       // Insert two rows into the "test" table.
       stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')");
       // Output a message indicating that the data is inserted.
       System.out.println("insert data successfully");
   

7. Query data.

   The sample code is as follows:

       // Output a data query message.
       System.out.println("query data : ");
       // Execute an SQL statement to query all data in the "test" table.
       ResultSet rs = stmt.executeQuery("select * from test");
       // Traverse the result set.
       while (rs.next()) {
       // Output the "id" and "name" values of each data record.
       System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
   

8. Update data.

   The sample code is as follows:

       // Update the value of the "name" column to "bbb" for the record whose ID is "1" in the "test" table.
       stmt.execute("update test set name = 'bbb' where id = 1");
       // Output a message indicating that the data is updated.
       System.out.println("update data successfully");
   

9. Delete data.

   The sample code is as follows:

       // Drop the "test" table.
       stmt.execute("drop table test");
       // Close the result set, statement, and database connection.
       rs.close();
       stmt.close();
       connection.close();
   

10. Handle exceptions.

    Any exception that occurs during the preceding database operations will be captured, and the error information and stack tracing details will be output.

    The sample code is as follows:

        } catch (Exception e) {
        System.out.println("error!");
        e.printStackTrace();
        }
    

Complete code

<?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>org.example</groupId>
    <artifactId>JDBCDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.40</version>
        </dependency>
        <!--        <dependency>-->
        <!--            <groupId>com.mysql</groupId>-->
        <!--            <artifactId>mysql-connector-j</artifactId>-->
        <!--            <version>8.0.33</version>-->
        <!--        </dependency>-->
    </dependencies>
</project>

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

public class JDBCTest {

    public static void main(String[] args) {
        try {
            // Load the driver.
            Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
//            Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8

            // Create a connection.
            Connection connection = DriverManager.getConnection("jdbc:mysql://{host}:{port}/{dbname}?user={username}&password={******}");
            System.out.println("jdbc version : " + connection.getMetaData().getDriverVersion());
            Statement stmt = connection.createStatement();

            // Create a table.
            stmt.execute("drop table if exists test");
            stmt.execute("create table test (id int, name varchar(25))");
            System.out.println("create table successfully");

            // Insert data.
            stmt.execute("insert into test values (1, 'aaa'),(2, 'bbb')");
            System.out.println("insert data successfully");

            // Query data.
            System.out.println("query data : ");
            ResultSet rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            // Update data.
            stmt.execute("update test set name = 'bbb' where id = 1");
            System.out.println("update data successfully");

            // Query data after update.
            System.out.println("query data after update : ");
            rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            //Delete data.
            stmt.execute("delete from test where id = 1");
            System.out.println("delete data successfully");

            // Query data after deletion.
            System.out.println("query data after delete : ");
            rs = stmt.executeQuery("select * from test");
            while (rs.next()) {
                System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
            }

            // Drop the table.
            stmt.execute("drop table test");
            //close
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            System.out.println("error!");
            e.printStackTrace();
        }
    }
}

More information

For more information about how to build a Java application, see the sample Java application in the open source community of OceanBase Database.