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

迫切左外连接：解决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<Menu> menuSet;
    private Set<Function> 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<Menu> getMenuSet() {
        return menuSet;
    }
    public void setMenuSet(Set<Menu> menuSet) {
        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<Function> getFunctionSet() {
        return functionSet;
    }

    public void setFunctionSet(Set<Function> 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<Function> 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<Function> getFunctionList() {
        return functionList;
    }

    public void setFunctionList(List<Function> 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 = "SELECT distinct(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 = "SELECT distinct(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:我在前进的道路上摸索，走在我前面的、走在我后面的同志，希望都能搭把手，让这条路更宽。