Basic CURD operations can be completed through the DSLContext API and Record API. This article mainly explains the most basic usage through some example codes. Some extensions and other advanced uses will be mentioned later

From then on, the following code block will not write the creation process of DSLContext in detail. For details, see section-1 Basic initialization method explained in

• DSLContext represents the instance of DSLContext
• S1 Uuser table description constants generated by the jOOQ plug-in
• S1 ﹐ user. * in table field constants generated by the jOOQ plug-in

Insert

There are two ways of data operation in jOOQ. The first is to use the DSLContext API to call in SQL like syntax. The second is to use the Record API to call

Quasi SQL mode

The most basic way to insert is to use the habit of writing SQL statements, call API to insert, and support batch insert

// The first parameter of the insertInto method is usually a table constant
dslContext.insertInto(S1_USER, S1_USER.USERNAME, S1_USER.ADDRESS, S1_USER.EMAIL)
        .values("username1", "demo-address1", "diamondfsd@gmail.com")
        .values("username2", "demo-address2", "diamondfsd@gmail.com")
        .execute();

// The newRecord() method id adds a record, and supports batch insertion through chain call
dslContext.insertInto(S1_USER)
        .set(S1_USER.USERNAME, "usernameSet1")
        .set(S1_USER.EMAIL, "diamondfsd@gmail.com")
        .newRecord()
        .set(S1_USER.USERNAME, "usernameSet2")
        .set(S1_USER.EMAIL, "diamondfsd@gmail.com")
        .execute();

Record API

In addition to inserting data by writing SQL like API, you can also insert data through Record API

• The dslContext.newRecord method creates a Record object according to the table. You can insert data through the record.insert() method

S1UserRecord record = dslContext.newRecord(S1_USER);
record.setUsername("usernameRecord1");
record.setEmail("diamondfsd@gmail.com");
record.setAddress("address hello");
record.insert();

Batch insertion

The method of dslcontext.batchinsert (collection <? Extends TableRecord <? > > records) can be used for batch insertion. The S1UserRecord generated by jOOQ implements the TableRecord interface

List<S1UserRecord> recordList = IntStream.range(0, 10).mapToObj(i -> {
    S1UserRecord s1UserRecord = new S1UserRecord();
    s1UserRecord.setUsername("usernameBatchInsert" + i);
    s1UserRecord.setEmail("diamondfsd@gmail.com");
    return s1UserRecord;
}).collect(Collectors.toList());
dslContext.batchInsert(recordList).execute();

Get auto increment primary key after inserting

• Through SQL like mode
  Through this method to insert data, you can read the data you want to return through the returning API. This syntax supports returning multiple values. You can get a Record object through the fetchOne() method

Integer userId = dslContext.insertInto(S1_USER,
    S1_USER.USERNAME, S1_USER.ADDRESS, S1_USER.EMAIL)
    .values("username1", "demo-address1", "diamondfsd@gmail.com")
    .returning(S1_USER.ID)
    .fetchOne().getId();

• Record API With this method, the auto added primary key will be automatically stored in the record

S1UserRecord record = dslContext.newRecord(S1_USER);
record.setUsername("usernameRecord1");
record.setEmail("diamondfsd@gmail.com");
record.setAddress("address hello");
record.insert();
// The ID here is the self-increasing ID returned by the database after insertion, which will be automatically stored in the record and can be obtained through the get method
record.getId();

Duplicate primary key processing

When the primary key is duplicate, you can do two operations: one is to ignore the insertion, the other is to update

• Duplicate primary key ignore insert

int affecteRow = dslContext.insertInto(S1_USER,
    S1_USER.ID, S1_USER.USERNAME)
    .values(1, "username-1")
    .onDuplicateKeyIgnore()
    .execute();
// After execution, the number of affected rows returned is 0
// Generated SQL: insert ignore into ` learn jooq '. ` S1 [user' (` ID ', ` username') values (1, 'username-1')

• Primary key is updated repeatedly

dslContext.insertInto(S1_USER)
    .set(S1_USER.ID, 1)
    .set(S1_USER.USERNAME, "duplicateKey-insert")
    .set(S1_USER.ADDRESS, "hello world")
    .onDuplicateKeyUpdate()
    .set(S1_USER.USERNAME, "duplicateKey-update")
    .set(S1_USER.ADDRESS, "update")
    .execute();
// Generate SQL: insert into ` learn jooq '. ` s1'user' (` ID ', ` username', ` address') values (1, 'duplicate key update', 'hello world') on duplicate key update ` learn jooq '. ` s1'user'. ` username '='duplicatekey update', 'learn jooq'. ` s1'user '. ` address' ='update'

Update

The usage of update and insert is similar. There are two ways to operate

Quasi SQL mode

dslContext.update(S1_USER)
    .set(S1_USER.USERNAME, "apiUsername-1")
    .set(S1_USER.ADDRESS, "update-address")
    .where(S1_USER.ID.eq(1))
    .execute()

Record API

The Record method defaults to the primary key as the where condition of the update statement

S1UserRecord record = dslContext.newRecord(S1_USER);
record.setId(1);
record.setUsername("usernameUpdate-2");
record.setAddress("record-address-2");
record.update();
// Generate SQL: update ` learn jooq '. ` s1'user' set ` learn jooq '. ` s1'user'. ` ID '= 1,' learn jooq '. ` s1'user'. ` username '='usernameupdate-2', ` learn jooq '. ` s1'user'. ` address' ='record-address-2 'where ` learn jooq'. ` s1'user '. ` ID' = 1


S1UserRecord record2 = dslContext.newRecord(S1_USER);
record2.setUsername("usernameUpdate-noID");
record2.update();
// Generate SQL: update 'learn jooq'. ` S1 user 'set' learn jooq '. ` S1 user'. ` username '=' usernameupdate noid 'where' learn jooq '. ` S1 user'. ` ID 'is null

Batch update

You can use dslContext.batchUpdate to batch update. Batch update or splicing update statements through primary key conditions is the same as the previous rules

S1UserRecord record1 = new S1UserRecord();
record1.setId(1);
record1.setUsername("batchUsername-1");
S1UserRecord record2 = new S1UserRecord();
record2.setId(2);
record2.setUsername("batchUsername-2");

List<S1UserRecord> userRecordList = new ArrayList<>();
userRecordList.add(record1);
userRecordList.add(record2);
dslContext.batchUpdate(userRecordList).execute();

Select

Query operations are basically performed through SQL like syntax

Single table query

The basic query method is to query all fields of the specified table by default and return the package of a result set. Through the Result.into method, you can convert the result set to any specified type set. Of course, you can also obtain any field value through the Record.getValue method. The value type depends on the field type

// select `learn-jooq`.`s1_user`.`id`, `learn-jooq`.`s1_user`.`username`, `learn-jooq`.`s1_user`.`email`, `learn-jooq`.`s1_user`.`address`, `learn-jooq`.`s1_user`.`create_time`, `learn-jooq`.`s1_user`.`update_time` from `learn-jooq`.`s1_user`
Result<Record> fetchResult = dslContext.select().from(S1_USER).fetch();
List<S1UserRecord> result = fetch.into(S1UserRecord.class);

// select `learn-jooq`.`s1_user`.`id`, `learn-jooq`.`s1_user`.`username`, `learn-jooq`.`s1_user`.`email`, `learn-jooq`.`s1_user`.`address`, `learn-jooq`.`s1_user`.`create_time`, `learn-jooq`.`s1_user`.`update_time` from `learn-jooq`.`s1_user` where `learn-jooq`.`s1_user`.`id` in (1, 2)
Result<Record> fetchAll = dslContext.select().from(S1_USER)
                .where(S1_USER.ID.in(1, 2)).fetch();
fetchAll.forEach(record -> {
    Integer id = record.getValue(S1_USER.ID);
    String username = record.getValue(S1_USER.USERNAME);
    String address = record.getValue(S1_USER.ADDRESS);
    Timestamp createTime = record.getValue(S1_USER.CREATE_TIME);
    Timestamp updateTime = record.getValue(S1_USER.UPDATE_TIME);
});

Relational query

Multi table associated query is also very simple. Similar to the method of writing SQL, associated query result set can customize a POJO to store data

Create a new POJO to store query results

public class UserMessagePojo {
    private String username;
    private String messageTitle;
    private String messageContent;
    //... getter/setter
}

As mentioned before, the result or result set can be converted to any type through the into method. jOOQ will fill the corresponding field value into the specified POJO through reflection. By associating the result set of a query, you can use this method to convert query results to a collection of a specified type.

Result<Record3<String, String, String>> record3Result =
        dslContext.select(S1_USER.USERNAME,
        S2_USER_MESSAGE.MESSAGE_TITLE,
        S2_USER_MESSAGE.MESSAGE_CONTENT)
        .from(S2_USER_MESSAGE)
        .leftJoin(S1_USER).on(S1_USER.ID.eq(S2_USER_MESSAGE.USER_ID))
        .fetch();
List<UserMessagePojo> userMessagePojoList = record3Result.into(UserMessagePojo.class);

Delete

Quasi SQL mode

In this way, you can flexibly build conditions for deletion

dslContext.delete(S1_USER).where(S1_USER.USERNAME.eq("demo1")).execute();

Record API mode

The Record.detele() method can be used for deletion. By calling this method, the deletion is based on the primary key of the corresponding table as a condition

S1UserRecord record = dslContext.newRecord(S1_USER);
record.setId(2);
int deleteRows = record.delete();
// deleteRows = 1
// SQL: delete from `learn-jooq`.`s1_user` where `learn-jooq`.`s1_user`.`id` = 2

S1UserRecord record2 = dslContext.newRecord(S1_USER);
record2.setUsername("demo1");
int deleteRows2 = record2.delete();
// deleteRows == 0
// SQL: delete from `learn-jooq`.`s1_user` where `learn-jooq`.`s1_user`.`id` is null

Batch deletion

This method allows mass deletion

S1UserRecord record1 = new S1UserRecord();
record1.setId(1);
S1UserRecord record2 = new S1UserRecord();
record2.setId(2);
dslContext.batchDelete(record1, record2).execute();
// 
List<S1UserRecord> recordList = new ArrayList<>();
recordList.add(record1);
recordList.add(record2);
dslContext.batchDelete(recordList).execute();

POJO and code generator configuration

In the previous code, when performing associated query, it is troublesome to process the result set. You need to create your own POJO class for operation. In the actual business, multi table associated query is a very common thing. If each query result needs to create POJO to store data, it is not a small job, and the operation is also very tedious. You need to confirm the name and type of each field

In such a case, it can be solved by the code generator of jOOQ. The code generator can be configured to generate POJO s corresponding to tables one by one when generating code. Only in the generator configuration generator block and related configurations can be added:

<generator>
    <generate>
        <pojos>true</pojos>
    </generate>
    <!-- ...  -->
</generator>

Through the above configuration, when the code is generated, POJO classes corresponding to table one by one will be generated at the same time. Because the code generation of jOOQ is fully generated each time, so when we write relevant business code, we cannot modify all the codes generated by jOOQ. If we want to add other fields based on an original POJO during association query, we can create a class with the same table name by ourselves, and then inherit the POJO object. In addition, we can The required fields, such as the S2 user message table in this code example, here we need to associate this table with S1 user table to query the user name corresponding to the user ID

Then the UserMessagePojo that we defined before becomes the direct inheritance of POJO class S2UserMessage, and then add the field name that needs to be associated query:

public class UserMessagePojo extends S2UserMessage {
    private String username;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
}

After adding the POJO production configuration, the final directory generated by jOOQ is shown in the figure below. Compared with the previous one, there is an additional pojos package for storing all pojos

├─src/main/java/.../codegen ---- // Generation path
│ ├─tables --------------------- // Table definition directory
│ │ ├─pojos -------------------- // Store POJO classes corresponding to tables one by one 
│ │ └─records ------------------ // Table Record object directory
│ ├─DefaultCatalog ------------- // Catalog object, containing Schema constants
│ ├─Indexes -------------------- // All constants of the current database
│ ├─Keys ----------------------- // Constants such as primary key and unique index of all tables in the current database
│ ├─LearnJooq ------------------ // Database 'learn jooq' constant, including all table description constants of the database
│ └─Tables --------------------- // All database table constants

At the same time, we also found that all POJO class names in pojos directory are the same as those of all table description objects in tables directory. In this way, when developing, there is a trouble, that is, when referencing, you need to pay attention to the package path. You can't directly see which is the table description class or POJO class. When using POJO and table description class in the same class at the same time, you will find that References to full path classes (xx.xx.xx.XXXX) reduce code readability

How to solve this problem? When jOOQ generates code, it uses the org.jooq.codegen.GeneratorStrategy interface to determine the generation rules of all file names. In the code generator configuration, parameters are provided to specify the implementation class of the interface:

<generator>
    <strategy>
        <name>com.diamondfsd.jooq.learn.CustomGeneratorStrategy</name>
    </strategy>
    <!-- ... -->
</generator>

CustomGeneratorStrategy the custom generator inherits the original DefaultGeneratorStrategy and overrides the method getJavaClassName. The main purpose is to distinguish POJO from the class name of table description. The POJO name generated through such configuration will change to S2UserMessagePojo, and the class name of table description is TS2UserMessage, which can better distinguish POJO from the class name of table description, and avoid code problems caused by import errors in the coding process.

public class CustomGeneratorStrategy extends DefaultGeneratorStrategy {
    @Override
    public String getJavaClassName(Definition definition, Mode mode) {
        String result = super.getJavaClassName(definition, mode);
        switch (mode) {
            case POJO:
                result +="Pojo";
                break;
            case DEFAULT:
                if (definition instanceof TableDefinition) {
                    result = "T" + result;
                }
                break;
            default:
                break;
        }
        return result;
    }
}

In this way, we can remove the suffix from the previous inherited classes

public class S2UserMessage extends S2UserMessagePojo {
    private String username;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
}

In addition, it should be noted that the inheritance class of POJO can not be placed in the package of jOOQ code generation target, because all contents in the specified target package will be deleted when generating code, so the POJO class created by ourselves needs to be placed in the same level or higher level of the target package of code generator, so it will not be deleted by the code generator of jOOQ

For example, the target package name of the jOOQ generator is com.diamondfsd.jooq.learn.codegen

Our inheritance classes can be placed in com.diamondfsd.jooq.learn.xxx or other top-level directories to avoid being deleted by the generator

Content summary

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

All the instance code is in the src/java/test directory, which is a written test case.

This chapter explains how to perform basic CURD operation and POJO generation. There are many people who question the difference between this POJO and Record, because Record also has getter/setter methods. Here I'll explain it to you

• The storage method of Record is to store the field description and value in two arrays with 11 corresponding subscripts. When get or set is used, its implementation is to find the corresponding subscript through the field and perform array operation. In this way, there is a problem that it cannot be serialized into a string through json. In addition, the record object usually contains some methods for field value selection, which are mainly used for data operation

• POJO is composed of member variables and getter/setter. It is a class purely used to access data. It can be serialized and deserialized through json, so that when we develop web, we can easily transform data

In the actual business, we usually do not directly use POJO classes generated by jOOQ, because jOOQ code generation is full. After we make some changes to POJO, such as adding some associated members of other tables, the code will be erased when it is regenerated. If you want to directly change the code generated by jOOQ, you need to back up the original code every time you regenerate it, and you need to change the code according to the changes

The solution to this problem is very simple. Instead of modifying the POJO class directly, create a subclass that inherits the POJO. The associated fields or other temporary fields set the member variables in the subclass, so that the code generated by jOOQ is not affected, and the desired effect can be achieved