目录

Life in Flow

知不知，尚矣；不知知，病矣。
不知不知，殆矣。

X

数据源

   
    0 0 410686931

SpringBoot 配置单数据源

引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>geektime.spring.data</groupId>
	<artifactId>datasource-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>datasource-demo</name>
	<description></description>

	<properties>
		<java.version>11</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

配置文件

application.properties

# 暴露Actuator
management.endpoints.web.exposure.include=*

# 如果你的终端支持ANSI，设置彩色输出会让日志更具可读性
# DETECT：会检查终端是否支持ANSI，是的话就采用彩色输出（推荐项）
# ALWAYS：总是使用ANSI-colored格式输出，若终端不支持的时候，会有很多干扰信息，不推荐
spring.output.ansi.enabled=ALWAYS

# 通用配置
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.username=sa
spring.datasource.password=
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver #(可选)，因为springboot会根据spring.datasource.url 自动判断，选择合适的驱动。

# 连接池配置
spring.datasource.hikari.maximumPoolSize=5
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.maxLifetime=1800000

schema.sql
 用来初始化数据库的 schema

CREATE TABLE FOO (ID INT IDENTITY, BAR VARCHAR(64));

data.sql
 用来初始化数据库中的数据

INSERT INTO FOO (ID, BAR) VALUES (1, 'aaa');
INSERT INTO FOO (ID, BAR) VALUES (2, 'bbb');

启动类

 实际应用中，我们会有在项目服务启动的时候就去加载一些数据或做一些事情这样的需求。为了解决这样的问题，Spring Boot 为我们提供了一个方法，通过实现接口 CommandLineRunner 来实现。

DataSourceDemoApplication

package geektime.spring.data.datasourcedemo;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootApplication
@Slf4j
@Order(value=2)  //如果有多个类都实现CommandLineRunner接口，则值越大的类的run方法越会优先运行
public class DataSourceDemoApplication implements CommandLineRunner {
	@Autowired
	private DataSource dataSource;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public static void main(String[] args) {
		SpringApplication.run(DataSourceDemoApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		showConnection();
		showData();
	}

	private void showConnection() throws SQLException {
		log.info(dataSource.toString());
		Connection conn = dataSource.getConnection();
		log.info(conn.toString());
		conn.close();
	}

	private void showData() {
		jdbcTemplate.queryForList("SELECT * FROM FOO")
				.forEach(row -> log.info(row.toString()));
	}
}

控制台输出

2020-01-06 13:50:36.250  INFO 34504 --- [           main] g.s.d.d.DataSourceDemoApplication        : HikariDataSource (HikariPool-1)
2020-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
2020-01-06 13:50:36.258  INFO 34504 --- [           main] g.s.d.d.DataSourceDemoApplication        : {ID=1, BAR=aaa}
2020-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

依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>geektime.spring.data</groupId>
    <artifactId>pure-spring-datasource-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <spring.version>5.1.3.RELEASE</spring.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>RELEASE</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
</project>

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <context:component-scan base-package="geektime.spring.data" />
    <!--
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <property name="driverClassName" value="org.h2.Driver"/>
        <property name="url" value="jdbc:h2:mem:testdb"/>
        <property name="username" value="SA"/>
        <property name="password" value=""/>
    </bean>
    -->
</beans>

启动类 + 配置类 二合一

package geektime.spring.data.datasourcedemo;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Properties;

@Configuration
@EnableTransactionManagement
public class DataSourceDemo {
    @Autowired
    private DataSource dataSource;

    public static void main(String[] args) throws SQLException {
        ApplicationContext applicationContext =
                new ClassPathXmlApplicationContext("applicationContext*.xml");
        //输出应用上下文中所有Bean
        showBeans(applicationContext);
        //查看数据源和连接信息
        dataSourceDemo(applicationContext);
    }

    /**
     * 配置数据源信息，生成数据源
     */
    @Bean(destroyMethod = "close")
    public DataSource dataSource() throws Exception {
        Properties properties = new Properties();
        properties.setProperty("driverClassName", "org.h2.Driver");
        properties.setProperty("url", "jdbc:h2:mem:testdb");
        properties.setProperty("username", "sa");
        return BasicDataSourceFactory.createDataSource(properties);
    }

    /**
     * 构建 PlatformTransactionManager
     */
    @Bean
    public PlatformTransactionManager transactionManager() throws Exception {
        return new DataSourceTransactionManager(dataSource());
    }

    /**
     *
     * @param applicationContext
     */
    private static void showBeans(ApplicationContext applicationContext) {
        System.out.println(Arrays.toString(applicationContext.getBeanDefinitionNames()));
    }

    /**
     * 获取DataSourceDemo类的实例dataSourceDemo
     * @param applicationContext
     * @throws SQLException
     */
    private static void dataSourceDemo(ApplicationContext applicationContext) throws SQLException {
        DataSourceDemo demo = applicationContext.getBean("dataSourceDemo", DataSourceDemo.class);
        demo.showDataSource();
    }

    /**
     * 打印数据源信息
     * @throws SQLException
     */
    public void showDataSource() throws SQLException {
        System.out.println(dataSource.toString()); //输出数据源
        Connection conn = dataSource.getConnection(); //数据连接信息
        System.out.println(conn.toString());
        conn.close();
    }
}

控制台输出

[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]

org.apache.commons.dbcp2.BasicDataSource@4f6ee6e4

2067180044, 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：在代码中自动配置和控制。

依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>geektime.spring.data</groupId>
	<artifactId>multi-datasource-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>multi-datasource-demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

application.properties 双数据源

management.endpoints.web.exposure.include=*
spring.output.ansi.enabled=ALWAYS

foo.datasource.url=jdbc:h2:mem:foo
foo.datasource.username=sa
foo.datasource.password=

bar.datasource.url=jdbc:h2:mem:bar
bar.datasource.username=sa
bar.datasource.password=

配置类 + 启动类 二合一

package geektime.spring.data.multidatasourcedemo;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;
//排除SpringBoot的自动配置类：改为手动配置：DataSource、DataSourceTransactionManager、JdbcTemplate
@SpringBootApplication(exclude = { DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class,
        JdbcTemplateAutoConfiguration.class})
@Slf4j
public class MultiDataSourceDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MultiDataSourceDemoApplication.class, args);
    }

    /**
     * 生成 foo 的 DataSourceProperties
     * @return
     */
    @Bean
    @ConfigurationProperties("foo.datasource")
    public DataSourceProperties fooDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * 生成 foo 的 DataSource
     * @return
     */
    @Bean
    public DataSource fooDataSource() {
        DataSourceProperties dataSourceProperties = fooDataSourceProperties();
        log.info("foo datasource: {}", dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 生成 foo 的 DataSourceTransactionManager
     * @param fooDataSource
     * @return
     */
    @Bean
    @Resource
    public PlatformTransactionManager fooTxManager(DataSource fooDataSource) {
        return new DataSourceTransactionManager(fooDataSource);
    }

    /**
     * 生成 bar 的 DataSourceProperties
     * @return
     */
    @Bean
    @ConfigurationProperties("bar.datasource")
    public DataSourceProperties barDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     * 生成 bar 的 DataSource
     * @return
     */
    @Bean
    public DataSource barDataSource() {
        DataSourceProperties dataSourceProperties = barDataSourceProperties();
        log.info("bar datasource: {}", dataSourceProperties.getUrl());
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 生成 bar 的 DataSourceTransactionManager
     * @param barDataSource
     * @return
     */
    @Bean
    @Resource
    public PlatformTransactionManager barTxManager(DataSource barDataSource) {
        return new DataSourceTransactionManager(barDataSource);
    }
}

通过 Actuator 查看
 barTxManager 中的数据源是 barDataSource，barDataSource 种的 DataSourceProperties 是 barDataSourceProperties。
 foo 也同理，多个数据源数据源，手工配置，一一对应。

"barTxManager": {
          "aliases": [
            
          ],
          "scope": "singleton",
          "type": "org.springframework.jdbc.datasource.DataSourceTransactionManager",
          "resource": "geektime.spring.data.multidatasourcedemo.MultiDataSourceDemoApplication",
          "dependencies": [
            "barDataSource"
          ]
        }

        "barDataSource": {
          "aliases": [
            
          ],
          "scope": "singleton",
          "type": "com.zaxxer.hikari.HikariDataSource",
          "resource": "geektime.spring.data.multidatasourcedemo.MultiDataSourceDemoApplication",
          "dependencies": [
            "barDataSourceProperties"
          ]
        },
作者：Soulboy