Spring Boot+mybatis create multi data source connection
1, Background: the company system needs to do heterogeneous database data migration, need to configure multiple data sources, there are also some small problems in the configuration process, so make a record here;
2, Code address:
3, Code explanation
1. Project structure:
2. Main configuration file: first configuration file application.yml
server:
port: 8086
servlet:
context-path: /
#http
http:
encoding:
charset: UTF-8
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
default-property-inclusion: non_null
spring:
profiles:
active: local
http:
encoding:
charset: UTF-8
datasource:
#druid
initialSize: 1 #Initialize size
minIdle: 1 #Minimum
maxActive: 300 #Maximum
maxWait: 60000 #Maximum waiting time in milliseconds
#timeBetweenEvictionRunsMillis: 300000L #How often is the interval detected? Detect the idle connections that need to be closed (unit: ms)
minEvictableIdleTimeMillis: 300000 #Minimum lifetime of a connection in the connection pool in milliseconds
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
#Open PSCache and specify the size of PSCache on each connection
poolPreparedStatements: true
maxOpenPreparedStatements: 20
#The filters intercepted by monitoring statistics cannot be counted by the sql of monitoring interface after removal. wall is used for firewall
filters: log4j
#Open the mergeSQL function through the connectionProperties property, slow SQL record
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#Merge monitoring data from multiple druiddatasources
useGlobalDataSourceStat: true
mybatis:
old:
mapper-locations: classpath:/mapper/old/*.xml
type-aliases-package: com.shallbuy.transfer.entity
configuration:
cache-enabled: true
lazy-loading-enabled: true
use-column-label: true
use-generated-keys: true
default-statement-timeout: 25000
log-prefix: mybatis
new:
mapper-locations: classpath:/mapper/newer/*.xml
type-aliases-package: com.shallbuy.transfer.entity
configuration:
cache-enabled: true
lazy-loading-enabled: true
use-column-label: true
use-generated-keys: true
default-statement-timeout: 25000
log-prefix: mybatisThen there is the reference application-local.yml
spring:
datasource:
druid:
old:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: *************************
username: ****************
password: ******************
initial-size: 8
min-idle: 3
max-active: 300
max-wait: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: false
max-open-prepared-statements: -1
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
use-global-data-source-stat: false
new:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: *****************
username: ************
password: **************
initial-size: 8
min-idle: 5
max-active: 300
max-wait: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: false
max-open-prepared-statements: -1
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
use-global-data-source-stat: falseParameters can be modified according to their own systems and businesses
4. Then the configuration class: DruidDataSourceNewConfig.java (new data source configuration class)
package com.shallbuy.transfer.config;
import com.alibaba.druid.pool.DruidDataSource;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
/**
* @author gtd
* description:
* date: Create in 9:31 2019/4/13
*/
@Configuration
@EnableTransactionManagement
public class DruidDataSourceNewConfig {
/**
* Data source configuration
* @return
*/
@Bean("newDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.druid.new")
public DataSource dataSource() {
return new DruidDataSource();
}
/**
* transaction management
* @return
*/
@Bean("newTransactionManager")
public PlatformTransactionManager transactionManager() {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(this.dataSource());
return dataSourceTransactionManager;
}
}
Old data source configuration class: DruidDataSourceOldConfig.java is similar to the new data source configuration
package com.shallbuy.transfer.config;
import com.alibaba.druid.pool.DruidDataSource;
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.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* @author gtd
* description:
* date: Create in 9:31 2019/4/13
*/
@Configuration
@EnableTransactionManagement
public class DruidDataSourceOldConfig {
@Bean("oldDataSource")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.druid.old")
public DataSource dataSource() {
return new DruidDataSource();
}
@Bean("oldTransactionManager")
public PlatformTransactionManager transactionManager() {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(this.dataSource());
return dataSourceTransactionManager;
}
}
Mybatis configuration class: MybatisNewDataSourceConfig.java the mybatis configuration class corresponding to the new data source
package com.shallbuy.transfer.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
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.stereotype.Component;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.IOException;
/**
* @author gtd
* description:
* date: Create in 21:36 2019/4/15
*/
@Configuration
@ConfigurationProperties(prefix = "mybatis.new")
@MapperScan(basePackages = {"com.shallbuy.transfer.dao.newer"}, sqlSessionFactoryRef = "newSqlSessionFactory")
public class MybatisNewDataSourceConfig {
private static final Logger logger = LoggerFactory.getLogger(MybatisNewDataSourceConfig.class);
@Resource
@Qualifier("newDataSource")
private DataSource newDataSource;
//Type alias
private String typeAliasesPackage;
//Scan Mapper address
private String mapperLocations;
public String getTypeAliasesPackage() {
return typeAliasesPackage;
}
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public String getMapperLocations() {
return mapperLocations;
}
public void setMapperLocations(String mapperLocations) {
this.mapperLocations = mapperLocations;
}
@Bean(name="newSqlSessionFactory")
public SqlSessionFactory newSqlSessionFactoryBean(){
logger.info("------Initialization newSqlSessionFactory------");
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
SqlSessionFactory sqlSessionFactory = null;
bean.setDataSource(newDataSource);
try {
org.springframework.core.io.Resource[] resources = new PathMatchingResourcePatternResolver()
.getResources(mapperLocations);
bean.setMapperLocations(resources);
bean.setTypeAliasesPackage(typeAliasesPackage);
sqlSessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactory;
}
}
mybatis configuration class of old data source: MybatisOldDataSourceConfig.java
package com.shallbuy.transfer.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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 javax.annotation.Resource;
import javax.sql.DataSource;
/**
* @author gtd
* description:
* date: Create in 21:36 2019/4/15
*/
@Configuration
@ConfigurationProperties(prefix = "mybatis.old")
@MapperScan(basePackages = {"com.shallbuy.transfer.dao.old"}, sqlSessionFactoryRef = "oldSqlSessionFactory")
public class MybatisOldDataSourceConfig {
private static final Logger logger = LoggerFactory.getLogger(MybatisOldDataSourceConfig.class);
@Resource
@Qualifier("oldDataSource")
private DataSource oldDataSource;
//Type alias
private String typeAliasesPackage;
//Scan Mapper address
private String mapperLocations;
public String getTypeAliasesPackage() {
return typeAliasesPackage;
}
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public String getMapperLocations() {
return mapperLocations;
}
public void setMapperLocations(String mapperLocations) {
this.mapperLocations = mapperLocations;
}
@Bean(name="oldSqlSessionFactory")
public SqlSessionFactory oldSqlSessionFactoryBean(){
logger.info("------Initialization oldSqlSessionFactory------");
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
SqlSessionFactory sqlSessionFactory = null;
bean.setDataSource(oldDataSource);
try {
org.springframework.core.io.Resource[] resources = new PathMatchingResourcePatternResolver()
.getResources(mapperLocations);
bean.setMapperLocations(resources);
bean.setTypeAliasesPackage(typeAliasesPackage);
sqlSessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactory;
}
}
5. In addition to the above configuration, the annotation on the startup class needs to be modified to @ SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
6. Other configurations are the same as those of ordinary Spring Boot projects, and then different data sources are used by scanning mapper files in different directories.
