如何创建自定义jOOQ Record？合并两个Record属性实现方案

Hey there! Let's break down how to create custom jOOQ Records, merge existing ones, and convert JOIN results into your custom Record type. Here's a step-by-step guide tailored to your example with CustomerRecord and ProductRecord:

First, you'll need to define a class that extends org.jooq.impl.CustomRecord, with the generic type set to the class itself. You can reference the table fields generated by jOOQ's code generator (recommended to avoid typos) or declare fields manually.

For your scenario, create a CustomerProductRecord like this:

import org.jooq.Configuration;
import org.jooq.impl.CustomRecord;
import static com.yourpackage.generated.tables.Customer.CUSTOMER;
import static com.yourpackage.generated.tables.Product.PRODUCT;

public class CustomerProductRecord extends CustomRecord<CustomerProductRecord> {

    // Constructor that accepts jOOQ's Configuration
    public CustomerProductRecord(Configuration configuration) {
        super(configuration);
    }

    // Getters and setters for each field from Customer and Product tables
    public Integer getCustomerId() {
        return get(CUSTOMER.ID);
    }

    public void setCustomerId(Integer id) {
        set(CUSTOMER.ID, id);
    }

    public String getCustomerName() {
        return get(CUSTOMER.NAME);
    }

    public void setCustomerName(String name) {
        set(CUSTOMER.NAME, name);
    }

    public String getCustomerSurname() {
        return get(CUSTOMER.SURNAME);
    }

    public void setCustomerSurname(String surname) {
        set(CUSTOMER.SURNAME, surname);
    }

    public Integer getProductId() {
        return get(PRODUCT.ID);
    }

    public void setProductId(Integer id) {
        set(PRODUCT.ID, id);
    }

    public String getProductDescription() {
        return get(PRODUCT.DESCRIPTION);
    }

    public void setProductDescription(String description) {
        set(PRODUCT.DESCRIPTION, description);
    }
}

Note: If you're using jOOQ's code generator, make sure to import the correct generated table classes. If not, you can manually create Field instances, but using generated fields is far more reliable.

Suppose you already have instances of CustomerRecord and ProductRecord. To merge their properties into a CustomerProductRecord, simply create an instance of your custom record and assign values from both source records:

// Assume you've retrieved these records from your database
CustomerRecord customerRecord = ...;
ProductRecord productRecord = ...;

// Create an instance of your custom record
CustomerProductRecord mergedRecord = new CustomerProductRecord(configuration);

// Copy fields from CustomerRecord
mergedRecord.setCustomerId(customerRecord.getId());
mergedRecord.setCustomerName(customerRecord.getName());
mergedRecord.setCustomerSurname(customerRecord.getSurname());

// Copy fields from ProductRecord
mergedRecord.setProductId(productRecord.getId());
mergedRecord.setProductDescription(productRecord.getDescription());

If manual assignment feels tedious, use jOOQ's Record.copyFrom() method to batch-copy all fields from a source record that match in the target:

// Copy all fields from CustomerRecord to the custom record
mergedRecord.copyFrom(customerRecord);
// Copy all fields from ProductRecord to the custom record
mergedRecord.copyFrom(productRecord);

This method automatically matches fields by their instance or name, so as long as your custom record includes the fields from both tables, it will handle the copy in one line.

When you execute your JOIN query, jOOQ returns a Record (specifically a RecordImpl instance) containing all fields from both tables. There are two common ways to convert this to your CustomerProductRecord:

方式1：手动字段映射 #

Extract values directly from the join result and set them to your custom record:

// Execute the JOIN query
Record joinResult = dslContext.select()
    .from(CUSTOMER)
    .join(PRODUCT)
    .on(CUSTOMER.ID.eq(PRODUCT.CUSTOMER_ID))
    .fetchOne();

if (joinResult != null) {
    CustomerProductRecord customRecord = new CustomerProductRecord(configuration);
    
    // Extract and set each field value
    customRecord.setCustomerId(joinResult.get(CUSTOMER.ID));
    customRecord.setCustomerName(joinResult.get(CUSTOMER.NAME));
    customRecord.setCustomerSurname(joinResult.get(CUSTOMER.SURNAME));
    customRecord.setProductId(joinResult.get(PRODUCT.ID));
    customRecord.setProductDescription(joinResult.get(PRODUCT.DESCRIPTION));
}

方式2：使用Record.into()方法 #

jOOQ's into() method can automatically map fields to your custom record, provided the fields in your custom record match those in the query result (by instance or name).

First, add a no-arg constructor to your CustomerProductRecord (or adjust the existing one to use a default Configuration if needed):

public CustomerProductRecord() {
    // Use your default Configuration (e.g., from DSL.using())
    super(DSL.using(SQLDialect.MYSQL));
}

Then use into() to convert the result:

Record joinResult = dslContext.select()
    .from(CUSTOMER)
    .join(PRODUCT)
    .on(CUSTOMER.ID.eq(PRODUCT.CUSTOMER_ID))
    .fetchOne();

if (joinResult != null) {
    CustomerProductRecord customRecord = joinResult.into(CustomerProductRecord.class);
}

For bulk conversion of multiple results, use fetchInto():

List<CustomerProductRecord> customRecords = dslContext.select()
    .from(CUSTOMER)
    .join(PRODUCT)
    .on(CUSTOMER.ID.eq(PRODUCT.CUSTOMER_ID))
    .fetchInto(CustomerProductRecord.class);

This will directly return a list of your custom records, saving you the trouble of manual mapping for each result.

内容的提问来源于stack exchange，提问作者Misha Kolpakov