JPA/Hibernate Entity with Collection for itself

Question

Hello, I have an entity called menu in my system. A menu can be child of another menu and so on. The table has the following structure:

The entity is mapped as follows:

@Entity
@NamedQuery(name="Menu.findAll", query="SELECT m FROM Menu m")
public class Menu extends AbstractEntityDomain implements Serializable {
private static final long serialVersionUID = 1L;

@Id
private String id;

private String descricao;

private String legenda;

private String link;

private int ordem;

//bi-directional many-to-one association to Menu
@ManyToOne
@JoinColumn(name="menupai")
private Menu menu;

//bi-directional many-to-one association to Menu
@OneToMany(mappedBy="menu", fetch=FetchType.LAZY)
private Set<Menu> menus;

//bi-directional many-to-one association to Grupomenu
@OneToMany(mappedBy="menu", fetch=FetchType.LAZY)
private Set<Grupomenu> grupomenus;


public Menu() {
}

public Menu(String id, String descricao, String legenda, String link, int ordem){
    this.id = id;
    this.descricao = descricao;
    this.legenda = legenda;
    this.link = link;
    this.ordem = ordem;
}

public String getId() {
    return this.id;
}

public void setId(String id) {
    this.id = id;
}

public String getDescricao() {
    return this.descricao;
}

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

public String getLegenda() {
    return this.legenda;
}

public void setLegenda(String legenda) {
    this.legenda = legenda;
}

public String getLink() {
    return this.link;
}

public void setLink(String link) {
    this.link = link;
}

public int getOrdem() {
    return this.ordem;
}

public void setOrdem(int ordem) {
    this.ordem = ordem;
}

public Menu getMenu() {
    return this.menu;
}

public void setMenu(Menu menu) {
    this.menu = menu;
}


public Set<Menu> getMenus() {
    return menus;
}

public void setMenus(Set<Menu> menus) {
    this.menus = menus;
}

public Menu addMenus(Menu menus) {
    getMenus().add(menus);
    menus.setMenu(this);

    return menus;
}

public Menu removeMenus(Menu menus) {
    getMenus().remove(menus);
    menus.setMenu(null);
    return menus;
}

public Set<Grupomenu> getGrupomenus() {
    return grupomenus;
}

public void setGrupomenus(Set<Grupomenu> grupomenus) {
    this.grupomenus = grupomenus;
}

I need to execute a query where I want to bring all the menus with your children, I prepared the query as follows:

StringBuilder sb = new StringBuilder();
    sb.append("SELECT m ");
    sb.append("FROM Menu m ");
    sb.append("LEFT JOIN FETCH m.menusFilho ");
    sb.append("WHERE m.menu is null ");//esta linha é porque os menus principais não tem pai

    List<Menu> lista = new ArrayList<Menu>(em.createQuery(sb.toString()).getResultList());

This query is returning duplicate values. For each menu that has children such as the permissions menu, it brings three occurrences of the menu permissions with the filled children’s Collections instead of bringing only one occurrence with a filled children’s Collections.

I have tried using distinct and the same error occurs, do you have any suggestions ? Sorry if the text got big, I tried to explain my scenario to the maximum.

Answer 1

This happens because the JOIN of JPA was made to imitate the JOIN of SQL and this behavior observed is exactly the JOIN behavior of SQL!

A JOIN between two JPA menus would be equivalent to the query below:

SELECT *
FROM Menu a JOIN Menu b
ON a.id = b.menupai;

Look below, an example table and the result of this query in this table:

As the JOIN of SQL works like this, the JPA was also made so that the logic of the queries was more similar to the SQL.

To solve your problem just use distinct in JPQL query:

StringBuilder sb = new StringBuilder();
sb.append("SELECT distinct m ");
sb.append("FROM Objeto m ");
sb.append("JOIN FETCH m.colec ");
sb.append("WHERE m.menu is null ");

This code worked perfectly in my test and is the normal way to avoid duplicate objects in the list.

However, you said this did not work in your code. So an alternative is to use a set, which is a data structure that takes all repetitions:

SET set = new HashSet(lista);

The set will take out all duplicate objects when being created. If you prefer you can convert to list again.

List lista = new ArrayList(set);