6_1.springboot2.x整合JDBC与数据源配置原理解析

小鱼儿 2023-06-02 09:15 8阅读 0赞

## 1、引言 ##

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

## **2、步骤** ##

**这里springboot版本是springboot2.18**

**1、引入starter        –spring-boot-starter-jdbc**

<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、配置application.yml**

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

注意：springboot2中默认数据源是Hikari，这里mysql驱动发生了更新，驱动名：

com.mysql.cj.jdbc.Driver

url: jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT

url要加入：**serverTimezone=UTC否则会报错**

**3、测试 **

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class Springboot06DataJdbcApplicationTests {
    
        @Autowired
        DataSource dataSource;
        @Test
        public void contextLoads() throws Exception {
            System.out.println(dataSource.getClass());
            Connection connection = dataSource. getConnection();
            System.out.println(connection);
            connection.close();
        }
    
    }

**运行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70][]

可以看出springboot2默认数据源使用的是hikariDatasorece

数据源的相关配置都是在

DataSourceProperties

## **3、原理解析** ##

数据源配置解析
    DataSourceConfiguration

**作用：实际的数据源导入配置，包括**

tomcat.jdbc.pool.DataSource、HikariDataSource、dbcp2.BasicDataSource

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 1][]

**1、自定义数据源：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 2][]

调用步骤：

DataSourceProperties

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 3][]

返回一个DataSourceBuilder对象，使用DataSourceBuilder创建数据源，利用反射创建响应type的数据源，并且绑定相关属性

DataSourceBuilder

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 4][]

**2、数据源自动配置解析**

DataSourceAutoConfiguration

![20191004165942945.png][]

**@Configuration**–> 配置类  
**@ConditionalOnClass(\{ DataSource.class, EmbeddedDatabaseType.class \})**–> 在当前类路径下存在DataSource.class,EmbeddedDatabaseType.class 时生效  
**@EnableConfigurationProperties(DataSourceProperties.class)** –> 可以通过spring.datasource.xxx 进行配置,同时导入了EnableConfigurationPropertiesImportSelector

**@Import(\{ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class \})**

*解析：**DataSourcePoolMetadataProvidersConfiguration.class 自动装配每个数据源的元数据***

DataSourcePoolMetadata的类图如下:

![DataSourcePoolMetadata-class-uml][]

1.DataSourcePoolMetadata 接口–> 提供大多数数据库都提供的元数据的接口.定义如下:

public interface DataSourcePoolMetadata {
    
    /**
     * 返回当前数据库连接池的使用量,返回值在0至1之间(或者是-1,如果当前数据库连接池没有限制的话)
     * <li>1 --> 该数据库连接池的链接数已达到最大数目</li>
     * <li>0 --> 该数据库连接池的链接数</li>
     * <li>-1 -->该数据库连接池的链接数没有限制 </li>
     * </li>
     * </ul>
     * 还有可能返回null，如果当前的数据库链接池不提供必要的信息进行计算的话
     */
    Float getUsage();
    
    // 返回当前数据库连接池中已经在使用中的(激活)链接或者返回null,如果该信息不可用的话
    Integer getActive();
    
    // 返回当前数据库连接池可分配的最大链接数, 返回-1 意味着没有限制,返回null,意味着当前信息不可用
    Integer getMax();
    
    // 返回当前数据库连接池可分配的最小链接数, 返回null,意味着当前信息不可用
    Integer getMin();
    
    // 返回用来检查当前链接是否可以的sql,返回null-->当前信息不可用
    String getValidationQuery();
    }

2.AbstractDataSourcePoolMetadata –> DataSourcePoolMetadata 的抽象实现,实现了getUsage 方法,其它实现只需继承它实现其他的方法即可,代码如下:

public Float getUsage() {
        // 1. 获得当前数据库连接池可分配的最大链接数和当前数据库连接池中已经在使用中的链接,如果其中1个等于null,则返回null
        Integer maxSize = getMax();
        Integer currentSize = getActive();
        if (maxSize == null || currentSize == null) {
            return null;
        }
        // 2. 如果最大链接数小于0,则直接返回-1
        if (maxSize < 0) {
            return -1F;
        }
        // 3. 如果使用中的(激活)链接等于0L
        if (currentSize == 0) {
            return 0F;
        }
        // 4. 通过currentSize/maxSize 进行计算
        return (float) currentSize / (float) maxSize;
    }

1.  获得当前数据库连接池可分配的最大链接数和当前数据库连接池中已经在使用中的链接,如果其中1个等于null,则返回null
2.  如果最大链接数小于0,则直接返回-1
3.  如果使用中的(激活)链接等于0L
4.  通过currentSize/maxSize 进行计算

同时,AbstractDataSourcePoolMetadata 持有了DataSource.通过构造器传入,代码如下:

private final T dataSource;
    
    protected AbstractDataSourcePoolMetadata(T dataSource) {
        this.dataSource = dataSource;
    }

3.CommonsDbcp2DataSourcePoolMetadata–> 继承自AbstractDataSourcePoolMetadata,其持有的数据源类型为BasicDataSource.关于接口的实现只是简单的调用 dpcp中相关的api即可

public class CommonsDbcp2DataSourcePoolMetadata
        extends AbstractDataSourcePoolMetadata<BasicDataSource> {
    
    public CommonsDbcp2DataSourcePoolMetadata(BasicDataSource dataSource) {
        super(dataSource);
    }
    
    @Override
    public Integer getActive() {
        return getDataSource().getNumActive();
    }
    
    @Override
    public Integer getMax() {
        return getDataSource().getMaxTotal();
    }
    
    @Override
    public Integer getMin() {
        return getDataSource().getMinIdle();
    }
    
    @Override
    public String getValidationQuery() {
        return getDataSource().getValidationQuery();
    }
    }

其自动装配是在CommonsDbcp2PoolDataSourceMetadataProviderConfiguration中声明的

/*
     * Copyright 2012-2019 the original author or authors.
     *
     * Licensed under the Apache License, Version 2.0 (the "License");
     * you may not use this file except in compliance with the License.
     * You may obtain a copy of the License at
     *
     *      https://www.apache.org/licenses/LICENSE-2.0
     *
     * Unless required by applicable law or agreed to in writing, software
     * distributed under the License is distributed on an "AS IS" BASIS,
     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
     * See the License for the specific language governing permissions and
     * limitations under the License.
     */
    
    package org.springframework.boot.autoconfigure.jdbc.metadata;
    
    import com.zaxxer.hikari.HikariDataSource;
    import org.apache.commons.dbcp2.BasicDataSource;
    
    import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
    import org.springframework.boot.jdbc.DataSourceUnwrapper;
    import org.springframework.boot.jdbc.metadata.CommonsDbcp2DataSourcePoolMetadata;
    import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
    import org.springframework.boot.jdbc.metadata.HikariDataSourcePoolMetadata;
    import org.springframework.boot.jdbc.metadata.TomcatDataSourcePoolMetadata;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    /**
     * Register the {@link DataSourcePoolMetadataProvider} instances for the supported data
     * sources.
     *
     * @author Stephane Nicoll
     * @since 1.2.0
     */
    @Configuration
    public class DataSourcePoolMetadataProvidersConfiguration {
    
    	@Configuration
    	@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
    	static class TomcatDataSourcePoolMetadataProviderConfiguration {
    
    		@Bean
    		public DataSourcePoolMetadataProvider tomcatPoolDataSourceMetadataProvider() {
    			return (dataSource) -> {
    				org.apache.tomcat.jdbc.pool.DataSource tomcatDataSource = DataSourceUnwrapper.unwrap(dataSource,
    						org.apache.tomcat.jdbc.pool.DataSource.class);
    				if (tomcatDataSource != null) {
    					return new TomcatDataSourcePoolMetadata(tomcatDataSource);
    				}
    				return null;
    			};
    		}
    
    	}
    
    	@Configuration
    	@ConditionalOnClass(HikariDataSource.class)
    	static class HikariPoolDataSourceMetadataProviderConfiguration {
    
    		@Bean
    		public DataSourcePoolMetadataProvider hikariPoolDataSourceMetadataProvider() {
    			return (dataSource) -> {
    				HikariDataSource hikariDataSource = DataSourceUnwrapper.unwrap(dataSource, HikariDataSource.class);
    				if (hikariDataSource != null) {
    					return new HikariDataSourcePoolMetadata(hikariDataSource);
    				}
    				return null;
    			};
    		}
    
    	}
    
    	@Configuration
    	@ConditionalOnClass(BasicDataSource.class)
    	static class CommonsDbcp2PoolDataSourceMetadataProviderConfiguration {
    
    		@Bean
    		public DataSourcePoolMetadataProvider commonsDbcp2PoolDataSourceMetadataProvider() {
    			return (dataSource) -> {
    				BasicDataSource dbcpDataSource = DataSourceUnwrapper.unwrap(dataSource, BasicDataSource.class);
    				if (dbcpDataSource != null) {
    					return new CommonsDbcp2DataSourcePoolMetadata(dbcpDataSource);
    				}
    				return null;
    			};
    		}
    
    	}
    
    }

*  @Configuration –> 配置类
 *  @ConditionalOnClass(BasicDataSource.class) –> 在BeanFactory中如果存在BasicDataSource类型的bean时该配置生效，如果生效的话,则注册id为commonsDbcp2PoolDataSourceMetadataProvider,类型为DataSourcePoolMetadataProvider的bean

### **DataSourcePoolMetadataProvider** ###

DataSourcePoolMetadataProvider的类图如下:

![SouthEast][]  
DataSourcePoolMetadataProvider接口–>根据DataSource 提供 DataSourcePoolMetadata.其只定义了1个方法,如下:

// 根据DataSource返回DataSourcePoolMetadata,或者返回null,如果给定的数据源没有对应的DataSourcePoolMetadata
    DataSourcePoolMetadata getDataSourcePoolMetadata(DataSource dataSource);

2.DataSourcePoolMetadataProviders 实现了DataSourcePoolMetadataProvider接口,其内部持有DataSourcePoolMetadataProvider类型的List,在构造器中会注入在BeanFactory所有DataSourcePoolMetadataProvider类型的bean.代码如下:

**会在DataSourcePoolMetadataProviders的构造器中注入在BeanFactory所有DataSourcePoolMetadataProvider类型的bean**

public class CompositeDataSourcePoolMetadataProvider implements DataSourcePoolMetadataProvider {
    
    	private final List<DataSourcePoolMetadataProvider> providers;
    
    	/**
    	 * Create a {@link CompositeDataSourcePoolMetadataProvider} instance with an initial
    	 * collection of delegates to use.
    	 * @param providers the data source pool metadata providers
    	 */
    // 会在DataSourcePoolMetadataProviders的构造器中注入在BeanFactory所有DataSourcePoolMetadataProvider类型的bean
    
    	public CompositeDataSourcePoolMetadataProvider(Collection<? extends DataSourcePoolMetadataProvider> providers) {
    		this.providers = (providers != null) ? Collections.unmodifiableList(new ArrayList<>(providers))
    				: Collections.emptyList();
    	}
    
    	@Override
    	public DataSourcePoolMetadata getDataSourcePoolMetadata(DataSource dataSource) {
    		for (DataSourcePoolMetadataProvider provider : this.providers) {
    			DataSourcePoolMetadata metadata = provider.getDataSourcePoolMetadata(dataSource);
    			if (metadata != null) {
    				return metadata;
    			}
    		}
    		return null;
    	}
    
    }

**getDataSourcePoolMetadata 会依次遍历持有的providers,如果能根据给定的DataSource获得DataSourcePoolMetadata,则直接返回,否则返回null**

***解析：DataSourceInitializationConfiguration.class,数据源的初始化配置***

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 5][]

**DataSourceInitializerInvoker.class**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 6][]  
作用:初始化的时候帮我们运行schene-\*文件(建表)，和data-\*（数据）  
afterPropertiesSet方法，初始化bean的时候执行，可以针对某个具体的bean进行配置。

@Override
    	public void afterPropertiesSet() {
    		DataSourceInitializer initializer = getDataSourceInitializer();
    		if (initializer != null) {
    			boolean schemaCreated = this.dataSourceInitializer.createSchema();
    			if (schemaCreated) {
    				initialize(initializer);
    			}
    		}
    	}

从容器中拿到数据源对象：

作用1：调用顺序->createSchema()->runScripts()，运行建表语句，

作用2：若建表语句创建则执行:initialize()->initSchema(),运行插入数据的语句；onApplicationEvent（）当监听到某个事件也会执行方法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 7][]

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

源代码如图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 8][]方法getScripts（）得到文件的位置

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 9][]

**注意：**SpringBoot2通过sql脚本文件生成表时不成功时：

(1)在application配置文件指定执行sql（静态资源）的地方加上initialization-mode:always即可

(2)如果你配置文件没有指定执行文件的名称而是使用默认的schema.sql或者schema-all.sql的话就在配置文件中加上spring.datasource.initialization-mode=always

因为SpringBoot在启动时，只有检测到spring.datasource.initialization-mode=ALWAYS配置，然后再检测spring.datasource.schema，且配置的sql角本命令不为空，才会去执行schema和spring.datasource.data。因此需要在scheme.sql中随便写一句sql语句。所以在application.properties/application.yml文件中必须配置spring.datasource.initialization-mode=ALWAYS

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 10][]

## **操作数据库：** ##

JdbcTemplateAutoConfiguration

@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);
    		}
    
    	}
    
    }

自动配置了JdbcTemplate操作数据库

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 11][]

测试：

![20191004190905609.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70]: /images/20230601/baadd2864cfe4e88ae3a8158d8c9872c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 1]: /images/20230601/0b7fa247246e4c5898e0cbd7f596858f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 2]: /images/20230601/87613eaad3fb4c1a9c20f9fa33322c89.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 3]: /images/20230601/6a5b0b4a68174101b213c647153cee03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 4]: /images/20230601/cb24c5a9a04a495b9a74669ac1124845.png
[20191004165942945.png]: /images/20230601/c748b18c68564a5e8d487b4618f9eb15.png
[DataSourcePoolMetadata-class-uml]: /images/20230601/66b02a62e41040b08f5a8e24da38a478.png
[SouthEast]: /images/20230601/c435210a22d242c0ab2c4cc18db5e775.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 5]: /images/20230601/3bb7174fcecb43249818767325f535ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 6]: /images/20230601/310d663b72d74ef7b9e4bd866c1d3904.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 7]: /images/20230601/ba28bbc305954749b2acad51656147f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 8]: /images/20230601/65e394f4d3d94d928d0ab51859b018d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 9]: /images/20230601/a7696b2f81b648ad8348cef529cd10f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 10]: /images/20230601/d4c7398ea1014babbb58a1fbf5b9efa9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2phdHBlbg_size_16_color_FFFFFF_t_70 11]: /images/20230601/7cd3b30102d844b79330dc5b57d75b31.png
[20191004190905609.png]: /images/20230601/3bba9020e369466aacfa0d011820ee62.png