springboot 配置多数据源

本项目一共配置了两个数据源，一个连接本地数据库，一个通过ssh连接的远程数据库。

1.Dependencies

dependencies {
    implementation ('org.springframework.boot:spring-boot-starter-data-jpa')
    implementation ('org.springframework.boot:spring-boot-starter-web')
    implementation ('org.springframework.boot:spring-boot-starter-aop')
    implementation ('org.springframework.boot:spring-boot-starter-thymeleaf')
    implementation ('org.springframework.retry:spring-retry')
    implementation ('org.springframework.boot:spring-boot-starter-mail')
    implementation group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'
    implementation group: 'org.apache.commons', name: 'commons-email', version: '1.4'
    implementation group: 'net.sf.ehcache', name: 'ehcache-core', version: '2.6.11'
    implementation group: 'org.apache.httpcomponents.client5', name: 'httpclient5', version: '5.0'
    implementation group: 'org.apache.httpcomponents.core5', name: 'httpcore5', version: '5.0'
    //ssh连接
    implementation group: 'com.jcraft', name: 'jsch', version: '0.1.55'
    }

2.数据源配置

spring.datasource.second.url=jdbc:mysql://127.0.0.1:3306/order?useUnicode=yes&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&autoReconnect=true
spring.datasource.second.username=admin
spring.datasource.second.password=
spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.first.url=jdbc:mysql://127.0.0.1:3307/login?useUnicode=yes&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&autoReconnect=true
spring.datasource.first.username=admin
spring.datasource.first.password=
spring.datasource.first.driver-class-name=com.mysql.cj.jdbc.Driver

#这个配置是无效的（实体类字段和表列名要做直接映射，多数据源情况下这个配置是不会生效的）
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
server.port=8099

3.配置目录

4.DataSourceConfig

@Configuration
public class DataSourceConfig {


    @Bean("firstProperties")
    @ConfigurationProperties(prefix = "spring.datasource.first")
    public DataSourceProperties firstProperties() {
        return new DataSourceProperties();
    }

    @Bean("secondProperties")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.second")
    public DataSourceProperties secondProperties() {
        return new DataSourceProperties();
    }


    @Bean(name = "firstDataSource")
    public DataSource thirdedDataSource(
            @Qualifier(value = "firstProperties") DataSourceProperties dataSourceProperties) {
        DataSource dataSource = dataSourceProperties.initializeDataSourceBuilder().build();
        return dataSource;
    }

    @Bean(name = "secondDataSource")
    @Primary
    public DataSource secondDataSource(
            @Qualifier(value = "secondProperties") DataSourceProperties dataSourceProperties) {
        DataSource dataSource = dataSourceProperties.initializeDataSourceBuilder().build();
        return dataSource;
    }


}

5.FirstConfig

@Slf4j
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.vogins.authentication.freeLogin.repository",
        entityManagerFactoryRef = "firstEntityManagerFactory",
        transactionManagerRef = "firstTransactionManager")

public class FirstConfig {


    @Bean(name = "firstJpaProperties")
    public JpaProperties jpaProperties() {
        return new JpaProperties();
    }

    @Bean(name = "firstEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("firstDataSource") DataSource firstDataSource,
            @Qualifier("firstJpaProperties") JpaProperties jpaProperties,
            EntityManagerFactoryBuilder builder) {
        log.info("jpaProperties-1 " + jpaProperties.getProperties());

        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean =
                builder
                        // 设置数据源
                        .dataSource(firstDataSource)
                        // 设置jpa配置
                        .properties(jpaProperties.getProperties())
                        // 设置hibernate配置
                        .properties(
                                new HibernateProperties()
                                        .determineHibernateProperties(
                                                jpaProperties.getProperties(), new HibernateSettings()))
                        // 设置实体包名
                        .packages("com.vogins.authentication.freeLogin.entity")
                        // 设置持久化单元名，用于@PersistenceContext注解获取EntityManager时指定数据源
                        .persistenceUnit("firstPersistenceUnit")
                        .build();
        return localContainerEntityManagerFactoryBean;
    }

    @Bean(name = "firstEntityManager")
    public EntityManager entityManager(
            @Qualifier("firstEntityManagerFactory") EntityManagerFactory factory) {
        return factory.createEntityManager();
    }

    @Bean(name = "firstTransactionManager")
    public PlatformTransactionManager transactionManager(
            @Qualifier("firstEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }


}

6.SecondConfig

@Slf4j
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = "com.vogins.authentication.base.repo",
        entityManagerFactoryRef = "secondEntityManagerFactory",
        transactionManagerRef = "secondTransactionManager")

public class SecondConfig {


    @Bean(name = "secondJpaProperties")
    @Primary
    public JpaProperties jpaProperties() {
        return new JpaProperties();
    }

    @Bean(name = "secondEntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            @Qualifier("secondDataSource") DataSource secondDataSource,
            @Qualifier("secondJpaProperties") JpaProperties jpaProperties,
            EntityManagerFactoryBuilder builder) {
        log.info("jpaProperties-2 " + jpaProperties.getProperties());
        Collection<HibernatePropertiesCustomizer> customizers = new ArrayList<>();
        //数据源2要配置字段和表列明的直接映射，就要重写HibernatePropertiesCustomizer 的 customize方法
        customizers.add(new ReWriteSecond());
        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean =
                builder
                        // 设置数据源
                        .dataSource(secondDataSource)
                        // 设置jpa配置
                        .properties(jpaProperties.getProperties())
                        // 设置hibernate配置
                        .properties(
                                new HibernateProperties()
                                        .determineHibernateProperties(
                                                jpaProperties.getProperties(), new HibernateSettings().hibernatePropertiesCustomizers(customizers)))
                        // 设置实体包名
                        .packages("com.vogins.authentication.base.entity")
                        // 设置持久化单元名，用于@PersistenceContext注解获取EntityManager时指定数据源
                        .persistenceUnit("secondPersistenceUnit")
                        .build();
        return localContainerEntityManagerFactoryBean;
    }

    @Bean(name = "secondEntityManager")
    @Primary
    public EntityManager entityManager(
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory factory) {
        return factory.createEntityManager();
    }

    @Bean(name = "secondTransactionManager")
    @Primary
    public PlatformTransactionManager transactionManager(
            @Qualifier("secondEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }


}

7.ReWriteSecond

public class ReWriteSecond implements HibernatePropertiesCustomizer {


    public ReWriteSecond() {

    }

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put("hibernate.physical_naming_strategy",
                new PhysicalNamingStrategyStandardImpl());
    }
}

8.SSHConnection

public class SSHConnection {
   // 服务器登录名
    String user = "admin";
    // 登陆密码
    String password = "";
   //服务器公网IP
    String host = "172.59.59.104";
    // 跳板机ssh开放的接口   默认端口 22
    int port = 63022;
    // 这个是本地的端口，很重要！！！选取一个没有占用的port即可
    int local_port = 3307;
     // 要访问的mysql所在的host    服务器局域网IP（127.0.0.1也行）
    String remote_host = "127.0.0.1";
   // 服务器上数据库端口号
    int remote_port = 3306;
    
    Session session = null;

    /**
     * 建立SSH连接
     */

    public void SSHConnection() throws Exception {
        try {
            JSch jsch = new JSch();
            session = jsch.getSession(user, host, port);
            session.setPassword(password);
            session.setConfig("StrictHostKeyChecking", "no");
            // 日志打印自己脑补
            session.connect();
            session.setPortForwardingL(local_port, remote_host, remote_port);
        } catch (Exception e) {
            // do something
        }
    }

    /**
     * 断开SSH连接
     */
    public void closeSSH() throws Exception {
        this.session.disconnect();
    }


}

9.SSHContextListener

@Profile("prod")
@Component
@WebListener
public class SSHContextListener implements ServletContextListener {

    private SSHConnection conexionssh;

    public SSHContextListener() {
        super();
    }

    @Override
    public void contextInitialized(ServletContextEvent arg0) {
        // 建立连接
        System.out.println("Context initialized ... !\n\n\n");
        try {
            conexionssh = new SSHConnection();
            conexionssh.SSHConnection();
            System.out.println("\n\n\n成功建立SSH连接！\n\n\n");
        } catch (Throwable e) {
            System.out.println("\n\n\nSSH连接失败！\n\n\n");
            e.printStackTrace(); // error connecting SSH server
        }
    }

    @Override
    public void contextDestroyed(ServletContextEvent arg0) {
        // 断开连接
        System.out.println("Context destroyed ... !\n\n\n");
        try {
            conexionssh.closeSSH(); // disconnect
            System.out.println("\n\n\n成功断开SSH连接!\n\n\n");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("\n\n\n断开SSH连接出错！\n\n\n");
        }
    }



}