数据源
SpringBoot 配置单数据源
引入依赖
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 http://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.1.1.RELEASE</version>
9 <relativePath/> <!-- lookup parent from repository -->
10 </parent>
11 <groupId>geektime.spring.data</groupId>
12 <artifactId>datasource-demo</artifactId>
13 <version>0.0.1-SNAPSHOT</version>
14 <name>datasource-demo</name>
15 <description></description>
16
17 <properties>
18 <java.version>11</java.version>
19 </properties>
20
21 <dependencies>
22 <dependency>
23 <groupId>org.springframework.boot</groupId>
24 <artifactId>spring-boot-starter-actuator</artifactId>
25 </dependency>
26 <dependency>
27 <groupId>org.springframework.boot</groupId>
28 <artifactId>spring-boot-starter-web</artifactId>
29 </dependency>
30 <dependency>
31 <groupId>org.springframework.boot</groupId>
32 <artifactId>spring-boot-starter-jdbc</artifactId>
33 </dependency>
34 <dependency>
35 <groupId>org.projectlombok</groupId>
36 <artifactId>lombok</artifactId>
37 </dependency>
38 <dependency>
39 <groupId>com.h2database</groupId>
40 <artifactId>h2</artifactId>
41 <scope>runtime</scope>
42 </dependency>
43 <dependency>
44 <groupId>org.springframework.boot</groupId>
45 <artifactId>spring-boot-starter-test</artifactId>
46 <scope>test</scope>
47 </dependency>
48 </dependencies>
49
50 <build>
51 <plugins>
52 <plugin>
53 <groupId>org.springframework.boot</groupId>
54 <artifactId>spring-boot-maven-plugin</artifactId>
55 </plugin>
56 </plugins>
57 </build>
58</project>
配置文件
application.properties
1# 暴露Actuator
2management.endpoints.web.exposure.include=*
3
4# 如果你的终端支持ANSI，设置彩色输出会让日志更具可读性
5# DETECT：会检查终端是否支持ANSI，是的话就采用彩色输出（推荐项）
6# ALWAYS：总是使用ANSI-colored格式输出，若终端不支持的时候，会有很多干扰信息，不推荐
7spring.output.ansi.enabled=ALWAYS
8
9# 通用配置
10spring.datasource.url=jdbc:h2:mem:testdb
11spring.datasource.username=sa
12spring.datasource.password=
13#spring.datasource.driver-class-name=com.mysql.jdbc.Driver #(可选)，因为springboot会根据spring.datasource.url 自动判断，选择合适的驱动。
14
15# 连接池配置
16spring.datasource.hikari.maximumPoolSize=5
17spring.datasource.hikari.minimumIdle=5
18spring.datasource.hikari.idleTimeout=600000
19spring.datasource.hikari.connectionTimeout=30000
20spring.datasource.hikari.maxLifetime=1800000
schema.sql
用来初始化数据库的 schema
1CREATE TABLE FOO (ID INT IDENTITY, BAR VARCHAR(64));
data.sql
用来初始化数据库中的数据
1INSERT INTO FOO (ID, BAR) VALUES (1, 'aaa');
2INSERT INTO FOO (ID, BAR) VALUES (2, 'bbb');
启动类
实际应用中，我们会有在项目服务启动的时候就去加载一些数据或做一些事情这样的需求。为了解决这样的问题，Spring Boot 为我们提供了一个方法，通过实现接口 CommandLineRunner 来实现。
DataSourceDemoApplication
1package geektime.spring.data.datasourcedemo;
2
3import lombok.extern.slf4j.Slf4j;
4import org.springframework.beans.factory.annotation.Autowired;
5import org.springframework.boot.CommandLineRunner;
6import org.springframework.boot.SpringApplication;
7import org.springframework.boot.autoconfigure.SpringBootApplication;
8import org.springframework.core.annotation.Order;
9import org.springframework.jdbc.core.JdbcTemplate;
10
11import javax.sql.DataSource;
12import java.sql.Connection;
13import java.sql.SQLException;
14
15@SpringBootApplication
16@Slf4j
17@Order(value=2) //如果有多个类都实现CommandLineRunner接口，则值越大的类的run方法越会优先运行
18public class DataSourceDemoApplication implements CommandLineRunner {
19 @Autowired
20 private DataSource dataSource;
21
22 @Autowired
23 private JdbcTemplate jdbcTemplate;
24
25 public static void main(String[] args) {
26 SpringApplication.run(DataSourceDemoApplication.class, args);
27 }
28
29 @Override
30 public void run(String... args) throws Exception {
31 showConnection();
32 showData();
33 }
34
35 private void showConnection() throws SQLException {
36 log.info(dataSource.toString());
37 Connection conn = dataSource.getConnection();
38 log.info(conn.toString());
39 conn.close();
40 }
41
42 private void showData() {
43 jdbcTemplate.queryForList("SELECT * FROM FOO")
44 .forEach(row -> log.info(row.toString()));
45 }
46}
控制台输出
12020-01-06 13:50:36.250 INFO 34504 --- [ main] g.s.d.d.DataSourceDemoApplication : HikariDataSource (HikariPool-1)
22020-01-06 13:50:36.250 INFO 34504 --- [ main] g.s.d.d.DataSourceDemoApplication : HikariProxyConnection@1796415927 wrapping conn0: url=jdbc:h2:mem:testdb user=SA
32020-01-06 13:50:36.258 INFO 34504 --- [ main] g.s.d.d.DataSourceDemoApplication : {ID=1, BAR=aaa}
42020-01-06 13:50:36.258 INFO 34504 --- [ main] g.s.d.d.DataSourceDemoApplication : {ID=2, BAR=bbb}
查看应用上下文
http://localhost:8080/actuator/beans
Pure Spring Datasource Demo
依赖
1<?xml version="1.0" encoding="UTF-8"?>
2<project xmlns="http://maven.apache.org/POM/4.0.0"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5 <modelVersion>4.0.0</modelVersion>
6
7 <groupId>geektime.spring.data</groupId>
8 <artifactId>pure-spring-datasource-demo</artifactId>
9 <version>1.0-SNAPSHOT</version>
10
11 <properties>
12 <spring.version>5.1.3.RELEASE</spring.version>
13 </properties>
14
15 <dependencies>
16 <dependency>
17 <groupId>org.springframework</groupId>
18 <artifactId>spring-context</artifactId>
19 <version>${spring.version}</version>
20 </dependency>
21 <dependency>
22 <groupId>org.springframework</groupId>
23 <artifactId>spring-jdbc</artifactId>
24 <version>${spring.version}</version>
25 </dependency>
26
27 <dependency>
28 <groupId>org.apache.commons</groupId>
29 <artifactId>commons-dbcp2</artifactId>
30 <version>RELEASE</version>
31 </dependency>
32 <dependency>
33 <groupId>com.h2database</groupId>
34 <artifactId>h2</artifactId>
35 <version>RELEASE</version>
36 <scope>runtime</scope>
37 </dependency>
38 </dependencies>
39</project>
applicationContext.xml
1<?xml version="1.0" encoding="UTF-8"?>
2<beans xmlns="http://www.springframework.org/schema/beans"
3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4 xmlns:context="http://www.springframework.org/schema/context"
5 xsi:schemaLocation="http://www.springframework.org/schema/beans
6 http://www.springframework.org/schema/beans/spring-beans.xsd
7 http://www.springframework.org/schema/context
8 http://www.springframework.org/schema/context/spring-context.xsd">
9
10 <context:component-scan base-package="geektime.spring.data" />
11 <!--
12 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
13 destroy-method="close">
14 <property name="driverClassName" value="org.h2.Driver"/>
15 <property name="url" value="jdbc:h2:mem:testdb"/>
16 <property name="username" value="SA"/>
17 <property name="password" value=""/>
18 </bean>
19 -->
20</beans>
启动类 + 配置类 二合一
1package geektime.spring.data.datasourcedemo;
2
3import org.apache.commons.dbcp2.BasicDataSourceFactory;
4import org.springframework.beans.factory.annotation.Autowired;
5import org.springframework.context.ApplicationContext;
6import org.springframework.context.annotation.Bean;
7import org.springframework.context.annotation.Configuration;
8import org.springframework.context.support.ClassPathXmlApplicationContext;
9import org.springframework.jdbc.datasource.DataSourceTransactionManager;
10import org.springframework.transaction.PlatformTransactionManager;
11import org.springframework.transaction.annotation.EnableTransactionManagement;
12
13import javax.sql.DataSource;
14import java.sql.Connection;
15import java.sql.SQLException;
16import java.util.Arrays;
17import java.util.Properties;
18
19@Configuration
20@EnableTransactionManagement
21public class DataSourceDemo {
22 @Autowired
23 private DataSource dataSource;
24
25 public static void main(String[] args) throws SQLException {
26 ApplicationContext applicationContext =
27 new ClassPathXmlApplicationContext("applicationContext*.xml");
28 //输出应用上下文中所有Bean
29 showBeans(applicationContext);
30 //查看数据源和连接信息
31 dataSourceDemo(applicationContext);
32 }
33
34 /**
35 * 配置数据源信息，生成数据源
36 */
37 @Bean(destroyMethod = "close")
38 public DataSource dataSource() throws Exception {
39 Properties properties = new Properties();
40 properties.setProperty("driverClassName", "org.h2.Driver");
41 properties.setProperty("url", "jdbc:h2:mem:testdb");
42 properties.setProperty("username", "sa");
43 return BasicDataSourceFactory.createDataSource(properties);
44 }
45
46 /**
47 * 构建 PlatformTransactionManager
48 */
49 @Bean
50 public PlatformTransactionManager transactionManager() throws Exception {
51 return new DataSourceTransactionManager(dataSource());
52 }
53
54 /**
55 *
56 * @param applicationContext
57 */
58 private static void showBeans(ApplicationContext applicationContext) {
59 System.out.println(Arrays.toString(applicationContext.getBeanDefinitionNames()));
60 }
61
62 /**
63 * 获取DataSourceDemo类的实例dataSourceDemo
64 * @param applicationContext
65 * @throws SQLException
66 */
67 private static void dataSourceDemo(ApplicationContext applicationContext) throws SQLException {
68 DataSourceDemo demo = applicationContext.getBean("dataSourceDemo", DataSourceDemo.class);
69 demo.showDataSource();
70 }
71
72 /**
73 * 打印数据源信息
74 * @throws SQLException
75 */
76 public void showDataSource() throws SQLException {
77 System.out.println(dataSource.toString()); //输出数据源
78 Connection conn = dataSource.getConnection(); //数据连接信息
79 System.out.println(conn.toString());
80 conn.close();
81 }
82}
控制台输出
1[dataSourceDemo, org.springframework.context.annotation.internalConfigurationAnnotationProcessor, org.springframework.context.annotation.internalAutowiredAnnotationProcessor, org.springframework.context.annotation.internalCommonAnnotationProcessor, org.springframework.context.event.internalEventListenerProcessor, org.springframework.context.event.internalEventListenerFactory, org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration, org.springframework.transaction.config.internalTransactionAdvisor, transactionAttributeSource, transactionInterceptor, org.springframework.transaction.config.internalTransactionalEventListenerFactory, dataSource, transactionManager, org.springframework.aop.config.internalAutoProxyCreator]
2
3org.apache.commons.dbcp2.BasicDataSource@4f6ee6e4
4
52067180044, URL=jdbc:h2:mem:testdb, UserName=SA, H2 JDBC Driver
SpringBoot 做了哪些配置
所有的自动配置都是按需配置，如果已经手动明确配置了数据源，则 SpringBoot 则会使用已存在的数据源。
- 自动配置 DataSource：DataSourceAutoConfiguration
- 自动配置 DataSourceTransactionManager：DataSourceTransactionManagerAutoConfiguration
- 自动配置 JdbcTemplate：JdbcTemplateAutoConfiguration
配置多数据源
- 不同数据源的配置要分开
- 关注每次使用的数据源：有多个 DataSource 时系统如何判断、对应设施（事务、ORM 等）如何选择 DataSource。
配置方式
- 手工配置两组 DataSource 及其相关内容
- 与 SpringBoot 协同工作（二选一）
与 SpringBoot 协同工作
- 配置 @Primary 类型的 Bean：SpringBoot 会把此 Bean 作为主要 Bean。
- 排除 SpringBoot 的自动配置类 DataSourceAutoConfiguration、DataSourceTransactionManagerAutoConfiguration、JdbcTemplateAutoConfiguration：在代码中自动配置和控制。
依赖
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 http://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.1.1.RELEASE</version>
9 <relativePath/> <!-- lookup parent from repository -->
10 </parent>
11 <groupId>geektime.spring.data</groupId>
12 <artifactId>multi-datasource-demo</artifactId>
13 <version>0.0.1-SNAPSHOT</version>
14 <name>multi-datasource-demo</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 <dependency>
23 <groupId>org.springframework.boot</groupId>
24 <artifactId>spring-boot-starter-actuator</artifactId>
25 </dependency>
26 <dependency>
27 <groupId>org.springframework.boot</groupId>
28 <artifactId>spring-boot-starter-jdbc</artifactId>
29 </dependency>
30 <dependency>
31 <groupId>org.springframework.boot</groupId>
32 <artifactId>spring-boot-starter-web</artifactId>
33 </dependency>
34
35 <dependency>
36 <groupId>com.h2database</groupId>
37 <artifactId>h2</artifactId>
38 <scope>runtime</scope>
39 </dependency>
40 <dependency>
41 <groupId>org.projectlombok</groupId>
42 <artifactId>lombok</artifactId>
43 <optional>true</optional>
44 </dependency>
45 <dependency>
46 <groupId>org.springframework.boot</groupId>
47 <artifactId>spring-boot-starter-test</artifactId>
48 <scope>test</scope>
49 </dependency>
50 </dependencies>
51
52 <build>
53 <plugins>
54 <plugin>
55 <groupId>org.springframework.boot</groupId>
56 <artifactId>spring-boot-maven-plugin</artifactId>
57 </plugin>
58 </plugins>
59 </build>
60</project>
application.properties 双数据源
1management.endpoints.web.exposure.include=*
2spring.output.ansi.enabled=ALWAYS
3
4foo.datasource.url=jdbc:h2:mem:foo
5foo.datasource.username=sa
6foo.datasource.password=
7
8bar.datasource.url=jdbc:h2:mem:bar
9bar.datasource.username=sa
10bar.datasource.password=
配置类 + 启动类 二合一
1package geektime.spring.data.multidatasourcedemo;
2
3import lombok.extern.slf4j.Slf4j;
4import org.springframework.boot.SpringApplication;
5import org.springframework.boot.autoconfigure.SpringBootApplication;
6import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
7import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
8import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
9import org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration;
10import org.springframework.boot.context.properties.ConfigurationProperties;
11import org.springframework.context.annotation.Bean;
12import org.springframework.jdbc.datasource.DataSourceTransactionManager;
13import org.springframework.transaction.PlatformTransactionManager;
14
15import javax.annotation.Resource;
16import javax.sql.DataSource;
17//排除SpringBoot的自动配置类：改为手动配置：DataSource、DataSourceTransactionManager、JdbcTemplate
18@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class,
19 DataSourceTransactionManagerAutoConfiguration.class,
20 JdbcTemplateAutoConfiguration.class})
21@Slf4j
22public class MultiDataSourceDemoApplication {
23
24 public static void main(String[] args) {
25 SpringApplication.run(MultiDataSourceDemoApplication.class, args);
26 }
27
28 /**
29 * 生成 foo 的 DataSourceProperties
30 * @return
31 */
32 @Bean
33 @ConfigurationProperties("foo.datasource")
34 public DataSourceProperties fooDataSourceProperties() {
35 return new DataSourceProperties();
36 }
37
38 /**
39 * 生成 foo 的 DataSource
40 * @return
41 */
42 @Bean
43 public DataSource fooDataSource() {
44 DataSourceProperties dataSourceProperties = fooDataSourceProperties();
45 log.info("foo datasource: {}", dataSourceProperties.getUrl());
46 return dataSourceProperties.initializeDataSourceBuilder().build();
47 }
48
49 /**
50 * 生成 foo 的 DataSourceTransactionManager
51 * @param fooDataSource
52 * @return
53 */
54 @Bean
55 @Resource
56 public PlatformTransactionManager fooTxManager(DataSource fooDataSource) {
57 return new DataSourceTransactionManager(fooDataSource);
58 }
59
60 /**
61 * 生成 bar 的 DataSourceProperties
62 * @return
63 */
64 @Bean
65 @ConfigurationProperties("bar.datasource")
66 public DataSourceProperties barDataSourceProperties() {
67 return new DataSourceProperties();
68 }
69
70 /**
71 * 生成 bar 的 DataSource
72 * @return
73 */
74 @Bean
75 public DataSource barDataSource() {
76 DataSourceProperties dataSourceProperties = barDataSourceProperties();
77 log.info("bar datasource: {}", dataSourceProperties.getUrl());
78 return dataSourceProperties.initializeDataSourceBuilder().build();
79 }
80
81 /**
82 * 生成 bar 的 DataSourceTransactionManager
83 * @param barDataSource
84 * @return
85 */
86 @Bean
87 @Resource
88 public PlatformTransactionManager barTxManager(DataSource barDataSource) {
89 return new DataSourceTransactionManager(barDataSource);
90 }
91}
通过 Actuator 查看
barTxManager 中的数据源是 barDataSource，barDataSource 种的 DataSourceProperties 是 barDataSourceProperties。
foo 也同理，多个数据源数据源，手工配置，一一对应。
1"barTxManager": {
2 "aliases": [
3
4 ],
5 "scope": "singleton",
6 "type": "org.springframework.jdbc.datasource.DataSourceTransactionManager",
7 "resource": "geektime.spring.data.multidatasourcedemo.MultiDataSourceDemoApplication",
8 "dependencies": [
9 "barDataSource"
10 ]
11 }
12
13 "barDataSource": {
14 "aliases": [
15
16 ],
17 "scope": "singleton",
18 "type": "com.zaxxer.hikari.HikariDataSource",
19 "resource": "geektime.spring.data.multidatasourcedemo.MultiDataSourceDemoApplication",
20 "dependencies": [
21 "barDataSourceProperties"
22 ]
23 },