Example Hibernate Multiple Join / Table SQL Criteria

Question

I have this SQL query

select pr.*,p.id, cf.nome, p.valor_total as "valor total", f.descricao as "forma de pagamento", t.descricao as "tipo de pagamento", s.descricao as "Status" from pedido p
    left join cliente c on c.id = p.id_cliente
    left join cliente_pessoa_fisica cf on cf.id_cliente = c.id
    left join forma_pagamento f on f.id = p.id_forma_pagamento
    left join tipo_pagamento t on t.id = p.id_tipo_pagamento
    left join status_pedido s on s.id = p.id_status_pedido
    left join itempedido itp on itp.id_pedido = p.id
    left join produto pr on pr.id = itp.id_produto

How can I use this query to create a Hibernate Criteria SQL query?

Answer 1

Here is my best shot, class names may vary

    CriteriaBuilder qb = entityMan.getCriteriaBuilder();
    CriteriaQuery<Pedido> criteriaQuery = qb.createQuery(Pedido.class);
    Root<Pedido> root = criteriaQuery.from(Pedido.class);

    /**
    * if the Cliente is a list you should use ListJoin instead of Join so it ListJoin<Pedido, Cliente>
    *if you don't have metadata you can use root.join(root.get("id_cliente")).
    */
    Join<Pedido, Cliente> cliente = root.join(Pedido_.id_cliente, JoinType.LEFT); 
    Join<Cliente, ClientePessoaFisica> clientePessoaFisica = cliente.join(Cliente_.id, JoinType.LEFT);//or if you don't have metadata you can use root.join(cliente.get("id_cliente"))
    Join<Pedido, FormaPagamento> formaPagamento = root.join(Pedido_.id_forma_pagamento, JoinType.LEFT); //or if you don't have metadata you can use root.join(root.get("id_forma_pagamento"))
    Join<Pedido, TipoPagamento> tipoPagamento = root.join(Pedido_.id_tipo_pagamento, JoinType.LEFT);//or if you don't have metadata you can use root.join(root.get("id_tipo_pagamento"))
    Join<Pedido, StatusPedido> statusPedido = root.join(Pedido_.id_status_pedido, JoinType.LEFT);//or if you don't have metadata you can use root.join(root.get("id_status_pedido"))
    Join<Pedido, Itempedido> itempedido = root.join(Pedido_.id, JoinType.LEFT);//or if you don't have metadata you can use root.join(root.get("id"))
    Join<Itempedido, Produto> produto = itempedido.join(Itempedido_.id_produto, JoinType.LEFT);//or if you don't have metadata you can use root.join(itempedido.get("id_produto"))

    /**
    * Here you can select what ever you want, here an example and you can complete it yourself :)
    * But I would remove this line and select the object itself Pedido
    * Now it depends on you FetchType of the relationships, you might need fetch join
    */
    criteriaQuery.multiselect(root.get("id"), root.get("valor_total"), formaPagamento.get("descricao"));

    TypedQuery<Pedido> query = entityMan.createQuery(criteriaQuery);
    return query.getResultList();