Multi data source configuration of spring boot mybatis

Time:2021-2-25

The case of configuring multiple data sources in the spring boot project is often encountered in the development. In this paper, we use spring boot + mybatis to build a MySQL + PostgreSQL dual data source project. Please refer to:

https://gitee.com/senn-wen/my…

1、 Dependency configuration

staypom.xmlFilepostgresqland mysqlThe driver file for.

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

2、 Spring boot configuration

two point one application.yml to configure

Configure the database connection parameters that need to be connected. The following example configures PostgreSQL and mysql. Because I choose hikaricp as the connection pool, I configure Hikari below. For online services, it is recommended to set the parameters according to the actual server performance. Remember the configuration path of your connection parameters, which will be used later.

spring:
  datasource:

    postgresql:
      driver-class-name: org.postgresql.Driver
      url: jdbc:postgresql://url:port/database
      username: postGIS
      password: postGIS

    mysql:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://url:port/database
      username: root
      password: root
      
    hikari:
      maximum-pool-size: 3
      connection-timeout: 500

2.2 exclude spring datasource automation configuration dependency

Because spring boot will automatically assemble your configuration, in order for your custom configuration to take effect, you need to exclude the configuration related to datasoure from @ springbootapplication. There are three main problems

  • Data source: datasoo rceAutoConfiguration.class
  • Transaction: datasourcetransactionmana gerAutoConfiguration.class
  • JDBC template: jdbctempl ateAutoConfiguration.class
@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class,
        JdbcTemplateAutoConfiguration.class
})
public class PostgresqlApplication {

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

}

3、 Data source configuration

After completing the above basic configuration, you need to configure the specific bean. It is suggested that each data source should be configured independently for later maintenance. There are two main steps in data source configuration

  • Configuration of source data (read configuration file, generate new datasource)
  • Specify where the ORM framework uses the data source

These two are not given to you by the framework. You can only configure them manually.

3.1 MySQL data source configuration

package com.senn.postgresql.config;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author senn
 * @version 1.0.0
 * @ClassName MsqlDataSourceConfig.java
 *@ description MySQL database configuration
 *@ createtime 09:02:00, January 15, 2021
 */
@Configuration
@Slf4j
@MapperScan(basePackages = "com.senn.postgresql.common.mapper.mysql",
        sqlSessionFactoryRef = "mysqlDataSourceFactory")
public class MsqlDataSourceConfig {


    /**
     *@ description MySQL datasource properties configuration
     * @updateTime  2021/1/15 9:04
     */

    @Bean(name = "mysqlDataSourceProperties")
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     *@ description MySQL datasource source configuration
     * @updateTime  2021/1/15 9:11
     */
    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties("spring.datasource.mysql.configuration")
    public javax.sql.DataSource mysqlDataSource() {
        DataSourceProperties dataSourceProperties = mysqlDataSourceProperties();
        log.info(dataSourceProperties.getUrl());
        //The database connection pool is set to Hikari
        return mysqlDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }


    //The following is related to mybatis
    /**
     *@ description MySQL factory configuration
     * @updateTime  2021/1/15 9:18
     */
    @Bean("mysqlDataSourceFactory")
    @DependsOn("mysqlDataSource")
    public SqlSessionFactory dataSourceFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(mysqlDataSource());
        return factoryBean.getObject();
    }

    /**
     *@ description MySQL session template configuration
     * @updateTime  2021/1/15 9:22
     */

    @Bean("mysqlSqlSessionTemplate")
    @DependsOn("mysqlDataSourceFactory")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mysqlDataSourceFactory") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

    /**
     *@ description MySQL transaction configuration
     * @updateTime  2021/1/15 9:22
     */
    @Bean(name = "mysqlTransactionManager")
    @DependsOn("mysqlDataSource")
    public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

PostgreSQL data source configuration

package com.senn.postgresql.config;

import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author senn
 * @version 1.0.0
 * @ClassName DataSourceConfig.java
 *@ description psotgresql database configuration
 *@ createtime 14 / 01 / 2021 15:56:00
 */
@Configuration
@Slf4j
@MapperScan(basePackages = "com.senn.postgresql.common.mapper.postgresql",
        sqlSessionFactoryRef = "postgresqlDataSourceFactory")
public class PostgresqlDataSourceConfig {


    /**
     *@ description PostgreSQL datasource properties configuration
     * @updateTime  2021/1/15 9:04
    */

    @Bean(name = "postgresqlDataSourceProperties")
    @ConfigurationProperties("spring.datasource.postgresql")
    public DataSourceProperties postgresqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    /**
     *@ description PostgreSQL datasource source configuration
     * @param
     * @updateTime  2021/1/15 9:11
     * @throws
    */
    @Bean(name = "postgresqlDataSource")
    @ConfigurationProperties("spring.datasource.postgresql.configuration")
    public DataSource postgresqlDataSource() {
        DataSourceProperties dataSourceProperties = postgresqlDataSourceProperties();
        log.info(dataSourceProperties.getUrl());
        //The database connection pool is set to Hikari
        return postgresqlDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    //The following is related to mybatis

    /**
     *@ description PostgreSQL project configuration
     * @updateTime  2021/1/15 9:18
    */
    @Bean("postgresqlDataSourceFactory")
    @DependsOn("postgresqlDataSource")
    public SqlSessionFactory dataSourceFactory() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(postgresqlDataSource());
        return factoryBean.getObject();
    }

    /**
     *@ description PostgreSQL session template configuration
     * @updateTime  2021/1/15 9:22
    */

    @Bean("postgresqlSqlSessionTemplate")
    @Primary
    @DependsOn("postgresqlDataSourceFactory")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("postgresqlDataSourceFactory") SqlSessionFactory sessionFactory) {
        return new SqlSessionTemplate(sessionFactory);
    }

    /**
     *@ description PostgreSQL transaction configuration
     * @updateTime  2021/1/15 9:22
     */
    @Bean(name = "postgresqlTransactionManager")
    @DependsOn("postgresqlDataSource")
    public DataSourceTransactionManager fawkesTransactionManager(@Qualifier("postgresqlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

@Senn Sen