How to query in ManyToMany tables?

5

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

Car (code, plate, color)
Accessory (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 am using the above clause to return the attachments, but my stacktrace tells me that besides the clause being in HQL is obsolete and that I should 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;
    }

}
    
asked by anonymous 07.10.2015 / 15:28

1 answer

2

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

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

So, 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=? )

    
18.11.2015 / 19:12