23.springboot与数据库访问

1.jdbc

快速建立springboot项目时，在sql场景里选择mysql和spring data jdbc

pom文件中：
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

1.在配置文件中(application.yml：配置对应的数据库连接)
    spring:
      datasource:
        username: root
        password: 521521
        url: jdbc:mysql://192.168.2.129:3306/jdbc
        driver-class-name: com.mysql.jdbc.Driver
      jdbc:
        template:
          query-timeout: 3
    
2.测试：
    @Autowired
    DataSource dataSource;
    @Test
    public void contextLoads() throws SQLException {
        System.out.println("加载驱动..");
        System.out.println(dataSource.getClass());
        System.out.println(dataSource.getConnection());
    }
    输出：
    加载驱动..
    class com.zaxxer.hikari.HikariDataSource
    HikariProxyConnection@1406763631 wrapping com.mysql.cj.jdbc.ConnectionImpl@1e84f3c
    
结论：发现默认的数据源是HikariDataSource，这里需要注意的是springboot高版本使用的数据源是HikariDataSource，低版本(2.0以前)默认使用的是org.apache.tomcat.jdbc.pool.DataSource


springboot支持的DataSource有：
1.org.apache.tomcat.jdbc.pool.DataSource 
2.HikariDataSource
3.BasicDataSource
4.自定义的数据源类型



测试:使用JdbcTemplate来进行操作
    @SpringBootTest
    @Slf4j
    class SpringBootDataJdbcApplicationTests {
        @Autowired
        JdbcTemplate jdbcTemplate;
        @Test
        public void contextLoads(){
           log.info(":::"+jdbcTemplate.queryForObject("select age from person where id=1", String.class));
            List<Person> personList = jdbcTemplate.query("select * from person", new BeanPropertyRowMapper<>(Person.class));
            log.info("长度："+personList.size());
            for (Person person : personList) {
                log.info(":"+person.toString());
            }
        }
    }

分析自动配置

1.自动配置类
    1.1：DataSourceAutoConfiguration(数据源的自动配置类)

使用Druid作为数据源

官方的参考地址：https://github.com/alibaba/druid   
1.导入druid的pom文件
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.17</version>
    </dependency>
    
2.手动往容器中加入DataSource的组件
    //重点1：标明当前类是springboot的配置类
    @Configuration
    public class MyDataSourceConfig {
        //重点2：往容器中加载组件
        @Bean
        //重点3：最重要的一点：将springboot配置文件中的配置和加入组件的属性进行绑定
        @ConfigurationProperties("spring.datasource")
        public DataSource dataSource(){
            DruidDataSource druidDataSource=new DruidDataSource();
            return druidDataSource;
        }
    }

3.配置文件中(application.yml)的写法
    spring:
      datasource:
        username: root
        password: 521521
        url: jdbc:mysql://192.168.2.129:3306/jdbc
        driver-class-name: com.mysql.jdbc.Driver
      jdbc:
        template:
          query-timeout: 3
          
4.测试类：
    @SpringBootTest
    @Slf4j
    class SpringBootDataJdbcApplicationTests {
        @Autowired
        JdbcTemplate jdbcTemplate;
        @Autowired
        DataSource dataSource;
        @Test
        public void contextLoads(){
            //重点1：此处输出的是：数据源类型：class com.alibaba.druid.pool.DruidDataSource(发现使用的是咱们自定义的数据源)
            log.info("数据源类型：{}",dataSource.getClass());
            //重点2：此处的连接是通过自定义数据源DruidDataSource取的连接进行数据库查询的
            log.info(":::"+jdbcTemplate.queryForObject("select age from person where id=1", String.class));
            List<Person> personList = jdbcTemplate.query("select * from person", new BeanPropertyRowMapper<>(Person.class));
            log.info("长度："+personList.size());
            for (Person person : personList) {
                log.info(":"+person.toString());
            }
        }
    }

使用Druid的内置监控页

1.在上述基础上，还得往容器中放入一个组件
    
    @Configuration
    public class MyDataSourceConfig {
    
        @Bean
        @ConfigurationProperties("spring.datasource")
        public DataSource dataSource(){
            DruidDataSource druidDataSource=new DruidDataSource();
            return druidDataSource;
        }
        //重点1：加入自定义的servlet
        @Bean
        public ServletRegistrationBean statViewServlet(){
            //重点2：加入StatViewServlet
            StatViewServlet statViewServlet=new StatViewServlet();
            //重点3：加入statViewServlet处理/druid/*的所有请求
            ServletRegistrationBean<StatViewServlet> registrationBean=new ServletRegistrationBean<>(statViewServlet,"/druid/*");
            return registrationBean;
        }
    }
启动项目后，访问http://localhost:8080/druid

监控页的sql监控功能：

按照规范上设置，必须打开Druid的监控统计功能
示例：
    在上述基础上
    @Configuration
    public class MyDataSourceConfig {
        @Bean
        @ConfigurationProperties("spring.datasource")
        public DataSource dataSource() throws SQLException {
            DruidDataSource druidDataSource=new DruidDataSource();
            //重点1：在上述基础上设置数据源的Filter属性为stat：这样即打开了sql统计功能
            druidDataSource.setFilters("stat");
            return druidDataSource;
        }
        @Bean
        public ServletRegistrationBean statViewServlet(){
            StatViewServlet statViewServlet=new StatViewServlet();
            ServletRegistrationBean<StatViewServlet> registrationBean=new ServletRegistrationBean<>(statViewServlet,"/druid/*");
            return registrationBean;
        }
    }

2.测试：写一个controller
    @RestController
        @Slf4j
        public class DruidController {
            @Autowired
            JdbcTemplate jdbcTemplate;
            @RequestMapping("/queryPerson")
            public List<Person> queryPerosn(){
                List<Person> personList =jdbcTemplate.query("select * from person", new BeanPropertyRowMapper<>(Person.class));
                int num=jdbcTemplate.queryForObject("select count(1) from person", Integer.class);
                log.info("数量："+num
                );
                for (Person person : personList) {
                    log.info(":"+person.toString());
                }
                return personList;
            }
        }

监控页的web应用监控和URI监控

按照规范配置：
   在上述基础上加上webStatFilter的配置
       @Configuration
        public class MyDataSourceConfig {
        
            @Bean
            @ConfigurationProperties("spring.datasource")
            public DataSource dataSource() throws SQLException {
                DruidDataSource druidDataSource=new DruidDataSource();
                druidDataSource.setFilters("stat");
                return druidDataSource;
            }
            @Bean
            public ServletRegistrationBean statViewServlet(){
                StatViewServlet statViewServlet=new StatViewServlet();
                ServletRegistrationBean<StatViewServlet> registrationBean=new ServletRegistrationBean<>(statViewServlet,"/druid/*");
                return registrationBean;
            }
            //重点1：向容器中加入webStatFilter组件
            @Bean
            public FilterRegistrationBean  webStatFilter(){
                WebStatFilter webStatFilter=new WebStatFilter();
                FilterRegistrationBean<WebStatFilter> filterFilterRegistrationBean=new FilterRegistrationBean<>(webStatFilter);
                //重点2：设置filter的拦截路径
                filterFilterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
                //重点3：设置filter的过滤路径，不加的话会拦截所有请求，包括静态资源的请求等等
                filterFilterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
                return filterFilterRegistrationBean;
            }
        } 

开启sql防火墙

要点1：在数据源配置中配置filter属性，和sql监控开关stat同时使用时用逗号隔开
样例如下：
    在上述基础上进行更改
        ...
        @Bean
        @ConfigurationProperties("spring.datasource")
        //要点1：是在数据源配置中进行配置
        public DataSource dataSource() throws SQLException {
            DruidDataSource druidDataSource=new DruidDataSource();
            //要点2：加上wall属性：
            //1.stat：sql监控开关
            //2.wall：防火墙开关
            druidDataSource.setFilters("stat,wall");
            return druidDataSource;
        }
        ....
        
第二种写法：
    1.因为数据源和配置文件中的spring.datasource进行了绑定，所以setFilte可以使用配置文件完成
    spring:
      datasource:
        username: root
        password: 521521
        url: jdbc:mysql://192.168.2.129:3306/jdbc
        driver-class-name: com.mysql.jdbc.Driver
        重点：在配置文件中加上filters: stat,wall属性
      filters: stat,wall
      jdbc:
        template:
          query-timeout: 3
          
      2.在配置类中就可以去掉druidDataSource.setFilters("stat,wall");
        public DataSource dataSource() throws SQLException {
            DruidDataSource druidDataSource=new DruidDataSource();
            //去掉druidDataSource.setFilters("stat,wall");
            return druidDataSource;
        }

里面包含了各种检查细节

设置监控页面的访问权限(1.页面的登录用户名/密码 2.访问ip的限制)

示例如下：
    在statViewServlet配置以下
    ...
    @Bean
    public ServletRegistrationBean statViewServlet(){
        StatViewServlet statViewServlet=new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean=new ServletRegistrationBean<>(statViewServlet,"/druid/*");
        //设置用户密码
        registrationBean.addInitParameter("loginUsername","admin");
        registrationBean.addInitParameter("loginPassword","521521");
        //设置登录页面的黑白名单
        registrationBean.addInitParameter("allow","192.168.0.107");
        registrationBean.addInitParameter("deny", "192.168.0.10");
        return registrationBean;
    }
    ...

使用stater去操作durid

步骤
    1.引入stater
        <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid-spring-boot-starter</artifactId>
           <version>1.1.17</version>
        </dependency>    

DruidDataSourceAutoConfigure(Druid数据源自动配置类中自动注入了以下的类)
各有什么作用呢
    1.DruidSpringAopConfiguration:(监控的是springbean的)-->配置项是spring.datasource.druid.aop-patterns
    2.DruidStatViewServletConfiguration:监控页的配置：spring.datasource.druid.stat-view-servlet默认是开启：(详解，主要配置的是sql监控页的登录账号和密码，以及允许登录的ip和不允许登录的ip)
    3.DruidWebStatFilterConfiguration：web监控的配置：spring.datasource.druid.web-stat-filter默认是开启
    4.DruidFilterConfiguration：所有Druid自己filter的配置


1.DruidSpringAopConfiguration说明：(监控的是springbean的)-->配置项是spring.datasource.druid.aop-patterns
    详细代码如下：
        @ConditionalOnProperty({"spring.datasource.druid.aop-patterns"})
        public class DruidSpringAopConfiguration {
            public DruidSpringAopConfiguration() {
            }
            ...
        }
    在application.yaml中配置示例如下：
        spring:
          datasource:
            username: root
            password: 521521
            url: jdbc:mysql://192.168.2.129:3306/jdbc
            driver-class-name: com.mysql.jdbc.Driver
            重点1：会监控com.wmd包下的组件信息
            druid:
              aop-patterns: com.wmd
              
  2.DruidStatViewServletConfiguration监控页的配置：spring.datasource.druid.stat-view-servlet默认是开启
      详解，主要配置的是sql监控页的登录账号和密码，以及允许登录的ip和不允许登录的ip
          使用示例：
              spring:
                  datasource:
                    username: root
                    password: 521521
                    url: jdbc:mysql://192.168.2.129:3306/jdbc
                    driver-class-name: com.mysql.jdbc.Driver
                    druid:
                      aop-patterns: com.wmd
                      重点1
                      stat-view-servlet:
                        //是否使用：默认是true
                        enabled: true
                        //sql监控页的登录用户名和密码
                        login-username: admin
                        login-password: 521521
                        //sql页登录的白名单和黑名单
                        allow: 192.168.0.107
                        deny: 192.168.0.10
                        
3.DruidWebStatFilterConfiguration：web监控的配置：spring.datasource.druid.web-stat-filter，默认是开启
    详解：页面上web监控的配置选项，监控各路径访问次数以及相关信息
        使用样例如下：
            spring:
              datasource:
                username: root
                password: 521521
                url: jdbc:mysql://192.168.2.129:3306/jdbc
                driver-class-name: com.mysql.jdbc.Driver
                druid:
                  aop-patterns: com.wmd
                  
                  stat-view-servlet:
                    enabled: true
                    login-username: admin
                    login-password: 521521
                    allow: 192.168.0.107
                    deny: 192.168.0.10
                 重点1：
                  web-stat-filter:
                    //是否打开:默认是true
                    enabled: true
                    //监控的路径：监控所有路径
                    url-pattern: /*
                    //不监控哪些路径
                    exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
                    

4.DruidFilterConfiguration：所有Durid中filter的自己的配置
    详情：可以配置以下的多种filter
        public class DruidFilterConfiguration {
            private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
            private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
            private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
            private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
            private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
            private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
            private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
            private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";
            private static final String FILTER_WALL_CONFIG_PREFIX = "spring.datasource.druid.filter.wall.config";
            ...
        }
        
    使用样例：
        spring:
          datasource:
            username: root
            password: 521521
            url: jdbc:mysql://192.168.2.129:3306/jdbc
            driver-class-name: com.mysql.jdbc.Driver
            druid:
              aop-patterns: com.wmd
        
        
              stat-view-servlet:
                enabled: true
                login-username: admin
                login-password: 521521
                allow: 192.168.0.107
                deny: 192.168.0.10
        
              web-stat-filter:
                enabled: true
                url-pattern: /*
                exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
              重点：1
              使用的filter
              filters: stat,wall,slf4j
              使用的filter具体的配置：具体的配置可以参考：https://github.com/alibaba/druid   
              filter:
                stat:
                  log-slow-sql: true
                  slow-sql-millis: 1000
                  enabled: true
                wall:
                  enabled: true
                  config:
                    drop-table-allow: false