SpringSecurity原理剖析及其实战（二）

Spring Security整合数据库认证服务器

Spring security整合数据库认证本人这边所使用的技术栈如下：

技术	描述
mybatis	持久层
mybatis-plus	MyBatis (opens new window) 的增强工具
hutool	java工具类库
lombok	一种 Java™ 实用工具
EasyCode	代码生成器

后面的整合oauth2、jwt中也会大量以上技术，包括最后整套Spring Cloud Alibaba - vue前后端分离体系
废话到这，下面正式开始

• 1.先导入依赖

  
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.6</version>
        </dependency>
         <!-- hutool-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.15</version>
        </dependency>

         <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>
        <!-- MyBatis-Plus模板引擎 -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.1</version>
        </dependency>
        <!--热部署  每次修改代码springboot自动重启服务-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>

整体目录如下，

我这边使用的是EasyCode代码生成器，也可自己去配置mybatis的代码生成器，如需使用EasyCode代码生成器，Plugins 搜索easycode下载重启idea即可

这里分享一下我自定义的模版，只需点击导入模版输入：171c65ee922df8a54fb81abdf018761f，即可使用，如过期评论区下方留言即可，后面会逐步完善模版

EasyCode使用方式如下图，选择服务，生成路径以后需要生成的文件

• 2.application.yaml的连接池配置如下(数据库表已经在上一章最后面贴出来了)

server:
  port: 8881
  servlet:
      context-path: /
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/hejinwen?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    username: root
    password: root
    hikari:
      minimum-idle: 5
      idle-timeout: 600000
      maximum-pool-size: 10
      auto-commit: true
      pool-name: MyHikariCP
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: SELECT 1

• 3.dao/TbPermissionDao.java

package com.csw.jdbc.dao;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.csw.jdbc.entity.TbPermission;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
 
/**
 * 权限表(TbPermission)表数据库访问层
 * @author chengshengwen
 * @description 
 * @since 2021-11-01 00:20:07
 */
@Mapper
public interface TbPermissionDao extends BaseMapper<TbPermission> {
    /**
     * 根据用户id查找数据
     * @param userId
     * @return
     */
    List<TbPermission> selectByUserId(Long userId);
}

mapper/TbPermissionMapper.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.csw.jdbc.dao.TbPermissionDao">
 
    <select id="selectByUserId" resultType="com.csw.jdbc.entity.TbPermission">
        select p.* from tb_user as u
        LEFT JOIN tb_user_role ur on u.id = ur.user_id
        LEFT JOIN tb_role as r  on r.id = ur.role_id
        LEFT JOIN tb_role_permission as p on r.id = p.permission_id
        WHERE u.id = #{userId}
    </select>

   
</mapper>

如下图，主要代码是selectByUserId()方法，其他是EasyCo de生成的方法，后面会逐步完善EasyCode模版：

service/TbPermissionService.java

package com.csw.jdbc.service;
 
import com.csw.jdbc.entity.TbPermission;
import java.util.List;

 /**
 * 权限表(TbPermission)表服务接口
 * @author chengshengwen
 * @description 
 * @since 2021-11-01 00:20:08
 */
public interface TbPermissionService {
 
    //根据用户id查询
    List<TbPermission>  selectByUserId(Long userId);

}

service/impl/TbPermissionServiceImpl.java

package com.csw.jdbc.service.impl;
 
import com.csw.jdbc.dao.TbUserDao;
import com.csw.jdbc.entity.TbPermission;
import com.csw.jdbc.dao.TbPermissionDao;
import com.csw.jdbc.service.TbPermissionService;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
 
 
 /**
 * 权限表(TbPermission)表服务实现类
 * @author chengshengwen
 * @description 
 * @since 2021-11-01 00:20:09
 */
@Service
@Transactional 
@AllArgsConstructor
public class TbPermissionServiceImpl implements TbPermissionService {

    private final TbPermissionDao tbPermissionDao;
 
     @Override
     public List<TbPermission> selectByUserId(Long userId) {
         return tbPermissionDao.selectByUserId(userId);
     }


 }

dao/TbUserDao.java

package com.csw.jdbc.dao;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.csw.jdbc.entity.TbUser;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;
 
/**
 * 用户表(TbUser)表数据库访问层
 * @author chengshengwen
 * @description 
 * @since 2021-10-31 23:27:00
 */
@Mapper
public interface TbUserDao extends BaseMapper<TbUser> {
}

service/TbUserService.java

//根据用户名获取用户信息
    package com.csw.jdbc.service;
 
import com.csw.jdbc.entity.TbUser;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;

import java.util.List;
 
 /**
 * 用户表(TbUser)表服务接口
 * @author chengshengwen
 * @description 
 * @since 2021-10-31 23:27:01
 */
public interface TbUserService {
 
    //根据用户名获取用户信息
    TbUser getByUsername(String username);
 
}

service/impl/TbUserServiceImpl.java（核心代码）

package com.csw.jdbc.service.impl;
 
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.csw.jdbc.dao.TbPermissionDao;
import com.csw.jdbc.entity.TbPermission;
import com.csw.jdbc.entity.TbUser;
import com.csw.jdbc.dao.TbUserDao;
import com.csw.jdbc.service.TbUserService;
import lombok.AllArgsConstructor;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
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;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;
 
 
 /**
 * 用户表(TbUser)表服务实现类
 * @author chengshengwen
 * @description 
 * @since 2021-10-31 23:27:01
 */
@Service
@Transactional
@AllArgsConstructor
public class TbUserServiceImpl implements TbUserService,UserDetailsService{


    private final TbUserDao tbUserDao;

    private final TbPermissionDao tbPermissionDao;
 
 
     @Override
     public TbUser getByUsername(String username) {
         QueryWrapper<TbUser> wrapper = new QueryWrapper<>();
         wrapper.eq("username",username);
         return this.tbUserDao.selectOne(wrapper);
     }

     @Override
     public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        //从数据库查用户
        TbUser user = getByUsername(username);
        ArrayList<GrantedAuthority> authorities = new ArrayList<>();
        if(user != null) {
            List<TbPermission> tbPermissions = tbPermissionDao.selectByUserId(user.getId());

            //设置权限
            tbPermissions.forEach(permissions -> {
                if(permissions != null && !StrUtil.isEmpty(permissions.getEnname())) {
                    SimpleGrantedAuthority grantedAuthority = new SimpleGrantedAuthority(permissions.getEnname());
                    authorities.add(grantedAuthority);
                }
            });
            //封装成UserDetails的实现类
            return new org.springframework.security.core.userdetails.User(
                    user.getUsername(),user.getPassword(),authorities);
        }else {
            throw new UsernameNotFoundException("用户名不存在");
        }
     }
 }

entity/TbUser.java

package com.csw.jdbc.entity;

import java.util.Date;
import java.io.Serializable;
import lombok.*;
/**
 * 用户表(TbUser)实体类
 * @author chengshengwen
 * @description 
 * @since 2021-10-31 23:26:58
 */
@Data
public class TbUser implements Serializable {
    private static final long serialVersionUID = 734210779083900189L;

        private Long id;

    /*** 用户名 */    private String username;

    /*** 密码，加密存储 */    private String password;

    /*** 注册手机号 */    private String phone;

    /*** 注册邮箱 */    private String email;

        private Date created;

        private Date updated;
}

entity/TbRole.java

package com.csw.jdbc.entity;

import java.util.Date;
import java.io.Serializable;
import lombok.*;
/**
 * 角色表(TbRole)实体类
 * @author chengshengwen
 * @description 
 * @since 2021-11-01 11:16:22
 */
@Data
public class TbRole implements Serializable {
    private static final long serialVersionUID = 768391666783014299L;

        private Long id;

    /*** 父角色 */    private Long parentId;

    /*** 角色名称 */    private String name;

    /*** 角色英文名称 */    private String enname;

    /*** 备注 */    private String description;

        private Date created;

        private Date updated;
}

entity/TbPermission.java

package com.csw.jdbc.entity;

import java.util.Date;
import java.io.Serializable;
import lombok.*;
/**
 * 权限表(TbPermission)实体类
 * @author chengshengwen
 * @description 
 * @since 2021-11-01 00:20:05
 */
@Data
public class TbPermission implements Serializable {
    private static final long serialVersionUID = -91733717429724780L;

        private Long id;

    /*** 父权限 */    private Long parentId;

    /*** 权限名称 */    private String name;

    /*** 权限英文名称 */    private String enname;

    /*** 授权路径 */    private String url;

    /*** 备注 */    private String description;

        private Date created;

        private Date updated; 
}

以上代码都可以使用EasyCode或者其他代码生成器生成的，核心代码只有TbUserServiceImpl.java里面的…
config/WebSecurityConfig.java

package com.csw.jdbc.config;

import com.csw.jdbc.service.impl.TbUserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@Configuration
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private TbUserServiceImpl userServiceImpl;


    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        //设置UserDetailsService的实现类
        auth.userDetailsService(userServiceImpl);
    }
    @Bean
    public PasswordEncoder passwordEncoder(){
        return new BCryptPasswordEncoder();
    }
}

到这里注意，我已经在TbUserServiceImpl去实现了UserDetailsService接口的loadUserByUsername方法

整体代码已上传至（https://gitee.com/JAVA8888/spring-security.git），顺便在这里打个广告，推荐一个不错的SpringCloud Alibaba + vue项目（https://gitee.com/youlaitech/youlai-mall）
我们试下登录后的效果：

debug登录认证效果如下图：

以上就是Spring security整合数据库认证，都是一些比较基础的东西，为整合Oauth2打下点基础，争取下篇把Spring Security完结了，喜欢的朋友点个关注，您的关注或点赞都是博主的动力
-> SpringSecurity原理剖析及其实战（三）