七、SpringBoot与数据访问

1、JDBC

1.1、添加jdbc依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

1.2、配置数据源

spring.datasource.url=jdbc:mysql:///springboot
spring.datasource.username=root
spring.datasource.password=admin

效果：

​ 默认是使用org.apache.tomcat.jdbc.pool.DataSource作为数据源，而数据源的相关配置在DataSourceProperties类中。

1.3、自动配置原理：

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

​ 2）、SpringBoot默认支持以下数据库连接池：

• org.apache.tomcat.jdbc.pool.DataSource

• com.zaxxer.hikari.HikariDataSource

• org.apache.commons.dbcp.BasicDataSource

• org.apache.commons.dbcp2.BasicDataSource

  3）、自定义数据源类型

  @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类型的监听器

class DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent> {
    private final DataSourceProperties properties;
    private final ApplicationContext applicationContext;
    private DataSource dataSource;
    private boolean initialized = false;
    DataSourceInitializer(DataSourceProperties properties,
            ApplicationContext applicationContext) {
        this.properties = properties;
        this.applicationContext = applicationContext;
    }
    @PostConstruct
    public void init() {
        //当DataSourceProperties初始化完成后
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running DDL scripts)");
            return;
        }
        if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
                false).length > 0) {
            //获取DataSource的bean
            this.dataSource = this.applicationContext.getBean(DataSource.class);
        }
        if (this.dataSource == null) {
            logger.debug("No DataSource found so not initializing");
            return;
        }
        //执行schema脚本
        runSchemaScripts();
    }

    private void runSchemaScripts() {
        //查看全局配置文件中是否有spring.datasource.schema
        List<Resource> scripts = getScripts("spring.datasource.schema",
                this.properties.getSchema(), "schema");
        if (!scripts.isEmpty()) {
            String username = this.properties.getSchemaUsername();
            String password = this.properties.getSchemaPassword();
            runScripts(scripts, username, password);
            try {
                this.applicationContext
                        .publishEvent(new DataSourceInitializedEvent(this.dataSource));
                // The listener might not be registered yet, so don't rely on it.
                if (!this.initialized) {
                    //执行数据插入脚本
                    runDataScripts();
                    this.initialized = true;
                }
            }
            catch (IllegalStateException ex) {
                logger.warn("Could not send event to complete DataSource initialization ("
                        + ex.getMessage() + ")");
            }
        }
    }

    @Override
    public void onApplicationEvent(DataSourceInitializedEvent event) {
        if (!this.properties.isInitialize()) {
            logger.debug("Initialization disabled (not running data scripts)");
            return;
        }
        // NOTE the event can happen more than once and
        // the event datasource is not used here
        //如果初始化完成后，执行数据插入
        if (!this.initialized) {
            runDataScripts();
            this.initialized = true;
        }
    }

    private void runDataScripts() {
        List<Resource> scripts = getScripts("spring.datasource.data",
                this.properties.getData(), "data");
        String username = this.properties.getDataUsername();
        String password = this.properties.getDataPassword();
        runScripts(scripts, username, password);
    }

    private List<Resource> getScripts(String propertyName, List<String> resources,
            String fallback) {
        if (resources != null) {
            //如果配置了，则从指定的资源文件中加载脚本
            return getResources(propertyName, resources, true);
        }
        //如果未配置，则使用classpath*:schema-all.sql或者classpath*：schema.sql作为脚本名，其中platform默认为all
        String platform = this.properties.getPlatform();
        List<String> fallbackResources = new ArrayList<String>();
        fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
        fallbackResources.add("classpath*:" + fallback + ".sql");
        return getResources(propertyName, fallbackResources, false);
    }

    private List<Resource> getResources(String propertyName, List<String> locations,
            boolean validate) {
        List<Resource> resources = new ArrayList<Resource>();
        for (String location : locations) {
            for (Resource resource : doGetResources(location)) {
                if (resource.exists()) {
                    resources.add(resource);
                }
                else if (validate) {
                    throw new ResourceNotFoundException(propertyName, resource);
                }
            }
        }
        return resources;
    }

    private Resource[] doGetResources(String location) {
        try {
            SortedResourcesFactoryBean factory = new SortedResourcesFactoryBean(
                    this.applicationContext, Collections.singletonList(location));
            factory.afterPropertiesSet();
            return factory.getObject();
        }
        catch (Exception ex) {
            throw new IllegalStateException("Unable to load resources from " + location,
                    ex);
        }
    }

    private void runScripts(List<Resource> resources, String username, String password) {
        if (resources.isEmpty()) {
            return;
        }
        ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.setContinueOnError(this.properties.isContinueOnError());
        populator.setSeparator(this.properties.getSeparator());
        if (this.properties.getSqlScriptEncoding() != null) {
            populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
        }
        for (Resource resource : resources) {
            populator.addScript(resource);
        }
        DataSource dataSource = this.dataSource;
        if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
            dataSource = DataSourceBuilder.create(this.properties.getClassLoader())
                    .driverClassName(this.properties.determineDriverClassName())
                    .url(this.properties.determineUrl()).username(username)
                    .password(password).build();
        }
        DatabasePopulatorUtils.execute(populator, dataSource);
    }
}

作用：

​ i）、runSchemaScripts();运行建表语句

​ ii）、runDataScripts();运行插入数据的sql语句

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

schema-*.sql        用于建表操作
data-*.sql          用于数据操作

​ 默认规则：schema.sql或者schema-all.sql

​ 自定义规则：spring.datasource.schema=[classpath:department.sql]

2、使用Druid数据源

2.1、导入Druid数据源依赖

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

2.2、Druid数据源配置

spring:
  datasource:
    url: jdbc:mysql:///springboot
    username: root
    password: admin
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20

2.3、Druid数据源配置类

@Configuration
public class DruidConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DruidDataSource 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", "");

        bean.setInitParameters(initParams);
        return bean;
    }

    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js, *.css, *.png, *.jpg, *.gif, /druid/*");
        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}