第103天学习打卡（SpringBoot 整合JDBC使用 自定义数据源 整合Driud 数据源 整合Mybatis SpringSecurity）

整合JDBC使用

package com.kuang.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;


    //查询数据库的所有信息
    //没有实体类，数据库中的东西，怎么获取？Map
    @GetMapping("/userList")//GetMapping只限定一个
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> list_map = jdbcTemplate.queryForList(sql);
        return list_map;

    }

    //这里我们使用jdbc  直接把数据写到sql里面 所以这里不用传值
    @GetMapping("/addUser")
    public String addUser(){
        String sql = "insert into mybatis.user(id, name, pwd) values(4,'小明','123456')";
        jdbcTemplate.update(sql);
        return "update-ok";


    }

    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id")int id){
        String sql = "update mybatis.user set name=?, pwd=? where id="+id;

        //封装
        Object[] objects = new Object[2];
        objects[0] = "小明";
        objects[1] = "zzzzzzz";
        jdbcTemplate.update(sql,objects);//上面的sqp是预编译sql  需要传到这里
        return "updateUser-ok";


    }

    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql = "delete from mybatis.user where id = ?";
        jdbcTemplate.update(sql,id);
        return "deleteUser-ok";


    }
    
}

自定义数据源 DruidDataSource

DURID简介

Druid是阿里巴巴开源平台上一个数据库连接池实现，结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控。

Druid可以很好的监控SB池连接和SQL的执行情况，天生就是针对监控而生的DB连接池。

Spring Boot2.0以上默认使用Hikari数据源，可以说Hikari与Driud都是当前java web上最优秀的数据源。

HikariDataSource号称Java WEB当前速度最快的数据源，相比于传统的C3P0、DBCP、Tomcat、等连接池优秀

引入数据源

第一步需要在应用的pom.xml文件中添加上Druid数据源依赖。

获取的官网地址：https://mvnrepository.com/artifact/com.alibaba/druid

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

log4j依赖：https://mvnrepository.com/artifact/log4j/log4j

<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

出现的错误： Failed to load property source from ‘file:/C:/Users/HP/Desktop/springboot/springboot-07-data/target/classes/application.yml’ (classpath:/application.yml)

ERROR org.springframework.boot.SpringApplication - Application run failed

解决办法：把application.yml里面的注解全部删掉，重新启动就可以了

application.yml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
# springboot 默认是不注入这些属性值的，需要自己绑定
# druid数据源专有配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
# 配置监控统计拦截器的fliters,stat:监控统计，log4j:日志记录

    filters: stat,wall,log4j2
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

整合Druid数据源

DruidConfig.java

package com.kuang.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }

    //后台监控 Servlet 因为SpringBoot内置了servlet 容器   内置的容器没有web.xml 通过ServletRegistrationBean去注册
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        //后台需要有人登录，账号密码配置
        HashMap<String, String> initParameters = new HashMap<>();

        //增加配置
        initParameters.put("loginUsername","admin");//登录的key loginUsername loginPassword是固定的 不可以写其他的
        initParameters.put("loginPassword","123456");

        //允许谁可以访问
        initParameters.put("allow","");
       //禁止谁能访问  initParameters.put("kuangshen","192.168.11.123");
        bean.setInitParameters(initParameters);//设置初始化参数
        return bean;

    }
    //filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        //可以过滤哪些请求呢？？
        HashMap<String,String> initParameters = new HashMap<>();
        //这些东西不进行统计
        initParameters.put("exclusion","*.js,*.css,/druid/*");

        bean.setInitParameters(initParameters);


        return bean;
    }
}

整合Mybatis

1.导入包

2.配置文件

3.mybatis配置

4.编写sql

5.service层调用dao层

6.controller层调用service层

导入pom.xml依赖

官网地址：https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter

<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>

application.properties

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

连接数据库：

jdbc:mysql://localhost:3306/?serverTimezone=GMT

导入lombok依赖

   <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

Controller

UserController.java

package com.kuang.controller;

import com.kuang.mapper.UserMapper;
import com.kuang.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {
    @Autowired
    private UserMapper userMapper;

    @GetMapping("/queryUserList")
    public  List<User> queryUserList(){
        List<User> userList = userMapper.queryUserList();
        for (User user : userList) {
            System.out.println(user);
        }
        return userList;
    }
    //添加一个用户
    @GetMapping("/addUser")
    public String addUser(){
        userMapper.addUser(new User(6,"阿毛","223344222"));
        return "ok";
    }
    //修改一个用户
    @GetMapping("/updateUser")
    public String updateUser(){
        userMapper.updateUser(new User(6,"阿毛","999900008"));
        return "ok";
    }
    //根据Id删除用户
    @GetMapping("/deleteUser")
    public String deleteUser(){
        userMapper.deleteUser(6);
        return "ok";
    }

}

mapper

UserMapper.java

package com.kuang.mapper;

import com.kuang.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

//这个注解表示了这是一个mybatis的mapper类  mapper 相当于dao   dao调用@Repository或者@Component
@Mapper
@Repository
public interface UserMapper {

    List<User> queryUserList();
    User queryUserById(int id);
    int addUser(User user);
    int updateUser(User user);
    int deleteUser(int id);
}

pojo

package com.kuang.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private int id;
    private String name;
    private String pwd;

}

resources -mybatis -mapper

UserMapper.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.kuang.mapper.UserMapper">
    
    <select id="queryUserList" resultType="User">
        select * from user
    </select>

    <select id="queryUserById" resultType="User">
        select * from user where id = #{id}
    </select>

    <insert id="addUser" parameterType="User">
        insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
    </insert>

    <update id="updateUser" parameterType="User">
        update user set name=#{name},pwd=#{pwd} where id = #{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </delete>

</mapper>

M:数据和业务

C:交接

V:HTML

SpringSecurity(安全)

Spring Security 是针对Spring项目的安全框架，也是Spring Boot底层安全模块默认的技术选型，他可以实现强大的Web安全控制，对于安全控制，我们仅需要引入spring-boot-starter-security模块，进行少量的配置，即可实现强大的安全管理！。

记住几个类：

• WebSecurityConfigurerAdapter:自定义Security策略
• AuthenticationManagerBuilder:自定义认证策略
• @EnableWebSecurity:开启WebSecurity模式 @Enablexxx 开启某个功能

Spring Security的两个主要目标是“认证”和“授权”（访问控制）

“认证”（Authentication）

“授权”(Authorization)

这个概念是通用的，而不是只在Spring Security中存在。

在web开发中，安全第一位！过滤器 ，拦截器

功能性需求：否

做网站：安全应该在什么时候考虑？ 在设计之初考虑

• 漏洞，隐私泄露
• 架构一旦确定 再考虑安全 会使得修改大量代码

shiro、SpringSecurity: 很像， 除了类不一样，名字不一样

功能： 认证 授权

• 功能权限
• 访问权限
• 菜单权限
• …拦截器 ，过滤器： 要使用大量的原生代码，会造成冗余

MVC-SPRING–SPRINGBOOT—框架思想

创建一个新项目：

在pom.xml中导入的依赖：

  <!--thymeleaf模板--> 
<dependency>
            <groupId>org.thymeleaf</groupId>
            <artifactId>thymeleaf-spring5</artifactId>
        </dependency>
        <dependency>
            <groupId>org.thymeleaf.extras</groupId>
            <artifactId>thymeleaf-extras-java8time</artifactId>
        </dependency>

测试

RouterController.java

package com.kuang.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class RouterController {

    @RequestMapping({"/","/index"})
    public String index(){
        return "index";
    }
    @RequestMapping("/toLogin")
    public String toLogin(){
        return "views/login";
    }

    @RequestMapping("/level1/{id}")
    public String level1(@PathVariable("id") int id){
        //加id 可以跳到对应的页面
        return "views/level1/" + id;
    }
    @RequestMapping("/level2/{id}")
    public String level2(@PathVariable("id") int id){
        return "views/level2/" + id;
    }
    @RequestMapping("/level3/{id}")
    public String level3(@PathVariable("id") int id){
        return "views/level3/" + id;
    }
}

在pom.xml中导入相关依赖

  <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>

spring security的官方文档地址：https://docs.spring.io/spring-security/site/docs/5.4.6/reference/html5/

不下载源码看不到注释

认证和授权

SecurityConfig .java

package com.kuang.config;

import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

//AOP横切  拦截器
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
// 授权
    //链式编程
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        //首页所有人可以访问   功能页只有对应有权限的人才能访问
        //请求授权的规则
        // "/"代表访问首页
        http.authorizeRequests().antMatchers("/").permitAll()
                .antMatchers("/level1/**").hasRole("vip1")
                .antMatchers("/level2/**").hasRole("vip1")
                .antMatchers("/level3/**").hasRole("vip1");

        //没有权限默认会到登录页  需要开启登录的页面
        //为什么会到 /login页面
        http.formLogin();


    }
//认证
    //密码编码：PasswordEncoder
    //在Spring Security 5.0+ 新增了很多的加密方法
    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        //这些数据正常应该从数据库中读
        auth.inMemoryAuthentication().passwordEncoder(new BCryptPasswordEncoder())
                .withUser("kuangshen").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1","vip2","vip3")
                .and()
                .withUser("root").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1","vip2","vip3")
                .and()
                .withUser("guest").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1");


    }
}

框架的网址：https://semantic-ui.com/elements/icon.html

pom.xml依赖引入

  <dependency>
            <groupId>org.thymeleaf.extras</groupId>
            <artifactId>thymeleaf-extras-springsecurity4</artifactId>
            <version>3.0.2.RELEASE</version>
        </dependency>

SecurityConfig.java完整代码

package com.kuang.config;

import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

//AOP横切  拦截器
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
// 授权
    //链式编程
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        //首页所有人可以访问   功能页只有对应有权限的人才能访问
        //请求授权的规则
        // "/"代表访问首页
        http.authorizeRequests().antMatchers("/").permitAll()
                .antMatchers("/level1/**").hasRole("vip1")
                .antMatchers("/level2/**").hasRole("vip1")
                .antMatchers("/level3/**").hasRole("vip1");

        //没有权限默认会到登录页  需要开启登录的页面
        //为什么会到 /login页面
        http.formLogin();

        //防止网站攻击
        http.csrf().disable();//关闭crsf     crsf:防止跨站请求伪造


        //开启了注销功能  跳到首页
        http.logout().logoutSuccessUrl("/");


    }
//认证
    //密码编码：PasswordEncoder
    //在Spring Security 5.0+ 新增了很多的加密方法
    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        //这些数据正常应该从数据库中读
        auth.inMemoryAuthentication().passwordEncoder(new BCryptPasswordEncoder())
                .withUser("kuangshen").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1","vip2","vip3")
                .and()
                .withUser("root").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1","vip2","vip3")
                .and()
                .withUser("guest").password(new BCryptPasswordEncoder().encode("123456")).roles("vip1");


    }
}

因为版本原因很多东西都显示不了 不知道什么原因我的降不了版本 一直爆红 在pom.xml 的parent中降版本

找到了解决的办法还是在pom.xml里面 修改：在这里面修改为thymeleaf-extras-springsecurity5 ，所有的功能都能显示了,也不用降版本。

        <dependency>
            <groupId>org.thymeleaf.extras</groupId>
            <artifactId>thymeleaf-extras-springsecurity5</artifactId>
            <version>3.0.4.RELEASE</version>
        </dependency>

index.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org"
      xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity4">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <title>首页</title>
    <!--semantic-ui-->
    <link href="https://cdn.bootcss.com/semantic-ui/2.4.1/semantic.min.css" rel="stylesheet">
    <link th:href="@{/qinjiang/css/qinstyle.css}" rel="stylesheet">
</head>
<body>

<!--主容器-->
<div class="ui container">

    <div class="ui segment" id="index-header-nav" th:fragment="nav-menu">
        <div class="ui secondary menu">
            <a class="item"  th:href="@{/index}">首页</a>

            <!--登录注销-->
            <div class="right menu">

<!--      如果未登录    显示登录按钮     isAuthenticated用户是否已经认证过   -->
                <div sec:authorize="!isAuthenticated()">
                    <a class="item" th:href="@{/toLogin}">
                        <i class="address card icon"></i> 登录
                    </a>
                </div>



                <!--                如果登录：显示 用户名，注销-->
                <div sec:authorize="isAuthenticated()">
                    <a class="item" t>
<!--                用户名：        获得用户的名字   角色：获得用户的权限-->
                       用户名:<span sec:authentication="name"></span>
                        角色:<span sec:authentication="principal.authorities"></span>
                    </a>
                </div>
                <div sec:authorize="isAuthenticated()">
                    <a class="item" th:href="@{/logout}">
                        <i class="sign-out icon"></i> 注销
                    </a>
                </div>

                <!--已登录
                <a th:href="@{/usr/toUserCenter}">
                    <i class="address card icon"></i> admin
                </a>
                -->
            </div>
        </div>
    </div>

    <div class="ui segment" style="text-align: center">
        <h3>Spring Security Study by 秦疆</h3>
    </div>

    <div>
        <br>
        <div class="ui three column stackable grid">
<!--菜单根据用户的 角色动态的显示-->
<!--有vip1就显示  没有就隐藏-->
            <div class="column" sec:authorize="hasRole('vip1')">
                <div class="ui raised segment">
                    <div class="ui">
                        <div class="content">
                            <h5 class="content">Level 1</h5>
                            <hr>
                            <div><a th:href="@{/level1/1}"><i class="bullhorn icon"></i> Level-1-1</a></div>
                            <div><a th:href="@{/level1/2}"><i class="bullhorn icon"></i> Level-1-2</a></div>
                            <div><a th:href="@{/level1/3}"><i class="bullhorn icon"></i> Level-1-3</a></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="column" sec:authorize="hasRole('vip2')">
                <div class="ui raised segment">
                    <div class="ui">
                        <div class="content">
                            <h5 class="content">Level 2</h5>
                            <hr>
                            <div><a th:href="@{/level2/1}"><i class="bullhorn icon"></i> Level-2-1</a></div>
                            <div><a th:href="@{/level2/2}"><i class="bullhorn icon"></i> Level-2-2</a></div>
                            <div><a th:href="@{/level2/3}"><i class="bullhorn icon"></i> Level-2-3</a></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="column" sec:authorize="hasRole('vip3')">
                <div class="ui raised segment">
                    <div class="ui">
                        <div class="content">
                            <h5 class="content">Level 3</h5>
                            <hr>
                            <div><a th:href="@{/level3/1}"><i class="bullhorn icon"></i> Level-3-1</a></div>
                            <div><a th:href="@{/level3/2}"><i class="bullhorn icon"></i> Level-3-2</a></div>
                            <div><a th:href="@{/level3/3}"><i class="bullhorn icon"></i> Level-3-3</a></div>
                        </div>
                    </div>
                </div>
            </div>

        </div>
    </div>
    
</div>


<script th:src="@{/qinjiang/js/jquery-3.1.1.min.js}"></script>
<script th:src="@{/qinjiang/js/semantic.min.js}"></script>

</body>
</html>