jOOQ中高效结合Multiset查询与POJO映射：全字段选择的优化实践

return dsl.select(
                USER.asterisk(), // 选择USER表所有字段
                multiset(
                    select(USER_ROLE.AUTHORITY_ID.as(ROLE.ID))
                        .from(USER_ROLE)
                        .where(USER_ROLE.USER_ID.eq(USER.ID)))
                    .as("roles")
                    .convertFrom(f -> f.intoSet(roleMapper)))
            .from(USER)
            .where(field.eq(value));

@Override
@SuppressWarnings("unchecked")
public User map(Record record) {
    final User user = new User();
    user.setId(record.get(USER.ID));
    user.setEmail(record.get(USER.EMAIL));
    // ... 更多字段的手动映射 ...
    user.setFirstName(record.get(USER.FIRST_NAME));
    user.setLastName(record.get(USER.LAST_NAME));
    // 处理multiset结果
    final Set<Role> roles = Optional.ofNullable(record.get("roles"))
            .map(r -> (Set<Role>) r)
            .orElse(Collections.emptySet());
    user.setRoles(roles);
    // ... 更多字段的手动映射 ...
    return user;
}

import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.UserRecord; // 假设jOOQ生成的UserRecord
import static com.example.jooq.codegen.Tables.USER; // 假设USER表定义

public class UserWithRolesMapper implements RecordMapper<Record, User> {

    private final RecordMapper<Record, Role> roleMapper; // 假设已有的Role映射器

    public UserWithRolesMapper(RecordMapper<Record, Role> roleMapper) {
        this.roleMapper = roleMapper;
    }

    @Override
    @SuppressWarnings("unchecked")
    public User map(Record record) {
        // 1. 使用record.into(User.class)直接将USER表字段映射到User POJO
        // 或者更精确地，先获取UserRecord，再转换为User POJO
        User user = record.into(USER).into(User.class);

        // 2. 处理multiset结果
        final Set<Role> roles = Optional.ofNullable(record.get("roles"))
                .map(r -> (Set<Role>) r)
                .orElse(Collections.emptySet());

        user.setRoles(roles);
        return user;
    }
}

import org.jooq.DSLContext;
import org.jooq.Record2; // jOOQ的Record2类型
import org.jooq.UserRecord;
import static com.example.jooq.codegen.Tables.USER;
import static com.example.jooq.codegen.Tables.USER_ROLE;
import static com.example.jooq.codegen.Tables.ROLE; // 假设ROLE表定义

// ...

public Record2<UserRecord, Set<Role>> fetchUserWithRoles(DSLContext dsl, Object field, Object value, RecordMapper<Record, Role> roleMapper) {
    return dsl.select(
                USER, // 直接投影USER表
                multiset(
                    select(USER_ROLE.AUTHORITY_ID.as(ROLE.ID))
                        .from(USER_ROLE)
                        .where(USER_ROLE.USER_ID.eq(USER.ID)))
                    .as("roles")
                    .convertFrom(f -> f.intoSet(roleMapper)))
            .from(USER)
            .where(field.eq(value))
            .fetchOne(); // 或 fetch() 返回多个
}

public User mapRecord2ToUser(Record2<UserRecord, Set<Role>> record2) {
    // 从第一个组件获取UserRecord并转换为User POJO
    User user = record2.component1().into(User.class);

    // 从第二个组件获取roles并设置
    Set<Role> roles = record2.component2();
    user.setRoles(roles);

    return user;
}