correct way to create relationship in hibernate

Question

i am having a mysql table relation with following criteria.

id - auto_increment, primary
a_id - FK to test_table primary key, index present
a_parent_id - FK to test_table primary key, index present
(a_id + a_parent_id) has unique constrain

table entries example

a_id a_parent_id
1    null
2    null
3    1
4    1
5    2
6    5
6    4

currently, i have mapped test_table in hibernate

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false)
    private Long id;

    @Column
    private String comment;

what is the correct way to map relation table in hibernate?

Answer 1

As you specify that you can have multiple children/parents and from the looks of the example data, I chose to go with Lists of parents/children instead of just chaining the relationship.

@Entity
@Table(name = "test_table")
public class Test {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false)
    private Long id;

    @Column
    private String comment;

    @ManyToMany(targetEntity = Test.class)
    @JoinTable(name = "relation", joinColumns = {
            @JoinColumn(referencedColumnName = "a_id") }, inverseJoinColumns = {
                    @JoinColumn(referencedColumnName = "a_parent_id") })
    private List<Test> parents;

    @ManyToMany(targetEntity = Test.class)
    @JoinTable(name = "relation", joinColumns = {
            @JoinColumn(referencedColumnName = "a_parent_id") }, inverseJoinColumns = {
                    @JoinColumn(referencedColumnName = "a_id") })
    private List<Test> children;

}

To find the parents, you look at the relation table with an SQL like

SELECT a_parent_id FROM relation WHERE a_id = ?

For the children you switch the columns like this:

SELECT a_id FROM relation WHERE a_parent_id = ?

This behavior should be represented by the @JoinTable annotations.