java Hibernate：自我加入混乱？

Hibernate : self join confusion?

提问by Stella

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

我有一个category表格。其中前 5 个是主要类别，其他是子类别。

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

我需要获取前 5 个主类别的子类别，所以我找到了 sql 查询

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

如何将 SQL 查询转换为 HQL 并以标准 json 格式将如上图所示的数据返回给用户？

获取子类别

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?

任何人都可以纠正我的错误并告诉我如何获取如上图所示的结果吗？

回答by Anoop M

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;
    }

回答by Rahul

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 @ManyToOneannotation and subordinates is mapped with @OneToMany. Also within @OneToManyattribute we defined mappedBy="parent"making parent as the relationship ownerand thus which manages the foreign relationship within table.

FetchSubCategory 实体类，我们定义了两个属性：FetchSubCategory parent and Set<FetchSubCategory> subCategory. 属性父级用@ManyToOne注释映射，下级用@OneToMany. 同样在@OneToMany属性中，我们mappedBy="parent"将父级定义为relationship owner，从而管理表中的外部关系。

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

此外，注释@JoinColumn是在parent使其成为关系所有者时定义的。@JoinColumn 定义了连接列，在我们的例子中是parent_category_id.