Connect to OceanBase Database by using Testcontainers for Java

Testcontainers is a set of open-source Java libraries. It allows you to perform automatic integration tests by using a Docker container. This topic describes how to connect to and use OceanBase Database by using Testcontainers.

Download the TestcontainersDemo sample project.

Prerequisites

• You have downloaded, installed, and started Docker.
• You have downloaded and installed IntelliJ IDEA.
• You have downloaded Java Development Kit (JDK) 1.8.0.
• (Optional) You have downloaded Maven and configured it in IntelliJ IDEA.

Perform check after the installation

1. Check whether Docker has been installed.

   docker -v
   

   You do not need to manually start Docker. It will be automatically started or stopped when you run the sample application in subsequent steps.

2. Check whether JDK has been installed.

   java -version
   

3. Check whether Maven has been installed.

   mvn -version
   

4. (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. Decompress the downloaded sample project code and use IDEA to open the code.
2. Run the sample project ExampleTest in src > test > java.
3. If the following result is returned, the database is connected and the sample project is correctly executed.

...
53575 [main] INFO  com.oceanbase.example.ExampleTest - Connect to OceanBase docker container successfully.
53577 [main] INFO  com.oceanbase.example.ExampleTest - Prepare database and table.
53892 [main] INFO  com.oceanbase.example.ExampleTest - Insert data to table `testcontainers`.`person`.
53928 [main] INFO  com.oceanbase.example.ExampleTest - Query rows from `testcontainers`.`person`.
53932 [main] INFO  com.oceanbase.example.ExampleTest - Row 0: name Adam, age 28.
53932 [main] INFO  com.oceanbase.example.ExampleTest - Row 1: name Eve, age 26.

Project code

Click Testcontainers to download the project code, which is a package named TestcontainersDemo.zip.

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

JDBCDemo
├── src
│   └── test
│       └── java
│           └── ExampleTest.java
└── pom.xml

The files and directories are described as follows:

• src: the root directory that stores the source code.
• test: a directory that stores the main code, including the major logic of the application.
• java: a directory that stores the Java source code.
• ExampleTest.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 external packages that the project depends on. You need to configure groupId, artifactId, and version for each external package.

   1. <groupId>: specifies the organization to which the dependency belongs.
   2. <artifactId>: specifies the name of the dependency.
   3. <version>: specifies the version of the dependency.
   4. <scope>: specifies the effective scope of the dependency. The value test indicates that the dependency is used only in tests.

   The sample code is as follows:

   <dependencies>
       <dependency>
           //If you use the MySQL JDBC driver, you need to replace the values of `groupId`, `artifactId`, and `version`.
           <groupId>com.oceanbase</groupId>
           <artifactId>oceanbase-client</artifactId>
           <version>2.4.9</version>
       </dependency>
       <dependency>
           <groupId>org.testcontainers</groupId>
           <artifactId>oceanbase</artifactId>
           <version>1.19.7</version>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>org.apache.logging.log4j</groupId>
           <artifactId>log4j-slf4j-impl</artifactId>
           <version>2.17.1</version>
           <scope>test</scope>
       </dependency>
   </dependencies>
   

Code in the ExampleTest.java file

The ExampleTest.java file is a part of the sample application. It builds a test Java class based on the Testcontainers framework and JUnit.

1. It builds a test environment where Testcontainers is used to run an OceanBase container, and defines some basic settings for the test class.

   The sample code is as follows:

   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   // Define a test class.
   public class ExampleTest {
       // Create an SLF4J Logger instance to record logs of the ExampleTest class.
       private static final Logger LOG = LoggerFactory.getLogger(ExampleTest.class);
       // Create an OceanBase container instance, use the specified Docker image, and configure the environment variables.
       public static final OceanBaseCEContainer CONTAINER =
               new OceanBaseCEContainer("oceanbase/oceanbase-ce:4.2.2")
                       .withEnv("MODE", "slim")        // Set the value of the environment variable `MODE` to `slim`.
                       .withEnv("FASTBOOT", "true")    // Set the value of the environment variable `FASTBOOT` to `true`.
                       .withLogConsumer(new Slf4jLogConsumer(LOG)); // Add a log consumer and use SLF4J to record the container logs.
   
       // Execute this method before testing to prepare the environment.
       @BeforeClass
       public static void startContainers() {
           // Start the container asynchronously and wait for the container to start.
           Startables.deepStart(Stream.of(CONTAINER)).join();
           // Output information about the startup of the container, such as the name of the Docker image as well as the host address and port of the container.
           LOG.info(
                   "OceanBase docker container started, image: {}, host: {}, sql port: {}, rpc port:{}.",
                   CONTAINER.getDockerImageName(),
                   CONTAINER.getHost(),
                   CONTAINER.getMappedPort(2881),
                   CONTAINER.getMappedPort(2882));
       }
   
       // After all tests are completed, execute this method to clear the resources.
       @AfterClass
       public static void closeContainers() {
           // Close the container.
           CONTAINER.close();
           // Record a log indicating that the container is stopped.
           LOG.info("OceanBase docker container stopped.");
       }
       ```
   
   
   

2. Write the test logic.

   The sample code is as follows:

   // Test the add and query operations in the database.
   @Test
   public void test() {
       // Define the database and table names.
       String database = "testcontainers";
       String table = "person";
       // Format the complete names of the database and table. Use backticks (`) to avoid conflicting with SQL keywords.
       String tableName = String.format("`%s`.`%s`", database, table);
   
       // Output a log recording an attempt to connect to the OceanBase container.
       LOG.info(
               "Try to connect to OceanBase docker container with url: {}.",
               CONTAINER.getJdbcUrl());
       // Create a database connection with the OceanBase container.
       try (Connection connection = CONTAINER.createConnection("?useSSL=false")) {
           LOG.info("Connect to OceanBase docker container successfully.");
           LOG.info("Prepare database and table.");
   

3. Create a table.

   The sample code is as follows:

   // Create a database and a table.
       try (Statement statement = connection.createStatement()) {
           statement.execute("CREATE DATABASE IF NOT EXISTS " + database);
           statement.execute("USE " + database);
           statement.execute(
                   "CREATE TABLE IF NOT EXISTS " + table + " (name VARCHAR(50), age INT)");
       } catch (SQLException e) {
           throw new RuntimeException(e);
       }
   

4. Insert data.

   The sample code is as follows:

   LOG.info("Insert data to table {}.", tableName);
         try (PreparedStatement ps =
                 connection.prepareStatement("INSERT INTO " + tableName + " values(?, ?)")) {
             ps.setString(1, "Adam");
             ps.setInt(2, 28);
             ps.executeUpdate();
             ps.setString(1, "Eve");
             ps.setInt(2, 26);
             ps.executeUpdate();
         }
   

5. Query data.

   The sample code is as follows:

   LOG.info("Query rows from {}.", tableName);
       try (PreparedStatement ps =
               connection.prepareStatement(
                       "SELECT * from " + tableName,
                       ResultSet.TYPE_FORWARD_ONLY,
                       ResultSet.CONCUR_READ_ONLY)) {
           ResultSet rs = ps.executeQuery();
           int count = 0;
           while (rs.next()) {
               LOG.info("Row {}: name {}, age {}.", count++, rs.getString(1), rs.getInt(2));
           }
   

6. 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 (SQLException e) {
       // Throw a runtime exception if any.
       throw new RuntimeException(e);
   }
   

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>com.oceanbase.example</groupId>
    <artifactId>testcontainers-java</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.oceanbase</groupId>
            <artifactId>oceanbase-client</artifactId>
            <version>2.4.9</version>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>oceanbase</artifactId>
            <version>1.19.7</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.17.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

</project>

package com.oceanbase.example;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.testcontainers.containers.output.Slf4jLogConsumer;
import org.testcontainers.lifecycle.Startables;
import org.testcontainers.oceanbase.OceanBaseCEContainer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.stream.Stream;

public class ExampleTest {

    private static final Logger LOG = LoggerFactory.getLogger(ExampleTest.class);

    public static final OceanBaseCEContainer CONTAINER =
            new OceanBaseCEContainer("oceanbase/oceanbase-ce:4.2.2")
                    .withEnv("MODE", "slim")
                    .withEnv("FASTBOOT", "true")
                    .withLogConsumer(new Slf4jLogConsumer(LOG));

    @BeforeClass
    public static void startContainers() {
        Startables.deepStart(Stream.of(CONTAINER)).join();
        LOG.info(
                "OceanBase docker container started, image: {}, host: {}, sql port: {}, rpc port:{}.",
                CONTAINER.getDockerImageName(),
                CONTAINER.getHost(),
                CONTAINER.getMappedPort(2881),
                CONTAINER.getMappedPort(2882));
    }

    @AfterClass
    public static void closeContainers() {
        CONTAINER.close();
        LOG.info("OceanBase docker container stopped.");
    }

    @Test
    public void test() {
        String database = "testcontainers";
        String table = "person";
        String tableName = String.format("`%s`.`%s`", database, table);

        LOG.info(
                "Try to connect to OceanBase docker container with url: {}.",
                CONTAINER.getJdbcUrl());
        try (Connection connection = CONTAINER.createConnection("?useSSL=false")) {
            LOG.info("Connect to OceanBase docker container successfully.");

            LOG.info("Prepare database and table.");
            try (Statement statement = connection.createStatement()) {
                statement.execute("CREATE DATABASE IF NOT EXISTS " + database);
                statement.execute("USE " + database);
                statement.execute(
                        "CREATE TABLE IF NOT EXISTS " + table + " (name VARCHAR(50), age INT)");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

            LOG.info("Insert data to table {}.", tableName);
            try (PreparedStatement ps =
                    connection.prepareStatement("INSERT INTO " + tableName + " values(?, ?)")) {
                ps.setString(1, "Adam");
                ps.setInt(2, 28);
                ps.executeUpdate();
                ps.setString(1, "Eve");
                ps.setInt(2, 26);
                ps.executeUpdate();
            }

            LOG.info("Query rows from {}.", tableName);
            try (PreparedStatement ps =
                    connection.prepareStatement(
                            "SELECT * from " + tableName,
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY)) {
                ResultSet rs = ps.executeQuery();
                int count = 0;
                while (rs.next()) {
                    LOG.info("Row {}: name {}, age {}.", count++, rs.getString(1), rs.getInt(2));
                }
                assert count == 2;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

References

• Testcontainers for Java documentation.
• To use a Docker image of OceanBase Database, you need to use a generic container. For more information, see OceanBase Module.