Spring Boot 2.0.X 多数据源及连接池配置

最新推荐文章于 2024-08-15 21:36:29 发布

weixin_34161083

最新推荐文章于 2024-08-15 21:36:29 发布

阅读量3.7k

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/pipimao/blog/3001295

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、单数据源

我们在使用Spring Boot配置单数据源的时候很简单，只要在配置文件中加上基本的数据库连接信息和连接池配置信息就可以，通常情况下不需要指定driver-class-name，Spring Boot可以从大多数数据库的url中推断出驱动类型

# 连接配置
spring.datasource.url=jdbc:mysql://localhost:part/test?characterEncoding=utf-8
spring.datasource.username=****
spring.datasource.password=****
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# 连接池配置
spring.datasource.hikari.pool-name=HikariPool
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

# jpa相关配置
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql = true
spring.jpa.properties.hibernate.max_fetch_depth = 1
spring.jpa.hibernate.ddl-auto = update
# SpringBoot2.0以后需要指定方言才能使用InnoDB引擎
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.open-in-view=false

Spring Boot 2.0以后默认使用 HikariCP 连接池，2.0以前默认使用的是 Tomcat 连接池，如果想在2.0以前使用 HikariCP 连接池，可以在maven中手动引入HikariCP 连接池依赖

<dependency>
       <groupId>com.zaxxer</groupId>
       <artifactId>HikariCP</artifactId>
       <version>2.7.8</version>
</dependency>

并在配置文件中添加如下配置即可

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

二、多数据源

1.配置多数据源连接配置，根据前缀来区分，并配置对应连接池信息

#主数据库
spring.datasource.primary.jdbc-url=jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf-8
spring.datasource.primary.username=root
spring.datasource.primary.password=mysql
spring.datasource.primary.driver-class-name = com.mysql.jdbc.Driver
#从数据库
spring.datasource.secondary.jdbc-url=jdbc:mysql://127.0.0.1:3306/test2?characterEncoding=utf-8
spring.datasource.secondary.username=root
spring.datasource.secondary.password=mysql
spring.datasource.secondary.driver-class-name = com.mysql.jdbc.Driver

##  Hikari 连接池配置 ------ 详细配置请访问：https://github.com/brettwooldridge/HikariCP
## 连接池名称
spring.datasource.primary.pool-name=HikariPool-1
## 最小空闲连接数量，默认是10
spring.datasource.primary.minimum-idle=5
## 连接池最大连接数，默认是10
spring.datasource.primary.maximum-pool-size=10
## 空闲连接存活最大时间，默认600000（10分钟）
spring.datasource.primary.idle-timeout=600000
## 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
spring.datasource.primary.max-lifetime=1800000
## 数据库连接超时时间,默认30秒，即30000
spring.datasource.primary.connection-timeout=30000

spring.datasource.secondary.pool-name=HikariPool-2
spring.datasource.secondary.minimum-idle=5
spring.datasource.secondary.maximum-pool-size=10
spring.datasource.secondary.idle-timeout=600000
spring.datasource.secondary.max-lifetime=1800000
spring.datasource.secondary.connection-timeout=30000

# jpa相关配置
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql = true
spring.jpa.properties.hibernate.max_fetch_depth = 1
spring.jpa.hibernate.ddl-auto = update
# SpringBoot2.0以后需要指定方言才能使用InnoDB引擎
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.open-in-view=false

2.手动配置创建多个数据库连接池，prefix配置属性的前缀要与配置文件中的一致，实例化Bean交给Spring容器管理

package com.***.***.config.datasource;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Slf4j
@Configuration
public class DataSourceConfig {

    @Bean(name = "primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }


    /**
     * 显示数据库连接池信息
     *
     * @param dataSource
     */
    public static void logDS(DataSource dataSource) {
        HikariDataSource hds = (HikariDataSource) dataSource;
        String info = "\n\n\tHikariCP连接池配置\n\t连接池名称：" +
                hds.getPoolName() +
                "\n\t最小空闲连接数：" +
                hds.getMinimumIdle() +
                "\n\t最大连接数：" +
                hds.getMaximumPoolSize() +
                "\n\t连接超时时间：" +
                hds.getConnectionTimeout() +
                "ms\n\t空闲连接超时时间：" +
                hds.getIdleTimeout() +
                "ms\n\t连接最长生命周期：" +
                hds.getMaxLifetime() +
                "ms\n";
        log.info(info);
    }
}

3.对每个连接池指定相对应的实体类和repository包路径，添加实体管理、jpa属性管理、事务管理配置。jpa属性可以在配置文件中统一配置，或者通过如以下 getVendorProperties 方法分开配置，尤其在连接不同数据库时。

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Slf4j
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.***.***.repository.applet"}) //设置Repository所在位置
public class PrimaryConfig {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        DataSourceConfig.logDS(primaryDataSource);
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties())
                .packages("com.***.***.model.applet") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    /**
     * jpa properties
     *
     * @return
     */
    private Map<String, Object> getVendorProperties() {
        Map<String, Object> ret = jpaProperties.getHibernateProperties(new HibernateSettings());
        ret.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        return ret;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Slf4j
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.***.***.repository.master"}) //设置Repository所在位置
public class SecondaryConfig {

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        DataSourceConfig.logDS(secondaryDataSource);
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties())
                .packages("com.***.***.model.master") //设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    /**
     * jpa properties
     *
     * @return
     */
    private Map<String, Object> getVendorProperties() {
        Map<String, Object> ret = jpaProperties.getHibernateProperties(new HibernateSettings());
        ret.put("hibernate.dialect", "org.hibernate.dialect.MySQL5InnoDBDialect");
        return ret;
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

至此Spring Boot 2.0配置多数据源就完成了，此方法也适用于其他数据库配置，并且可以继续添加数据源，只需修改或添加application.properties中的数据源配置即可。