使用SpringBoot整合JDBC和Druid

1.整合JDBC

1.创建一个Springboot项目

2.添加项目的依赖jar包

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QF4gqyvp-1602130715828)(C:\Users\11111\AppData\Roaming\Typora\typora-user-images\image-20200920114222788.png)]

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

3.配置application.yml文件(配置jdbc数据源)

spring:
  datasource:
    username: root
    password: 123
    url:jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.jdbc.Driver

4.连接mysql数据库

5.编写查看数据源和连接

6.创建Controller类进行对数据库的增删改查：

package com.ddf.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")
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);

        return list_maps;
    }

    //添加
    @GetMapping("/addUser")
    public String addUser(){
        String sql = "insert into user(id,name,pwd) values(8,'小张','123')";
        jdbcTemplate.update(sql);
        return "add-ok";
    }

    //修改
    @GetMapping("/addUser/{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] = "小明z";
        objects[1] = "zzzzzz";

        jdbcTemplate.update(sql,objects);
        return "update-ok";
    }

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

2.整合Druid数据源

1、添加项目依赖jar包：

<!--Druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>
<!--log4j-->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
<!--web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--JDBC-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--引入mybatis，这是Mybatis官方提供的适配Spring Boot的，而不是Spring Boot自己的-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

2.配置application.yml文件(配置druid数据源)

spring:
  datasource:
    username: root
    password: 123
    #假如时区报错误了，就增加一个时区的配置就OK了， serverTimezone=UTC
    url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&userUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.zaxxer.hikari.util.DriverDataSource


    #Spring Boot 默认是不注入这些属性值的，需要自己绑定
    #druid 数据源专有配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetWeenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 30000
    validationQuery: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true

    #配置监控统计拦截的filters，stat：监控统计，Log4j：日志记录，wall：防御sql注入
    #如果允许时报错 java.Lang.ClassNotFoundException: org.apache.Log4j.Priority
    #则导入 Log4j 依赖即可，Maven 地址：https://mvnrepository.com/ortifact/logyj/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    userGlobelDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

3.配置Druid，jdbc的Controller类进行对数据库的增删改查也可以使用

4.创建config类，配置Druid配置属性注入到Bean中：

package com.ddf.config;

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

import java.util.HashMap;

@Configuration
public class DruidConfig {

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

    //后台监控 : 类型于web.xml,  ServletRegistrationBean
    //  因为Springboot内置了Servlet容器，所有没有web.xml,所有使用了ServletRegistrationBean给他注册进去
    @Bean
    public ServletRegistrationBean statViewServlet(){
        //设置访问路径
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");

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

        //增加配置
        initParameters.put("loginUsername","admin");  //登陆key 是固定的 loginUsername loginPassword
        initParameters.put("loginPassword","123456");

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

        //禁止谁能访问  initParameters.put("zhang","192.168.11.123");

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

        return bean;
    }
    
    //配置filter过滤器
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();

        bean.setFilter(new WebStatFilter());
        //设置过滤请求
        Map<String,String> initParameters = new HashMap<>();
        //这些东西不进行统计
        initParameters.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParameters);

        return bean;
    }
}

5.运行项目访问自己定义的druid：http://localhost:8080/druid

​ 运行成功可以跳转到druid的index首页：

然后登陆设置好的账号密码进去，我们可以查看到数据的一个监控；