一、數(shù)據(jù)庫結(jié)構(gòu)

二、查詢所有數(shù)據(jù)記錄（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、數(shù)據(jù)訪問層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 { /** * 查詢所有關聯(lián)的數(shù)據(jù) * * @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、業(yè)務層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() + '權(quán)限列表：' + 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、數(shù)據(jù)訪問層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'> <!--查詢 用戶信息，角色信息，權(quán)限信息--> <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、業(yè)務層service （接口及實現(xiàn)類）

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; /** * 跳轉(zhuǎn)頁面 * * @return */ @RequestMapping(value = 'index') public String index() { return 'index'; } /** * 查詢所有關聯(lián)的數(shù)據(jù) 用戶信息，角色信息，權(quán)限信息 * @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>權(quán)限名稱</th> <th>權(quán)限描述</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>中追加數(shù)據(jù)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 三表外關聯(lián)查詢的實現(xiàn)(用戶、角色、權(quán)限)的文章就介紹到這了,更多相關MyBatis 外關聯(lián)查詢內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網(wǎng)！