Hibernate query on many to many mapping

Question

I have been using many to many mapping here is my POJO classes.

Menu.java :

@Entity
@Table(name = "menu")
public class Menu {

    @Id
    @Column(name = "menuid")
    @GeneratedValue
    private int menuid;

    @Column(name = "parentid")
    private int parentid;

    @Column(name = "menuname")
    private String menuname;

    @Column(name = "url")
    private String url;

    @Column(name = "status")
    private String status;

    @Column(name = "usertype")
    private String usertype;

    @Column(name = "isparent")
    private boolean isParent;
    private ArrayList<Menu> childMenu;

    @ManyToMany(mappedBy="menus")                                       
    private List<User> users;

    public Menu(Integer menuid){
        this.menuid=menuid;
    }

    public Menu(){
    }

User.java :

@Entity
@Table(name = "user")
public class User implements Serializable {

    @Id
    @Column(name = "userid")
    @GeneratedValue
    private Integer userId;

    @Column(name = "OUTLET_ID")
    private int outletId;

    @Column(name = "NAME")
    private String name;

    @Column(name = "USERTYPE")
    private String userType;

    @Column(name = "LOGINID")
    private String loginId;

    @Column(name = "PASSWORD")
    private String password;

    @Column(name = "CREATEDDATE")
    private String createdDate;

    @Column(name = "CONTACTNUMBER")
    private String contactNumber;

    @Column(name = "EMAILID")
    private String emailId;

    @Column(name = "OUTLETTYPE")
    private String outlettype; 

    @Transient
    private String nsec;

    @javax.persistence.Transient
    ArrayList<Integer> menuid;

    @javax.persistence.Transient
    ArrayList<Long> clientid;

    @javax.persistence.Transient
    ArrayList<String> clientName;

    @ManyToMany(fetch=FetchType.EAGER) @JsonIgnore
    @JoinTable(name="user_menu",joinColumns={@JoinColumn(name="userid")},
                                                    inverseJoinColumns={@JoinColumn(name="menuid")})
    public List<Menu> menus;

    @ManyToMany(fetch=FetchType.EAGER) @JsonIgnore
    @JoinTable(name="user_client",joinColumns={@JoinColumn(name="userid")},
                                                    inverseJoinColumns={@JoinColumn(name="outletid")})

    public List<Client> clients;

    public User() { 
    }

I have user,menu and third mapping table user_menu which is created automatically, I successfully get result when fire following query in mysql

select * from menu m inner join user_menu um on m.menuid = um.menuid where um.userid = 41;

I want to write this query in hibernate how to this stuff ???

have you seen this link-> http://stackoverflow.com/questions/13412112/inner-join-using-hql — Riya Saxena, Jan 19 '17 at 11:44

score 0 · Accepted Answer · answered Jan 19 '17 at 13:15

Finally I find my answer, here is my hql query,

    String sql = "select m.menuid as menuid,m.parentid as parentid,m.menuname as menuname,m.url as url,m.status as status,m.usertype as usertype,m.isParent as isParent,m.childMenu as childMenu from Menu m join m.users u where u.userId = "+userid +""; 
    q  =  session.createQuery(sql).setResultTransformer(Transformers.aliasToBean(Menu.class));

Hibernate query on many to many mapping

1 Answers1