Spring9 min de leitura

jOOQ + Records: um-para-muitos em uma query só, com o operador MULTISET

Foto de Guilherme Gomes
Guilherme Gomes

jOOQ + Records: um-para-muitos em uma query só, com o operador MULTISET

Introdução

Tela de detalhe do pedido, com a lista de itens embaixo. A tela demora e ninguém sabe explicar por quê.

Você ativa o log de SQL e a resposta aparece na hora:

select p.id, p.numero, p.criado_em from pedido p where p.status = ?
select i.* from item_pedido i where i.pedido_id = ?
select i.* from item_pedido i where i.pedido_id = ?
select i.* from item_pedido i where i.pedido_id = ?
... (mais 47 vezes)

Cinquenta pedidos na tela, cinquenta e uma queries no banco. É o velho N+1.

Se o seu código está assim, não tem nada de errado com você. É o comportamento padrão de uma coleção LAZY no JPA, e ele aparece justamente quando alguém escreve um serviço novo em cima de uma entidade que já existia.

Este artigo mostra as duas saídas que o JPA oferece, por que nenhuma delas é confortável, e como o jOOQ resolve o problema com um operador de SQL padrão que quase ninguém usa.

Repositório do projeto: jooq-multiset-demo

As duas saídas do JPA

Saída 1: deixar LAZY e pagar em round trip

Cada acesso a pedido.getItens() dispara uma query nova. Cinquenta pedidos, cinquenta idas ao banco. Cada ida tem latência de rede, e a soma dessas latências é a maior parte do tempo da tela.

Funciona, mas o custo cresce junto com a quantidade de linhas exibidas.

Saída 2: usar join fetch e pagar em duplicação

@Query("""
        select p from Pedido p
        join fetch p.itens
        where p.status = :status
        """)
List<Pedido> buscarPorStatus(@Param("status") StatusPedido status);

Agora é uma query só. O problema mudou de lugar.

O banco devolve uma linha por item, com os dados do pedido repetidos em todas elas. Um pedido com 8 itens vira 8 linhas. Os 50 pedidos da tela podem virar 400 linhas trafegando pela rede, com número, data e status repetidos em cada uma. É o produto cartesiano.

O Hibernate junta essas linhas de volta em memória, mas você ainda precisa lidar com as consequências:

return pedidos.stream()
        .distinct()
        .map(p -> new ResumoPedido(
                p.getNumero(),
                p.getCriadoEm(),
                p.getItens().stream()
                        .map(i -> new ItemPedido(
                                i.getSku(),
                                i.getDescricao(),
                                i.getQuantidade(),
                                i.getValorUnitario()))
                        .toList()))
        .toList();

E tem um detalhe que costuma aparecer depois, em produção: paginar esse resultado não funciona do jeito que você espera. Quando a query traz uma coleção com join fetch, o limit do banco corta linhas, não pedidos. Cortar na linha errada significa trazer um pedido pela metade. Por isso o Hibernate pagina esse caso em memória, o que só faz sentido enquanto o volume é pequeno.

Resumindo o dilema: ou você paga em round trip, ou paga em dados repetidos e trabalho manual para desfazer a repetição.

O que o MULTISET faz de diferente

MULTISET é um operador do SQL padrão que existe desde os anos 90 e quase ninguém usa, porque poucos bancos o implementaram de forma nativa. Ele permite que uma coluna do resultado seja, ela mesma, uma coleção de linhas.

O jOOQ trouxe o operador para o dia a dia na versão 3.15. Onde o banco não tem suporte nativo, o jOOQ emula o comportamento com SQL/JSON ou SQL/XML e faz a leitura desse documento no cliente. Você escreve o mesmo código no Postgres, no MySQL, no SQL Server e no Oracle.

Na prática, isso muda a forma do resultado. Em vez de linhas repetidas que você precisa agrupar, o banco devolve uma linha por pedido, com os itens dentro dela.

Os Records descrevem o formato que você quer

Comece pelo fim: declare a estrutura que a camada de cima precisa receber.

public record ItemPedido(
        String sku,
        String descricao,
        Integer quantidade,
        BigDecimal valorUnitario) {}

public record ResumoPedido(
        String numero,
        LocalDateTime criadoEm,
        List<ItemPedido> itens) {}

São dois Records comuns, sem anotação, sem dependência do jOOQ e sem nada da JPA. Dá para serializar direto pro JSON da API, e o compilador conhece cada componente.

A query

import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.select;
import static org.jooq.Records.mapping;

List<ResumoPedido> pedidos = ctx.select(
                PEDIDO.NUMERO,
                PEDIDO.CRIADO_EM,
                multiset(
                        select(
                                ITEM.SKU,
                                ITEM.DESCRICAO,
                                ITEM.QUANTIDADE,
                                ITEM.VALOR_UNITARIO)
                                .from(ITEM)
                                .where(ITEM.PEDIDO_ID.eq(PEDIDO.ID))
                                .orderBy(ITEM.SKU)
                ).as("itens").convertFrom(r -> r.map(mapping(ItemPedido::new))))
        .from(PEDIDO)
        .where(PEDIDO.STATUS.eq("ABERTO"))
        .orderBy(PEDIDO.CRIADO_EM.desc())
        .fetch(mapping(ResumoPedido::new));

Lendo de dentro para fora:

O select interno busca as colunas do item e se correlaciona com o pedido de fora pelo where. É a mesma condição que você escreveria em um join.

O multiset envolve esse select e transforma o resultado inteiro em uma única coluna do tipo coleção.

O convertFrom(r -> r.map(mapping(ItemPedido::new))) diz como cada linha dessa coleção vira um objeto. mapping recebe a referência do construtor e devolve uma função que o jOOQ aplica em cada registro.

O fetch(mapping(ResumoPedido::new)) faz a mesma coisa no nível de fora, montando o Record que envolve tudo.

Repare que a ordenação dos itens vai dentro do multiset, e a ordenação dos pedidos vai fora. Cada nível cuida da sua própria ordem.

O SQL que sai disso

No Postgres, o jOOQ gera algo parecido com isto (simplificado, com nomes de coluna encurtados):

select
  p.numero,
  p.criado_em,
  (
    select coalesce(
             jsonb_agg(jsonb_build_array(t.v0, t.v1, t.v2, t.v3)),
             jsonb_build_array()
           )
    from (
      select i.sku v0, i.descricao v1, i.quantidade v2, i.valor_unitario v3
      from item_pedido i
      where i.pedido_id = p.id
      order by i.sku
    ) t
  ) as itens
from pedido p
where p.status = 'ABERTO'
order by p.criado_em desc

Dois detalhes valem a leitura.

O jOOQ serializa cada registro como array JSON, não como objeto. Isso evita repetir o nome da coluna em cada item do documento, o que deixa a resposta bem menor quando a coleção tem muitas linhas.

E o coalesce garante que um pedido sem itens volte com uma lista vazia, não com null. Do lado do Java, List<ItemPedido> nunca chega nulo.

Por que essa abordagem é confortável de manter

O compilador cobre o mapeamento

mapping(ItemPedido::new) é referência de construtor. Não tem string com nome de campo, não tem reflexão procurando setter, não tem convenção de nomes que precisa bater.

Se alguém trocar a ordem dos componentes do Record, adicionar um campo ou mudar um tipo, o build quebra na hora. E como as colunas vêm das classes geradas pelo jOOQ a partir do schema, renomear uma coluna no banco e rodar a geração também quebra a compilação, apontando exatamente onde.

Uma ida ao banco, sem linha repetida

O round trip por pedido some, e os dados do pedido trafegam uma vez só. O que volta pela rede é o que a tela precisa mostrar, sem repetição.

Vale ser honesto sobre o que acontece por baixo: a emulação usa subconsulta correlacionada, então o banco ainda executa a busca de itens uma vez por pedido. A diferença é que isso roda inteiro dentro do banco, sem latência de rede entre uma e outra. O próprio autor do jOOQ mediu esse cenário e a versão com MULTISET saiu na frente tanto do N+1 quanto do join com de-duplicação, principalmente quando o resultado tem mais de uma coleção aninhada.

A paginação volta a funcionar

Como cada pedido ocupa exatamente uma linha, limit e offset cortam pedidos, não itens. Paginação normal, sem cuidado especial.

O resultado já chega pronto

Sem distinct, sem groupingBy, sem laço para remontar a hierarquia. O que você declarou nos Records é o que o fetch devolve.

Coleções aninhadas em mais de um nível

O multiset compõe. Se o pedido tem itens e cada item tem uma lista de movimentações de estoque, é só aninhar de novo:

public record Movimentacao(LocalDateTime data, String tipo, Integer quantidade) {}

public record ItemPedido(
        String sku,
        String descricao,
        Integer quantidade,
        BigDecimal valorUnitario,
        List<Movimentacao> movimentacoes) {}

multiset(
        select(
                ITEM.SKU,
                ITEM.DESCRICAO,
                ITEM.QUANTIDADE,
                ITEM.VALOR_UNITARIO,
                multiset(
                        select(MOV.DATA, MOV.TIPO, MOV.QUANTIDADE)
                                .from(MOV)
                                .where(MOV.ITEM_ID.eq(ITEM.ID))
                ).convertFrom(r -> r.map(mapping(Movimentacao::new))))
                .from(ITEM)
                .where(ITEM.PEDIDO_ID.eq(PEDIDO.ID))
).as("itens").convertFrom(r -> r.map(mapping(ItemPedido::new)))

É aqui que a diferença fica gritante. Duas coleções aninhadas em JPA significam ou duas rodadas de N+1, ou um join que multiplica pedidos por itens por movimentações. Com multiset, continua sendo uma query.

Quando não usar

Coleções grandes. O documento JSON é montado no banco e lido inteiro na memória do cliente. Para uma tela que mostra os itens de um pedido, ótimo. Para trazer o histórico completo de milhares de movimentações de uma vez, não.

Quando o destino final é JSON mesmo. Se o resultado vai direto para o front sem passar por regra de negócio, o jOOQ tem suporte a SQL/JSON nativo desde a 3.14. Aí você monta o JSON no banco e devolve o texto, sem converter para objeto Java e depois de volta para JSON.

Escrita. MULTISET é leitura. Para persistir agregado com regra de negócio, ciclo de vida e cascata, a JPA continua fazendo bem o trabalho dela.

O arranjo que costuma funcionar melhor é o mais chato de todos: JPA para escrita e para o modelo de domínio, jOOQ para as consultas de leitura que alimentam tela e relatório. Os dois convivem na mesma aplicação e na mesma transação do Spring, compartilhando o DataSource.

Conclusão

Mapear Um-para-Muitos em DTO é um problema antigo e as soluções tradicionais sempre cobraram em algum lugar: latência, dados repetidos ou código de montagem espalhado pelo serviço.

O multiset do jOOQ resolve isso em outro nível. Você descreve o formato que quer em Records comuns, escreve a query com a estrutura aninhada explícita, e o compilador garante que as duas coisas continuem combinando ao longo do tempo.

Se você já trabalha com jOOQ e ainda não usou o operador, essa é a feature que mais muda o dia a dia. E se você nunca usou jOOQ, esse é um bom motivo para experimentar em uma consulta de leitura, sem precisar mexer no resto da aplicação.

Repositório do projeto: jooq-multiset-demo

Este artigo faz parte da série Records na prática · Spring · JPA · jOOQ do getcaramelo.dev.

Tem dúvidas ou quer aprofundar algum ponto? Me encontra no LinkedIn ou no GitHub.

Gostou do conteúdo? Me segue no @getcaramelo.dev

← Voltar aos Artigos