java hql左连接_hibernate三表以上迫切左外连接

为什么要使用迫切左外连接？

迫切左外连接：解决hibernate n+1次查询问题。

二表的迫切左外连接语句修改：实现三表及以上的迫切左外连接。

场景描述：

有五张表：角色表、菜单表、功能表、角色-菜单表、角色-功能表

角色表有二个外键(menuid和functionid)

菜单表有一个外键(functionid)

角色表里的二个外键，使用 ManyToMany、ManyToMany

菜单表里的一个外键，使用 OneToMany

hibernate做完关联之后，查询一切正常，但是打印出来的SQL 语句非常多。类似这样：

菜单表有10条记录，我加载菜单列表，会产生11条SQL语句。查询菜单表一条记录，10个菜单实例，每个实例都查询一次功能表。

这是一个追求完美的程序员所不允许的。

下面讲述如何去除多余的SQL查询。

源码：

包括：角色类、菜单类、功能类、二表左外迫切连接语句、三表左外迫切连接语句、sql打印

角色类package basic.basicBean;

import javax.persistence.*;

import java.util.Set;

@Entity

@Table(name = "role", catalog="cms2017banksvr")

public class Role {

private long id;

private String rolename;

private String rolenumber;

private String info;

private Set

private Set functionSet;

@Id

@Column(name = "id")

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

@Basic

@Column(name = "rolename")

public String getRolename() {

return rolename;

}

public void setRolename(String rolename) {

this.rolename = rolename;

}

@Basic

@Column(name = "rolenumber")

public String getRolenumber() {

return rolenumber;

}

public void setRolenumber(String rolenumber) {

this.rolenumber = rolenumber;

}

@Basic

@Column(name = "info")

public String getInfo() {

return info;

}

public void setInfo(String info) {

this.info = info;

}

@ManyToMany(cascade = {CascadeType.MERGE,CascadeType.REFRESH},fetch=FetchType.EAGER)

@JoinTable(name="rolemenu",joinColumns = { @JoinColumn(name ="roleid" )},inverseJoinColumns = { @JoinColumn(name = "menuid") })

public Set

return menuSet;

}

public void setMenuSet(Set

this.menuSet = menuSet;

}

@ManyToMany(cascade = {CascadeType.MERGE,CascadeType.REFRESH},fetch=FetchType.EAGER)

@JoinTable(name="rolefunction",joinColumns = { @JoinColumn(name ="roleid" )},inverseJoinColumns = { @JoinColumn(name = "functionid") })

public Set getFunctionSet() {

return functionSet;

}

public void setFunctionSet(Set functionSet) {

this.functionSet = functionSet;

}

}

菜单类package basic.basicBean;

import org.hibernate.annotations.Fetch;

import org.hibernate.annotations.FetchMode;

import javax.persistence.*;

import java.util.ArrayList;

import java.util.List;

import java.util.Set;

@Entity

@Table(name = "menu", catalog="cms2017banksvr")

public class Menu {

private long id;

private String name;

private int orderNum;

private Long parentid;

private String url;

private String iconcls;

private String selectedurl;

private String info;

private List functionList;

@Id

@Column(name = "id")

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

@Basic

@Column(name = "name")

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

@Basic

@Column(name = "orderNum")

public int getOrderNum() {

return orderNum;

}

public void setOrderNum(int orderNum) {

this.orderNum = orderNum;

}

@Basic

@Column(name = "parentid")

public Long getParentid() {

return parentid;

}

public void setParentid(Long parentid) {

this.parentid = parentid;

}

@Basic

@Column(name = "url")

public String getUrl() {

return url;

}

public void setUrl(String url) {

this.url = url;

}

@Basic

@Column(name = "iconcls")

public String getIconcls() {

return iconcls;

}

public void setIconcls(String iconcls) {

this.iconcls = iconcls;

}

@Basic

@Column(name = "selectedurl")

public String getSelectedurl() {

return selectedurl;

}

public void setSelectedurl(String selectedurl) {

this.selectedurl = selectedurl;

}

@Basic

@Column(name = "info")

public String getInfo() {

return info;

}

public void setInfo(String info) {

this.info = info;

}

@OneToMany(cascade = {CascadeType.MERGE,CascadeType.REFRESH})

@JoinColumn(name="menuid")

public List getFunctionList() {

return functionList;

}

public void setFunctionList(List functionList) {

this.functionList = functionList;

}

}

功能类package basic.basicBean;

import javax.persistence.*;

@Entity

@Table(name = "function", catalog="cms2017banksvr")

public class Function {

private long id;

private String name;

private int orderNum;

private String url;

private String img;

private String info;

private Long menuid;

@Id

@Column(name = "id")

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

@Basic

@Column(name = "name")

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

@Basic

@Column(name = "orderNum")

public int getOrderNum() {

return orderNum;

}

public void setOrderNum(int orderNum) {

this.orderNum = orderNum;

}

@Basic

@Column(name = "url")

public String getUrl() {

return url;

}

public void setUrl(String url) {

this.url = url;

}

@Basic

@Column(name = "img")

public String getImg() {

return img;

}

public void setImg(String img) {

this.img = img;

}

@Basic

@Column(name = "info")

public String getInfo() {

return info;

}

public void setInfo(String info) {

this.info = info;

}

@Basic

@Column(name = "menuid")

public Long getMenuid() {

return menuid;

}

public void setMenuid(Long menuid) {

this.menuid = menuid;

}

}

二表左外迫切连接源码hql = "SELECTdistinct(m) FROM Menu m left outer join fetch m.functionList f order by m.orderNum asc";//distinct 去除重复记录ArrayList menuList = basicService.queryList(hql,null);

调用后的sql打印

Hibernate: select menu0_.id as id3_0_, functionli1_.id as id2_1_, menu0_.iconcls as iconcls3_0_, menu0_.info as info3_0_, menu0_.name as name3_0_, menu0_.orderNum as orderNum3_0_, menu0_.parentid as parentid3_0_, menu0_.selectedurl as selected7_3_0_, menu0_.url as url3_0_, functionli1_.img as img2_1_, functionli1_.info as info2_1_, functionli1_.menuid as menuid2_1_, functionli1_.name as name2_1_, functionli1_.orderNum as orderNum2_1_, functionli1_.url as url2_1_, functionli1_.menuid as menuid3_0__, functionli1_.id as id0__ from cms2017banksvr.menu menu0_ left outer join cms2017banksvr.function functionli1_ on menu0_.id=functionli1_.menuid order by menu0_.orderNum asc

三表左外迫切连接源码String hql = "SELECTdistinct(r) FROM Role r left outer join fetch r.functionSet f left outer join fetch r.menuSet m order by r.id asc";//distinct去除重复记录ArrayList roleList = basicService.queryList(hql,null);

调用后的sql打印

Hibernate: select role0_.id as id4_0_, function2_.id as id2_1_, menu4_.id as id3_2_, role0_.info as info4_0_, role0_.rolename as rolename4_0_, role0_.rolenumber as rolenumber4_0_, function2_.img as img2_1_, function2_.info as info2_1_, function2_.menuid as menuid2_1_, function2_.name as name2_1_, function2_.orderNum as orderNum2_1_, function2_.url as url2_1_, functionse1_.roleid as roleid4_0__, functionse1_.functionid as functionid0__, menu4_.iconcls as iconcls3_2_, menu4_.info as info3_2_, menu4_.name as name3_2_, menu4_.orderNum as orderNum3_2_, menu4_.parentid as parentid3_2_, menu4_.selectedurl as selected7_3_2_, menu4_.url as url3_2_, menuset3_.roleid as roleid4_1__, menuset3_.menuid as menuid1__ from cms2017banksvr.role role0_ left outer join rolefunction functionse1_ on role0_.id=functionse1_.roleid left outer join cms2017banksvr.function function2_ on functionse1_.functionid=function2_.id left outer join rolemenu menuset3_ on role0_.id=menuset3_.roleid left outer join cms2017banksvr.menu menu4_ on menuset3_.menuid=menu4_.id order by role0_.id asc

希望能够帮助大家解决sql执行 n+1 的问题。

hibernate  ManyToMany中的 cascade的配置我还未理解，大家不要照抄我的配置哈。如果有理解比较透的希望能够留言解惑一下。

ps:我在前进的道路上摸索，走在我前面的、走在我后面的同志，希望都能搭把手，让这条路更宽。