SpringBoot数据访问之JDBC

川长思鸟来 2021-11-01 07:36 365阅读 0赞

对于数据访问层，无论是SQL还是NOSQL，Spring Boot默认采用整合 Spring Data 的方式进行统一处理，添加大量自动配置，屏蔽了很多设置。引入各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。我们将在数据访问章节测试使用SQL相关、NOSQL在缓存、消息、检索等章节测试。

JDBC、MyBatis、JPA

# 一、SpringBoot配置JDBC #

## 1、添加依赖 ##

**（1）手动**

<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>

**（2）创建项目时选定**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMzg2NzU0_size_16_color_FFFFFF_t_70][]

## 2、编写application.yml配置文件 ##

spring:
      datasource:
        username: root
        password: 123456
        url: jdbc:mysql://localhost:3306/springboottest
        driver-class-name: com.mysql.jdbc.Driver

在测试类中,测试获取数据源对象

@Autowired
    private DataSource dataSource;
    @Test
    public void contextLoads() throws SQLException {
       System.out.println("================================>"+dataSource);
       Connection connection = dataSource.getConnection();
       System.out.println("================================>"+connection);
    }

![20190811011647437.png][]

![20190811011654935.png][]

**作用：**

*  默认是用org.apache.tomcat.jdbc.pool.DataSource作为数据源；
 *  数据源的相关配置都在DataSourceProperties里面；

# 二、自动配置原理 #

org.springframework.boot.autoconfigure.jdbc

1、参考DataSourceConfiguration，根据配置创建数据源，默认使用Tomcat连接池；可以使用spring.datasource.type指定自定义的数据源类型；

2、SpringBoot默认可以支持

org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource、

3、自定义数据源类型

Spring Boot 启动会调用 DataSourceAutoConfiguration 。

/** Generic DataSource configuration.*/
    @ConditionalOnMissingBean(DataSource.class)
    @ConditionalOnProperty(name = "spring.datasource.type")
    static class Generic {
       @Bean
       public DataSource dataSource(DataSourceProperties properties) {
           //使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并且绑定相关属性
          return properties.initializeDataSourceBuilder().build();
       }
    }

**4、DataSourceInitializer：ApplicationListener**

（1）、作用：

*  runSchemaScripts()；运行建表语句；
 *  runDataScripts()；运行插入数据的sql语句；

schema-*.sql、data-*.sql
    默认规则：schema.sql，schema-all.sql；
    可以使用   
    	schema:
          - classpath:department.sql
          指定位置

**5、操作数据库**

自动配置了JdbcTemplate操作数据库

# 三、整合Druid数据源（第一种） #

[https://github.com/alibaba/druid][https_github.com_alibaba_druid]

**1、添加依赖**

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.17</version>
    </dependency>

**2、修改SpringBoot配置文件**

将数据连接源类型改为： type: com.alibaba.druid.pool.DruidDataSource

spring:
      datasource:
        username: root
        password: 123456
        url: jdbc:mysql://localhost:3306/springboottest
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
    
    #配置数据链接初始化信息
      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，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

**3、编写自定义数据源配置类**

@Configuration
    public class MyDruidConf {
        @ConfigurationProperties(prefix = "spring.datasource")//制定配置项开头
        @Bean
        public DataSource druid() {
            return new DruidDataSource();
        }
    }

**4、配置Druid的监控**

//1、配置一个管理后台的Servlet
        @Bean
        public ServletRegistrationBean statViewServlet(){
            ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            Map<String,String> initParams = new HashMap<>();
            initParams.put("loginUsername","admin");
            initParams.put("loginPassword","123456");
            initParams.put("allow","");//默认就是允许所有访问
            initParams.put("deny","192.168.15.21");
            bean.setInitParameters(initParams);
            return bean;
        }
        //2、配置一个web监控的filter
        @Bean
        public FilterRegistrationBean webStatFilter(){
            FilterRegistrationBean bean = new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
            Map<String,String> initParams = new HashMap<>();
            initParams.put("exclusions","*.js,*.css,/druid/*");
            bean.setInitParameters(initParams);
            bean.setUrlPatterns(Arrays.asList("/*"));
            return  bean;
        }

**5、登录Druid后台管理页面**

*  [http://localhost:8080/druid/login.html][http_localhost_8080_druid_login.html]
 *  [admin][http_localhost_8080_druid_login.html]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMzg2NzU0_size_16_color_FFFFFF_t_70]: /images/20211101/4f7bc14467a343abb2289656dc34cab5.png
[20190811011647437.png]: /images/20211101/ba4c87ea064142a0aa08393030c359c1.png
[20190811011654935.png]: /images/20211101/5de833cc992e49b3b10ee098a0fbe8ef.png
[https_github.com_alibaba_druid]: https://github.com/alibaba/druid
[http_localhost_8080_druid_login.html]: http://localhost:8080/druid/login.html