How to query in Manytomany tables?

Question

I have two tables/entities in the database with the following attributes:

Car (code, plate, color)
Acessorio (code, description).

public Carro buscarCarroComAcessorio(Long codigo) {
    return (Carro) em.createQuery("select c from Carro c JOIN c.acessorios here c.codigo=?").setParameter(1, codigo).getSingleResult();
}

I’m using the above clause to return the accessories, but mine stacktrace tells me that in addition to the clause being in HQL is obsolete and that I must write it in JPQL.

Structure of classes/entities

Car:

package com.jpa.model;

import java.io.Serializable;
import java.util.List;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;

@Entity
public class Carro implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long codigo;
    private String placa;
    private String cor;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "carro_acessorio",
            joinColumns = @JoinColumn(name = "codigo_carro"),
            inverseJoinColumns = @JoinColumn(name = "codigo_acessorio"))
    private List<Acessorio> acessorios;

    //getters and setters
    public Long getCodigo() {
        return codigo;
    }

    public void setCodigo(Long codigo) {
        this.codigo = codigo;
    }

    public String getPlaca() {
        return placa;
    }

    public void setPlaca(String placa) {
        this.placa = placa;
    }

    public String getCor() {
        return cor;
    }

    public void setCor(String cor) {
        this.cor = cor;
    }

    public List<Acessorio> getAcessorios() {
        return acessorios;
    }

    public void setAcessorios(List<Acessorio> acessorios) {
        this.acessorios = acessorios;
    }

    ///hashcode
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((codigo == null) ? 0 : codigo.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        Carro other = (Carro) obj;
        if (codigo == null) {
            if (other.codigo != null) {
                return false;
            }
        } else if (!codigo.equals(other.codigo)) {
            return false;
        }
        return true;

    }
}

Accessory:

package com.jpa.model;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Acessorio implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long codigo;
    private String descricao;

    //getters and setters
    public Long getCodigo() {
        return codigo;
    }

    public void setCodigo(Long codigo) {
        this.codigo = codigo;
    }

    public String getDescricao() {
        return descricao;
    }

    public void setDescricao(String descricao) {
        this.descricao = descricao;
    }

    ///hashing
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((codigo == null) ? 0 : codigo.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        Acessorio other = (Acessorio) obj;
        if (codigo == null) {
            if (other.codigo != null) {
                return false;
            }
        } else if (!codigo.equals(other.codigo)) {
            return false;
        }
        return true;
    }

}

Answer 1

The query seems to be incorrect, if you want to return the accessories too, in a single JPQL query. In this case, you need to use the FETCH. At first, I would write the consultation this way:

SELECT car FROM Carro as car JOIN FETCH car.acessorios WHERE car.codigo=?

Thus, you return the car entity (SELECT car FROM Carro as car) with the accessories of each car (JOIN FETCH car.acessorios) of cars with code equal to the parameter code (WHERE car.codigo=?)