Connect to OceanBase Database with jOOQ|OceanBase Database|docs|OceanBase Documentations

jOOQ is a framework that models SQL as a type-safe Java API. It generates type-safe Java classes from the database schema using a code generator. This topic describes how to connect to OceanBase Database by using jOOQ, including Maven configuration, code generation, and usage examples. jOOQ is designed for OceanBase Database and leverages its MySQL compatibility to provide type-safe database operations. It is suitable for Java application development scenarios that require type-safe SQL queries, complex business logic, and database schema management.

Core features
- Type safety: jOOQ generates type-safe Java classes from the database schema using a code generator, eliminating the risk of SQL injection.
- OceanBase compatibility: jOOQ uses the official OceanBase JDBC driver, which is fully compatible with the MySQL protocol.
- Connection pool optimization: jOOQ integrates with HikariCP connection pools to provide high-performance database connection management.
- Code generation: jOOQ automatically generates Java classes corresponding to the table structure, including Record, POJO, and DAO classes.
- Chained API: jOOQ supports fluent chaining, making SQL writing more intuitive.
Use cases
- Java applications that require type-safe SQL queries
- Database operations involving complex business logic
- Projects with frequent database schema changes
- Enterprise applications requiring high-performance database access

Version compatibility

OceanBase Database version: ≥ V4.2.5
JDK version: 17 or later (JDK21 is used as an example in this topic)
Maven version: 3.6

Prerequisites

Before you use jOOQ, make sure that:

You have installed jOOQ. For more information, see Install jOOQ.
You have deployed OceanBase Database and created a MySQL user tenant.
You have installed JDK 17 or a later version.
You have installed Maven 3.6 or a later version.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment personnel to obtain the connection string, for example:

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

Parameter description:

$host: the connection IP address. For ODP connection, use the ODP address. For direct connection, use the OBServer IP address.
$port: the connection port. For ODP connection, the default value is 2883. For direct connection, the default value is 2881.
$database_name: the database name.

Notice

The user used to connect to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database.
$user_name: the connection account. For ODP connection, the format is user@tenant#cluster or cluster:tenant:user. For direct connection, the format is user@tenant.
$password: the account password.

Here is an example:

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

Step 2: Create a sample table

To demonstrate jOOQ code generation and query operations, create a sample table in the target database.

CREATE TABLE students (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  age TINYINT UNSIGNED NOT NULL
);

You can insert test data as needed, for example:

INSERT INTO students (name, age) VALUES
('Zhang San', 20),
('Li Si', 22),
('Wang Wu', 18);

After the table is created, jOOQ generates corresponding Java classes based on the table structure.

Step 3: Configure the Maven project

The sample project provided in this topic is a complete Maven project. It uses jOOQ V3.21.1 and OceanBase JDBC V2.4.3 to demonstrate how to integrate jOOQ with OceanBase Database. The project contains the following key components:

Maven configuration: Defines the dependencies for jOOQ, OceanBase Database JDBC driver, HikariCP connection pool, and SLF4J logging.
jOOQ code generation plugin configuration: Configures the parameters for connecting to OceanBase Database and the code generation strategy.
Java application code: Demonstrates how to use the generated type-safe API for database operations.

After you create the Maven project, edit the pom.xml file in the project root directory and add the configurations for jOOQ, OceanBase Database JDBC driver, connection pool, and code generation plugin.

<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.example</groupId>
  <artifactId>jooq-oceanbase-demo</artifactId>
  <version>1.0.0</version>
  <packaging>jar</packaging>

  <properties>
    <!-- Version definitions -->
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
    <jooq.version>3.21.1</jooq.version>
    <oceanbase.driver.version>2.4.3</oceanbase.driver.version>
    <hikari.version>5.1.0</hikari.version>
  </properties>

  <dependencies>
    <!-- 1. Core jOOQ runtime dependency -->
    <dependency>
      <groupId>org.jooq</groupId>
      <artifactId>jooq</artifactId>
      <version>${jooq.version}</version>
    </dependency>

    <!-- 2. OceanBase Database JDBC driver (officially recommended) -->
    <!-- Note: Fully compatible with MySQL protocol and optimized for OceanBase Database -->
    <dependency>
      <groupId>com.oceanbase</groupId>
      <artifactId>oceanbase-client</artifactId>
      <version>${oceanbase.driver.version}</version>
    </dependency>

    <!-- 3. Database connection pool (HikariCP is recommended for production environments) -->
    <dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>${hikari.version}</version>
    </dependency>

    <!-- 4. Logging dependency (jOOQ requires an SLF4J implementation) -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-simple</artifactId>
      <version>2.0.9</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <!-- ========================================== -->
      <!-- Plugin A: jOOQ code generator            -->
      <!-- ========================================== -->
      <plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>
        <version>${jooq.version}</version>

        <executions>
          <execution>
            <id>generate-oceanbase-sources</id>
            <phase>generate-sources</phase>
            <goals>
              <goal>generate</goal>
            </goals>
          </execution>
        </executions>

        <configuration>
          <!-- [Key] JDBC connection configuration -->
          <jdbc>
            <!-- Driver class: Use the official OceanBase Database driver -->
            <driver>com.oceanbase.jdbc.Driver</driver>

            <!--
            Connection URL format:
            jdbc:oceanbase://<HOST>:<PORT>/<DATABASE>?params
            You can also use jdbc:mysql://... (fully compatible)
            -->
            <url>jdbc:oceanbase://$host:$port/$database_name?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true</url>

                        <!-- Username (In OceanBase Database tenant mode, the username is usually in the format of user@tenant) -->
                        <user>$user_name</user>

                        <!-- Password -->
                        <password>$password</password>
                    </jdbc>

                    <!-- Code generation strategy configuration -->
                    <generator>
                        <database>
                            <!--
                              [Core configuration] Specify the dialect as MySQL
                              OceanBase MySQL mode must use org.jooq.meta.mysql.MySQLDatabase
                            -->
                            <name>org.jooq.meta.mysql.MySQLDatabase</name>

                            <!-- Tables to include (regular expression), .* means all tables -->
                            <includes>.*</includes>

                            <!-- Tables to exclude (regular expression), for example, exclude migration history tables -->
                            <excludes>flyway_schema_history|__migrate_.*</excludes>

                            <!-- Target schema (i.e., database name) -->
                            <inputSchema>$database_name</inputSchema>

                            <!-- Optional: Force type mapping (e.g., map TINYINT(1) to BOOLEAN) -->
                            <forcedTypes>
                                <forcedType>
                                    <name>BOOLEAN</name>
                                    <includeExpression>(?i).*is_.*</includeExpression>
                                    <includeTypes>TINYINT</includeTypes>
                                </forcedType>
                            </forcedTypes>
                        </database>

                        <target>
                            <!-- The package name for the generated Java code -->
                            <packageName>com.example.generated</packageName>
                            <!-- The output directory for the generated code -->
                            <directory>target/generated-sources/jooq</directory>
                        </target>

                        <!-- Generate switches -->
                        <generate>
                            <records>true</records>       <!-- Generate Record classes -->
                            <pojos>true</pojos>           <!-- Generate POJO classes -->
                            <daos>true</daos>             <!-- Generate DAO classes -->
                            <fluentSetters>true</fluentSetters> <!-- Enable fluent setters -->
                            <javaTimeTypes>true</javaTimeTypes> <!-- Use java.time (JSR-310) -->
                            <deprecated>false</deprecated>
                        </generate>
                    </generator>
                </configuration>

                <!--
                  [Important] Ensure that the code generation plugin can load the OceanBase driver
                  If this is not added, mvn generate-sources may throw a ClassNotFoundException
                -->
                <dependencies>
                    <dependency>
                        <groupId>com.oceanbase</groupId>
                        <artifactId>oceanbase-client</artifactId>
                        <version>${oceanbase.driver.version}</version>
                    </dependency>
                </dependencies>
            </plugin>

            <!-- ========================================== -->
            <!-- Plugin B: Build Helper (Add generated code to the build path) -->
            <!-- ========================================== -->
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>build-helper-maven-plugin</artifactId>
                <version>3.4.0</version>
                <executions>
                    <execution>
                        <id>add-generated-source</id>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>add-source</goal>
                        </goals>
                        <configuration>
                            <sources>
                                <source>target/generated-sources/jooq</source>
                            </sources>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

Description:

Edit the <jdbc> section of the pom.xml file:

**<url>**: Replace it with the IP address, port number, and database name of your OceanBase database.
**<user>**: Replace it with your username (make sure to use the OceanBase tenant format, e.g., user@tenant).
**<password>**: Replace with your password.
**<inputSchema>**: Replace this placeholder with the name of the database for which you want to generate code.
**<packageName>**: Replace it with your project's actual package name.

Step 4: Execute the jOOQ code generation

Run the following command in the project root directory. Maven will connect to OceanBase, read the metadata, and generate type-safe Java classes.

mvn clean compile

You can also run the following command to execute the code generation:

mvn jooq-codegen:generate

Step 5: Write and run the sample program

After the code generation is completed, you can use the generated API to operate OceanBase Database. The following sample program demonstrates how to query the students table and insert a new record.

package com.example;

import static com.example.generated.tables.Students.STUDENTS;

import com.example.generated.tables.records.StudentsRecord;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.util.List;

import javax.sql.DataSource;

import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.jooq.types.UByte;

public class OceanBaseApp {

    public static void main(String[] args) {
        // 1. Configure the data source
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:oceanbase://$host:$port/$database_name?useSSL=false");
        config.setUsername("$user_name");
        config.setPassword("$password");
        config.setDriverClassName("com.oceanbase.jdbc.Driver");

        // Connection pool optimization recommendation
        config.setMaximumPoolSize(10);
        DataSource dataSource = new HikariDataSource(config);

        // 2. Create a DSLContext
        // [Key point] Even though the underlying database is OceanBase, you must use SQLDialect.MYSQL here.
        DSLContext dsl = DSL.using(dataSource, SQLDialect.MYSQL);

        // 3. Execute type-safe queries
        List<StudentsRecord> students = dsl
            .selectFrom(STUDENTS)
            .where(STUDENTS.AGE.gt(UByte.valueOf(18)))
            .orderBy(STUDENTS.ID.desc())
            .limit(10)
            .fetch();

        // 4. Process the results
        for (StudentsRecord student : students) {
            System.out.println("ID: " + student.getId() + ", Name: " + student.getName());
        }

        // 5. Insert data (automatically obtain the auto-incremented primary key)
        StudentsRecord newStudent = dsl.newRecord(STUDENTS);
        newStudent.setName("Alice");
        newStudent.setAge(UByte.valueOf(25));
        newStudent.store(); // Execute the INSERT statement

        System.out.println("Generated ID: " + newStudent.getId());
    }
}

Configuration notes:

Edit the config section in the code example:

**JdbcUrl**: Replace it with the address, port, and database name of your OceanBase Database.
**Username**: Replace it with your username. Note that the tenant name of OceanBase Database is in the user@tenant format.
**Password**: Replace it with your password.

Run the following command to operate OceanBase Database:

mvn exec:java -Dexec.mainClass="com.example.OceanBaseApp"

Result verification

Verify the generated results

Check the target/generated-sources/jooq directory. You should see files with a structure similar to the following:

com/example/generated/Tables.java (static reference entry for tables)
com/example/generated/tables/User.java (assuming you have a user table)
com/example/generated/tables/records/UserRecord.java (record class)
com/example/generated/tables/pojos/User.java (POJO class)
com/example/generated/tables/daos/UserDao.java (DAO class)

Verify the execution results

After successfully running the program, you should see output similar to the following:

ID: 20, Name: You Er'er
ID: 18, Name: Zhu Ershi
ID: 16, Name: Han Shiqi
ID: 15, Name: Shen Shiqi
ID: 13, Name: Wei Shiwu
ID: 12, Name: Chu Shi
ID: 10, Name: Feng Shier
ID: 8, Name: Zheng Shi
ID: 7, Name: Wu Ji

Generated ID: 21