Get OrderBy a ManyToOne relation In Hibernate

Question

I Have 2 tables

1.User

2.Company

For each user there is a company. For each company it can multiple users.

UserBean.java

import java.io.Serializable;
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 javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

@Entity
@Table(name = "tab_user")
public class UserBean implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    @Column(name = "user_id")
    private int user_id;
    @Column(name="user_login_pwd")
    private String user_login_pwd;


    @ManyToOne
    @JoinColumn(name="comp_id")
    private CompanyBean companyBean

And my CompanyBean is

import java.io.Serializable;
import java.util.Date;
import java.util.List;

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.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

@Entity
@Table(name = "tab_company")

public class CompanyBean implements Serializable{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    @Column(name = "comp_id")
    private Integer comp_id;
    @Column(name = "comp_code")
    private String comp_code;
@OneToMany(mappedBy = "companyBean" , fetch = FetchType.EAGER,  cascade = CascadeType.ALL)
    @Fetch(value = FetchMode.SUBSELECT)
    private List<UserBean> companyUserList;

This is my CompanyBean mapping class.

Now i need to show list of users order by 'comp_code'. My DAO implementation for users list is

@SuppressWarnings("unchecked")
    @Override
    public List<UserBean> getUserList( String orderBy, String orderField) throws Exception{
        List<UserBean> userList = null;
        Session session = HibernateUtil.getSessionFactory().openSession();
        try{
            Criteria crit = session.createCriteria(UserBean.class);

            if(orderBy.equals("asc")){
                crit.addOrder(Order.asc(orderField));
            }else{
                crit.addOrder(Order.desc(orderField));
            }

            crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

            userList = crit.list();
        }finally{
            session.close();
        }
        return userList;
    }

So how can i get order by comp_code of CompanyBean in usersList? Please help.

Answer 1

By creating alias for beans we can get orderBy from manyToOne field

@SuppressWarnings("unchecked")
            @Override
            public List<UserBean> getUserList( String orderBy, String orderField) throws Exception{
                List<UserBean> userList = null;
                Session session = HibernateUtil.getSessionFactory().openSession();
                try{
                    Criteria crit = session.createCriteria(UserBean.class,"user").createAlias("user.companyBean", "company");

                    /*If my order field is from company bean then it should be company.company_field*/
                    if(orderBy.equals("asc")){
                        crit.addOrder(Order.asc(orderField));
                    }else{
                        crit.addOrder(Order.desc(orderField));
                    }

                    crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

                    userList = crit.list();
                }finally{
                    session.close();
                }
                return userList;
            }

Answer 2

No Hibernate solution is to create your own Comparator and to order with Collections.sort;

Hibernate solution is using of @OrderBy annotation. Using hibernate with annotations, i want a one-many relationship to be sorted