SpringBoot整合JDBC以及DRUID

Springboot整合jdbc:

步骤一：导入jar包

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

步骤二：添加数据源：

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

步骤三：可直接使用JDBCTemplate进行数据操作

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate template;

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

    //添加用户
    @RequestMapping("/addUser")
    public String addUser(){
        String sql="insert into user (id,name,pwd) values(4,'零用地','1234')";
        template.execute(sql);
        return "成功--》"+sql;
    }

    //修改用户
    @RequestMapping("/updateUser")
    public String editUser(){
        String sql="update user set name ='地地道道' where id=4";
        template.update(sql);
        return "成功--》"+sql;
    }

SpringBoot整合Druid

   DRUID简介：

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

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

  SpringBoot2.0上以默认都使用HiKari数据源，可以说Hikari与Druid都是当前JavaWeb上最优秀的数据源，现在重点来介绍SpringBoot如何集成Druid数据源，如何实现数据库的监控。

操作步骤：

1. 添加jar包引用 

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

2、application.properties配置不变

3、新建一个类：DruidConfig，注意包的引入，特别是：

@Configuration
public class DruidConfig {

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

    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        //ServletRegistrationBean提供类的进行注册
        ServletRegistrationBean servletRegistrationBean =
                new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //添加初始化参数：initParams
        //白名单：
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        //IP黑名单（同时存在时，deny优先于allow）
        //如果满足deny，就提示：sorry，you are not permitted to view this page
        servletRegistrationBean.addInitParameter("deny", "192.168.1.73");
        //登录查看信息的账号和密码
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }


    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean =
                new FilterRegistrationBean(new WebStatFilter());
        //添加过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        //添加需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif," +
                "*.jpg,*.png, *.css,*.ico,/druid/*");
        return filterRegistrationBean;

    }

4、对于SpringBoot的启动类，加入注解：@ServletComponentScan

5、输入：http://localhost:9091/druid/sql.html，观察Druid监控面板

 

三、通过在方案一基础上加入如下代码，实现验证登录

#设置访问druid监控页的账号和密码,默认没有
## Druid WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.gif,*.png,*.jpg,*.html,*.js,*.css,*.ico,/druid/*
# Druid StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=

四、测试