跟我学springboot（二十四）springboot-数据访问整合JDBC和druid数据源

落日映苍穹つ 2022-05-10 05:04 260阅读 0赞

# 1.springboot数据访问简介 #

主流我们使用的是JDBC、MyBatis、Spring Data JPA

进入spring官网https://spring.io/projects, 找到spring data打开进入：

![在这里插入图片描述][70]

从左侧栏我们可以找到自己需要的springdata。而我们经常使用的starter从他的命名规则来看也通常是以spring data开头的starter，比如：

![在这里插入图片描述][70 1]

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

# 2.整合JDBC与数据源 #

新建一个项目来整合：

*  File- new project

![在这里插入图片描述][70 2]

![在这里插入图片描述][70 3]

Next,勾选我们需要的mysql和jdbc:

![在这里插入图片描述][70 4]

next,勾选web模块：

![在这里插入图片描述][70 5]

Finish！

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://192.168.15.22:3306/jdbc
        driver-class-name: com.mysql.jdbc.Driver

默认是用org.apache.tomcat.jdbc.pool.DataSource作为数据源；数据源的相关配置都在DataSourceProperties里面；自动配置原理：org.springframework.boot.autoconfigure.jdbc

数据源：

*  参考DataSourceConfiguration，根据配置创建数据源，默认使用Tomcat连接池；可以使用spring.datasource.type指定自定义的数据源类型；
    
    查看源码：
    
        // // Source code recreated from a .class file by IntelliJ IDEA // (powered by Fernflower decompiler) // package org.springframework.boot.autoconfigure.jdbc; import com.zaxxer.hikari.HikariDataSource; import javax.sql.DataSource; import org.apache.commons.dbcp2.BasicDataSource; import org.springframework.boot.autoconfigure.condition.ConditionalOnClass; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DatabaseDriver; import org.springframework.context.annotation.Bean; abstract class DataSourceConfiguration { DataSourceConfiguration() { } protected <T> T createDataSource(DataSourceProperties properties, Class<? extends DataSource> type) { return properties.initializeDataSourceBuilder().type(type).build(); } @ConditionalOnMissingBean({ DataSource.class}) @ConditionalOnProperty( name = { "spring.datasource.type"} ) static class Generic { Generic() { } @Bean public DataSource dataSource(DataSourceProperties properties) { return properties.initializeDataSourceBuilder().build(); } } @ConditionalOnClass({ BasicDataSource.class}) @ConditionalOnProperty( name = { "spring.datasource.type"}, havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true ) static class Dbcp2 extends DataSourceConfiguration { Dbcp2() { } @Bean @ConfigurationProperties( prefix = "spring.datasource.dbcp2" ) public BasicDataSource dataSource(DataSourceProperties properties) { return (BasicDataSource)this.createDataSource(properties, BasicDataSource.class); } } /** @deprecated */ @ConditionalOnClass({ org.apache.commons.dbcp.BasicDataSource.class}) @ConditionalOnProperty( name = { "spring.datasource.type"}, havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true ) @Deprecated static class Dbcp extends DataSourceConfiguration { Dbcp() { } @Bean @ConfigurationProperties( prefix = "spring.datasource.dbcp" ) public org.apache.commons.dbcp.BasicDataSource dataSource(DataSourceProperties properties) { org.apache.commons.dbcp.BasicDataSource dataSource = (org.apache.commons.dbcp.BasicDataSource)this.createDataSource(properties, org.apache.commons.dbcp.BasicDataSource.class); DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl()); String validationQuery = databaseDriver.getValidationQuery(); if (validationQuery != null) { dataSource.setTestOnBorrow(true); dataSource.setValidationQuery(validationQuery); } return dataSource; } } @ConditionalOnClass({ HikariDataSource.class}) @ConditionalOnProperty( name = { "spring.datasource.type"}, havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true ) static class Hikari extends DataSourceConfiguration { Hikari() { } @Bean @ConfigurationProperties( prefix = "spring.datasource.hikari" ) public HikariDataSource dataSource(DataSourceProperties properties) { return (HikariDataSource)this.createDataSource(properties, HikariDataSource.class); } } @ConditionalOnClass({ org.apache.tomcat.jdbc.pool.DataSource.class}) @ConditionalOnProperty( name = { "spring.datasource.type"}, havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true ) static class Tomcat extends DataSourceConfiguration { Tomcat() { } @Bean @ConfigurationProperties( prefix = "spring.datasource.tomcat" ) public org.apache.tomcat.jdbc.pool.DataSource dataSource(DataSourceProperties properties) { org.apache.tomcat.jdbc.pool.DataSource dataSource = (org.apache.tomcat.jdbc.pool.DataSource)this.createDataSource(properties, org.apache.tomcat.jdbc.pool.DataSource.class); DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl()); String validationQuery = databaseDriver.getValidationQuery(); if (validationQuery != null) { dataSource.setTestOnBorrow(true); dataSource.setValidationQuery(validationQuery); } return dataSource; } } }
 *  SpringBoot默认可以支持；org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource
 *  自定义数据源类型
    
        /** * 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(); } }

DataSourceInitializer：ApplicationListener；

作用：  
1）、runSchemaScripts();运行建表语句；  
2）、runDataScripts();运行插入数据的sql语句；

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

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

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

3、测试

在resources下添加我们自己的sql文件，命名为department.sql：

DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `departmentName` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在application.yml引入：

schema:
          - classpath:department.sql

那么在项目启动的时候回自动执行schema里面的语句。

编写测试类:

@Controller public class HelloController { @Autowired JdbcTemplate jdbcTemplate; @ResponseBody @GetMapping("/query") public Map<String,Object> map(){ List<Map<String, Object>> list = jdbcTemplate.queryForList("select * FROM department"); return list.get(0); } }

4、高级配置：使用druid数据源  
– 引入druid

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

– 配置属性

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

5、配置druid数据源监控

@Configuration public class DruidConfig { //获取配置文件的配置项，以spring.datasource开头 @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druid(){ return new DruidDataSource(); } //配置Druid的监控 //1、配置一个管理后台的Servlet @Bean public ServletRegistrationBean statViewServlet(){ //在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; }

运行:[http://localhost:8080/druid/index.html][http_localhost_8080_druid_index.html]

[70]: /images/20220510/14cf44ef8d7b44f280d2aebef7b8c5d3.png
[70 1]: /images/20220510/e9c2f17b38c849639e97a558f02dba83.png
[70 2]: /images/20220510/8c5b34b681ce4b88972acc2d3f449808.png
[70 3]: /images/20220510/9270779bd54b449b80a3ce0f47b71c94.png
[70 4]: /images/20220510/101fe10901b74e9d98d668f0628948e3.png
[70 5]: /images/20220510/05505512d4e2498093dbddf02beb77c4.png
[http_localhost_8080_druid_index.html]: http://localhost:8080/druid/index.html