一、數據庫結構

二、查詢所有數據記錄（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='http://www.cgvv.com.cn/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 外關聯查詢內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！