how to join two tables and get all matched record in hibernate(using entity class mapping)

Question

I have two entities called FeeTerms.java and FeeTermDates.java

I want to get all matched records from these two entities using pure HQL

Look at entities:

FeeTerms.java

package com.rasvek.cg.entity;
// Generated May 14, 2018 11:39:07 PM by Hibernate Tools 5.1.7.Final

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import static javax.persistence.GenerationType.IDENTITY;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnore;

/**
 * FeeTerms generated by hbm2java
 */
@Entity
@Table(name = "fee_terms", catalog = "campus_guru_01")
public class FeeTerms implements java.io.Serializable {

    private Integer termId;
    private String termName;
    private String termCount;
    private Set<FeeTermDates> feeTermDateses = new HashSet<FeeTermDates>(0);
    private Set<AssocFeeTerms> assocFeeTermses = new HashSet<AssocFeeTerms>(0);

    public FeeTerms() {
    }

    public FeeTerms(String termName, String termCount, Set<FeeTermDates> feeTermDateses,
            Set<AssocFeeTerms> assocFeeTermses) {
        this.termName = termName;
        this.termCount = termCount;
        this.feeTermDateses = feeTermDateses;
        this.assocFeeTermses = assocFeeTermses;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "term_id", unique = true, nullable = false)
    public Integer getTermId() {
        return this.termId;
    }

    public void setTermId(Integer termId) {
        this.termId = termId;
    }

    @Column(name = "term_name")
    public String getTermName() {
        return this.termName;
    }

    public void setTermName(String termName) {
        this.termName = termName;
    }

    @Column(name = "term_count", length = 45)
    public String getTermCount() {
        return this.termCount;
    }

    public void setTermCount(String termCount) {
        this.termCount = termCount;
    }  

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "feeTerms")
    public Set<FeeTermDates> getFeeTermDateses() {
        return this.feeTermDateses;
    }  

    public void setFeeTermDateses(Set<FeeTermDates> feeTermDateses) {
        this.feeTermDateses = feeTermDateses;
    }

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "feeTerms")
    public Set<AssocFeeTerms> getAssocFeeTermses() {
        return this.assocFeeTermses;
    }

    public void setAssocFeeTermses(Set<AssocFeeTerms> assocFeeTermses) {
        this.assocFeeTermses = assocFeeTermses;
    }

}

FeeTermDates.java

package com.rasvek.cg.entity;
// Generated May 14, 2018 11:39:07 PM by Hibernate Tools 5.1.7.Final

import static javax.persistence.GenerationType.IDENTITY;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnore;

/**
 * FeeTermDates generated by hbm2java
 */
@Entity
@Table(name = "fee_term_dates", catalog = "campus_guru_01")
public class FeeTermDates implements java.io.Serializable {

    private int tdmId;
    private FeeTerms feeTerms;
    private String date;

    public FeeTermDates() {
    }

    public FeeTermDates(int tdmId, FeeTerms feeTerms) {
        this.tdmId = tdmId;
        this.feeTerms = feeTerms;
    }

    public FeeTermDates(int tdmId, FeeTerms feeTerms, String date) {
        this.tdmId = tdmId;
        this.feeTerms = feeTerms;
        this.date = date;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "tdm_id", unique = true, nullable = false)
    public int getTdmId() {
        return this.tdmId;
    }

    public void setTdmId(int tdmId) {
        this.tdmId = tdmId;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "term_id", nullable = false)
    public FeeTerms getFeeTerms() {
        return this.feeTerms;  
    }  

    public void setFeeTerms(FeeTerms feeTerms) {
        this.feeTerms = feeTerms;
    }

    @Column(name = "date")
    public String getDate() {
        return this.date;
    }

    public void setDate(String date) {
        this.date = date;
    }

}

i have tried with following code but i am not getting it

String hql="select FT.termId , FT.termName , FT.termCount,FT.feeTermDateses  from FeeTerms FT ,FeeTermDates FD where  FT.termId=FD.feeTerms" ;
                query = currentSession.createQuery(hql);

                termDatesList= query.getResultList(); 

how to achieve it as pure HQL. i am very new to Hibernate and HQl.

i have got something like below in another post,

public List<Category> getCategoryList(int id) {
    List<Category> groupList;
    Session session = sessionFactory.getCurrentSession();
    Query query = session.createQuery("select c from Category c join fetch c.events where c.parentCategory.categoryId = 1");
    //query.setParameter("id", id);
    groupList = query.list();
    return groupList;
}

Is it possible to achieve my query as above done?

Answer 1

You can receive a list of Object[] with the values that you want. Like:

String hql="select FT.termId , FT.termName , FT.termCount, FT.feeTermDateses  from FeeTerms FT, FeeTermDates FD where FT.termId = FD.feeTerms.id";

Query query = currentSession.createQuery(hql);
List<Object[]> results = query.getResultList(); 
for (Object[] obj : results) {
    Integer termId = obj[0];
    String termName = obj[1];
    String termCount = obj[2];
    Set<FeeTermDates> feeTermDates  = obj[4];
}

But, I could suggest a better version:

String hql = "SELECT ft FROM FeeTerms ft JOIN ft.feeTermDateses feeTermDateses";
Query query = currentSession.createQuery(hql);
List<FeeTerms> results = query.getResultList(); 

This already brings to you all FeeTerms that have FeeTermDates.