40-数据访问(上)
1. 数据源的自动配置
1.1 导入 JDBC 场景
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
查看依赖:
为什么导入 JDBC 场景,官方不导入驱动?因为官方不知道我们接下要操作什么数据库。
<properties>
...
<mysql.version>5.1.49</mysql.version>
</properties>
<dependencies>
<!-- 版本控制依赖的是 8.x.x,但本机上的数据库是 5.7.x,↑ ↓ 修改依赖版本的两种方式 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!-- <version>5.1.49</version> -->
</dependency>
...
<dependencies>
1.2 相关自动配置
DataSourceAutoConfiguration(数据源的自动配置类)
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties implements ... { ... }
// ··········································································
@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@ConditionalOnMissingBean(type = "io.r2dbc.spi.ConnectionFactory") // 响应式编程
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({
DataSourcePoolMetadataProvidersConfiguration.class,
DataSourceInitializationConfiguration.class
})
public class DataSourceAutoConfiguration {
@Configuration(proxyBeanMethods = false)
@Conditional(PooledDataSourceCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
protected static class PooledDataSourceConfiguration {}
// ...
}
数据库连接池的配置,须在容器中没有 DataSource 的情况下才会自动配置;查看上面场景依赖可知,底层配置好的连接池是 Hikari。
修改数据源的配置信息:
spring:
datasource:
url: jdbc:mysql://localhost:3306/test
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
# type: com.zaxxer.hikari.HikariDataSource
补充:
- DataSourceTransactionManagerAutoConfiguration(事务管理器的自动配置)
- XADataSourceAutoConfiguration(分布式事务相关的自动配置)
2. 使用 Druid 数据源
整合第三方技术的两种方式:① 自定义√;② starter
2.1 自定义方式
2.1.1 配置数据源
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.17</version>
</dependency>
虽然 data-jdbc 场景默认导入了 Hikari 数据源,但是可以看到 DataSourceConfiguration 类中的各种数据源配置上都有 @ConditionalOnMissingBean(DataSource.class)
这个条件,所以我们只需自己在配置类里配置一个 DruidDataSource,Hikari 就不会生效了。
@Configuration
public class DaoConfig {
@Bean
@ConfigurationProperties("spring.datasource")
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
return dataSource;
}
}
2.1.2 监控统计功能
(1)Druid 内置提供一个 StatFilter,用于统计监控信息。
StatFilter 的别名是 stat,这个别名映射配置信息保存在 druid-xxx.jar!/META-INF/druid-filter.properties 中。在 Spring 中使用别名配置方式如下:
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" ...>
...
<property name="filters" value="stat" />
</bean>
转换成 SpringBoot 的写法:① 在核心配置文件中配置 spring.datasource.filters=stat
以绑定到 DruidDataSource 上;② 配置数据源的时候直接 set 进去,如下所示。
@Bean
@ConfigurationProperties("spring.datasource")
public DataSource dataSource() throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setFilters("stat");
return dataSource;
}
(2)Druid 内置提供了一个 StatViewServlet 用于展示 Druid 的统计信息。
这个 StatViewServlet 的用途包括:① 提供监控信息展示的 html 页面;② 提供监控信息的 JSON API。
根据配置中的 url-pattern 来访问内置监控页面,如果是下面的配置,内置监控页面的首页是 /druid/index.html。
<servlet>
<servlet-name>DruidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DruidStatView</servlet-name>
<url-pattern>/druid/*</url-pattern>
</servlet-mapping>
转换成 SpringBoot 的写法:
@Bean
public ServletRegistrationBean statViewServlet() {
StatViewServlet servlet = new StatViewServlet();
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(servlet, "/druid/*");
return bean;
}
如果不想该页面直接被访问,可以配置监控页面访问密码(loginUsername&loginPassword),还可以配置 allow 和 deny(访问 IP)。
(3)WebStatFilter 用于采集 web-jdbc 关联监控的数据(详见上面的超链接)。
2.2 starter 方式
https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
2.2.1 导入 starter
注掉 druid 的依赖,引入 druid-starter 的依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
2.2.2 自动配置类
@Configuration
@ConditionalOnClass(DruidDataSource.class)
// before! 不然就先创建 Hikari 了!
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class, DruidStatViewServletConfiguration.class,
DruidWebStatFilterConfiguration.class, DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {
@Bean(initMethod = "init")
@ConditionalOnMissingBean
public DataSource dataSource() {
LOGGER.info("Init DruidDataSource");
return new DruidDataSourceWrapper();
}
}
2.2.3 配置类
(1)DruidStatProperties
@ConfigurationProperties("spring.datasource.druid")
public class DruidStatProperties {
private String[] aopPatterns;
private StatViewServlet statViewServlet = new StatViewServlet();
private WebStatFilter webStatFilter = new WebStatFilter();
public static class StatViewServlet {
/**
* Enable StatViewServlet, default false.
*/
private boolean enabled;
private String urlPattern;
private String allow;
private String deny;
private String loginUsername;
private String loginPassword;
private String resetEnable;
}
public static class WebStatFilter {
/**
* Enable WebStatFilter, default false.
*/
private boolean enabled;
private String urlPattern;
private String exclusions;
private String sessionStatMaxCount;
private String sessionStatEnable;
private String principalSessionName;
private String principalCookieName;
private String profileEnable;
}
}
(2)DruidSpringAopConfiguration
@ConditionalOnProperty("spring.datasource.druid.aop-patterns")
public class DruidSpringAopConfiguration { ... }
(3)DruidStatViewServletConfiguration
@ConditionalOnWebApplication
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled", havingValue = "true")
public class DruidStatViewServletConfiguration { ... }
(4)DruidWebStatFilterConfiguration
@ConditionalOnWebApplication
@ConditionalOnProperty(name = "spring.datasource.druid.web-stat-filter.enabled", havingValue = "true")
public class DruidWebStatFilterConfiguration { ... }
(5)DruidFilterConfiguration
public class DruidFilterConfiguration {
@Bean
@ConfigurationProperties(XXX)
@ConditionalOnProperty(prefix = XXX, name = "enabled")
@ConditionalOnMissingBean
public XXX xXX() {
return new XXX();
}
private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";
private static final String FILTER_WALL_CONFIG_PREFIX = FILTER_WALL_PREFIX + ".config";
}
2.2.4 配置示例
spring:
datasource:
url: jdbc:mysql://localhost:3306/test
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
druid:
aop-patterns: cn.edu.nuist.admin.* # 监控 SpringBean
filters: stat,wall # 底层开启功能: stat(sql监控)、wall(防火墙)
stat-view-servlet: # 配置监控页功能
enabled: true
login-username: admin
login-password: admin
resetEnable: false
web-stat-filter: # 监控 web
enabled: true
urlPattern: /*
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
filter:
stat: # 对上面 filters 中开启的 StatFilter 的详细配置
slow-sql-millis: 1000
logSlowSql: true
enabled: true
wall: # 配置 WallFilter
enabled: true
config:
drop-table-allow: false
3. 整合 MyBatis
3.1 导入依赖&自动配置
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
@ConfigurationProperties(prefix = MybatisProperties.MYBATIS_PREFIX)
public class MybatisProperties {
public static final String MYBATIS_PREFIX = "mybatis";
// ...
}
// ·································································
@org.springframework.context.annotation.Configuration
@ConditionalOnClass({ SqlSessionFactory.class, SqlSessionFactoryBean.class })
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(MybatisProperties.class)
@AutoConfigureAfter({
DataSourceAutoConfiguration.class, MybatisLanguageDriverAutoConfiguration.class
})
public class MybatisAutoConfiguration implements InitializingBean {
private static final Logger logger;
private final MybatisProperties properties;
private final Interceptor[] interceptors;
private final TypeHandler[] typeHandlers;
private final LanguageDriver[] languageDrivers;
private final ResourceLoader resourceLoader;
private final DatabaseIdProvider databaseIdProvider;
private final List<ConfigurationCustomizer> configurationCustomizers;
@Bean
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception { ... }
@Bean
@ConditionalOnMissingBean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
ExecutorType executorType = this.properties.getExecutorType();
if (executorType != null) {
return new SqlSessionTemplate(sqlSessionFactory, executorType);
} else {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
// ...
/**
* This will just scan the same base package as Spring Boot does. If you want more power,
* you can explicitly use {@link org.mybatis.spring.annotation.MapperScan} but this will
* get typed mappers working correctly.
*/
public static class AutoConfiguredMapperScannerRegistrar
implements BeanFactoryAware, ImportBeanDefinitionRegistrar {
private BeanFactory beanFactory;
@Override
public void registerBeanDefinitions(
AnnotationMetadata importingClassMetadata, BeanDefinitionRegistry registry) { ... }
}
/**
* If mapper registering configuration or mapper scanning configuration not present,
* this configuration allow to scan mappers based on the same component-scanning path
* as Spring Boot itself.
*/
@org.springframework.context.annotation.Configuration
@Import(AutoConfiguredMapperScannerRegistrar.class)
@ConditionalOnMissingBean({ MapperFactoryBean.class, MapperScannerConfigurer.class })
public static class MapperScannerRegistrarNotFoundConfiguration ...
}
3.2 简单整合示例
http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
CityMapper
@Mapper // 若觉得在每个 Mapper 接口上写注解麻烦,可以直接在启动类上增加 @MapperScan
public interface CityMapper {
City getCityById(Long id);
@Insert("INSERT INTO city(`name`,`state`,`country`) VALUES (#{name},#{state},#{country})")
@Options(useGeneratedKeys = true, keyProperty = "id")
boolean insertCity(City city);
}
CityMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.edu.nuist.mapper.CityMapper">
<select id="getCityById">
SELECT * FROM city WHERE id = #{id}
</select>
</mapper>
application.yml
# mybatis.configuration(推荐) 和 mybatis.config-location 不能同时存在,全局配置要不然就
# 放全局配置文件中,要不就用 mybatis.configuration.xxx的方式挨个配好,不能混用。
mybatis:
mapper-locations: classpath:mybatis.mapper/*.xml
configuration:
map-underscore-to-camel-case: true
4. 整合 MyBatisPlus
4.1 导入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
只需要引入一个 MP,mybatis-starter 和 jdbc-starter 就都不用再引了。
4.2 整合测试
(1)实体类 UserPlus
@Data
@TableName("user") // 默认找 `user_plus` 表
public class UserPlus {
private Long id;
private String name;
private Integer age;
private String email;
@TableField(exist = false)
private String future;
}
(2)映射文件
因为 MybatisPlusProperties 已经有 String[] mapperLocations = new String[]{"classpath*:/mapper/**/*.xml"}
的默认配置,所以在 application.yml 中就没弄任何关于 mybatis / mybatis-plus 开头的配置,就把 *Mapper.xml 的外层目录由 resource/mybatis/mapper 改为 resource/mapper,简单整合嘛这不~
(3)映射文件绑定的接口(没加 @Mapper
注解,直接在主启动类上加 @MapperScan("cn.edu.nuist.mapper")
一次搞定)
public interface UserPlusMapper extends BaseMapper<UserPlus> {}
(4)Service 接口
public interface UserPlusService extends IService<UserPlus> {}
(5)Service 实现
@Service
public class UserPlusServiceImpl
extends ServiceImpl<UserPlusMapper, UserPlus> implements UserPlusService {}
(6)分页插件
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
(7)Controller
@GetMapping("/user/delete/{id}")
public String delUser(@RequestParam(value = "pn", defaultValue = "1") Integer pn,
@PathVariable("id") Long id, RedirectAttributes ra ) {
userPlusService.removeById(id);
ra.addAttribute("pn", pn);
return "redirect:/dynamic_table";
}
@GetMapping("/user/table")
public String users(@RequestParam(value="pn", defaultValue="1") Integer pn, Model model) {
Page<UserPlus> page = new Page<>(pn, 3);
userPlusService.page(page, null);
model.addAttribute("users", page);
return "table/dynamic_table";
}