20210418——SpringBoot微服务Druid数据源（四）

Data

对于数据库访问层，无论是关系型数据库还是非关系型数据库，SpringBoot底层都采用Spring Data的方式统一进行处理

配置文件application.yml

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

测试

package com.mmz;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;

@SpringBootTest
class Springboot04DataApplicationTests {

    @Autowired
    DataSource datasource;

    @Test
    void contextLoads() {
        // 查看一下默认的数据源
        System.out.println(datasource.getClass());


    }

}

JdbcTemplate

Controller

package com.mmz.controller;


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

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

/**
 * @Classname JDBCController
 * @Description TODO
 * @Date 2021/4/18 22:25
 * @Created by mmz
 */
@RestController
public class JDBCController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    // 查询数据库的所有信息
    @RequestMapping("/userlist")
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }
}

SpringBoot已经帮我们封装好了JdbcTemplate，我们拿来用就可以了。

增加User

// 增加User
    @RequestMapping("/adduser")
    public String addUser(){
        String sql =  "insert into user(id,name,pwd) values (4,'zjz','123456' )";
        jdbcTemplate.update(sql);

        return "update-ok";
    }

修改

 // 修改User
    @RequestMapping("/updateuser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql =  "update user set name=?,pwd=? where id ="+id;

        Object[] objects = new Object[2];
        objects[0] = "zzz";
        objects[1] = "ZZZ";
        jdbcTemplate.update(sql,objects);

        return "updateUser-ok";
    }

删除

    // 删除User
    @RequestMapping("/deleteuser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql =  "delete from user where id = ?";
        jdbcTemplate.update(sql,id);

        return "deleteUser-ok";
    }

Druid数据源

记住，数据库的底层，基于java，永远都是jdbc

导入druid的starter

    <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.22</version>
        </dependency>

在配置文件中进行修改

    type: com.alibaba.druid.pool.DruidDataSource

配置拓展插件

配置文件

spring:
  datasource:
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatismmz?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      filters: stat,wall,log4j

配置Spring

package com.mmz.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletListenerRegistrationBean;
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;

/**
 * @Classname DruidConfig
 * @Description TODO
 * @Date 2021/4/19 22:17
 * @Created by mmz
 */
@Configuration
public class DruidConfig  {

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

    // 后台监控
    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

        // 后台需要有人登陆，帐号密码配置
        HashMap<String, String> initParameters = new HashMap<>();
        // 增加配置 固定配置
        initParameters.put("loginUsername","admin");
        initParameters.put("loginPassword","123456");

        // 允许谁可以访问
        initParameters.put("allow","");

        // 禁止谁可以访问
//        initParameters.put("mmz","ip地址")；

        bean.setInitParameters(initParameters); // 初始化参数

        return bean;

    }
}

测试sql

上面内种方式适用于导入的是druid，而不是starter这种方式，后来我改成了starter这种方式

配置文件

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: password
    url: jdbc:mysql://localhost:3306/mybatismmz?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      stat-view-servlet:
        url-pattern: /druid/*
        # 登录用户名/密码
        login-username: root
        login-password: 123

Druid三种配置方式，我用的一种

当然也有很多其他的配置项目，比如过滤请求，在web-stat-filter中

druid:
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1 FROM sys_user
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开 PSCache，并且指定每个连接上 PSCache 的大小
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的 Filter，去掉后监控界面 SQL 无法统计，wall 用于防火墙
      filters: stat,wall,log4j
      # 通过 connection-properties 属性打开 mergeSql 功能；慢 SQL 记录
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      # 配置 DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
      # 配置 DruidStatViewServlet
      stat-view-servlet:
        url-pattern: /druid/*
        # IP 白名单，没有配置或者为空，则允许所有访问
        allow: 127.0.0.1
        # IP 黑名单，若白名单也存在，则优先使用
        deny: 192.168.31.253
        # 禁用 HTML 中 Reset All 按钮
        reset-enable: false
        # 登录用户名/密码
        login-username: root
        login-password: 123