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

score 4 · Accepted Answer · edited May 23 '17 at 12:32

4

Some of the options that you could utilize:

Database views. Create the view that does custom join for you and map the entity to the view.
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.
@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.

edited May 23 '17 at 12:32

Community

1
1

answered Dec 31 '15 at 12:31

Dragan Bozanovic

23,102
5
43
110

Both Join formula and Subselect seem promising. But I rather not like to write SQL in these annotations, so I keep looking for an answer that suits this question. – STheFox Dec 31 '15 at 12:40
@STheFox In that case, please see my updated answer. Anyway, there is no annotation specifically designed for your case; you'll have to use some workarounds. – Dragan Bozanovic Dec 31 '15 at 12:51
Yes, I was able to fetch 2 lists, but I was looking to write better and cleaner code. My code was similar to your code. Isn't there an annotation to do this at once? – STheFox Dec 31 '15 at 12:53
@STheFox No, there isn't any such annotation. – Dragan Bozanovic Dec 31 '15 at 13:00

score -2 · Answer 2 · edited Dec 31 '15 at 12:19

-2

You can use @FilterDef and @Filter annotations.

edited Dec 31 '15 at 12:19

Ajinkya

22,324
33
110
161

answered Dec 31 '15 at 12:13

Amit Jain

143
8

Annotation to join columns with OR condition instead of AND condition

2 Answers2

Linked