Maven项目使用mybatis实现多表联查

一.数据库表

user表

role表 

 menu表

funs表

role_menu表

二.分析关系 

1、数据库的表之间的关系

	1.一个user对应一个role 一个role 对应多个user
		user对role 是多对一关系
		role对user是一对多关系
	2.一个role对应多个menu，一个menu对应多个role
		memu和role是多对多关系
	3.一个menu对应多个fun 一个fun对应一个menu
		menue对fun 是一对多关系
		fun对menu 是多对一关系

 

2、pojo实体类的关系

	1.user和role两个实体类的关系
		user类：因为一个user对应一个role，所以user中除了本表的字段，还应该包含role对象的引用
		role类：因为一个role对应多个user，所以role类中应该存放user的一个集合。
	2.role和menu两个实体类关系
		因为role和menu是多对多的关系，所以两个类中均应该含有对方的集合属性
	3.menu和funs俩个实体类关系
		menu：因为一个munu对应多个fun所以menu中应该含有fun的集合
		funs：因为funs对应一个menu，所以funs中含有一个menu引用

 

三.各实体类的属性及互相持有的引用如下 

User类

package com.wmh.pojo;

public class User {
   
    private String id;

    
    private String username;

    
    private String password;

   
    private Integer roleid;

    
    private Role role;
    
    public Role getRole() {
		return role;
	}

	public void setRole(Role role) {
		this.role = role;
	}

	private String iconurl;
	


    @Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password="
				+ password + ", roleid=" + roleid + ", role=" + role
				+ ", iconurl=" + iconurl + "]";
	}

	
    public String getId() {
        return id;
    }

    
    public void setId(String id) {
        this.id = id;
    }

   
    public String getUsername() {
        return username;
    }

   
    public void setUsername(String username) {
        this.username = username;
    }

   
    public String getPassword() {
        return password;
    }

    
    public void setPassword(String password) {
        this.password = password;
    }

   
    public Integer getRoleid() {
        return roleid;
    }

   
    public void setRoleid(Integer roleid) {
        this.roleid = roleid;
    }

   
    public String getIconurl() {
        return iconurl;
    }

   
    public void setIconurl(String iconurl) {
        this.iconurl = iconurl;
    }
}

Role类

package com.wmh.pojo;

import java.util.List;

public class Role {
    
    private Integer roleId;
    
    private String roleName;
    
    private List<User> users;
    
    private List<Menu> menus;



	public List<Menu> getMenus() {
		return menus;
	}

	public void setMenus(List<Menu> menus) {
		this.menus = menus;
	}


    @Override
	public String toString() {
		return "Role [roleId=" + roleId + ", roleName=" + roleName + ", users="
				+ users + ", menus=" + menus + "]";
	}

	public Integer getRoleId() {
		return roleId;
	}

	public void setRoleId(Integer roleId) {
		this.roleId = roleId;
	}

	public String getRoleName() {
		return roleName;
	}

	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}

	public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }
}

Funs类

package com.wmh.pojo;

public class Funs {
    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column funs.funId
     *
     * @mbggenerated Mon Jul 27 10:03:57 CST 2020
     */
    private Integer funId;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column funs.funName
     *
     * @mbggenerated Mon Jul 27 10:03:57 CST 2020
     */
    private String funName;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column funs.funURL
     *
     * @mbggenerated Mon Jul 27 10:03:57 CST 2020
     */
    private String funURL;

    /**
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column funs.menuId
     *
     * @mbggenerated Mon Jul 27 10:03:57 CST 2020
     */
    private Integer menuId;
    
    private Menu menu;
    
    public Integer getFunId() {
		return funId;
	}

	public void setFunId(Integer funId) {
		this.funId = funId;
	}

	public String getFunName() {
		return funName;
	}

	public void setFunName(String funName) {
		this.funName = funName;
	}

	public String getFunURL() {
		return funURL;
	}

	public void setFunURL(String funURL) {
		this.funURL = funURL;
	}

	public Integer getMenuId() {
		return menuId;
	}

	public void setMenuId(Integer menuId) {
		this.menuId = menuId;
	}



    @Override
	public String toString() {
		return "Funs [funId=" + funId + ", funName=" + funName + ", funURL="
				+ funURL + ", menuId=" + menuId + ", menu=" + menu + "]";
	}

	public Menu getMenu() {
		return menu;
	}

	public void setMenu(Menu menu) {
		this.menu = menu;
	}

}

Menu类

package com.wmh.pojo;

import java.util.List;

public class Menu {
    
    private Integer menuId;

    private String menuName;
    
    private String menuURL;
    
    private Integer fatherId;
    
    private List<Role> roles;
    private List<Funs> funs;

    public List<Role> getRoles() {
		return roles;
	}

	public void setRoles(List<Role> roles) {
		this.roles = roles;
	}

	public List<Funs> getFuns() {
		return funs;
	}

	public void setFuns(List<Funs> funs) {
		this.funs = funs;
	}

	public Integer getMenuId() {
		return menuId;
	}

	public void setMenuId(Integer menuId) {
		this.menuId = menuId;
	}

	public String getMenuName() {
		return menuName;
	}

	public void setMenuName(String menuName) {
		this.menuName = menuName;
	}

	public String getMenuURL() {
		return menuURL;
	}

	public void setMenuURL(String menuURL) {
		this.menuURL = menuURL;
	}

	public Integer getFatherId() {
		return fatherId;
	}

	public void setFatherId(Integer fatherId) {
		this.fatherId = fatherId;
	}

	@Override
	public String toString() {
		return "Menu [menuId=" + menuId + ", menuName=" + menuName
				+ ", menuURL=" + menuURL + ", fatherId=" + fatherId
				+ ", roles=" + roles + ", funs=" + funs + "]";
	}

	
	
}

UserMapper.XML

<mapper namespace="com.wmh.mapper.UserMapper">

    <!--定义user的映射-->
  <resultMap id="BaseResultMap" type="com.wmh.pojo.User">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="roleId" jdbcType="INTEGER" property="roleid" />
    <result column="iconURL" jdbcType="VARCHAR" property="iconurl" />
    
    <association property="role" javaType="com.wmh.pojo.Role" resultMap="roleMapping">
    <!-- <id column="roleId" jdbcType="INTEGER" property="roleid" />
    <result column="roleName" jdbcType="VARCHAR" property="rolename" /> -->
    </association>
  </resultMap>
  
  <!--定义role的映射-->
    <resultMap id="roleMapping" type="com.wmh.pojo.Role">
        <id column="roleId" property="roleId"></id>
        <result column="roleName" property="roleName"></result>
        <collection property="menus" ofType="com.wmh.pojo.Menu" resultMap="menuMapping"></collection>
    </resultMap>
    
    <!--定义menu的映射-->
    <resultMap id="menuMapping" type="com.wmh.pojo.Menu">
        <id column="menuId" property="menuId"></id>
        <result column="menuName" property="menuName"></result>
        <result column="menuURL" property="menuURL"></result>
        <result column="fatherId" property="fatherId" jdbcType="INTEGER" />
        <collection property="funs" ofType="com.wmh.pojo.Funs" resultMap="funsMapping"></collection>
    </resultMap>
    
    <!--定义funs的映射-->
    <resultMap id="funsMapping" type="com.wmh.pojo.Funs">
        <id column="funId" property="funId"></id>
        <result column="funName" property="funName"></result>
        <result column="funURL" property="funURL"></result>
        <result column="menuId" property="menuId" jdbcType="INTEGER" />
    </resultMap>

  <sql id="Base_Column_List">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Mon Jul 27 10:03:57 CST 2020.
    -->
    id, username, password, roleId, iconURL
  </sql>
  <!-- <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Mon Jul 27 10:03:57 CST 2020.
   
    select 
    u.id,u.userName,u.password,u.iconURL,u.roleId,r.roleId,r.roleName
    from user u,role r
    where u.roleId = r.roleId
    and id = #{id,jdbcType=VARCHAR}
  </select> -->
  
  <!--SQL语句进行四表联查-->
    <select id="selectfindByName" resultMap="BaseResultMap" parameterType="java.lang.String">
     select
    u.id,u.username,u.password,u.iconURL,r.roleId,r.roleName,
    m.menuId,m.menuName,m.menuURL,m.fatherId,f.funId,f.funName,f.funURL
    from user u left join role r
    on u.roleId =r.roleId left join role_menu rm on
    r.roleId = rm.roleId left join menu m on
    rm.menuId = m.menuId left join funs f on
    m.menuId = f.menuId
    where u.username like concat('%',#{username},'%')

  </select>
</mapper>

四.测试类

package com.wmh.test;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.wmh.mapper.UserMapper;
import com.wmh.pojo.User;

public class Test01 {
	public static void main(String[] args) {
		
		try {
			InputStream is= Resources.getResourceAsStream("mybatis-config.xml");
	        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
	        SqlSession session= sqlSessionFactory.openSession();
	        UserMapper mapper  =session.getMapper(UserMapper.class);
	        User u =mapper.selectfindByName("zhangsan");
	        System.out.println(u);
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		

	}

}

测试结果

User{id='00001', role=Role{roleid=1, menus=[Menu{menuid=1, menuname='系统菜单', menuurl='null', roles=null, funss=[Funs{funid=1, funname='主页', funurl='index.jsp', menuid=1}, Funs{funid=2, funname='头页面', funurl='header.jsp', menuid=1}, Funs{funid=3, funname='菜单页面', funurl='menu.jsp', menuid=1}, Funs{funid=4, funname='操作页面', funurl='body.jsp', menuid=1}, Funs{funid=5, funname='底页面', funurl='footer.jsp', menuid=1}, Funs{funid=6, funname='成功页面', funurl='ok.jsp', menuid=1}, Funs{funid=7, funname='错误页面', funurl='error.jsp', menuid=1}, Funs{funid=8, funname='角色错误页面', funurl='norole.jsp', menuid=1}], fatherid=-1}, Menu{menuid=2, menuname='用户管理', menuurl='null', roles=null, funss=[Funs{funid=null, funname='null', funurl='null', menuid=2}], fatherid=1}, Menu{menuid=3, menuname='添加用户', menuurl='register.jsp', roles=null, funss=[Funs{funid=50, funname='添加用户页面', funurl='register.jsp', menuid=3}, Funs{funid=51, funname='添加用户的动作', funurl='registerServlet.do', menuid=3}], fatherid=2}, Menu{menuid=4, menuname='查询用户', menuurl='body.jsp', roles=null, funss=[Funs{funid=60, funname='查询用户页面', funurl='body.jsp', menuid=4}, Funs{funid=61, funname='查询用户动作', funurl='registerServlet.do', menuid=4}, Funs{funid=62, funname='显示用户页面', funurl='body.jsp', menuid=4}, Funs{funid=63, funname='预更新用户查询动作', funurl='preUpdata.do', menuid=4}, Funs{funid=64, funname='更新用户页面', funurl='updataUser.jsp', menuid=4}, Funs{funid=65, funname='更新用户动作', funurl='updataUser.do', menuid=4}, Funs{funid=66, funname='删除用户动作', funurl='deleteUser.do', menuid=4}], fatherid=2}, Menu{menuid=5, menuname='商品管理', menuurl='null', roles=null, funss=[Funs{funid=null, funname='null', funurl='null', menuid=5}], fatherid=1}, Menu{menuid=6, menuname='添加商品', menuurl='addGoods.jsp', roles=null, funss=[Funs{funid=70, funname='添加商品页面', funurl='addProduct.jsp', menuid=6}, Funs{funid=71, funname='添加商品动作', funurl='addProduct.do', menuid=6}], fatherid=5}, Menu{menuid=7, menuname='查询商品', menuurl='findGoods.jsp', roles=null, funss=[Funs{funid=80, funname='查询商品页面', funurl='findProduct.jsp', menuid=7}, Funs{funid=81, funname='查询商品动作', funurl='findProduct.do', menuid=7}, Funs{funid=82, funname='预更新商品动作', funurl='preUpdataProduct.do', menuid=7}, Funs{funid=83, funname='显示更新商品页面', funurl='updataProduct.jsp', menuid=7}, Funs{funid=84, funname='更新商品动作', funurl='updataProduct.do', menuid=7}, Funs{funid=85, funname='删除商品动作', funurl='deleteProduct.do', menuid=7}], fatherid=5}, Menu{menuid=8, menuname='查看购物车', menuurl='null', roles=null, funss=[Funs{funid=null, funname='null', funurl='null', menuid=8}], fatherid=1}, Menu{menuid=9, menuname='购物车', menuurl='shopping.jsp', roles=null, funss=[Funs{funid=90, funname='查看购物车页面', funurl='findcar.jsp', menuid=9}], fatherid=8}], rolename='管理员'}, username='zhangsan', password='1231230', roleid=1, iconurl='null'}