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

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 roleEntityList;

private List authorityEntityList;

}

2、数据访问层dao、Mhttp://apper

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 selectAllUserRoleAuthority();

}

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

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

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 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 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 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 clasiVICfzaFDs 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

}

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

iVICfzaFD 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 userhttp://_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

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

package cn.lemon.demo.service;

import java.util.List;

import java.util.Map;

public interface IUserService {

List

}

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

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

JSONObject json = new JSONObject();

json.put("mapList",mapList);

System.out.println(json.toJSONString());

return json.toJSONString();

}

}

5、前端页面 index.html

运行 localhost:8080 显示：

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。