SpringBoot 数据访问

简介

对于数据访问层，无论是SQL还是NOSQL，Spring Boot默认采用整合Spring Data的方式进行统一处理，添加大量自动配置，屏蔽了很多设置。引入各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。

JDBC

<!--pom.xml-->
<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>

#application.yml
spring: 
  datasource: 
  username: root 
  password: 123456 
  url: jdbc:mysql://localhost:3306/jdbc 
  driver‐class‐name: com.mysql.jdbc.Driver

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBoot06DataJdbcApplicationTests {
  @Autowired
  DataSource dataSource;

  @Test
  public void contextLoads() throws SQLException {
    //org.apache.tomcat.jdbc.pool.DataSource
    System.out.println(dataSource.getClass());

    Connection connection = dataSource.getConnection();
    System.out.println(connection);
    connection.close();
  }
}

在默认的情况下，使用org.apache.tomcat.jdbc.pool.DataSource作为数据源，数据源的所有配置都在DataSourceProperties中能找到。

自动配置原理：

org.springframework.boot.autoconfifigure.jdbc：

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

2、SpringBoot默认可以支持；

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

3、自定义数据源类型

/**
* 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：implement ApplicationListener

作用：

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

默认只需要将文件命名为：

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

5、操作数据库：自动配置了JdbcTemplate操作数据库

package org.springframework.boot.autoconfigure.jdbc;

@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcTemplateAutoConfiguration {

    @Configuration
    static class JdbcTemplateConfiguration {

        private final DataSource dataSource;

        private final JdbcProperties properties;

        JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
            this.dataSource = dataSource;
            this.properties = properties;
        }

        @Bean
        @Primary
        @ConditionalOnMissingBean(JdbcOperations.class)
        public JdbcTemplate jdbcTemplate() {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
            JdbcProperties.Template template = this.properties.getTemplate();
            jdbcTemplate.setFetchSize(template.getFetchSize());
            jdbcTemplate.setMaxRows(template.getMaxRows());
            if (template.getQueryTimeout() != null) {
                jdbcTemplate
                    .setQueryTimeout((int) template.getQueryTimeout().getSeconds());
            }
            return jdbcTemplate;
        }
    }

    @Configuration
    @Import(JdbcTemplateConfiguration.class)
    static class NamedParameterJdbcTemplateConfiguration {
        @Bean
        @Primary
        @ConditionalOnSingleCandidate(JdbcTemplate.class)
        @ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
        public NamedParameterJdbcTemplate namedParameterJdbcTemplate(
            JdbcTemplate jdbcTemplate) {
            return new NamedParameterJdbcTemplate(jdbcTemplate);
        }
    }
}

整合druid数据源

<!--pom.xml-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

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

默认情况下，druid的数据源的连接池等相关配置信息是不会被自动注入配置的，需要手动配置。

@Configuration
public class DruidConfig {
  
    //通过前缀相同，将属性注入到druidDataSource中
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
       return  new DruidDataSource();
    }

    //配置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"); //禁止该IP访问

        bean.setInitParameters(initParams);
        return bean;
    }


    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //设置不拦截掉静态资源、druid请求
        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParams);
        //设置过滤器拦截所有请求
        bean.setUrlPatterns(Arrays.asList("/*"));

        return  bean;
    }
}