Hibernate : self join confusion?

Question

I have a category table.In which first 5 are main category and others are sub category.

I need to fetch the sub categories of first 5 main category so i have found the sql query

SELECT m.category_id,m.category_name  AS 'Parent',
      e.category_name AS 'Sub'
FROM category e
INNER JOIN category m ON m.category_id = e.parent_category_id
ORDER BY Parent

The query is joining the same table itself.and am getting the result given below

Result

How can i convert the SQL query to HQL and return the data like above image to user in standard json format ?

FetchSubCategory

import java.io.Serializable;
import java.util.Set;
import javax.persistence.*;

@Entity
@Table(name = "category")
public class FetchSubCategory implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "category_id")
    private Integer categoryId;

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

    @ManyToOne(cascade = {CascadeType.ALL})
    @JoinColumn(name = "parent_category_id")
    private FetchSubCategory parent;

    @OneToMany(mappedBy = "parent")
    private Set<FetchSubCategory> subCategory;

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public FetchSubCategory getParent() {
        return parent;
    }

    public void setParent(FetchSubCategory parent) {
        this.parent = parent;
    }

    public Set<FetchSubCategory> getSubCategory() {
        return subCategory;
    }

    public void setSubCategory(Set<FetchSubCategory> subCategory) {
        this.subCategory = subCategory;
    }

}

Method

public Set<FetchSubCategory> fetchSubCategory() throws SQLException, ClassNotFoundException, IOException {
        Set<FetchSubCategory> groupList = null;
        try {
            Session session = sessionFactory.getCurrentSession();
            Query query = session.createQuery("SELECT m.categoryName AS 'Parent', e.categoryName AS 'Sub' FROM FetchSubCategory e INNER JOIN FetchSubCategory m ORDER BY Parent");

            groupList = (Set<FetchSubCategory>) query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return groupList;
    }

Can any one please correct my mistake and tell me how to fetch result like above image?

What have you tried? Have you read the documentation on HQL and joins? http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch16.html — JB Nizet, Jul 28 '15 at 06:23
Please help me to do this so that i can understand the basic steps then after follow the same. @JB Nizet — Stella, Jul 28 '15 at 06:26
StackOverflow is not a "do my job please, because I'm too lazy to read the documentation and try something by myself" site. Do your part of the job. You might... learn something useful in the process. — JB Nizet, Jul 28 '15 at 06:31
It's not because lazy i don't know to use AS 'Parent' and AS 'Sub' like these things in Hibernate.that's why asked my doubt here..i have already read those document also.. — Stella, Jul 28 '15 at 06:34
You have `@OneToMany(mappedBy = "category")` but you don't have a `category` property in the `FetchSubCategory` class. You probably meant `@OneToMany(mappedBy = "manager")`. The way you have structured your class, every instance of `FetchSubCategory` holds a reference to its parent category (`manager`), if one exists. Therefore, you shouldn't even need to perform a join query. If you simply fire `from FetchSubCategory where manager is not null order by manager.categoryName`, you will get all categories that have a manager assigned. Then, you can simply iterate over the results and that's it. — manish, Jul 28 '15 at 07:06
@Stella, is anything unclear? The full answer is there in the comment. Have you tried this out? — manish, Jul 28 '15 at 07:28
There is indeed no database column called `manager` according to your code. The column is named `parent_category_id`. Are you sure you are using an HQL query and not a native SQL query? If you were using HQL, it would have worked fine because HQL uses field names and you do have a field named `manager` (unless you have posted incorrect code). Please update your question with actual code showing how you are running the query and the actual query you are running. — manish, Jul 28 '15 at 07:33
I have updated the method...@manish please post answer according to that the upadate — Stella, Jul 28 '15 at 08:03
We will appreciate if you post correct and actual code and stick to one path of investigation. Changing the code in your post arbitrarily does not help anyone and creates a moving target that makes it difficult to solve the problem. I took [your class](https://github.com/manish-in-java/jpa-hibernate-jdbc/blob/master/src/main/java/org/example/domain/Category.java) and added [a test case](https://github.com/manish-in-java/jpa-hibernate-jdbc/blob/master/src/test/java/org/example/hibernate/CategoryTest.java) to a sample app of mine. The tests pass with your data. Compare your code with this. — manish, Jul 28 '15 at 12:50

score 5 · Accepted Answer · answered Jul 29 '15 at 04:05

This stuff will solve your problem

@Entity
@Table(name = "category")
public class FetchSubCategory implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "category_id")
    private Integer categoryId;

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

    @NotFound(action = NotFoundAction.IGNORE)
    @ManyToOne
    @JsonIgnore
    @JoinColumn(name = "parent_category_id")
    private FetchSubCategory mainCategory;

    @JsonSerialize(include = JsonSerialize.Inclusion.NON_EMPTY)//Avoiding empty json arrays.objects
    @OneToMany(mappedBy = "mainCategory", fetch = FetchType.EAGER)
    private List<FetchSubCategory> subCategory;

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public FetchSubCategory getMainCategory() {
        return mainCategory;
    }

    public void setMainCategory(FetchSubCategory mainCategory) {
        this.mainCategory = mainCategory;
    }

    public List<FetchSubCategory> getSubCategory() {
        return subCategory;
    }

    public void setSubCategory(List<FetchSubCategory> subCategory) {
        this.subCategory = subCategory;
    }

Get your sub categories

public List<FetchSubCategory> fetchSubCategory() throws SQLException, ClassNotFoundException, IOException {
        List<FetchSubCategory> groupList = null;
        try {
            Session session = sessionFactory.getCurrentSession();
            Query query = session.createQuery("select distinct e FROM FetchSubCategory e INNER JOIN e.subCategory m ORDER BY m.mainCategory");
            groupList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return groupList;
    }

what if , we want this realtionship to be OnetoOne? how will the code look — Narendra Pandey, Jan 31 '19 at 10:26

Rahul · Answer 2 · 2015-07-28T06:52:20.873

For self join as in your case, below will work for you.

    @ManyToOne(cascade={CascadeType.ALL})    
    @JoinColumn(name = "parent_category_id")
    private FetchSubCategory parent;

    @OneToMany(mappedBy = "parent")
    private Set<FetchSubCategory> subCategory;

FetchSubCategory entity class, we defined two attributes: FetchSubCategory parent and Set<FetchSubCategory> subCategory. Attribute parent is mapped with @ManyToOne annotation and subordinates is mapped with @OneToMany. Also within @OneToMany attribute we defined mappedBy="parent" making parent as the relationship owner and thus which manages the foreign relationship within table.

Also the annotation @JoinColumn is defined on parent making it the relationship owner. @JoinColumn defines the joining column which in our case is parent_category_id.

Plz tell me How the query would be ? – Stella Jul 28 '15 at 06:46 — Stella, Jul 28 '15 at 06:46

Hibernate : self join confusion?

2 Answers2

Linked