Introduction to jOOQ

jOOQ Is an ORM framework that allows you to quickly build secure SQL statements with type constraints using Java code and a smooth API

jOOQ allows us to focus on business logic, while the basic interaction between Java and SQL can be handled by jOQ.jOOQ generally supports many databases, and there are differences between commercial and community versions. The main difference between commercial and community versions is that they support different databases, which can be used in Authorization Note Page As you can see above, the open source version only supports some open source databases, such as MySQL, which already meets the needs of most companies. This series of tutorials is also based on the MySQL database.

The core advantage of jOOQ is that you can map a database table structure to a Java class that contains the basic description of the table and all the table fields.The API provided by jOQ, together with the generated Java code, makes it easy to operate the database

Generated Java code field types are based on the Java type mapped to the database. When setting up and querying, because Java code has strong type checks, it is naturally safe for data input and greatly reduces the risk of SQL injection.

jOOQ's code generation strategy is to generate in full based on configuration. Any changes to the database that affect business code will be discovered during compilation and can be repaired in time

This series of articles relies on the following basic environments, not to mention that other dependencies will be added as part of the tutorial and will be noted in the project

• MySQL 5.6 or higher
• JDK 1.8
• jOOQ - 3.12.3
• JUnit 5
• Maven 3.6.0

How to start

When using jOOQ, the general development process is:

1. Create/update database tables
2. Generating Java code through jOOQ plug-ins
3. Develop business logic

Test Database

Import Initialization Database Script:

• https://github.com/k55k32/learn-jooq/blob/master/learn-jooq.sql
• https://github.com/k55k32/learn-jooq/blob/master/learn-jooq-2.sql

Maven Configuration

jOOQ provides the Maven plug-in jooq-codegen-maven, which can be configured to generate code. The configuration items are mainly jdbc connection, target database, table, and generated path package name.

<properties>
    <jooq.version>3.12.3</jooq.version>
</properties>

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.18</version>
    </dependency>

    <!-- base jooq dependency -->
    <dependency>
        <groupId>org.jooq</groupId>
        <artifactId>jooq</artifactId>
        <version>${jooq.version}</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>

        <!-- Code Generator Plug-in -->
        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>${jooq.version}</version>
            <configuration>
                <jdbc>
                    <driver>com.mysql.cj.jdbc.Driver</driver>
                    <url>jdbc:mysql://127.0.0.1:3306/learn-jooq?serverTimezone=GMT%2B8</url>
                    <user>root</user>
                    <password>root</password>
                </jdbc>
                <generator>
                    <database>
                        <includes>s1-.*</includes>
                        <inputSchema>learn-jooq</inputSchema>
                    </database>
                    <target>
                        <packageName>com.diamondfsd.jooq.learn.codegen</packageName>
                        <directory>/src/main/java</directory>
                    </target>
                </generator>
            </configuration>
        </plugin>
    </plugins>
</build>

code generation

Code generation works by reading the metadata of the database, converting it to Java code, and generating the specified file, which is stored in the configured directory.

The target path of jOQs generated code suggests configuring separate subpackages, as each code generation is full and will be deleted by the generator if mixed with other business code

# This command allows you to call the jooq-codegen-maven plug-in for code generation
mvn jooq-codegen:generate

Once the code generator is executed, the following directories are generated:

├─src/main/java/.../codegen ---- // Generate Path
│ ├─tables --------------------- // Table Definition Directory
│ │ ├─S1User ------------------- // The s1_user table description contains: field, primary key, index, belonging Schema
│ │ └─records ------------------ // Table Operations Object Directory
│ │   └─S1UserRecord ----------- // s1_user table action object, containing field get,set methods
│ ├─DefaultCatalog ------------- // Catalog object, containing Schema constant
│ ├─Indexes -------------------- // All constants in the current database
│ ├─Keys ----------------------- // Constants such as primary key, unique index, etc. for all tables in the current database
│ ├─LearnJooq ------------------ // Database `learn-jooq`constant, containing all table description constants for the library
│ └─Tables --------------------- // All database table constants

Basic Query Operations

public static void main(String[] args) {

    String jdbcUrl = "jdbc:mysql://localhost:3306/learn-jooq?serverTimezone=GMT%2B8";
    String jdbcUsername = "root";
    String jdbcPassword = "root";

    // Get JDBC Link
    try (Connection connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)) {
        // Get jOOQ Executor
        DSLContext dslContext = DSL.using(connection, SQLDialect.MYSQL);

        // The fetch method returns a result set object Result
        // The Result object of jOQ implements the List interface and can be used directly as a collection
        Result<Record> recordResult = dslContext.select().from(S1_USER).fetch();
        recordResult.forEach(record -> {
            Integer id = record.getValue(S1_USER.ID);
            String username = record.getValue(S1_USER.USERNAME);
            System.out.println("fetch Record     id: " + id + " , username: " + username);
        });

        // The Record.into method allows you to convert the default Record object to a Table Record object, such as S1UserRecord
        // The Result interface also defines an into method that converts the entire result set to the result set of the specified table Record
        // Table objects can be obtained directly from S1UserRecord by get method
        // XXXRecord objects for all tables are subclasses that implement Record objects
        Result<S1UserRecord> userRecordResult = recordResult.into(S1_USER);
        userRecordResult.forEach(record -> {
            Integer id = record.getId();
            String username = record.getUsername();
            System.out.println("into S1UserRecord   id: " + id + " , username: " + username);
        });

        // The fetchInto method can pass in any class type or table constant
        // Returns a List collection of any class type directly, or specifies a result set object for Table Record
        List<S1UserRecord> fetchIntoClassResultList = dslContext.select().from(S1_USER).fetchInto(S1UserRecord.class);
        Result<S1UserRecord> fetchIntoTableResultList = dslContext.select().from(S1_USER).fetchInto(S1_USER);

        System.out.println("fetchIntoClassResultList: \n" + fetchIntoClassResultList.toString());
        System.out.println("fetchIntoTableResultList: \n" + fetchIntoTableResultList.toString());

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The above code will output:

fetch Record     id: 1 , username: demo1
fetch Record     id: 2 , username: admin1
into S1UserRecord   id: 1 , username: demo1
into S1UserRecord   id: 2 , username: admin1
fetchIntoClassResultList: 
[+----+--------+--------------------+------------------------+---------------------+---------------------+
|  id|username|email               |address                 |create_time          |update_time          |
+----+--------+--------------------+------------------------+---------------------+---------------------+
|   1|demo1   |demo1@diamondfds.com|China Guangdong Shenzhen|2019-12-27 16:41:42.0|2019-12-27 16:41:42.0|
+----+--------+--------------------+------------------------+---------------------+---------------------+
, +----+--------+---------------------------+-------------------------+---------------------+---------------------+
|  id|username|email                      |address                  |create_time          |update_time          |
+----+--------+---------------------------+-------------------------+---------------------+---------------------+
|   2|admin1  |admin1@diamondfsd@gmail.com|China Guanddong Guangzhou|2019-12-27 16:41:42.0|2019-12-27 16:41:42.0|
+----+--------+---------------------------+-------------------------+---------------------+---------------------+
]
fetchIntoTableResultList: 
+----+--------+---------------------------+-------------------------+---------------------+---------------------+
|  id|username|email                      |address                  |create_time          |update_time          |
+----+--------+---------------------------+-------------------------+---------------------+---------------------+
|   1|demo1   |demo1@diamondfds.com       |China Guangdong Shenzhen |2019-12-27 16:41:42.0|2019-12-27 16:41:42.0|
|   2|admin1  |admin1@diamondfsd@gmail.com|China Guanddong Guangzhou|2019-12-27 16:41:42.0|2019-12-27 16:41:42.0|
+----+--------+---------------------------+-------------------------+---------------------+---------------------+

Content Summary

Source code for this chapter: https://github.com/k55k32/learn-jooq/tree/master/section-1

This section mainly introduces the most basic usage of jOQ, mainly want to introduce several interfaces for you:

• org.jooq.Result
  Result Set Interface, which implements the List interface and can be operated as a collection, is a wrapper class for the result set of a database query. In addition to the related methods of the collection, the interface also provides some methods such as result set conversion, formatting, extracting fields and so on.Usually the result of our query is the implementation class of this interface. Mastering this interface is the basic interface of jOQ. This interface will be encountered by all the basic SQL query operations.

• org.jooq.Record
  When using relational databases, this interface is mainly used to define database table records. The contents stored are the fields and values of a table record. Each value stores the type of corresponding field. You can get the values of corresponding fields through the general getValue (Field) method, or you can think of this interface as a field/value mapping of a record.

Once the code generator is used, an implementation class for the corresponding table is generated based on this interface, which generates a get/set method for all fields based on the database table fields, and the specified values can be intuitively obtained or set by getXXX/setXXX(..) for subsequent operations such as reading/updating.Code readability is greatly improved for encoding

• org.jooq.DSLContext
  One of the core interfaces of jOOQ, can be understood as a SQL executor, through the static method DSL.using, you can get an instance of DSLContext, which abstracts all the operation APIs for SQL and makes it easy to operate SQL through the APIs it provides

  /**
  * Create an executor object through database connections and dialect configuration
  * @param connection Database Connection
  * @param dialect Specifies the dialect, and this parameter is passed in to generate the SQL statement string using different syntax rules according to the SQL dialect configuration when jOQ renders the SQL statement.
  */
  public static DSLContext using(Connection connection, SQLDialect dialect) {
      return new DefaultDSLContext(connection, dialect, null);
  }