Leader and Java Recursion Led Logic

Question

I have a relatively simple problem to solve, but I’m not succeeding because of a logical deficiency. My problem is this::

I have a hierarchy of leaders and leaders. For example:

Lider 1
    Lider 1.1
        Lider 1.1.1
        Lider 1.1.2
        Lider 1.1.3
            Lider 1.1.3.1
    Lider 1.2
        Lider 1.2.1
    Lider 1.3

And so it goes. As many levels are needed. In the database, this is already mapped. That is, I can know who is the leader of whom using the following example query:

select * from colaboradores where lider_id = ?;

However, I’m unable to mount an efficient Java recursion to return all the leaders below. For example, if I pass leader ID 1.1 to my method, it should return to me a list like this:

Lider 1.1
Lider 1.1.1
Lider 1.1.2
Lider 1.1.3
Lider 1.1.3.1

Somebody got a light for me there?

UPDATING: I tried mapping JPA itself. Just one note: The relationship between leaders and led is not done via class ID, but another field that is not foreign key. This other field is the license plate. Look:

@ManyToOne(targetEntity = Colaborador.class, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "matricula_lider", referencedColumnName = "matricula", insertable = false, updatable = false)
private Colaborador lider;

@OneToMany(mappedBy="lider", cascade = CascadeType.ALL, fetch=FetchType.EAGER)
private List<Colaborador> liderados;

Well, if I just use the mapping:

@ManyToOne(targetEntity = Colaborador.class, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "matricula_lider", referencedColumnName = "matricula", insertable = false, updatable = false)
private Colaboradorlider;

It works cool and I can get the person’s leader. If I use both mappings (bi-directional), error while trying to recover. Actually no error, it seems that enters in infinite loop until giving timeout of the transaction.

javax.persistence.PersistenceException: org.hibernate.HibernateException: Transaction was rolled back in a different thread!
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)

...

My mapping is wrong?

Answer 1

There are different techniques for modeling a hierarchical structure in a relational database, as can be seen in the question:

How to model a tree data structure using a relational database?

The most direct, yet achievable solution in terms of performance is to use a criterion with like. Example:

select * from colaboradores where lider_id like ? || '%'; -- oracle
select * from colaboradores where lider_id like ? + '%'; -- sql server

Note: The field is considered to be lider_id be a VARCHAR or VARCHAR2.

This solution just doesn’t get so bad if there is an index created for the field lider_id, ensuring that the SGBDR does not need to read the whole table (table scan).

Text column indexes work up to a certain number of characters and if you do not use '%' at the beginning, but only at the end of the search criteria.

Answer 2

Whereas your database hierarchy is mapped using a foreign key that points to the class itself,

You can do the same concept in java:

class Pessoa {
   Pessoa lider;
   List<Pessoa> colaboradores;
}

A person is a leader if he has collaborators, and the leader of a person is another person. This is recursive, but you will get a challenge that is Transforming Object to Relational and Vice versa.

If using Hibernate it will take care of this problem:

@Entity
class Pessoa {
@ManyToOne
Pessoa lider;
@OneToMany(mappedBy="lider", fetch=FetchType.LAZY )
List<Pessoa> colaboradores;
}

And then using a JQL

EntityManager em;
// Obtem o entitymanager conforme o padrão usado, JTA ou RESOURCE_LOCAL
em.createQuery("select p from Pessoa p where p.lider = :lider_id").setParam("lider_id", 2).getResultList(); // Retorna uma lista de Pessoas