一、SpringBoot数据库访问
1、导入依赖
1 <?xml version="1.0" encoding="UTF-8"?>
2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
4 <modelVersion>4.0.0</modelVersion>
5 <parent>
6 <groupId>org.springframework.boot</groupId>
7 <artifactId>spring-boot-starter-parent</artifactId>
8 <version>2.2.6.RELEASE</version>
9 <relativePath/> <!-- lookup parent from repository -->
10 </parent>
11 <groupId>com.lxy</groupId>
12 <artifactId>springboot-05-data</artifactId>
13 <version>0.0.1-SNAPSHOT</version>
14 <name>springboot-05-data</name>
15 <description>Demo project for Spring Boot</description>
16
17 <properties>
18 <java.version>1.8</java.version>
19 </properties>
20
21 <dependencies>
22
23 <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
24 <dependency>
25 <groupId>com.alibaba</groupId>
26 <artifactId>druid</artifactId>
27 <version>1.1.21</version>
28 </dependency>
29
30 <!--log4j-->
31 <dependency>
32 <groupId>log4j</groupId>
33 <artifactId>log4j</artifactId>
34 <version>1.2.17</version>
35 </dependency>
36
37 <!--web-->
38 <dependency>
39 <groupId>org.springframework.boot</groupId>
40 <artifactId>spring-boot-starter-web</artifactId>
41 </dependency>
42
43 <!--jdbc-->
44 <dependency>
45 <groupId>org.springframework.boot</groupId>
46 <artifactId>spring-boot-starter-jdbc</artifactId>
47 </dependency>
48
49 <!--mybatis-->
50 <dependency>
51 <groupId>org.mybatis.spring.boot</groupId>
52 <artifactId>mybatis-spring-boot-starter</artifactId>
53 <version>2.1.2</version>
54 </dependency>
55
56 <dependency>
57 <groupId>mysql</groupId>
58 <artifactId>mysql-connector-java</artifactId>
59 <scope>runtime</scope>
60 </dependency>
61 <dependency>
62 <groupId>org.springframework.boot</groupId>
63 <artifactId>spring-boot-starter-test</artifactId>
64 <scope>test</scope>
65 <exclusions>
66 <exclusion>
67 <groupId>org.junit.vintage</groupId>
68 <artifactId>junit-vintage-engine</artifactId>
69 </exclusion>
70 </exclusions>
71 </dependency>
72 </dependencies>
73
74 <build>
75 <plugins>
76 <plugin>
77 <groupId>org.springframework.boot</groupId>
78 <artifactId>spring-boot-maven-plugin</artifactId>
79 </plugin>
80 </plugins>
81 </build>
82
83 </project>
2、配置application.yml文件中关于数据源的参数
1 spring:
2 datasource:
3 username: root
4 password: mysql
5 url: jdbc:mysql://localhost:3306/mybatis?&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8&useSSL=false
6 driver-class-name: com.mysql.cj.jdbc.Driver
7 type: com.alibaba.druid.pool.DruidDataSource
8
9
10 #Spring Boot 默认是不注入这些属性值的，需要自己绑定
11 #druid 数据源专有配置
12 initialSize: 5
13 minIdle: 5
14 maxActive: 20
15 maxWait: 60000
16 timeBetweenEvictionRunsMillis: 60000
17 minEvictableIdleTimeMillis: 300000
18 validationQuery: SELECT 1 FROM DUAL
19 testWhileIdle: true
20 testOnBorrow: false
21 testOnReturn: false
22 poolPreparedStatements: true
23
24 #配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
25 #如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
26 #则导入 log4j 依赖即可，Maven 地址：https://mvnrepository.com/artifact/log4j/log4j
27 filters: stat,wall,log4j
28 maxPoolPreparedStatementPerConnectionSize: 20
29 useGlobalDataSourceStat: true
30 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
二、自动配置原理
1、参考 DataSourceConfiguration 根据配置创建数据源。默认使用 HikariDataSource 数据源
2、SpringBoot默认还可以支持：org.apache.tomcat.jdbc.pool.DataSource.class、org.apache.commons.dbcp2.BasicDataSource.class数据源
3、自定义数据源类型
1 /**
2 * Generic DataSource configuration.
3 */
4 @Configuration(proxyBeanMethods = false)
5 @ConditionalOnMissingBean(DataSource.class)
6 @ConditionalOnProperty(name = "spring.datasource.type")
7 static class Generic {
8
9 @Bean
10 DataSource dataSource(DataSourceProperties properties) {
11 // 使用DataSourceBuilder创建数据源，利用反射创建相应type的数据源，并绑定相关属性
12 return properties.initializeDataSourceBuilder().build();
13 }
14 }
4、DataSourceInitiallizer：ApplicationListener
作用：在运行期间创建表
配置规则： 把相应的.sql文件放入resource/sql文件夹下即可
1 spring:
2 datasource:
3 initialization-mode: always
4 schema:
5 - classpath:sql/department.sql
6 - classpath:sql/employee.sql
三、配置Druid数据源
1、添加依赖
1 <!--druid -->
2 <dependency>
3 <groupId>com.alibaba</groupId>
4 <artifactId>druid</artifactId>
5 <version>1.1.21</version>
6 </dependency>
7
8 <!--log4j-->
9 <dependency>
10 <groupId>log4j</groupId>
11 <artifactId>log4j</artifactId>
12 <version>1.2.17</version>
13 </dependency>
2、在application.yml中配置druid参数 同上一、2
3、配置druidController
1 package com.lxy.config;
2
3 import com.alibaba.druid.pool.DruidDataSource;
4 import com.alibaba.druid.support.http.StatViewServlet;
5 import com.alibaba.druid.support.http.WebStatFilter;
6 import org.springframework.boot.context.properties.ConfigurationProperties;
7 import org.springframework.boot.web.servlet.FilterRegistrationBean;
8 import org.springframework.boot.web.servlet.ServletRegistrationBean;
9 import org.springframework.context.annotation.Bean;
10 import org.springframework.context.annotation.Configuration;
11
12
13 import javax.sql.DataSource;
14 import java.util.Arrays;
15 import java.util.HashMap;
16 import java.util.Map;
17
18 @Configuration
19 public class DruidConfig {
20 @ConfigurationProperties(prefix = "spring.datasource")
21 @Bean
22 public DataSource druid(){
23 return new DruidDataSource();
24 }
25 //配置Druid的监控
26 //1、配置一个管理后台的Servlet
27 @Bean
28 public ServletRegistrationBean statViewServlet(){
29 ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),
30 "/druid/*");
31 Map<String,String> initParams = new HashMap<>();
32 initParams.put("loginUsername","admin");
33 initParams.put("loginPassword","123456");
34 initParams.put("allow","");//默认就是允许所有访问
35 initParams.put("deny","192.168.15.21");
36 bean.setInitParameters(initParams);
37 return bean;
38 }
39
40 //2、配置一个web监控的filter
41 @Bean
42 public FilterRegistrationBean webStatFilter(){
43 FilterRegistrationBean bean = new FilterRegistrationBean();
44 bean.setFilter(new WebStatFilter());
45 Map<String,String> initParams = new HashMap<>();
46 initParams.put("exclusions","*.js,*.css,/druid/*");
47 bean.setInitParameters(initParams);
48 bean.setUrlPatterns(Arrays.asList("/*"));
49 return bean;
50 }
51 }