MyBatis 三表外关联查询的实现(用户、角色、权限)

一、数据库结构

二、查询所有数据记录（SQL语句）

SQL语句：

SELECT u.*, r.*, a.* FROM( ( ( user u INNER JOIN user_role ur ON ur.user_id = u.user_id ) INNER JOIN role r ON r.role_id = ur.role_id ) INNER JOIN role_authority ra ON ra.role_id = r.role_id)INNER JOIN authority a ON ra.authority_id = a.authority_id

三、详细代码（第一中方式）

1、实体类entity

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;@Datapublic class AuthorityEntity implements Serializable { private Integer authorityId; private String authorityName; private String authorityDescription;}

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;@Datapublic class RoleEntity implements Serializable { private Integer roleId; private String roleName; private String roleDescription;}

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;import java.util.Date;import java.util.List;@Datapublic class UserEntity implements Serializable { private Integer userId; private String userName; private String userSex; private Date userBirthday; private String userAddress; private List<RoleEntity> roleEntityList; private List<AuthorityEntity> authorityEntityList;}

2、数据访问层dao、Mapper

package cn.lemon.demo.dao;import cn.lemon.demo.entity.UserEntity;import org.springframework.stereotype.Repository;import java.util.List;@Repositorypublic interface IUserDao { /** * 查询所有关联的数据 * * @return */ List<UserEntity> selectAllUserRoleAuthority();}

<?xml version='1.0' encoding='UTF-8'?><!DOCTYPE mapper PUBLIC '-//mybatis.org//DTD Mapper 3.0//EN' 'http://mybatis.org/dtd/mybatis-3-mapper.dtd'><mapper namespace='cn.lemon.demo.dao.IUserDao'> <select resultMap='userMap'> SELECT u.*, r.*, a.* FROM ( (( user u INNER JOIN user_role ur ON ur.user_id = u.user_id )INNER JOIN role r ON r.role_id = ur.role_id ) INNER JOIN role_authority ra ON ra.role_id = r.role_id ) INNER JOIN authority a ON ra.authority_id = a.authority_id </select> <resultMap type='cn.lemon.demo.entity.UserEntity'> <id property='userId' column='user_id'/> <result property='userName' column='user_name'/> <result property='userSex' column='user_sex'/> <result property='userBirthday' column='user_birthday'/> <result property='userAddress' column='user_address'/> <collection property='roleEntityList' ofType='cn.lemon.demo.entity.RoleEntity' resultMap='roleMap'/> <collection property='authorityEntityList' ofType='cn.lemon.demo.entity.AuthorityEntity' resultMap='authorityMap'/> </resultMap> <resultMap type='cn.lemon.demo.entity.RoleEntity'> <id property='roleId' column='role_id'/> <result property='roleName' column='role_name'/> <result property='roleDescription' column='role_description'/> </resultMap> <resultMap type='cn.lemon.demo.entity.AuthorityEntity'> <id property='authorityId' column='authority_id'/> <result property='authorityName' column='authority_name'/> <result property='authorityDescription' column='authority_description'/> </resultMap></mapper>

3、业务层service

package cn.lemon.demo.service;import cn.lemon.demo.entity.UserEntity;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic interface IUserService { List<UserEntity> selectAllUserRoleAuthority();}

package cn.lemon.demo.service.impl;import cn.lemon.demo.dao.IUserDao;import cn.lemon.demo.entity.UserEntity;import cn.lemon.demo.service.IUserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic class UserServiceImpl implements IUserService { @Autowired private IUserDao userDao; @Override public List<UserEntity> selectAllUserRoleAuthority() { return userDao.selectAllUserRoleAuthority(); }}

4、测试类

package cn.lemon.demo.service.impl;import cn.lemon.demo.entity.UserEntity;import cn.lemon.demo.service.IUserService;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.test.context.junit4.SpringRunner;import java.util.List;@SpringBootTest@RunWith(SpringRunner.class)public class UserServiceImplTest { @Autowired private IUserService userService; @Test public void selectAllUserRoleAuthority() { List<UserEntity> userEntities = userService.selectAllUserRoleAuthority(); for (UserEntity userEntity : userEntities) { System.out.println( '用户姓名：' + userEntity.getUserName() + '用户地址：' + userEntity.getUserAddress() + '权限列表：' + userEntity.getAuthorityEntityList() + '角色列表：' + userEntity.getRoleEntityList()); System.out.println('--------------------------------------'); } }}

四、详细代码（第二中方式）

1、实体类entity （实体类可以省略不写）

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;import java.util.Date;@Datapublic class UserEntity implements Serializable { private Long userId; private String userName; private String userSex; private Date userBirthday; private String userAddress;}

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;@Datapublic class RoleEntity implements Serializable { private Long roleId; private String roleName; private String roleDescription;}

package cn.lemon.demo.entity;import lombok.Data;import java.io.Serializable;@Datapublic class AuthorityEntity implements Serializable { private Long authorityId; private String authorityName; private String authorityDescription;}

2、数据访问层dao、Mapper

package cn.lemon.demo.dao;import java.util.List;import java.util.Map;public interface IUserDao { List<Map> selectAllUserRoleAuthority();}

<?xml version='1.0' encoding='UTF-8'?><!DOCTYPE mapper PUBLIC '-//mybatis.org//DTD Mapper 3.0//EN' 'http://mybatis.org/dtd/mybatis-3-mapper.dtd'><mapper namespace='cn.lemon.demo.dao.IUserDao'> <!--查询 用户信息，角色信息，权限信息--> <select resultType='java.util.Map'> SELECT u.user_id userId, u.user_name userName, u.user_sex userSex, u.user_birthday userBirthday, u.user_address userAddress, r.role_name roleName, r.role_description roleDescription, a.authority_name authorityName, a.authority_description authorityDescription FROM (( ( USER u INNER JOIN user_role ur ON u.user_id = ur.user_id ) INNER JOIN role r ON r.role_id = ur.role_id)INNER JOIN role_authority ra ON ra.role_id = r.role_id ) INNER JOIN authority a ON a.authority_id = ra.authority_id </select></mapper>

3、业务层service （接口及实现类）

package cn.lemon.demo.service;import java.util.List;import java.util.Map;public interface IUserService { List<Map> selectAllUserRoleAuthority();}

package cn.lemon.demo.service.impl;import cn.lemon.demo.dao.IUserDao;import cn.lemon.demo.service.IUserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;import java.util.Map;@Servicepublic class UserServiceImpl implements IUserService { @Autowired private IUserDao userDao; @Override public List<Map> selectAllUserRoleAuthority() { return userDao.selectAllUserRoleAuthority(); }}

4、控制层controller

package cn.lemon.demo.controller;import cn.lemon.demo.service.IUserService;import com.alibaba.fastjson.JSONObject;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.ResponseBody;import java.util.List;import java.util.Map;@Controller@RequestMapping(value = '/')public class SystemController { @Autowired private IUserService userService; /** * 跳转页面 * * @return */ @RequestMapping(value = 'index') public String index() { return 'index'; } /** * 查询所有关联的数据 用户信息，角色信息，权限信息 * @return */ @RequestMapping(value = 'selectAll',method = RequestMethod.POST) @ResponseBody public String selectAll(){ List<Map> mapList = userService.selectAllUserRoleAuthority(); JSONObject json = new JSONObject(); json.put('mapList',mapList); System.out.println(json.toJSONString()); return json.toJSONString(); }}

5、前端页面 index.html

<!DOCTYPE html><html lang='en' xmlns:th='http://www.thymeleaf.org'><head> <meta charset='UTF-8'> <title>首页</title> <script type='text/javascript' th:src='https://www.haobala.com/bcjs/@{/static/js/jquery-1.11.3.min.js}'></script></head><body><div id='head'> <table border='2px' cellspacing='2px'> <thead> <tr> <th>用户编号</th> <th>用户姓名</th> <th>用户性别</th> <th>用户生日</th> <th>用户地址</th> <th>角色名称</th> <th>角色描述</th> <th>权限名称</th> <th>权限描述</th> </tr> </thead> <tbody id='tbody'> </tbody> </table></div><script type='text/javascript'> $(function () { $.ajax({ type: 'post', url: ’/selectAll’, contentType: 'application/json;charset=utf-8', dataType: ’json’, //async: false,/*表示请求为同步方式*/ success: function (data) {//在<tbody>中追加数据for (var i = 0; i < data.mapList.length; i++) { $('#tbody').append('<tr><td>' + data.mapList[i].userId + '</td>' + '<td>' + data.mapList[i].userName + '</td>' + '<td>' + data.mapList[i].userSex + '</td>' + '<td>' + data.mapList[i].userBirthday + '</td>' + '<td>' + data.mapList[i].userAddress + '</td>' + '<td>' + data.mapList[i].roleName + '</td>' + '<td>' + data.mapList[i].roleDescription + '</td>' + '<td>' + data.mapList[i].authorityName + '</td>' + '<td>' + data.mapList[i].authorityDescription + '</td>' + '</tr>');} }, error: function () {window.alert('查询失败'); } }); });</script></body></html>

运行 localhost:8080 显示：

到此这篇关于MyBatis 三表外关联查询的实现(用户、角色、权限)的文章就介绍到这了,更多相关MyBatis 外关联查询内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！