Connect to OceanBase Database by using MySQL Connector/J

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

Click to download the mysql-connector/j sample project

Prerequisites

• You have downloaded and installed IntelliJ IDEA.
• You have downloaded JDK 1.8.0.
• You have downloaded Maven and configured it in IntelliJ IDEA.
• You have downloaded the sample project code used in this topic.

Version compatibility

Features

• Supports the standard JDBC API.
• Provides connection pool management.
• Supports SSL connections.
• Supports batch operations.
• Supports prepared statements.

Post-installation check

1. Check whether JDK is installed successfully:

   java -version
   

2. Check whether Maven is installed successfully:

   mvn -version
   

3. (Optional) Check whether Maven is 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 is installed successfully:

      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 sample project code that you downloaded.
3. Run the sample project code.

Create a Java application

Step 1: Obtain the database connection string

Obtain the database connection string from an OceanBase deployment engineer or administrator. Example:

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

Parameter description:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

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

  Notice

  The user used to connect to the 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: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the account password.

For more information about connection strings, 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. Start IntelliJ IDEA and click File > Open to add the project files.

3. Modify the database connection parameters in the JDBCDemo > src > main > java > JDBCTest file based on the connection string obtained in Step 1.

   Modify the database connection parameters in the code. Use the following template and concatenate the corresponding values based on the 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 the OceanBase Database or the ODP, which is obtained based on the -h parameter.

   • port: the port for connecting to the OceanBase Database or the ODP, which is obtained based on the -P parameter.

   • dbname: the name of the database to be accessed, which is obtained based on the -D parameter.

   • username: the username for connecting to the tenant, which is obtained based on the -u parameter. The format of the username is 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 the database, but is required when you connect to the database through an ODP.

   • password: the user password, which is obtained based on the -p parameter.

   Example of modified code:

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

   Notice

   If you are using MySQL Connector/J 8.x, do not include the # character in the account or password. Otherwise, an error occurs while running the sample project.

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

Sample 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 driver
            Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
//            Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8

            //create 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 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 delete
            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 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 following result is returned, the database is connected and the sample project is correctly executed.

Project code introduction

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

After decompressing it, you will find a folder named JDBCDemo. The directory structure is as follows:

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

File description:

• src: the root directory for source code.
• main: the main code directory, containing the core logic of the application.
• java: the directory for storing the Java source code.
• JDBCTest.java: the main class that contains logic such as table creation and data insertion.
• pom.xml: the configuration file of the Maven project, used to manage project dependencies and build settings.

Introduction to pom.xml

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

The pom.xml file in this topic mainly includes the following sections:

1. Declaration statements.

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

   Sample code:

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

2. Configure namespaces and the POM model version.

   1. Use xmlns to set the POM namespace to http://maven.apache.org/POM/4.0.0.
   2. Use xmlns:xsi to set the XML namespace to http://www.w3.org/2001/XMLSchema-instance.
   3. Use xsi:schemaLocation to set the POM namespace to http://maven.apache.org/POM/4.0.0, and the URI of the corresponding XSD file to http://maven.apache.org/xsd/maven-4.0.0.xsd.
   4. Use <modelVersion> to set the POM model version used by the POM file to 4.0.0.

   Sample code:

   <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. Use <groupId> to specify the project group as com.example.
   2. Use <artifactId> to specify the project name as JDBCDemo.
   3. Use <version> to specify the project version as 1.0-SNAPSHOT.

   Sample code:

       <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 interaction with the database, and configure the following parameters:

      Note

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

      1. Use <groupId> to specify the dependency group as mysql.
      2. Use <artifactId> to specify the dependency name as mysql-connector-java.
      3. Use <version> to specify the dependency version as 5.1.40.

      Sample code:

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

Introduction to JDBCTest.java

The JDBCTest.java file is part of the sample application and demonstrates how to use MySQL Connector/J to perform database operations. The file first configures the database connection information, then creates a JDBCTest object to perform database operations. The sample application also includes examples of creating tables, inserting data, and querying data.

The code in this topic for the JDBCTest.java file consists of the following sections:

1. Import the Java classes required for using the JDBC API in your Java program.

   1. Connection: a Java class that is used to establish a connection with the database.
   2. DriverManager: a Java class that is used to manage a set of JDBC drivers.
   3. ResultSet: a Java class that is used to process the data returned by SQL queries.
   4. Statement: a Java class that is used to execute static SQL statements and return the results.

   Sample code:

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

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

   1. Define a public class named JDBCTest, which serves as the entry point of the program. The class name must be the same as the file name.
   2. Define a public static method named main, which serves as the starting execution point of the program.
   3. Other database operations.

   Sample code:

   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. Use Class.forName() to load and register the MySQL JDBC driver.
   2. Use DriverManager.getConnection() to define the URL, username, and password for connecting to the database. Replace $host, $port, $database_name, $user_name, and $password with the actual database connection information.

   Sample code:

       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");
   

   Parameter description:

   • $host: the IP address for connecting to OceanBase Database. For connection through ODP, use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.
   • $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 format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.
   • $password: the password of the account.

4. Create and execute an SQL statement.

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

5. Create a table.

   Sample code:

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

6. Insert data.

   Sample code:

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

7. Query data.

   Sample code:

       // Print a message indicating data querying.
       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()) {
       // Print the "id" and "name" field values of each data record.
       System.out.println(rs.getString("id") + "\t" + rs.getString("name"));
   

8. Update data.

   Sample code:

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

9. Delete data.

   Sample code:

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

10. Exception handling.

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

    Sample code:

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

Full 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 driver
            Class.forName("com.mysql.jdbc.Driver"); //mysql-jdbc 5
//            Class.forName("com.mysql.cj.jdbc.Driver"); //mysql-jdbc 8

            //create 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 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 delete
            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 table
            stmt.execute("drop table test");
            //close
            rs.close();
            stmt.close();
            connection.close();
        } catch (Exception e) {
            System.out.println("error!");
            e.printStackTrace();
        }
    }
}

Best practices

Batch operations

Use batch processing to improve performance:

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO test VALUES (?, ?)");
for (int i = 0; i < 1000; i++) {
    pstmt.setInt(1, i);
    pstmt.setString(2, "name" + i);
    pstmt.addBatch();
}
pstmt.executeBatch();

Prepared statements

Use prepared statements to prevent SQL injection:

PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM test WHERE id = ?");
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();

FAQ

1. Connection timeout

Specify the connection timeout parameter as follows:

jdbc:mysql://host:port/database?connectTimeout=30000&socketTimeout=60000

2. Character set issue

Specify the correct character set as follows:

jdbc:mysql://host:port/database?characterEncoding=utf8&useUnicode=true

3. SSL connection

Enable SSL connection as follows:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true

4. Special characters in account name or password

If the account name or password contains special characters such as #, URL encode the account name or password.

String encodedPassword = URLEncoder.encode(password, "UTF-8");

More information

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