Автоматическое сопоставление Jooq для отношения 1:1, содержащего отношение 1:N

С помощью Лукаса Эдера (см. выше) и связанной страницы с примерами для специальных преобразователей.

• см.: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/ad-hoc-converter/
• тоже актуально, мультимножество: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/

• Конвертация сделана раньше! выполнение утверждения, как желательно в вопросе.
• Преобразование на самом деле не является неявным, поскольку специальный преобразователь должен быть настроен для конкретного варианта использования (обнуляемое отношение 1:1 с подсписком 1:N)

Таким образом, этот код можно повторно использовать для разных вариантов использования (fetch(), fetchStream(),...) без необходимости переобъявлять преобразователь для результатов этих различных функций вариантов использования, но преобразование можно централизовать вместе. с этим заявлением DSL для повторного использования.

SelectFinalStep<? extends Record> selectStep = dsl()
    .select(
        asterisk(),
        multiset(
            selectDistinct(
                asterisk(),
                multiset(
                    selectDistinct(
                        asterisk()
                    )
                    .from(UserRole.USER_ROLE)
                    .where(UserRole.USER_ROLE.USERID.eq(User.USER.USERID))
                ).as("roles")
            )
            .from(User.USER)
            .where(User.USER.USERID.eq(Product.PRODUCT.CREATORID))
        ).as("creator").convertFrom(r -> {
            // multiset always gives us a list-result, 
            // but we expect a nullable 1:1 relationship here for sure...
            if (r.isEmpty()) {
                return (UserDTO) null;
            } else {
                Record first = r.getFirst();
                List<UserRoleDTO> roles = first.get("roles", Result.class).into(UserRole.USER_ROLE).into(UserRoleDTO.class);
                return first.into(User.USER).into(UserDTO.class).setRoles(roles);
            }
        })
    )
    .from(Product.PRODUCT)
    .leftJoin(User.USER)
    .on(User.USER.USERID.eq(Product.PRODUCT.CREATORID))
    .where(...)
    .groupBy(Product.PRODUCT.PRODUCTID)
    .orderBy(...)
    .offset(...)
    .limit(...);

// case1 - selecting
selectStep.fetchInto(ProductDTO.class);

// case2 - counting
dsl().fetchCount(selectStep);

// case3 - streaming
selectStep.fetchStreamInto(ProductDTO.class);

Некоторые общие практические правила:

• Вам почти никогда не придется использовать оператор asterisk().
• Вам почти никогда не придется использовать литералы .class в ваших картографах (которые используют отражение, что не является типобезопасным). Конечно, вы все еще можете это сделать, в принципе это поддерживается, но тогда вы не будете знать, правильно ли вы написали запрос и сопоставление, пока не запустите его.
• Вам почти никогда не придется вручную извлекать значения из результатов или устанавливать значения в DTO.

Не правда ли, это выглядит намного проще?

dsk().select(

         // Project the PRODUCT fields
         PRODUCT.ID,
         PRODUCT.NAME,
         ...

         // Nest a projection of USER fields (using implicit joins)
         row(PRODUCT.user().ID, PRODUCT.user().NAME, ...).mapping(UserDTO::new),

         // Nest a collection of USER_ROLE fields (using join path correlation)
         multiset(
             select(
                 PRODUCT.user().role().ID, 
                 PRODUCT.user().role().NAME, ...)
             .from(PRODUCT.user().role())
         ).convertFrom(r -> r.map(Records.mapping(UserRoleDTO::new))))
    .from(PRODUCT)
    .where(...)
    .orderBy(...)
    .offset(...)
    .limit(...)
    .fetch(Records.mapping(ProductDTO::new));

Предполагается, что вы проектируете свои DTO следующим образом, например:

record UserRoleDTO(long id, String name, ...) {}
record UserDTO(long id, String name, ...) {}
record ProductDTO(long id, String name, UserDTO creator, List<UserRoleDTO> roles) {}

Для простоты вышеприведенное использует

• jOOQ 3.19 корреляция путей присоединения
• Неявные соединения jOOQ 3.11

Обе эти функции действительно упрощают эти MULTISET запросы, хотя они, очевидно, не являются обязательными. Если вы предпочитаете, вы все равно можете использовать явные соединения.

Повторное использование запроса для значения COUNT(*)

Вместо выполнения дополнительного обхода для подсчета результатов запроса вместо этого есть возможность использовать оконные функции для вычисления значения COUNT(*) в одном запросе.

Хотя, учитывая, что значение COUNT(*) не зависит от всей вложенности USER и USER_ROLE, возможно, в любом случае лучше выполнить 2 отдельных запроса, где запрос COUNT(*) опускает все вложенные данные, в зависимости от того, как именно вы собираетесь представлять нумерацию страниц.

Хотя принятый ответ — лучший способ решить эту проблему с помощью чистой настройки, посмотрев на некоторые таблицы в моем проекте, которые имеют более 22 столбцов (что бывает редко), я решил использовать этот другой подход, который (должен) также быть типобезопасным, но не такой гибкий, поскольку всегда учитывает все поля каждой таблицы (что хорошо для моего варианта использования).

dsl()
    .select(
        row(
            // Project the PRODUCT table
            PRODUCT,

            // USER fields and corresponding ROLES via join path (1:1 containing 1:N)
            row(
                PRODUCT.creator(),
                multiset(
                    select(
                        PRODUCT.creator().user_role()
                    ).from(PRODUCT.creator().user_role())
                ).convertFrom(r -> r.map(RoleDTO::create))
            ).convertFrom(UserDTO::createOrNull),

            // PRODUCT_LANG 1:N via join path
            multiset(
                select(
                    PRODUCT.product_lang()
                ).from(PRODUCT.product_lang())
            ).convertFrom(r -> r.map(ProductLangDTO::create))
        ).convertFrom(ProductDTO::create)
    )

В этом подходе я использую тот факт, что jOOQ автоматически сопоставляет определения «Таблицы» внутри блока «row()» с соответствующими (сгенерированными кодом) классами записей. Единственное, чего тогда не хватает, - это сопоставить эти классы записей с классами DTO, для которых могут быть определены конструкторы/функции внутри DTO, которые создают DTO из RecordN.

Например. Конструкторы/функции для примера следующие:

ПродуктDTO:

    public static ProductDTO create(
        Record3<ProductRecord, UserDTO, List<ProductLangDTO>> r
    ) {
        ProductDTO product = new ProductDTO();
        product.from(r.value1());
        product.setCreator(r.value2());
        product.setLangs(r.value3());
        return product;
    }

ПользовательDTO:

    public static @Nullable UserDTO createOrNull(
        Record2<UserRecord, List<RoleDTO>> r
    ) {
        UserRecord rec = r.value1();
        if (rec.getUserId() == null) {
            return null;
        }
        UserDTO user = new UserDTO();
        user.from(r.value1())
        user.setRoles(r.value2());
        return user;
    }

Хорошо, что сами DTO также содержат методы «into»/«from», которые обеспечивают типобезопасность. Однако разработчику не следует забывать добавлять/удалять сюда новые/измененные поля, иначе они не будут отображаться. Например:

    @Override
    public void from(IUser from) {
        setUserId(from.getUserId());
        setClientId(from.getClientId());
        setEmail(from.getEmail());
        setFirstname(from.getFirstname());
        setLastname(from.getLastname());
    }

Поэтому сопоставление записи с DTO является типобезопасным и не вызывает проблем.