Annotation to join columns with OR condition instead of AND condition

Question

I have 2 java classes, Relation and Person, which both are present in my database.

Person:

@Entity
@Table(name = "persons")
public class Person {
    @Id
    @Column
    private int id;

    @Column
    private String name;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumns({
        @JoinColumn(name = "slave_id", referencedColumnName="id"),
        @JoinColumn(name = "master_id", referencedColumnName="id")
    })
    private List<Relation> relations;

    //Getters and setters
}

Relation:

@Entity
@Table(name = "relations")
public class Relation {
    @Id
    @Column
    private int id;

    @Column
    private int child_id;

    @Column
    private int parent_id;

    @Column
    private String type;

    //Getters and setters
}

Each Person has a list of relations (or not), the relation should be added to the list when the child_id or the parent_id of the relation is equal to the id of the person.

TL;DR: When relation.child_id OR relation.parent_id = person.id => add relation to list of relations to the person

The issue I am facing is that this annotation:

@JoinColumns({
            @JoinColumn(name = "child_id", referencedColumnName="id"),
            @JoinColumn(name = "parent_id", referencedColumnName="id")
        })

creates following SQL (just the necessary part):

relations relations6_ 
            on this_.id=relations6_.slave_id 
            and this_.id=relations6_.master_id

What is the correct annotation in Java Hibernate to generate an SQL statement saying OR instead of AND

Answer 1

Some of the options that you could utilize:

1. Database views. Create the view that does custom join for you and map the entity to the view.
2. Join formula. I managed to make them work only on many-to-one associations. Nevertheless, you could make the association bidirectional and apply the formula in the Relation entity.
3. @Subselect. This is a kind of Hibernate view, suitable if you can't afford to create a real database view or change the db schema to better suit the entity model structure.

This and this answer could also be helpful.

Also, you can always use two separate associations for slaves and masters:

public class Person {
    @OneToMany
    @JoinColumn(name = "slave_id"),
    private List<Relation> slaves;

    @OneToMany
    @JoinColumn(name = "master_id"),
    private List<Relation> masters;

    public List<Relation> getRelations() {
        List<Relation> result = new ArrayList<>(slaves);
        result.addAll(masters);
        return result;
    }
}

However, keep in mind that joining all of them in a single query requires full Cartesian product between masters and slaves.