1、预置数据库数据及其他环境信息
1.1 准备数据库表和数据(这里我已经准备好了)
预置数据信息
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for bs_menu
-- ----------------------------
DROP TABLE IF EXISTS `bs_menu`;
CREATE TABLE `bs_menu` (
`menu_id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
`menu_type` tinyint(2) NULL DEFAULT NULL COMMENT '菜单类型1-菜单2-二级菜单3-按钮/链接',
`menu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
`parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父级ID',
`status` tinyint(1) NULL DEFAULT NULL COMMENT '状态',
`menu_perms` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '权限',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注信息',
PRIMARY KEY (`menu_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bs_menu
-- ----------------------------
INSERT INTO `bs_menu` VALUES (1, 1, '系统管理', NULL, 1, '', NULL);
INSERT INTO `bs_menu` VALUES (2, 2, '用户管理', 1, 1, '', NULL);
INSERT INTO `bs_menu` VALUES (3, 3, '用户列表', 2, 1, 'system:user:list', NULL);
INSERT INTO `bs_menu` VALUES (4, 3, '新增用户', 2, 1, 'system:user:insert', NULL);
INSERT INTO `bs_menu` VALUES (5, 3, '修改用户', 2, 1, 'system:user:update', NULL);
INSERT INTO `bs_menu` VALUES (6, 3, '删除用户', 2, 1, 'system:user:delete', NULL);
INSERT INTO `bs_menu` VALUES (7, 2, '角色管理', 1, 1, '', NULL);
INSERT INTO `bs_menu` VALUES (8, 3, '角色列表', 7, 1, 'system:role:list', NULL);
INSERT INTO `bs_menu` VALUES (9, 3, '新增角色', 7, 1, 'system:role:insert', NULL);
INSERT INTO `bs_menu` VALUES (10, 3, '修改角色', 7, 1, 'system:role:update', NULL);
INSERT INTO `bs_menu` VALUES (11, 3, '删除角色', 7, 1, 'system:role:delete', NULL);
INSERT INTO `bs_menu` VALUES (12, 2, '菜单管理', 1, 1, '', NULL);
INSERT INTO `bs_menu` VALUES (13, 3, '菜单列表', 12, 1, 'system:menu:list', NULL);
INSERT INTO `bs_menu` VALUES (14, 3, '新增菜单', 12, 1, 'system:menu:insert', NULL);
INSERT INTO `bs_menu` VALUES (15, 3, '修改菜单', 12, 1, 'system:menu:update', NULL);
INSERT INTO `bs_menu` VALUES (16, 3, '删除菜单', 12, 1, 'system:menu:delete', NULL);
INSERT INTO `bs_menu` VALUES (17, 1, '商品管理', NULL, 1, NULL, NULL);
INSERT INTO `bs_menu` VALUES (18, 2, '分类管理', 17, 1, NULL, NULL);
INSERT INTO `bs_menu` VALUES (19, 2, '商品列表', 17, 1, NULL, NULL);
INSERT INTO `bs_menu` VALUES (20, 3, '分类列表', 18, 1, 'good:cate:list', NULL);
INSERT INTO `bs_menu` VALUES (21, 3, '新增分类', 18, 1, 'good:cate:insert', NULL);
INSERT INTO `bs_menu` VALUES (22, 3, '修改分类', 18, 1, 'good:cate:update', NULL);
INSERT INTO `bs_menu` VALUES (23, 3, '删除分类', 18, 1, 'good:cate:delete', NULL);
INSERT INTO `bs_menu` VALUES (24, 3, '新增商品', 19, 1, 'good:insert', NULL);
INSERT INTO `bs_menu` VALUES (25, 3, '修改商品', 20, 1, 'good:update', NULL);
INSERT INTO `bs_menu` VALUES (26, 3, '删除商品', 21, 1, 'good:delete', NULL);
-- ----------------------------
-- Table structure for bs_role
-- ----------------------------
DROP TABLE IF EXISTS `bs_role`;
CREATE TABLE `bs_role` (
`role_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
`role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称',
`used` tinyint(255) NULL DEFAULT NULL COMMENT '是否启用',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`role_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bs_role
-- ----------------------------
INSERT INTO `bs_role` VALUES (1, 'admin', 1, NULL);
INSERT INTO `bs_role` VALUES (2, 'common', 1, NULL);
-- ----------------------------
-- Table structure for bs_role_permisson
-- ----------------------------
DROP TABLE IF EXISTS `bs_role_permisson`;
CREATE TABLE `bs_role_permisson` (
`menu_id` bigint(11) NOT NULL COMMENT '菜单ID',
`role_id` int(8) NOT NULL COMMENT '角色ID'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bs_role_permisson
-- ----------------------------
INSERT INTO `bs_role_permisson` VALUES (1, 1);
INSERT INTO `bs_role_permisson` VALUES (2, 1);
INSERT INTO `bs_role_permisson` VALUES (3, 1);
INSERT INTO `bs_role_permisson` VALUES (4, 1);
INSERT INTO `bs_role_permisson` VALUES (5, 1);
INSERT INTO `bs_role_permisson` VALUES (6, 1);
INSERT INTO `bs_role_permisson` VALUES (7, 1);
INSERT INTO `bs_role_permisson` VALUES (8, 1);
INSERT INTO `bs_role_permisson` VALUES (9, 1);
INSERT INTO `bs_role_permisson` VALUES (10, 1);
INSERT INTO `bs_role_permisson` VALUES (11, 1);
INSERT INTO `bs_role_permisson` VALUES (12, 1);
INSERT INTO `bs_role_permisson` VALUES (13, 1);
INSERT INTO `bs_role_permisson` VALUES (14, 1);
INSERT INTO `bs_role_permisson` VALUES (15, 1);
INSERT INTO `bs_role_permisson` VALUES (16, 1);
INSERT INTO `bs_role_permisson` VALUES (18, 1);
INSERT INTO `bs_role_permisson` VALUES (19, 1);
INSERT INTO `bs_role_permisson` VALUES (20, 1);
INSERT INTO `bs_role_permisson` VALUES (21, 1);
INSERT INTO `bs_role_permisson` VALUES (22, 1);
INSERT INTO `bs_role_permisson` VALUES (23, 1);
INSERT INTO `bs_role_permisson` VALUES (24, 1);
INSERT INTO `bs_role_permisson` VALUES (25, 1);
INSERT INTO `bs_role_permisson` VALUES (26, 1);
INSERT INTO `bs_role_permisson` VALUES (17, 2);
INSERT INTO `bs_role_permisson` VALUES (18, 2);
INSERT INTO `bs_role_permisson` VALUES (19, 2);
INSERT INTO `bs_role_permisson` VALUES (20, 2);
INSERT INTO `bs_role_permisson` VALUES (21, 2);
INSERT INTO `bs_role_permisson` VALUES (22, 2);
INSERT INTO `bs_role_permisson` VALUES (24, 2);
INSERT INTO `bs_role_permisson` VALUES (25, 2);
-- ----------------------------
-- Table structure for bs_user
-- ----------------------------
DROP TABLE IF EXISTS `bs_user`;
CREATE TABLE `bs_user` (
`user_id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户密码',
PRIMARY KEY (`user_id`) USING BTREE,
INDEX `uni_user_name`(`username`) USING BTREE COMMENT '用户名唯一'
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bs_user
-- ----------------------------
INSERT INTO `bs_user` VALUES (1, 'manager', '$2a$10$4yE1Mz9Oph07Tli6pEGvxOm1m/.EW8U/CSd6MVCYKhsh8byDPQEQG');
INSERT INTO `bs_user` VALUES (2, 'student001', '$2a$10$3r69Qo0IVhKqSRvuAM8kUuLcEWMwdzWIK.ShOHNoALyQbueMxfIpG');
-- ----------------------------
-- Table structure for bs_user_role
-- ----------------------------
DROP TABLE IF EXISTS `bs_user_role`;
CREATE TABLE `bs_user_role` (
`user_id` bigint(11) NOT NULL COMMENT '用户ID',
`role_id` int(8) NOT NULL COMMENT '角色ID',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bs_user_role
-- ----------------------------
INSERT INTO `bs_user_role` VALUES (1, 1);
INSERT INTO `bs_user_role` VALUES (2, 2);
SET FOREIGN_KEY_CHECKS = 1;
1.2 整合Mybatis框架和MySQL驱动
我这里还使用了druid数据库连接池，起始有没有没所谓。
- pom.xml文件引入依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.75</version>
</dependency>
- application.yml和application-dev.yml配置数据库连接信息
spring:
datasource:
druid:
# 改成你自己的mysql数据库的host、port以及数据库名
url: jdbc:mysql://192.168.1.218:3306/boot-security?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
# 修改成你的数据库用户名
username: root
# 修改成你的数据库密码
password: root
mybatis:
# 这里修改成你项目中mapper文件的存放路径
mapper-locations: classpath*:mapper/**/*.xml
# 这里修改成你项目中实体类的包路径
type-aliases-package: com.example.**.pojo
1.3 设置配置类ApplicationConfig，让mapper包在Springboot项目启动时可以被扫描到
这里有两种实现方式
- 第一种：直接在项目的启动类上添加@MapperScan注解
- 第二种：自定义配置类，在配置类上添加@MapperScan注解
两种方式都可以实现在Springboot项目启动时可以被扫描到mappe包下的所有*Mapper文件。我个人喜欢更第二种，我不太喜欢在
springboot项目的启动类上添加太多的注解。
1.4 代码层面的实现
- service层
package com.yige.service;
import com.yige.pojo.SygUser;
public interface SygUserSerivce {
SygUser selectUserByUserName(String username);
}
- service.impl实现类
package com.yige.service.impl;
import com.yige.mapper.SygUserMapper;
import com.yige.pojo.SygUser;
import com.yige.service.SygUserSerivce;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class SygUserServiceImpl implements SygUserSerivce {
@Autowired
private SygUserMapper userMapper;
@Override
public SygUser selectUserByUserName(String username) {
return userMapper.selectSygUserByUsername(username);
}
}
- mapper层
package com.yige.mapper;
import com.yige.pojo.SygUser;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface SygUserMapper {
SygUser selectSygUserByUsername(@Param("username") String username);
}
- *Mapper.xml层
<?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="com.yige.mapper.SygUserMapper">
<resultMap id="baseSygUserMap" type="com.yige.pojo.SygUser">
<id property="userId" column="user_id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
</resultMap>
<select id="selectSygUserByUsername" resultMap="baseSygUserMap">
select
user_id, username, password
from
bs_user
where
username = #{username}
</select>
</mapper>
- 实体类
package com.yige.pojo;
import com.fasterxml.jackson.annotation.JsonIgnore;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import java.io.Serializable;
import java.util.Collection;
public class SygUser implements Serializable {
private Long userId;
private String username;
// @JsonIgnore(value = false)
private String password;
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "SygUser{" +
"userId=" + userId +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
2、编写一个测试类，验证一下从数据库获取用户数据信息
测试类代码
package com.yige;
import com.yige.service.SygUserSerivce;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
/**
* @Author: karma
* @Date: 2022/3/21 0021 - 03 - 21 - 15:16
* @Description: com.yige
* @version: 1.0
*/
@SpringBootTest
public class SygUserServiceTest {
@Autowired
private SygUserSerivce userSerivce;
@Test
public void selectUserByNameTest() {
System.out.println(userSerivce.selectUserByUserName("student001"));
}
}
测试结果：
通过以上步骤， 我们数据库层面的接口就写完了， 环境已经都准备好了
3、重写MyUserDetailsServiceImpl实现类
package com.yige.service.impl;
import com.yige.pojo.SygUser;
import com.yige.service.SygUserSerivce;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.authority.AuthorityUtils;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;
/**
* @Author: karma
* @Date: 2022/3/21 0021 - 03 - 21 - 11:20
* @Description: com.yige.service.impl
* @version: 1.0
*/
@Service
public class MyUserDetailsServiceImpl implements UserDetailsService {
@Autowired
private SygUserSerivce userSerivce;
@Override
public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
SygUser sygUser = userSerivce.selectUserByUserName(userName);
if(null == sygUser) throw new UsernameNotFoundException("用户不存在");
// 因为我们这里还没有实现查询用户权限的相关功能， 这里先使用 AuthorityUtils.NO_AUTHORITIES代替
return new User(sygUser.getUsername(), sygUser.getPassword(), AuthorityUtils.NO_AUTHORITIES);
}
}
重新启动项目，使用数据库里面预置的两个账号manager和student001来进行测试
账号：student001 密码：student001，登录结果：成功
账号：manager 密码：manager，登录结果：成功
通过以上步骤我们就可以实现Spring Security结合MySQL数据库完成用户的登录认证了。
4、下一篇主要内容
虽然现在实现了用户认证，但是用户权限这块儿的内容还没有写活， 还是使用的 AuthorityUtils.NO_AUTHORITIES来代替权限信息，下一篇主要讲解怎么把权限这块儿写活，如果喜欢我的文章内容， 记得关注我哦， 我会尽快更新~。