场景

访问 http://localhost:8080/druid/ 就会登录 帐号 admin/admin 访问 http://localhost:8080/blogList 或 http://localhost:8080/updateBlog/4 会在 "SQL监控" 显示列表

pom配置

//jetbrains://idea/navigate/reference?project=sprintboot&path=spingboot-04-data/pom.xml:21:4
// https://mvnrepository.com/artifact/com.alibaba/druid

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->

<!-- 方式1 使用这个 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.10</version>
</dependency>


<!-- 方式二 使用springboot默认的连接池 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

application.yml

//jetbrains://idea/navigate/reference?project=sprintboot&path=application.yml:10:3
//Druid连接池配置详解 https://blog.csdn.net/loveLifeLoveCoding/article/details/119674832

<!-- 方式1 使用这个 -->

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #sping boot默认是不注入这些属性值的,需求自己绑定
    #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
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500




<!-- 方式二 使用springboot默认的连接池 -->
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #sping boot默认是不注入这些属性值的,需求自己绑定
    #druid 数据源专用配置
    druid:
      #配置初始化大小、最小、最大线程数
      initial-size: 5
      min-idle: 5
      #CPU核数+1，也可以大些但不要超过20，数据库加锁时连接过多性能下降
      max-active: 20
      #最大等待时间，内网：800，外网：1200（三次握手1s）
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      #配置一个连接在池中最大空间时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      validation-query: select 1
      test-while-idle: true
      #设置从连接池"获取"连接时是否检查连接有效性，true检查，false不检查
      test-on-borrow: false
      #设置从连接池"归还"连接时是否检查连接有效性，true检查，false不检查
      test-on-return: false
      #可以支持PSCache（提升写入、查询效率）
      pool-prepared-statements: true

      #配置旅控统i计挡龄filters，stat：脂控统i计、log4j：月志记录、walL：防部sql法入
      #如果允洋时报错 java.Lang.ClassNotFoundException:org.apache.Log4j.Priority
      #妙导入Log4j依糊即可，Maven 地址：https://mvnrepository.com/artifact/Log4j/Log4j

      #配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      max-pool-prepared-statement-per-connection-size: 20
      use-global-data-source-stat: true
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

配置类

// jetbrains://idea/navigate/reference?project=sprintboot&path=com/cmk/spingboot04data/config/DruidConfig.java:22:1

@Configuration
public class DruidConfig {

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

    //后台监控: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");
        initParameters.put("loginPassword", "admin");

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

        //禁止谁能访问 initParameters.put（"cmk"，"192.168.11.123"）;

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

        return bean;
    }
}

控制器

jetbrains://idea/navigate/reference?project=sprintboot&path=com/cmk/spingboot04data/controller/JDBCController.java:22:3

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/blogList")
    public List<Map<String,Object>> blogList(){
        String sql = "select * from blog";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @GetMapping("/addBlog")
    public String addBlog(){
        String sql = "insert into test.blog (title, author, create_time, views)\n" +
                "values  ('hello11', 'cmk11', '2022-04-08 14:59:37', 9999)";
        jdbcTemplate.update(sql);
        return "add ok";
    }

    @GetMapping("/updateBlog/{id}")
    public String updateBlog(@PathVariable("id") int id){
        String sql = "update test.blog  set title=?,author=? where id = "+id;
        Object[] objects = new Object[2];
        objects[0] = "小明xx";
        objects[1] = "小黑";
        jdbcTemplate.update(sql,objects);
        return "update ok";
    }


    @GetMapping("/delBlog/{id}")
    public String delBlog(@PathVariable("id") int id){
        String sql = "delete from test.blog where id =?";
        jdbcTemplate.update(sql,id);
        return "delete ok";

    }





}