Springboot—数据访问

目录

SQL：

自动配置分析：

  DataSourceAutoConfiguration：数据源的自动配置

JdbcTemplateAutoConfiguration:

Druid数据源：

DruidDataSourceAutoConfigure

然后我们看下关键的DruidStatProperties：

DruidDataSourceAutoConfigure:

 yml全部：

自定义实现监控页与监控功能：

---

SQL：

   1.首先导入JDBC场景：

1.  
   <dependency>
2.  
   <groupId>org.springframework.boot</groupId>
3.  
   <artifactId>spring-boot-starter-data-jdbc</artifactId>
4.  
   </dependency>

2.然后我们导入驱动（数据库版本尽量与驱动版本保持一致）：

1.  
   <dependency>
2.  
   <!-- 数据库版本最好与数据库版本一致:可以利用就近原则约定-->
3.  
   <groupId>mysql</groupId>
4.  
   <artifactId>mysql-connector-java</artifactId>
5.  
   <version>5.1.49</version>
6.  
   </dependency>

自动配置分析：

  DataSourceAutoConfiguration：数据源的自动配置

1.  
   @EnableConfigurationProperties({DataSourceProperties.class})
2.  
   @Import({DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class})
3.  
   public class DataSourceAutoConfiguration {
4.  
   public DataSourceAutoConfiguration() {
5.  
   }

  可以发现底层默认配置好的连接池为：HikariDataSource

1.  
   @Configuration(proxyBeanMethods = false)
2.  
   @Conditional(PooledDataSourceCondition.class)
3.  
   @ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
4.  
   @Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
5.  
   DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
6.  
   DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
7.  
   protected static class PooledDataSourceConfiguration

然后我们看看被数据源自动配置所绑定的DataSourceProperties：

1.  
   @ConfigurationProperties(
2.  
   prefix = "spring.datasource"
3.  
   )
4.  
   public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {
5.  
   private ClassLoader classLoader;
6.  
   private boolean generateUniqueName = true;
7.  
   private String name;
8.  
   private Class<? extends DataSource> type;
9.  
   private String driverClassName;
10.  
    private String url;
11.  
    private String username;
12.  
    private String password;
13.  
    private String jndiName;

所以说我们可以用yml对数据源驱动进行配置（以spring.datasource.xxx进行配置）

1.  
   spring:
2.  
   datasource:
3.  
   url: jdbc:mysql://localhost:3306/db_account
4.  
   username: root
5.  
   password: 123456
6.  
   driver-class-name: com.mysql.jdbc.Driver

JdbcTemplateAutoConfiguration:

  JdbcTemplate的自动配置，里面有对数据库的crud；

1.  
    
2.  
   @Configuration(
3.  
   proxyBeanMethods = false
4.  
   )
5.  
   @ConditionalOnClass({DataSource.class, JdbcTemplate.class})
6.  
   @ConditionalOnSingleCandidate(DataSource.class)
7.  
   @AutoConfigureAfter({DataSourceAutoConfiguration.class})
8.  
   @EnableConfigurationProperties({JdbcProperties.class})
9.  
   @Import({JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class})
10.  
    public class JdbcTemplateAutoConfiguration {
11.  
    public JdbcTemplateAutoConfiguration() {
12.  
    }
13.  
    }

JdbcProperties中绑定了jdbc操作（以spring.jdbc.xxx在yml中配置）：

1.  
    
2.  
   @ConfigurationProperties(
3.  
   prefix = "spring.jdbc"
4.  
   )
5.  
   public class JdbcProperties {
6.  
   private final JdbcProperties.Template template = new JdbcProperties.Template();
7.  
    
8.  
   public JdbcProperties() {
9.  
   }

扩：

• DataSourceTransactionManagerAutoConfiguration： 事务管理器的自动配置
• JdbcTemplateAutoConfiguration： JdbcTemplate的自动配置，可以来对数据库进行crud

• 可以修改这个配置项@ConfigurationProperties(prefix = "spring.jdbc") 来修改JdbcTemplate
• @Bean@Primary JdbcTemplate；容器中有这个组件
• JndiDataSourceAutoConfiguration： jndi的自动配置
• XADataSourceAutoConfiguration： 分布式事务相关的

测试：

1.  
   @Slf4j
2.  
   @SpringBootTest
3.  
   class Boot05WebAdminApplicationTests {
4.  
    
5.  
   @Autowired
6.  
   JdbcTemplate jdbcTemplate;
7.  
    
8.  
    
9.  
   @Test
10.  
    void contextLoads() {
11.  
     
12.  
    // jdbcTemplate.queryForObject("select * from account_tbl")
13.  
    // jdbcTemplate.queryForList("select * from account_tbl",)
14.  
    Long aLong = jdbcTemplate.queryForObject("select count(*) from account_tbl", Long.class);
15.  
    log.info("记录总数：{}",aLong);
16.  
    }
17.  
     
18.  
    }

Druid数据源：

  这里放上druid的文档

• https://github.com/alibaba/druid/wiki/常见问题
• English https://github.com/alibaba/druid/wiki/FAQ

  在springboot中，我们不能像之前那样导入bean来配置数据源，或者在web.xml中通过servlet映射来配置功能了；

而是自定义Configuration，注册组件的方式或者通过yaml配置的方式来配置数据源增加配置功能；

DruidDataSourceAutoConfigure

 可以发现它是要在DataSourceAutoConfiguration之前执行的，因为DataSourceAutoConfiguration里面配置了默认数据源，所以要先执行；

1.  
    
2.  
   @Configuration
3.  
   @ConditionalOnClass({DruidDataSource.class})
4.  
   @AutoConfigureBefore({DataSourceAutoConfiguration.class})
5.  
   @EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
6.  
   @Import({DruidSpringAopConfiguration.class, DruidStatViewServletConfiguration.class, DruidWebStatFilterConfiguration.class, DruidFilterConfiguration.class})
7.  
   public class DruidDataSourceAutoConfigure {
8.  
   private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);
9.  
    
10.  
    public DruidDataSourceAutoConfigure() {
11.  
    }
12.  
     
13.  
    @Bean(
14.  
    initMethod = "init"
15.  
    )
16.  
    @ConditionalOnMissingBean
17.  
    public DataSource dataSource() {
18.  
    LOGGER.info("Init DruidDataSource");
19.  
    return new DruidDataSourceWrapper();
20.  
    }
21.  
    }

然后我们看下关键的DruidStatProperties：

里面能够配置的东西有很多：以spring.datasource.druid为前缀，实现aoppatterns（监控springBean）；还可以开启filters：stat,wall,slf4j等功能（全局性的，不是具体的）

大致有两个功能：1：StatViewServlet:配置监控页功能；2：WebStatFilter:监控web

1.  
    
2.  
   @ConfigurationProperties("spring.datasource.druid")
3.  
   public class DruidStatProperties {
4.  
   private String[] aopPatterns;
5.  
   private DruidStatProperties.StatViewServlet statViewServlet = new DruidStatProperties.StatViewServlet();
6.  
   private DruidStatProperties.WebStatFilter webStatFilter = new DruidStatProperties.WebStatFilter();
7.  
    
8.  
   public DruidStatProperties() {
9.  
   }
10.  
     
11.  
    public String[] getAopPatterns() {
12.  
    return this.aopPatterns;
13.  
    }
14.  
     
15.  
    public void setAopPatterns(String[] aopPatterns) {
16.  
    this.aopPatterns = aopPatterns;
17.  
    }
18.  
     
19.  
    public DruidStatProperties.StatViewServlet getStatViewServlet() {
20.  
    return this.statViewServlet;
21.  
    }
22.  
     
23.  
    public void setStatViewServlet(DruidStatProperties.StatViewServlet statViewServlet) {
24.  
    this.statViewServlet = statViewServlet;
25.  
    }
26.  
     
27.  
    public DruidStatProperties.WebStatFilter getWebStatFilter() {
28.  
    return this.webStatFilter;
29.  
    }
30.  
     
31.  
    public void setWebStatFilter(DruidStatProperties.WebStatFilter webStatFilter) {
32.  
    this.webStatFilter = webStatFilter;
33.  
    }
34.  
     
35.  
    public static class WebStatFilter {
36.  
    private boolean enabled;
37.  
    private String urlPattern;
38.  
    private String exclusions;
39.  
    private String sessionStatMaxCount;
40.  
    private String sessionStatEnable;
41.  
    private String principalSessionName;
42.  
    private String principalCookieName;
43.  
    private String profileEnable;}
44.  
     
45.  
    public static class StatViewServlet {
46.  
    private boolean enabled;
47.  
    private String urlPattern;
48.  
    private String allow;
49.  
    private String deny;
50.  
    private String loginUsername;
51.  
    private String loginPassword;
52.  
    private String resetEnable;}
53.  
     

 在yml中实现对statviewServlet与webstatFilter的全局配置：

1.  
   spring:
2.  
   datasource:
3.  
   url: jdbc:mysql://localhost:3306/db_account
4.  
   username: Wuyuhang
5.  
   password: 2002514wyh11
6.  
   driver-class-name: com.mysql.jdbc.Driver
7.  
    
8.  
   druid:
9.  
   filters: stat,wall,slf4j #开启监控和防火墙and日志功能
10.  
    stat-view-servlet: admin
11.  
    login-password: 123456
12.  
    reset-enable: false
13.  
    #监控web应用
14.  
    web-stat-filter:
15.  
    enabled: true
16.  
    url-pattern: /*
17.  
    exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
18.  
    aop-patterns: com.atguigu.admin.*

DruidDataSourceAutoConfigure:

   它在DruidDataSourceAutoConfigure类中被导入，作用：

你可以发现它里面导入了非常多的组件，监控、防火墙、日志slf4j等等：你可以自己配置这些功能，很明显它组件都用了@ConditionOnMissingBean，说明他是优先自定义的；

1.  
   //
2.  
   // Source code recreated from a .class file by IntelliJ IDEA
3.  
   // (powered by FernFlower decompiler)
4.  
   //
5.  
    
6.  
   package com.alibaba.druid.spring.boot.autoconfigure.stat;
7.  
    
8.  
   import com.alibaba.druid.filter.config.ConfigFilter;
9.  
   import com.alibaba.druid.filter.encoding.EncodingConvertFilter;
10.  
    import com.alibaba.druid.filter.logging.CommonsLogFilter;
11.  
    import com.alibaba.druid.filter.logging.Log4j2Filter;
12.  
    import com.alibaba.druid.filter.logging.Log4jFilter;
13.  
    import com.alibaba.druid.filter.logging.Slf4jLogFilter;
14.  
    import com.alibaba.druid.filter.stat.StatFilter;
15.  
    import com.alibaba.druid.wall.WallConfig;
16.  
    import com.alibaba.druid.wall.WallFilter;
17.  
    import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
18.  
    import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
19.  
    import org.springframework.boot.context.properties.ConfigurationProperties;
20.  
    import org.springframework.context.annotation.Bean;
21.  
     
22.  
    public class DruidFilterConfiguration {
23.  
    private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
24.  
    private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
25.  
    private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
26.  
    private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
27.  
    private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
28.  
    private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
29.  
    private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
30.  
    private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";
31.  
    private static final String FILTER_WALL_CONFIG_PREFIX = "spring.datasource.druid.filter.wall.config";
32.  
     
33.  
    public DruidFilterConfiguration() {
34.  
    }
35.  
     
36.  
    @Bean
37.  
    @ConfigurationProperties("spring.datasource.druid.filter.stat")
38.  
    @ConditionalOnProperty(
39.  
    prefix = "spring.datasource.druid.filter.stat",
40.  
    name = {"enabled"}
41.  
    )
42.  
    @ConditionalOnMissingBean
43.  
    public StatFilter statFilter() {
44.  
    return new StatFilter();
45.  
    }
46.  
     
47.  
    @Bean
48.  
    @ConfigurationProperties("spring.datasource.druid.filter.config")
49.  
    @ConditionalOnProperty(
50.  
    prefix = "spring.datasource.druid.filter.config",
51.  
    name = {"enabled"}
52.  
    )
53.  
    @ConditionalOnMissingBean
54.  
    public ConfigFilter configFilter() {
55.  
    return new ConfigFilter();
56.  
    }
57.  
     
58.  
    @Bean
59.  
    @ConfigurationProperties("spring.datasource.druid.filter.encoding")
60.  
    @ConditionalOnProperty(
61.  
    prefix = "spring.datasource.druid.filter.encoding",
62.  
    name = {"enabled"}
63.  
    )
64.  
    @ConditionalOnMissingBean
65.  
    public EncodingConvertFilter encodingConvertFilter() {
66.  
    return new EncodingConvertFilter();
67.  
    }
68.  
     
69.  
    @Bean
70.  
    @ConfigurationProperties("spring.datasource.druid.filter.slf4j")
71.  
    @ConditionalOnProperty(
72.  
    prefix = "spring.datasource.druid.filter.slf4j",
73.  
    name = {"enabled"}
74.  
    )
75.  
    @ConditionalOnMissingBean
76.  
    public Slf4jLogFilter slf4jLogFilter() {
77.  
    return new Slf4jLogFilter();
78.  
    }
79.  
     
80.  
    @Bean
81.  
    @ConfigurationProperties("spring.datasource.druid.filter.log4j")
82.  
    @ConditionalOnProperty(
83.  
    prefix = "spring.datasource.druid.filter.log4j",
84.  
    name = {"enabled"}
85.  
    )
86.  
    @ConditionalOnMissingBean
87.  
    public Log4jFilter log4jFilter() {
88.  
    return new Log4jFilter();
89.  
    }
90.  
     
91.  
    @Bean
92.  
    @ConfigurationProperties("spring.datasource.druid.filter.log4j2")
93.  
    @ConditionalOnProperty(
94.  
    prefix = "spring.datasource.druid.filter.log4j2",
95.  
    name = {"enabled"}
96.  
    )
97.  
    @ConditionalOnMissingBean
98.  
    public Log4j2Filter log4j2Filter() {
99.  
    return new Log4j2Filter();
100.  
     }
101.  
      
102.  
     @Bean
103.  
     @ConfigurationProperties("spring.datasource.druid.filter.commons-log")
104.  
     @ConditionalOnProperty(
105.  
     prefix = "spring.datasource.druid.filter.commons-log",
106.  
     name = {"enabled"}
107.  
     )
108.  
     @ConditionalOnMissingBean
109.  
     public CommonsLogFilter commonsLogFilter() {
110.  
     return new CommonsLogFilter();
111.  
     }
112.  
      
113.  
     @Bean
114.  
     @ConfigurationProperties("spring.datasource.druid.filter.wall.config")
115.  
     @ConditionalOnProperty(
116.  
     prefix = "spring.datasource.druid.filter.wall",
117.  
     name = {"enabled"}
118.  
     )
119.  
     @ConditionalOnMissingBean
120.  
     public WallConfig wallConfig() {
121.  
     return new WallConfig();
122.  
     }
123.  
      
124.  
     @Bean
125.  
     @ConfigurationProperties("spring.datasource.druid.filter.wall")
126.  
     @ConditionalOnProperty(
127.  
     prefix = "spring.datasource.druid.filter.wall",
128.  
     name = {"enabled"}
129.  
     )
130.  
     @ConditionalOnMissingBean
131.  
     public WallFilter wallFilter(WallConfig wallConfig) {
132.  
     WallFilter filter = new WallFilter();
133.  
     filter.setConfig(wallConfig);
134.  
     return filter;
135.  
     }
136.  
     }

这里是对stat、wall防火墙、slf4j等配置的详细配置：

1.  
   //对上面的全局配置filters详细配置
2.  
   filter:
3.  
   stat: #对上面filters的详细配置
4.  
   slow-sql-millis: 1000 #超过1000ms都为慢查询
5.  
   log-slow-sql: true
6.  
   enabled: true #是否开启
7.  
   wall:
8.  
   enabled: true
9.  
   config:
10.  
    drop-table-allow: false #防火墙阻止所有删表的操作

 yml全部：

1.  
   spring:
2.  
   datasource:
3.  
   url: jdbc:mysql://localhost:3306/db_account
4.  
   username: root
5.  
   password: 123456
6.  
   driver-class-name: com.mysql.jdbc.Driver
7.  
    
8.  
   druid:
9.  
   aop-patterns: com.atguigu.admin.* #监控SpringBean
10.  
    filters: stat,wall # 底层开启功能，stat（sql监控），wall（防火墙）
11.  
     
12.  
    stat-view-servlet: # 配置监控页功能
13.  
    enabled: true
14.  
    login-username: admin
15.  
    login-password: admin
16.  
    resetEnable: false
17.  
     
18.  
    web-stat-filter: # 监控web
19.  
    enabled: true
20.  
    urlPattern: /*
21.  
    exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
22.  
     
23.  
     
24.  
    filter:
25.  
    stat: # 对上面filters里面的stat的详细配置
26.  
    slow-sql-millis: 1000
27.  
    logSlowSql: true
28.  
    enabled: true
29.  
    wall:
30.  
    enabled: true
31.  
    config:
32.  
    drop-table-allow: false
33.  
     

自定义实现监控页与监控功能：

  还是一样的思想，按照上面的来，将自定义的StatViewServlet（监控视图）与WebStatFilter(监控)的组件注册到容器就可以了，要注意的是：分别要利用ServletRegisterationBean与FilterRegisterationBean去装载对应的组件然后实现功能，最后返回；

1.  
   /**
2.  
   * 配置 druid的监控页功能
3.  
   * @return
4.  
   */
5.  
   @Bean
6.  
   public ServletRegistrationBean statViewServlet(){
7.  
   // statViewServlet就是监控视图响应
8.  
   StatViewServlet statViewServlet = new StatViewServlet();
9.  
   ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(statViewServlet, "/druid/*");
10.  
     
11.  
    registrationBean.addInitParameter("loginUsername","admin");
12.  
    registrationBean.addInitParameter("loginPassword","123456");
13.  
     
14.  
     
15.  
    return registrationBean;
16.  
    }
17.  
    /**
18.  
    * WebStatFilter 用于采集web-jdbc关联监控的数据。
19.  
    */
20.  
    @Bean
21.  
    public FilterRegistrationBean webStatFilter(){
22.  
    WebStatFilter webStatFilter = new WebStatFilter();
23.  
     
24.  
    FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(webStatFilter);
25.  
    filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
26.  
    filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
27.  
     
28.  
    return filterRegistrationBean;
29.  
    }

这篇好文章是转载于：学新通技术网