CODEWITHSUNDEEP
javahqlhibernate-annotations
Kriss Morra
Kriss Morra

Reputation: 13

HQL query for the Entity (single table) with parent-children relations

I'm trying to create simple select query with HQL. There is an Entity that will be used in query. It Looks like the following:

@Entity
@Table(name = "my_table")
public class MyTable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "test_id")
    private Long testId;

    @Column(name = "parent_id")
    private Long parentId;

    @OneToMany(mappedBy = "parentId", 
               fetch = FetchType.EAGER, 
               cascade = CascadeType.ALL)
    private Set<MyTable> children = new HashSet<MyTable>();

    //getters and setters
}

Hierarchy is simple. There are Parents (which have null parent_id value) and their children. So two levels only.

I would like to create query that selects all parents and their children, but there is a condition for children: it should equal to specific test_id. E.g. it is required to have children only with test_id = 1. Table consists of Parent1 with Child1 (test_id = 2) and Parent2 with Child2 (test_id = 1). The query result should be Parent1 without children and Parent2 with Child2.

Query:

from MyTable as myTable left fetch join myTable.children as child 
where child.testId = 1

As a result - I'm getting only those parents which have children with "1" test_id. But I need all parents to see even if there no needed children. What is wrong here: mapping or query? And how should it actually be?

Thanks in advance.

Upvotes: 1

Views: 4640

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

Your mapping is wrong. You have a unidirectional OneToMany association. SO you obviously must tell Hibernate how this association is mapped. And what you tell is:

@OneToMany(mappedBy = "parentId", ...)

This means: "Go look at the other side of this bidirectional association to find how this association is mapped. I'm only the inverse side".

But there is no other side. Your association is unidirectional.

The mapping should be:

@Entity
@Table(name = "my_table")
public class MyTable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "test_id")
    private Long testId;

    @OneToMany(fetch = FetchType.EAGER, 
               cascade = CascadeType.ALL)
    @JoinColumn(name = "parentId")
    private Set<MyTable> children = new HashSet<MyTable>();

    //getters and setters
}

Note that the parentId field has been removed, since it's already used to map the children association.

Another problem is your expectations. A query returns columns, or it returns entities. If it returns a parent entity and you ask this entity for its children, all the children will be returned. AN entity represents what is in the database. It doesn't represent the result of a specific query.

What you could do, if you only want to have some of the children, is to search for children, and get their parent. To do that, you would need to make the association bidirectional:

@Entity
@Table(name = "my_table")
public class MyTable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "test_id")
    private Long testId;

    @ManyToOne
    @JoinColumn(name="parentId")
    private MyTable parent;

    @OneToMany(mappedBy="parent",
               fetch = FetchType.EAGER, 
               cascade = CascadeType.ALL)
    @JoinColumn(name = "parentId")
    private Set<MyTable> children = new HashSet<MyTable>();

    //getters and setters
}

And the query would be:

select child from MyTable child 
left join fetch child.parent
where child.testId = 1

This would return all the children with testId = 1, along with their parent. You would need a second query to get all the other parents. Something like

select parent from MyTable parent 
where parent.id not in (
    select parent2.id from MyTable child   
    left join child.parent parent2
    where child.testId = 1)

Upvotes: 3

Related Questions