Springboot+druid+mybatis Configuring multiple data sources

When we develop a project, we may encounter the need to read and write multiple databases, and then we have to configure multiple data sources in the project. In the development of Java project, the most commonly used data operation framework is Mybatis, and the development framework is basically used springboot. And Druid claims to be the best database connection pool, is also widely used.

So this article will demonstrate how Springboot+druid+mybatis can configure a multi-data source. First create a Springboot project in idea:

Select a few basic packages:

Complete the creation:

The dependencies of the Pom.xml configuration are as follows:

<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artif actid>spring-boot-starter-web</artifactid> </dependency> <dependency> <groupid>org .mybatis.spring.boot</groupid> <artifactId>mybatis-spring-boot-starter</artifactId> <ve        rsion>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependenc y> <dependency> <groupId>org.springframework.boot</groupId> <artifactid>spring -boot-starter-test</artifactid> <scope>test</scope> </dependency> <!--Alibaba Dru ID database Connection pool-<dependency> <groupId>com.alibaba</groupId> &LT;ARTIFACTID&GT;DRUID-SPR Ing-boot-starter</artIfactid> <version>1.1.9</version> </dependency></dependencies> 

The

is then editing the Springboot configuration file, which I am using in yml format. It is important to note that in the case of multiple data sources, it is necessary to distinguish between the primary data source and the data source, otherwise an error will be given. The contents of the APPLICATION.YML configuration file are as follows:

Spring:datasource: #使用druid连接池 type:com.alibaba.druid.pool.druiddatasource# Custom Primary Data source configuration information Primary:datasource:      #druid相关配置 Druid: #监控统计拦截的filters filters:stat driverClassName:com.mysql.jdbc.Driver #配置基本属性 url:jdbc:mysql://127.0.0.1:3306/primary_database?useunicode=true&characterencoding=utf-8& Allowmultiqueries=true&autoreconnect=true&usessl=false username:root Password:password #配置初始化大小/most Small/maximum initialsize:1 minidle:1 maxactive:20 #获取连接等待超时时间 maxwait:60000 #间隔多久进行一次检测, detects the need to close the empty Idle connection timebetweenevictionrunsmillis:60000 #一个连接在池中最小生存的时间 minevictableidletimemillis:300000 validation Query:select ' x ' testwhileidle:true testonborrow:false testonreturn:false #打开PSCache, and specify PSCAC on each connection The size of He. Set the Oracle setting to True,mysql to false.  It is recommended to set the Poolpreparedstatements:false maxpoolpreparedstatementperconnectionsize:20# custom data source configuration information back:   DataSource #druid相关配置 Druid: #监控统计拦截的filters filters:stat driverClassName:com.mysql.jdbc.Driver #配置基本属性 url:jdbc:mysql://127.0.0.1:3306/back_database?useunicode=true&characterencoding=utf-8& Allowmultiqueries=true&autoreconnect=true&usessl=false username:root Password:password #配置初始化大小/most Small/maximum initialsize:1 minidle:1 maxactive:20 #获取连接等待超时时间 maxwait:60000 #间隔多久进行一次检测, detects the need to close the empty Idle connection timebetweenevictionrunsmillis:60000 #一个连接在池中最小生存的时间 minevictableidletimemillis:300000 validation Query:select ' x ' testwhileidle:true testonborrow:false testonreturn:false #打开PSCache, and specify PSCAC on each connection The size of He. Set the Oracle setting to True,mysql to false. The list of sub-Libraries is more recommended set to False Poolpreparedstatements:false maxpoolpreparedstatementperconnectionsize:20

The

Then creates a new config package in the project, creating a Primarydatabaseconfig class under the package as our main data source configuration class for loading custom master data source configuration information, and creating instances such as data sources and session connection factories:

Package Com.dabo.mini.game.zhaxinle.config;import Com.alibaba.druid.pool.druiddatasource;import Lombok. Data;import Org.apache.ibatis.session.sqlsessionfactory;import Org.mybatis.spring.sqlsessionfactorybean;import Org.mybatis.spring.annotation.mapperscan;import Org.springframework.beans.factory.annotation.qualifier;import Org.springframework.boot.context.properties.configurationproperties;import Org.springframework.context.annotation.bean;import Org.springframework.context.annotation.configuration;import Org.springframework.context.annotation.primary;import Org.springframework.core.io.support.pathmatchingresourcepatternresolver;import Org.springframework.jdbc.datasource.datasourcetransactionmanager;import Javax.sql.datasource;import java.sql.sqlexception;/** * @ProjectName Zhaxinle * @Author: Zerojun * @Date: 2018/8/16 16:49 * @Description: Primary Data Source Configuration class */@d ata@configuration//prefix primary.datasource.druid configuration information @configurationproperties (prefix = "Primary.datasource.druid") @MapperScan (BasepackagEs = primarydatabaseconfig.package, sqlsessionfactoryref = "Primarysqlsessionfactory") public class primarydatabaseconfig {/** * DAO layer Packet Path */static final String package = "Com.dabo.mini.game.zhaxinle.dao.pri    Mary ";    /** * MAPPER File relative path */private static final String mapper_location = "Classpath:mappers/primary/*.xml";    private String filters;    Private String URL;    Private String username;    private String password;    Private String Driverclassname;    private int initialsize;    private int minidle;    private int maxactive;    Private long maxwait;    Private long Timebetweenevictionrunsmillis;    Private long Minevictableidletimemillis;    Private String validationquery;    Private Boolean Testwhileidle;    Private Boolean Testonborrow;    Private Boolean Testonreturn;    Private Boolean poolpreparedstatements;    private int maxpoolpreparedstatementperconnectionsize; The primary data source uses @primary annotations to identify @Primary @Bean (name = "Primarydatasource") publIC DataSource Primarydatasource () throws SQLException {Druiddatasource druid = new Druiddatasource ();        Filters Druid.setfilters (filters) for monitoring statistics interception;        Configure basic Properties Druid.setdriverclassname (Driverclassname);        Druid.setusername (username);        Druid.setpassword (password);        Druid.seturl (URL);        Number of physical connections established at initialization druid.setinitialsize (initialsize);        Maximum number of connection pools druid.setmaxactive (maxactive);        Minimum number of connection pools druid.setminidle (minidle);        Gets the maximum wait time, in milliseconds, for a connection.        Druid.setmaxwait (maxwait);        How often the interval is detected and the idle connection Druid.settimebetweenevictionrunsmillis (Timebetweenevictionrunsmillis) that needs to be closed is detected;        A connection in the pool the minimum time to survive Druid.setminevictableidletimemillis (Minevictableidletimemillis);        SQL Druid.setvalidationquery (validationquery) to detect if the connection is valid;        It is recommended to configure true to not affect performance and ensure security.        Druid.settestwhileidle (Testwhileidle); Perform validationquery to detect if the connection is valid when requesting a connection Druid.settestonborrow (TestONborrow);        Druid.settestonreturn (Testonreturn); Whether to cache PreparedStatement, that is, pscache,oracle set to True,mysql set to false.        It is recommended that the sub-list is set to False druid.setpoolpreparedstatements (poolpreparedstatements). When Pscache is turned on, specify the size of Pscache on each connection druid.setmaxpoolpreparedstatementperconnectionsize (        Maxpoolpreparedstatementperconnectionsize);    Return druid; }//Create transaction Management @Primary for the data source @Bean (name = "Primarytransactionmanager") public Datasourcetransactionmanager Primar    Ytransactionmanager () throws SQLException {return new Datasourcetransactionmanager (Primarydatasource ()); }//Create a connection session factory instance for MyBatis @Primary @Bean (name = "Primarysqlsessionfactory") public sqlsessionfactory Primarysqls Essionfactory (@Qualifier ("Primarydatasource") DataSource Primarydatasource) throws Exception {final sqlsessionfact        Orybean sessionfactory = new Sqlsessionfactorybean ();  Sessionfactory.setdatasource (Primarydatasource); Set the data source Bean Sessionfactory.setmApperlocations (New Pathmatchingresourcepatternresolver (). Getresources (Primarydatabaseconfig.mapper_locatio  N));    Set Mapper file path return sessionfactory.getobject (); }}

Again, you need to create a configuration class from the data source, unlike the primary data source configuration class, where the configuration class from the data source cannot use @Primary annotations, which means that it is one from the data source. The code is as follows:

Package Com.dabo.mini.game.zhaxinle.config;import Com.alibaba.druid.pool.druiddatasource;import Lombok. Data;import Org.apache.ibatis.session.sqlsessionfactory;import Org.mybatis.spring.sqlsessionfactorybean;import Org.mybatis.spring.annotation.mapperscan;import Org.springframework.beans.factory.annotation.qualifier;import Org.springframework.boot.context.properties.configurationproperties;import Org.springframework.context.annotation.bean;import Org.springframework.context.annotation.configuration;import Org.springframework.core.io.support.pathmatchingresourcepatternresolver;import Org.springframework.jdbc.datasource.datasourcetransactionmanager;import Javax.sql.datasource;import java.sql.sqlexception;/** * @ProjectName Zhaxinle * @Author: Zerojun * @Date: 2018/8/16 16:49 * @Description: Background data Source Configuration class */@ Data@configuration@configurationproperties (prefix = "Back.datasource.druid") @MapperScan (basepackages = Backdatabaseconfig.package, sqlsessionfactoryref = "backsqlsessionfactory") public class backdatabaseconfig {/** * DAO layer Packet Path */static final String package = "Com.dabo.mini.game.zhaxinle.dao.back    ";    /** * MAPPER File relative path */private static final String mapper_location = "Classpath:mappers/back/*.xml";    private String filters;    Private String URL;    Private String username;    private String password;    Private String Driverclassname;    private int initialsize;    private int minidle;    private int maxactive;    Private long maxwait;    Private long Timebetweenevictionrunsmillis;    Private long Minevictableidletimemillis;    Private String validationquery;    Private Boolean Testwhileidle;    Private Boolean Testonborrow;    Private Boolean Testonreturn;    Private Boolean poolpreparedstatements;    private int maxpoolpreparedstatementperconnectionsize; @Bean (name = "Backdatasource") public DataSource Backdatasource () throws SQLException {Druiddatasource Druid =        New Druiddatasource (); Filters Druid.set for Monitoring statistics interceptionFilters (Filters);        Configure basic Properties Druid.setdriverclassname (Driverclassname);        Druid.setusername (username);        Druid.setpassword (password);        Druid.seturl (URL);        Number of physical connections established at initialization druid.setinitialsize (initialsize);        Maximum number of connection pools druid.setmaxactive (maxactive);        Minimum number of connection pools druid.setminidle (minidle);        Gets the maximum wait time, in milliseconds, for a connection.        Druid.setmaxwait (maxwait);        How often the interval is detected and the idle connection Druid.settimebetweenevictionrunsmillis (Timebetweenevictionrunsmillis) that needs to be closed is detected;        A connection in the pool the minimum time to survive Druid.setminevictableidletimemillis (Minevictableidletimemillis);        SQL Druid.setvalidationquery (validationquery) to detect if the connection is valid;        It is recommended to configure true to not affect performance and ensure security.        Druid.settestwhileidle (Testwhileidle);        When applying for connection, perform validationquery to detect if the connection is valid Druid.settestonborrow (Testonborrow);        Druid.settestonreturn (Testonreturn); Whether to cache PreparedStatement, that is, pscache,oracle set to True,mysql set to false. The list of sub-Libraries is more recommended set to False druid. setpoolpreparedstatements (poolpreparedstatements); When Pscache is turned on, specify the size of Pscache on each connection druid.setmaxpoolpreparedstatementperconnectionsize (        Maxpoolpreparedstatementperconnectionsize);    Return druid; } @Bean (name = "Backtransactionmanager") public Datasourcetransactionmanager Backtransactionmanager () throws Sqlexce    ption {return new Datasourcetransactionmanager (Backdatasource ()); } @Bean (name = "Backsqlsessionfactory") public sqlsessionfactory backsqlsessionfactory (@Qualifier ("Backdatasource") DataSource Backdatasource) throws Exception {final Sqlsessionfactorybean sessionfactory = new Sqlsessionfactorybea        N ();        Sessionfactory.setdatasource (Backdatasource); Sessionfactory.setmapperlocations (New Pathmatchingresourcepatternresolver (). Getresources (BackDataBaseConfi        g.mapper_location));    return Sessionfactory.getobject (); }}

After the above configuration, the project has the ability to connect two databases, if you want to configure more than two databases is the same, configure multiple from the data source. Business code level in addition to the different data sources related to the Mapper, DAO, Pojo sub-packet storage for easy scanning, the code is written in the same time as the previous single data source, so there is no business code posted here.

Springboot+druid+mybatis Configuring multiple data sources