简介
对于数据访问层，无论是 SQL 还是 NOSQL，SpringBoot 默认采用整合 SpringData 的方式进行统一处理，添加了大量的自动配置，引入了各种 Template、Repository 来简化我们对数据访问层的操作，我们使用时只需进行简单的配置即可。
整合JDBC
数据源获取
1、使用 maven 构建 SpringBoot 项目，引入如下场景启动器：
2、配置数据库连接相关信息：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_jdbc
driver-class-name: com.mysql.jdbc.Driver
application.yml
3、做完上述两个操作我们就可以直接测试获取数据源了：
package com.springboot.data_jdbc;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DataJdbcApplicationTests {
@Autowired
public DataSource dataSource;
@Test
public void testDataSource() throws SQLException {
System.out.println(dataSource);
System.out.println(dataSource.getClass());
/*
org.apache.tomcat.jdbc.pool.DataSource@6107165{ConnectionPool[defaultAutoCommit=null; defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null; driverClassName=com.mysql.jdbc.Driver; maxActive=100;...}
class org.apache.tomcat.jdbc.pool.DataSource
*/
}
}
test
操作数据库
SpringBoot 通过 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration 自动配置类为我们自动配置了 JdbcTemplate，所以可以直接从容器中获取使用它。
package com.springboot.data_jdbc;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DataJdbcApplicationTests {
@Autowired
public JdbcTemplate jdbcTemplate;
@Test
public void testJdbcTemplate() throws SQLException {
List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from user;");
System.out.println(maps);
/*
[{id=1, name=张三}]
*/
}
}
源码分析
上述使用中我们只是在配置文件中配置了数据库连接信息然后我们就可以直接获取到数据源，原因也是因为 SpringBoot 给我们做了大量的自动配置，对应的相关自动配置类在 org.springframework.boot.autoconfigure.jdbc 包下：
查看 DataSourceConfiguration 类：
abstract class DataSourceConfiguration {
@SuppressWarnings("unchecked")
protected static <T> T createDataSource(DataSourceProperties properties,
Class<? extends DataSource> type) {
return (T) properties.initializeDataSourceBuilder().type(type).build();
}
@ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
static class Tomcat {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.tomcat")
public org.apache.tomcat.jdbc.pool.DataSource dataSource(
DataSourceProperties properties) {
org.apache.tomcat.jdbc.pool.DataSource dataSource = 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;
}
}
@ConditionalOnClass(HikariDataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
static class Hikari {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public HikariDataSource dataSource(DataSourceProperties properties) {
return createDataSource(properties, HikariDataSource.class);
}
}
@ConditionalOnClass(org.apache.commons.dbcp.BasicDataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true)
@Deprecated
static class Dbcp {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.dbcp")
public org.apache.commons.dbcp.BasicDataSource dataSource(
DataSourceProperties properties) {
org.apache.commons.dbcp.BasicDataSource dataSource = 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(org.apache.commons.dbcp2.BasicDataSource.class)
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
static class Dbcp2 {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.dbcp2")
public org.apache.commons.dbcp2.BasicDataSource dataSource(
DataSourceProperties properties) {
return createDataSource(properties,
org.apache.commons.dbcp2.BasicDataSource.class);
}
}
@ConditionalOnMissingBean(DataSource.class)
@ConditionalOnProperty(name = "spring.datasource.type")
static class Generic {
@Bean
public DataSource dataSource(DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
}
}
org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration
可以看到在当前工程引入不同数据源依赖时 SpringBoot 会给我们自动注册不同类型的数据源 bean，默认提供如下几个数据源的自动配置：
org.apache.tomcat.jdbc.pool.DataSource # 因 web 场景启动器默认引入了 tomcat 依赖，所以默认使用该数据源 com.zaxxer.hikari.HikariDataSource org.apache.commons.dbcp.BasicDataSource org.apache.commons.dbcp2.BasicDataSource
除了上面几个可自动配置的数据源，在第 86-93 行还有一个 Generic 内部类，该内部类的作用是为我们提供定制其它数据源功能的支持。它是如何让我们实现自定义数据源的呢？
首先该内部类起作用的前提是我们在 IoC 容器中没有注册数据源，并且还在配置中通过 spring.datasource.type 指定了数据源类型。满足这两个条件后才会做如下操作：
dataSource 方法是用来想容器中注册一个数据源 bean，而这个 bean 的是由第 90 行通过 properties.initializeDataSourceBuilder() 初始化的一个数据源构建器的 build() 生成的，查看该方法：
public DataSourceBuilder initializeDataSourceBuilder() {
return DataSourceBuilder.create(getClassLoader()).type(getType())
.driverClassName(determineDriverClassName()).url(determineUrl())
.username(determineUsername()).password(determinePassword());
}
org.springframework.boot.autoconfigure.jdbc.DataSourceProperties#initializeDataSourceBuilder
该方法创建了一个数据源构建器，接着将数据库连接信息绑定到该构建器，而这些数据库连接信息的值正是我们在配置文件中配置的 spring.datasource 节下的属性值，最后返回该构建器的实例，接着调用该构建器的 build() 方法：
public DataSource build() {
Class<? extends DataSource> type = getType();
DataSource result = BeanUtils.instantiate(type);
maybeGetDriverClassName();
bind(result);
return result;
}
org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder#build
最终利用反射创建对应类型数据源的实例，绑定数据库连接信息，返回了数据源。
再查看 DataSourceAutoConfiguration 类：
@Configuration
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
public class DataSourceAutoConfiguration {
private static final Log logger = LogFactory
.getLog(DataSourceAutoConfiguration.class);
@Bean
@ConditionalOnMissingBean
public DataSourceInitializer dataSourceInitializer(DataSourceProperties properties,
ApplicationContext applicationContext) {
return new DataSourceInitializer(properties, applicationContext);
}
public static boolean containsAutoConfiguredDataSource(
ConfigurableListableBeanFactory beanFactory) {
try {
BeanDefinition beanDefinition = beanFactory.getBeanDefinition("dataSource");
return EmbeddedDataSourceConfiguration.class.getName()
.equals(beanDefinition.getFactoryBeanName());
}
catch (NoSuchBeanDefinitionException ex) {
return false;
}
}
@Configuration
@Conditional(EmbeddedDatabaseCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
@Import(EmbeddedDataSourceConfiguration.class)
protected static class EmbeddedDatabaseConfiguration {
}
@Configuration
@Conditional(PooledDataSourceCondition.class)
@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
@Import({ DataSourceConfiguration.Tomcat.class, DataSourceConfiguration.Hikari.class,
DataSourceConfiguration.Dbcp.class, DataSourceConfiguration.Dbcp2.class,
DataSourceConfiguration.Generic.class })
@SuppressWarnings("deprecation")
protected static class PooledDataSourceConfiguration {
}
@Configuration
@ConditionalOnProperty(prefix = "spring.datasource", name = "jmx-enabled")
@ConditionalOnClass(name = "org.apache.tomcat.jdbc.pool.DataSourceProxy")
@Conditional(DataSourceAutoConfiguration.DataSourceAvailableCondition.class)
@ConditionalOnMissingBean(name = "dataSourceMBean")
protected static class TomcatDataSourceJmxConfiguration {
@Bean
public Object dataSourceMBean(DataSource dataSource) {
if (dataSource instanceof DataSourceProxy) {
try {
return ((DataSourceProxy) dataSource).createPool().getJmxPool();
}
catch (SQLException ex) {
logger.warn("Cannot expose DataSource to JMX (could not connect)");
}
}
return null;
}
}
static class PooledDataSourceCondition extends AnyNestedCondition {
PooledDataSourceCondition() {
super(ConfigurationPhase.PARSE_CONFIGURATION);
}
@ConditionalOnProperty(prefix = "spring.datasource", name = "type")
static class ExplicitType {
}
@Conditional(PooledDataSourceAvailableCondition.class)
static class PooledDataSourceAvailable {
}
}
static class PooledDataSourceAvailableCondition extends SpringBootCondition {
@Override
public ConditionOutcome getMatchOutcome(ConditionContext context,
AnnotatedTypeMetadata metadata) {
ConditionMessage.Builder message = ConditionMessage
.forCondition("PooledDataSource");
if (getDataSourceClassLoader(context) != null) {
return ConditionOutcome
.match(message.foundExactly("supported DataSource"));
}
return ConditionOutcome
.noMatch(message.didNotFind("supported DataSource").atAll());
}
private ClassLoader getDataSourceClassLoader(ConditionContext context) {
Class<?> dataSourceClass = new DataSourceBuilder(context.getClassLoader())
.findType();
return (dataSourceClass != null) ? dataSourceClass.getClassLoader() : null;
}
}
static class EmbeddedDatabaseCondition extends SpringBootCondition {
private final SpringBootCondition pooledCondition = new PooledDataSourceCondition();
@Override
public ConditionOutcome getMatchOutcome(ConditionContext context,
AnnotatedTypeMetadata metadata) {
ConditionMessage.Builder message = ConditionMessage
.forCondition("EmbeddedDataSource");
if (anyMatches(context, metadata, this.pooledCondition)) {
return ConditionOutcome
.noMatch(message.foundExactly("supported pooled data source"));
}
EmbeddedDatabaseType type = EmbeddedDatabaseConnection
.get(context.getClassLoader()).getType();
if (type == null) {
return ConditionOutcome
.noMatch(message.didNotFind("embedded database").atAll());
}
return ConditionOutcome.match(message.found("embedded database").items(type));
}
}
@Order(Ordered.LOWEST_PRECEDENCE - 10)
static class DataSourceAvailableCondition extends SpringBootCondition {
private final SpringBootCondition pooledCondition = new PooledDataSourceCondition();
private final SpringBootCondition embeddedCondition = new EmbeddedDatabaseCondition();
@Override
public ConditionOutcome getMatchOutcome(ConditionContext context,
AnnotatedTypeMetadata metadata) {
ConditionMessage.Builder message = ConditionMessage
.forCondition("DataSourceAvailable");
if (hasBean(context, DataSource.class)
|| hasBean(context, XADataSource.class)) {
return ConditionOutcome
.match(message.foundExactly("existing data source bean"));
}
if (anyMatches(context, metadata, this.pooledCondition,
this.embeddedCondition)) {
return ConditionOutcome.match(message
.foundExactly("existing auto-configured data source bean"));
}
return ConditionOutcome
.noMatch(message.didNotFind("any existing data source bean").atAll());
}
private boolean hasBean(ConditionContext context, Class<?> type) {
return BeanFactoryUtils.beanNamesForTypeIncludingAncestors(
context.getBeanFactory(), type, true, false).length > 0;
}
}
}
org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration
看第 12 行， dataSourceInitializer() 方法给容器中注册了一个数据源初始化器，查看初始化器类：
class DataSourceInitializer implements ApplicationListener<DataSourceInitializedEvent> {
private static final Log logger = LogFactory.getLog(DataSourceInitializer.class);
private final DataSourceProperties properties;
private final ApplicationContext applicationContext;
private DataSource dataSource;
private boolean initialized = false;
DataSourceInitializer(DataSourceProperties properties,
ApplicationContext applicationContext) {
this.properties = properties;
this.applicationContext = applicationContext;
}
@PostConstruct
public void init() {
if (!this.properties.isInitialize()) {
logger.debug("Initialization disabled (not running DDL scripts)");
return;
}
if (this.applicationContext.getBeanNamesForType(DataSource.class, false,
false).length > 0) {
this.dataSource = this.applicationContext.getBean(DataSource.class);
}
if (this.dataSource == null) {
logger.debug("No DataSource found so not initializing");
return;
}
runSchemaScripts();
}
private void runSchemaScripts() {
List<Resource> scripts = getScripts("spring.datasource.schema",
this.properties.getSchema(), "schema");
if (!scripts.isEmpty()) {
String username = this.properties.getSchemaUsername();
String password = this.properties.getSchemaPassword();
runScripts(scripts, username, password);
try {
this.applicationContext
.publishEvent(new DataSourceInitializedEvent(this.dataSource));
if (!this.initialized) {
runDataScripts();
this.initialized = true;
}
}
catch (IllegalStateException ex) {
logger.warn("Could not send event to complete DataSource initialization ("
+ ex.getMessage() + ")");
}
}
}
@Override
public void onApplicationEvent(DataSourceInitializedEvent event) {
if (!this.properties.isInitialize()) {
logger.debug("Initialization disabled (not running data scripts)");
return;
}
if (!this.initialized) {
runDataScripts();
this.initialized = true;
}
}
private void runDataScripts() {
List<Resource> scripts = getScripts("spring.datasource.data",
this.properties.getData(), "data");
String username = this.properties.getDataUsername();
String password = this.properties.getDataPassword();
runScripts(scripts, username, password);
}
private List<Resource> getScripts(String propertyName, List<String> resources,
String fallback) {
if (resources != null) {
return getResources(propertyName, resources, true);
}
String platform = this.properties.getPlatform();
List<String> fallbackResources = new ArrayList<String>();
fallbackResources.add("classpath*:" + fallback + "-" + platform + ".sql");
fallbackResources.add("classpath*:" + fallback + ".sql");
return getResources(propertyName, fallbackResources, false);
}
private List<Resource> getResources(String propertyName, List<String> locations,
boolean validate) {
List<Resource> resources = new ArrayList<Resource>();
for (String location : locations) {
for (Resource resource : doGetResources(location)) {
if (resource.exists()) {
resources.add(resource);
}
else if (validate) {
throw new ResourceNotFoundException(propertyName, resource);
}
}
}
return resources;
}
private Resource[] doGetResources(String location) {
try {
SortedResourcesFactoryBean factory = new SortedResourcesFactoryBean(
this.applicationContext, Collections.singletonList(location));
factory.afterPropertiesSet();
return factory.getObject();
}
catch (Exception ex) {
throw new IllegalStateException("Unable to load resources from " + location,
ex);
}
}
private void runScripts(List<Resource> resources, String username, String password) {
if (resources.isEmpty()) {
return;
}
ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
populator.setContinueOnError(this.properties.isContinueOnError());
populator.setSeparator(this.properties.getSeparator());
if (this.properties.getSqlScriptEncoding() != null) {
populator.setSqlScriptEncoding(this.properties.getSqlScriptEncoding().name());
}
for (Resource resource : resources) {
populator.addScript(resource);
}
DataSource dataSource = this.dataSource;
if (StringUtils.hasText(username) && StringUtils.hasText(password)) {
dataSource = DataSourceBuilder.create(this.properties.getClassLoader())
.driverClassName(this.properties.determineDriverClassName())
.url(this.properties.determineUrl()).username(username)
.password(password).build();
}
DatabasePopulatorUtils.execute(populator, dataSource);
}
}
org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer
在第 20 行有一个初始化方法，该方法会在当前类实例创建完成之后执行，在第 33 行执行了 runSchemaScripts() 方法，这里直接说明该方法的作用，该方法是使用来执行指定位置存放的 sql 文件中的 DDL 语句。
接着在第 37 行通过 getScripts("spring.datasource.schema", this.properties.getSchema(), "schema") 方法获取一个 DDL 脚本资源列表。接着来到第 78 行的 getScripts 方法，如果我们没有在配置文件中通过 spring.datasource.schema 属性指定 DDL sql 文件路径列表，那么将默认使用 classpath*:schema-all.sql 和 classpath*:schema.sql 位置的资源，即会执行该 sql 资源文件中的 DDL 语句。也可以通过配置 spring.datasource.schema 属性来指定一个存放有 DDL 语句的 sql 文件资源路径列表。
可以看到该类还实现了 ApplicationListener 监听器接口，即应用程序启动完成后会调用该类实例的 onApplicationEvent 方法，在该方法中执行了 runDataScripts() 方法，而该方法的作用是用来执行指定位置存放的 sql 文件中的 DML 语句。
接着在第 70 行的 runDataScripts() 方法中执行了 getScripts("spring.datasource.data", this.properties.getData(), "data") 来获取 DML 脚本资源列表，然后在第 71 行执行 getScripts("spring.datasource.data", this.properties.getData(), "data") 方法，与之前的 runSchemaScripts() 类似，如果我们没有在配置文件中通过 spring.datasource.data 属性指定 DML sql 文件的路径列表，那么将默认使用 classpath*:data-all.sql 和 classpath*:data.sql 位置的资源，即会执行该 sql 资源文件中的 DML 语句。也可以通过配置 spring.datasource.data 属性指定 DML sql 文件路径列表。
总结：
- 可以在项目的 classpath 下放置存放有 DDL 语句的 schema-all.sql 和 schema.sql sql 脚本文件，也可以通过配置 spring.datasource.schema 属性自定义 DDL 语句 sql 文件的存放路径列表，SpringBoot 将会在项目启动时执行这些文件。
- 可以在项目的 classpath 下放置存放有 DML 语句的 data-all.sql 和 data.sql sql 脚本文件，也可以通过配置 spring.datasource.data 属性自定义 DML 语句 sql 文件的存放路径列表，SpringBoot 将会在项目启动时执行这些文件。
例：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_jdbc
driver-class-name: com.mysql.jdbc.Driver
schema:
- classpath:myschema.sql
data:
- classpath:mydata.sql
application.yml
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` ) NOT NULL AUTO_INCREMENT, `name` ) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE DEFAULT CHARSET=utf8mb4;
myschema.sql
insert into user(name) values('张三');
mydata.sql
项目启动时将会执行 myschema 创建 user 表，并会执行 mydata.sql 往 user 表中添加一条数据。
切换数据源为Druid
要引入 Druid 依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.15</version>
</dependency>
方式一：配置数据源类型
通过上面的源码分析我们已经知道，如果我们要切换数据源，只需要配置 spring.datasource.type 为指定的数据源类型即可，如下：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_jdbc
driver-class-name: com.mysql.jdbc.Driver
schema:
- classpath:myschema.sql
data:
- classpath:mydata.sql
type: com.alibaba.druid.pool.DruidDataSource
application.yml
测试：
package com.springboot.data_jdbc;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DataJdbcApplicationTests {
@Autowired
public DataSource dataSource;
@Test
public void testDataSource() throws SQLException {
System.out.println(dataSource.getClass());
/*
class com.alibaba.druid.pool.DruidDataSource
*/
}
}
test
方式二：手动注册数据源
方式一其实有一个弊端，如果我们要使用 Druid 独有的配置，例如要配置 Druid 监控，仅仅在配置文件中是完成不了这个需求的，此时我们就需要手动注册数据源，并且手动将配置的属性的绑定到数据源实例。
配置：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_jdbc
driver-class-name: com.mysql.jdbc.Driver
# Durid 独有的属性
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
# 配置监控统计拦截的 filter ,去掉后监控界面无法统计，wall 用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
application.yml
手动注册数据源 bean 并配置监控：
package com.springboot.data_jdbc.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DuridConfig {
@ConfigurationProperties("spring.datasource") // 手动绑定配置属性
@Bean
public DataSource duridDataSource(){
return new DruidDataSource();
}
// 配置 Druid 监控
// 1、配置一个管理后台的 Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername", "admin"); // 登录后台的用户名
initParams.put("loginPassword", "123456");
initParams.put("allow",""); // 默认允许所有域名及 IP 访问
initParams.put("deny","127.0.0.1"); // 拒绝 127.0.0.1 访问
servletRegistrationBean.setInitParameters(initParams);
return servletRegistrationBean;
}
// 2、配置一个监控的 filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
WebStatFilter webStatFilter = new WebStatFilter();
filterRegistrationBean.setFilter(webStatFilter);
Map<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
filterRegistrationBean.setInitParameters(initParams);
filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
return filterRegistrationBean;
}
}
com.springboot.data_jdbc.config.DuridConfig
测试：
package com.springboot.data_jdbc;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DataJdbcApplicationTests {
@Autowired
public DataSource dataSource;
@Test
public void testDataSource() throws SQLException {
System.out.println(dataSource.getClass());
/*
class com.alibaba.druid.pool.DruidDataSource
*/
}
}
test
启动项目，可通过 localhost:8080/druid 访问到监控平台：
整合MyBatis
1、使用 maven 构建 SpringBoot 项目，引入如下场景启动器：
2、引入 Druid 依赖，配置 Druid 数据源，初始化测试表：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_mybatis
driver-class-name: com.mysql.jdbc.Driver
# Durid 独有的属性
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
# 配置监控统计拦截的 filter ,去掉后监控界面无法统计，wall 用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
schema:
- classpath:sql/user-schema.sql
data:
- classpath:sql/user-data.sql
application.yml
; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` ) NOT NULL AUTO_INCREMENT, `name` ) DEFAULT NULL, `gender` ) DEFAULT NULL COMMENT '0：女 1：男', `birthday` date DEFAULT NULL, `address` ) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE DEFAULT CHARSET=utf8mb4;
sql/user-schema.sql
', '1997-02-23', '北京'); ', '1998-02-03', '武汉'); ', '1996-06-04', '上海');
sql/user-data.sql
package com.springboot.data_mybatis.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DuridConfig {
@ConfigurationProperties("spring.datasource") // 手动绑定配置属性
@Bean
public DataSource duridDataSource(){
return new DruidDataSource();
}
// 配置 Druid 监控
// 1、配置一个管理后台的 Servlet
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername", "admin"); // 登录后台的用户名
initParams.put("loginPassword", "123456");
initParams.put("allow",""); // 默认允许所有域名及 IP 访问
initParams.put("deny","127.0.0.1"); // 拒绝 127.0.0.1 访问
servletRegistrationBean.setInitParameters(initParams);
return servletRegistrationBean;
}
// 2、配置一个监控的 filter
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
WebStatFilter webStatFilter = new WebStatFilter();
filterRegistrationBean.setFilter(webStatFilter);
Map<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "*.js,*.css,/druid/*");
filterRegistrationBean.setInitParameters(initParams);
filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
return filterRegistrationBean;
}
}
com.springboot.data_mybatis.config.DuridConfig
3、创建与测试表对应的 JavaBean：
package com.springboot.data_mybatis.bean;
import java.util.Date;
public class User {
private Integer id;
private String name;
private Integer gender;
private Date birthday;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
com.springboot.data_mybatis.bean.User
注解方式
编写 mapper 类，并在 mapper 类中通过注解绑定 sql：
package com.springboot.data_mybatis.mapper;
import com.springboot.data_mybatis.bean.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMappper {
@Select("select * from user")
public List<User> getAll();
@Select("select * from user where id=#{id}")
public User getById(Integer id);
@Delete("delete from user where id=#{id}")
public Integer deleteById(Integer id);
@Options(useGeneratedKeys = true,keyProperty = "id")
@Insert("insert into user(name,gender,birthday,address) values(#{name},#{gender},#{birthday},#{address})")
public Integer add(User user);
@Update("update user set name=#{name},gender=#{gender},birthday=#{birthday},address=#{address} where id=#{id}")
public Integer update(User user);
}
com.springboot.data_mybatis.mapper.UserMappper
测试：
package com.springboot.data_mybatis;
import com.springboot.data_mybatis.bean.User;
import com.springboot.data_mybatis.mapper.UserMappper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
@SuppressWarnings("all")
public class DataMybatisApplicationTests {
@Autowired
private DataSource dataSource;
@Test
public void test() {
System.out.println(dataSource.getClass());
}
@Autowired
private UserMappper userMappper;
@Test
public void testGetAll(){
List<User> all = userMappper.getAll();
System.out.println(all);
/*
[User{id=1, name='张三'}, User{id=2, name='李四'}, User{id=3, name='王五'}]
*/
}
@Test
public void testGetById(){
User user = userMappper.getById(1);
System.out.println(user);
/*
User{id=1, name='张三'}
*/
}
@Test
public void testAdd() throws ParseException {
User user = new User();
user.setName("赵六");
user.setGender(1);
user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1998-2-2"));
user.setAddress("南京");
Integer count = userMappper.add(user);
System.out.println(count);
/*
1
*/
}
@Test
public void testUpdate(){
User user = userMappper.getById(4);
user.setGender(0);
Integer count = userMappper.update(user);
System.out.println(count);
/*
1
*/
}
@Test
public void testDelete(){
Integer count = userMappper.deleteById(4);
System.out.println(count);
/*
1
*/
}
}
test
示例中是在 mapper 类上添加了 @Mapper 注解用来标识所标注的类是一个 Mapper 类，还可以通过 @MapperScan 注解配置 mapper 类的包扫描：
@MapperScan("com.springboot.data_mybatis.mapper")
可以看到上述注解方式没有 MyBatis 的核心配置文件，如果需要自定制 MyBatis 的部分配置，SpringBoot 给我们提供了 MyBatis 配置自定制类，我们只需要设置好该类实例相关属性将其放入 IoC 容器即可生效：
package com.springboot.data_mybatis.config;
import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
@Bean
public ConfigurationCustomizer configurationCustomizer(){
return new ConfigurationCustomizer() {
@Override
public void customize(Configuration configuration) {
// 启用驼峰命名，表字段 user_name 可映射到 userName 属性
configuration.setMapUnderscoreToCamelCase(true);
}
};
}
}
com.springboot.data_mybatis.config.MyBatisConfig
XML配置方式
添加 MyBatis 核心配置文件：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!--启用驼峰命名，表字段 user_name 可映射到 userName -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
mybatis/mybatis-config.xml
在 SpringBoot 配置文件中指定 MyBatis 核心配置文件和映射文件位置：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_mybatis
driver-class-name: com.mysql.jdbc.Driver
# Durid 独有的属性
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
# 配置监控统计拦截的 filter ,去掉后监控界面无法统计，wall 用于防火墙
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
# schema:
# - classpath:sql/user-schema.sql
# data:
# - classpath:sql/user-data.sql
mybatis:
config-location: classpath:mybatis/mybatis-config.xml
mapper-locations: classpath:mybatis/mapper/*.xml
application.yml
编写 mapper 类：
package com.springboot.data_mybatis.mapper;
import com.springboot.data_mybatis.bean.User;
import java.util.List;
public interface UserMappper {
public List<User> getAll();
public User getById(Integer id);
public Integer deleteById(Integer id);
public Integer add(User user);
public Integer update(User user);
}
com.springboot.data_mybatis.mapper.UserMappper
编写映射文件：
<?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="com.springboot.data_mybatis.mapper.UserMappper">
<select id="getAll" resultType="com.springboot.data_mybatis.bean.User">
select * from user
</select>
<select id="getById" parameterType="int" resultType="com.springboot.data_mybatis.bean.User">
select * from user where id=#{id}
</select>
<delete id="deleteById" parameterType="int">
delete from user where id=#{id}
</delete>
<insert id="add" parameterType="com.springboot.data_mybatis.bean.User" useGeneratedKeys="true" keyProperty="id">
insert into user(name,gender,birthday,address) values(#{name},#{gender},#{birthday},#{address})
</insert>
<update id="update" parameterType="com.springboot.data_mybatis.bean.User">
update user set name=#{name},gender=#{gender},birthday=#{birthday},address=#{address} where id=#{id}
</update>
</mapper>
mybatis/mapper/UserMapper.xml
使用注解配置 mapper 类的包扫描：
package com.springboot.data_mybatis;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.springboot.data_mybatis.mapper")
public class DataMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(DataMybatisApplication.class, args);
}
}
com.springboot.data_mybatis.DataMybatisApplication
测试：
package com.springboot.data_mybatis;
import com.springboot.data_mybatis.bean.User;
import com.springboot.data_mybatis.mapper.UserMappper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.sql.DataSource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
@SuppressWarnings("all")
public class DataMybatisApplicationTests {
@Autowired
private DataSource dataSource;
@Test
public void test() {
System.out.println(dataSource.getClass());
}
@Autowired
private UserMappper userMappper;
@Test
public void testGetAll(){
List<User> all = userMappper.getAll();
System.out.println(all);
/*
[User{id=1, name='张三'}, User{id=2, name='李四'}, User{id=3, name='王五'}]
*/
}
@Test
public void testGetById(){
User user = userMappper.getById(1);
System.out.println(user);
/*
User{id=1, name='张三'}
*/
}
@Test
public void testAdd() throws ParseException {
User user = new User();
user.setName("赵六");
user.setGender(1);
user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1998-2-2"));
user.setAddress("南京");
Integer count = userMappper.add(user);
System.out.println(count);
/*
1
*/
}
@Test
public void testUpdate(){
User user = userMappper.getById(5);
user.setGender(0);
Integer count = userMappper.update(user);
System.out.println(count);
/*
1
*/
}
@Test
public void testDelete(){
Integer count = userMappper.deleteById(5);
System.out.println(count);
/*
1
*/
}
}
test
整合JPA
1、使用 maven 构建 SpringBoot 项目，引入以下依赖：
2、配置数据源及 JPA：
spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.202.135:3306/springboot_jpa
driver-class-name: com.mysql.jdbc.Driver
jpa:
# JPA 默认使用 Hibernate 作为实现，所以可以使用 Hibernate 配置
hibernate:
# 更新或创建数据表
ddl-auto: update
# 输出执行的 sql
show-sql: true
application.yml
3、编写一个用于和数据库表映射的 JavaBean 即实体类，并配置好映射关系：
package com.springboot.data_jpa.bean;
import javax.persistence.*;
import java.util.Date;
// 使用 JPA 注解配置映射关系
@Entity // 告诉 JPA 这是一个实体类
@Table(name = "user") // 指定与哪张数据表对应，如果省略默认表名为类名首字母小写
public class User {
@Id // 标识该字段为主键
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "name",length = 40) // 指定与表中的哪个字段对应
private String name;
@Column // 如果省略默认列名为属性名
private Integer gender;
@Column
private Date birthday;
@Column
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
com.springboot.data_jpa.bean.User
4、编写 Repository 接口来操作实体类对应的数据表：
package com.springboot.data_jpa.repository;
import com.springboot.data_jpa.bean.User;
import org.springframework.data.jpa.repository.JpaRepository;
/**
* 第一个类型参数为要操作的实体类型
* 第二个类型参数为实体对应的主键类型
*/
public interface UserRepository extends JpaRepository<User,Integer> {
}
com.springboot.data_jpa.repository.UserRepository
5、测试：
package com.springboot.data_jpa;
import com.springboot.data_jpa.bean.User;
import com.springboot.data_jpa.repository.UserRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
@RunWith(SpringRunner.class)
@SpringBootTest
public class DataJpaApplicationTests {
@Autowired
private UserRepository userRepository;
@Test
public void test() {
System.out.println(userRepository);
/*
org.springframework.data.jpa.repository.support.SimpleJpaRepository@b022551
*/
}
@Test
public void testAdd() throws ParseException {
User user = new User();
user.setName("赵六");
user.setGender(1);
user.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse("1998-2-2"));
user.setAddress("南京");
User u = userRepository.save(user);
System.out.println(u);
/*
Hibernate: insert into user (address, birthday, gender, name) values (?, ?, ?, ?)
User{id=4, name='赵六'}
*/
}
@Test
public void testGetAll(){
List<User> all = userRepository.findAll();
System.out.println(all);
/*
Hibernate: select user0_.id as id1_0_, user0_.address as address2_0_, user0_.birthday as birthday3_0_, user0_.gender as gender4_0_, user0_.name as name5_0_ from user user0_
[User{id=1, name='张三'}, User{id=2, name='李四'}, User{id=3, name='王五'}, User{id=4, name='赵六'}]
*/
}
@Test
public void testGetById(){
User user = userRepository.findOne(1);
System.out.println(user);
/*
Hibernate: select user0_.id as id1_0_0_, user0_.address as address2_0_0_, user0_.birthday as birthday3_0_0_, user0_.gender as gender4_0_0_, user0_.name as name5_0_0_ from user user0_ where user0_.id=?
User{id=1, name='张三'}
*/
}
@Test
public void testUpdate(){
User user = userRepository.findOne(4);
user.setGender(1);
// 有主键时修改 否则保存
userRepository.save(user);
/*
Hibernate: select user0_.id as id1_0_0_, user0_.address as address2_0_0_, user0_.birthday as birthday3_0_0_, user0_.gender as gender4_0_0_, user0_.name as name5_0_0_ from user user0_ where user0_.id=?
Hibernate: update user set address=?, birthday=?, gender=?, name=? where id=?
*/
}
@Test
public void testDelete(){
userRepository.delete(4);
/*
Hibernate: select user0_.id as id1_0_0_, user0_.address as address2_0_0_, user0_.birthday as birthday3_0_0_, user0_.gender as gender4_0_0_, user0_.name as name5_0_0_ from user user0_ where user0_.id=?
Hibernate: delete from user where id=?
*/
}
}
test