Convert complex SQL to HQL?

Question

I have an SQL query which produces the result that I need:

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

How can I convert this to HQL?

Category Table

Actual Result After Executing The Query

Mapping class

import java.io.Serializable;
import java.util.List;
import javax.persistence.*;
import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

@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(cascade = {CascadeType.ALL})
    @JoinColumn(name = "parent_category_id")
    private FetchSubCategory parent;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "parent", cascade = CascadeType.ALL)
    private List<FetchSubCategory> subCategory;

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

    public void setSubCategory(List<FetchSubCategory> subCategory) {
        this.subCategory = 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;
    }

}

Fetching method

public List<FetchSubCategory> fetchSubCategory() throws SQLException, ClassNotFoundException, IOException {
        List<FetchSubCategory> groupList = null;
        try {
            Session session = sessionFactory.getCurrentSession();

            Query query = session.createQuery("FROM FetchSubCategory e INNER JOIN e.subCategory m ORDER BY m.parent");
            groupList = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return groupList;
    }

I have previously asked question related to this and I haven't got the answer, I am stuck totally.

From your SQL query I can see that you only want the parent.id, parent.name and child.name, but in your HQL attempt you expect FetchSubCategory objects. What is it you really want? — luksch, Jul 28 '15 at 11:17
possible duplicate of [Hibernate : self join confusion?](http://stackoverflow.com/questions/31668522/hibernate-self-join-confusion) — kryger, Jul 28 '15 at 11:20
I haven't get any answer for that.that's why i asked with little bit explained more..don't do this am stucked with this. — Stella, Jul 28 '15 at 11:22

Rahul Wagh · Answer 1 · 2015-07-28T11:59:24.637

1

The HQL for your problem would be

select fsc.categoryId,fsc.categoryName,fsc.parent.categoryName  from FetchSubCategory fsc

where fsc.categoryId=fsc.parent.categoryId

order by fsc.categoryName

To get more detail how HQL work please follow this article link

edited Jul 28 '15 at 11:59

answered Jul 28 '15 at 11:31

Rahul Wagh

470
1
6
23

score 0 · Answer 2 · answered Jul 28 '15 at 11:20

why dont you use hibernate configurations, using either xml or annotations.

Example - @OneToMany

You can have a primary key in 'Actual Result' table and then can configure 'One To Many' relationship between two tables - with this you can use a simple HQL. Example -

select actualResult from ActualResult actualResult. This would fetch the data from Actual Result and related data from Category table.

Convert complex SQL to HQL?

2 Answers2