Spring Data JPA - Query via Specification - Manytomany and Join with Multiple Entities

Question

Hello, I’ve assembled the following Specification class:

public class ItemVendedorSpecification implements Specification<ItemVendedor> {

    private static final long serialVersionUID = 1L;

    @Autowired
    CategoriaRepository categoriaRepository;

    @Autowired
    FabricanteRepository fabricanteRepository;

    @Autowired
    VendedorRepository vendedorRepository;

    private String descricao;

    private List<Long> categorias;
    private List<Long> fabricantes;
    private List<Long> vendedores;

    List<Predicate> predicates = new ArrayList<>();

    public ItemVendedorSpecification(String descricao, List<Long> categorias, List<Long> fabricantes, List<Long> vendedores) {
        super();
        this.descricao = descricao;
        this.categorias = categorias;
        this.fabricantes = fabricantes;
        this.vendedores = vendedores;
    }

    @Override
    public Predicate toPredicate(Root<ItemVendedor> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        if (!descricao.isEmpty()) {
            String PalavraChave[] = descricao.split(" ");
            for (String filtro : PalavraChave) {
                predicates.add(builder.like(builder.upper(root.get("id").get("produto").get("descricaoDetalhada")), "%" + filtro.toUpperCase() + "%"));
            }
        }

        predicates.add(builder.isTrue(root.get("disponivel")));

        if(!fabricantes.isEmpty()) {
            predicates.add(root.get("id").get("produto").get("fabricante").get("id").in(fabricantes));
        }

        if(!vendedores.isEmpty()) {
            predicates.add(root.get("id").get("vendedor").get("id").in(vendedores));

        }

        if(!categorias.isEmpty()) {
            predicates.add(root.join("id").get("produto").get("categorias").get("id").in(categorias));
        }

        return builder.and(predicates.toArray(new Predicate[1]));
    }
}

Almost all predicares are working, except what inserts the category criterion. It’s not working and I’m having a hard time creating it.

As it is returning the following error:

"Illegal attempt to dereference path source [null.produto.categorias] of basic type; nested exception is java.lang.IllegalStateException: Illegal attempt to dereference path source [null.produto.categorias] of basic type",

Someone could help me in making this if?

Below the detail of the class Itemvendedor:

public class ItemVendedor implements Serializable{

    private static final long serialVersionUID = 1L;


    private ItemVendedorPK id = new ItemVendedorPK();
    private BigDecimal preco;
    private Boolean disponivel;
    private Date dt_insert;
    private Date dt_update;
    private BigDecimal desconto;

    public ItemVendedor() {

    }

    public ItemVendedor(Produto produto, Vendedor vendedor, BigDecimal preco, BigDecimal desconto ,Boolean disponivel) {
        super();
        this.id.setProduto(produto);
        this.id.setVendedor(vendedor);
        this.preco = preco;
        this.disponivel = disponivel;
        this.desconto = desconto;
    }

//GETs and SETs

As you can see it has a field called id which is a key composed of the fields Vendedor vendedor and Produto produto.

Within the Product class I have a List<Categoria> categorias. Because a product can belong to several categories.

In turn the Category Class has the id.

What I want to put on Specification, is a way to seek all ItemVendedor that have within their category list some category that I have cited as parameter in another list List<Long> categorias.

Guys, I’m putting some more information to simplify the understanding, below is a simplified class diagram to understand the context of the situation:

As you can see in the Product class, a product may have several categories, and a category may have several products.

What I want to do is this: I want to give you a list of Long-type values that refer to category codes. This parameter is passed through a list (List<Long> categoriasId) and will be used in the following consultation:

I want you to return all objects of type Itemvendedor, where, the list of product categories has at least 1 item that this within the list of parameters passed.

This approach is being done through Specification because it is a dynamic query, which will aggregate other criteria according to the class code.

Sincerely yours.

Answer 1

I managed to solve it. I was using the query in the wrong way. Below follows the resolution code:

predicates.add(root.join("id").join("produto").join("categorias").get("id").in(categorias));

In order to be able to perform the verification to know if the product had any category that was within the parameter it was necessary to perform some joins.

The first Join is relative to what in the class code is id, this field in the Itemvendedor table is actually a composite key class (@Embeddable), in turn this class had to carry out the Join with the class of Products, which had the field of Categories.

So we can get to the list of categories of each product and so buy if we have any element of this list within the query parameter informed.