MyBatis 三表外关联查询的实现(用户、角色、权限)
CSDN-Lemon 人气:0本文着重讲解了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; @Data public 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; @Data public 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; @Data public 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; @Repository public 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 id="selectAllUserRoleAuthority" 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 id="userMap" 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 id="roleMap" 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 id="authorityMap" 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; @Service public 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; @Service public 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; @Data public 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; @Data public 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; @Data public 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 id="selectAllUserRoleAuthority" 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; @Service public 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="@{/static/js/jquery-1.11.3.min.js}"></script> </head> <body> <div id="head"> <table width="100%" align="center" 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 显示:
加载全部内容