通用Mapper连接多数据源

背景

新的项目需要用到公司不同数据库的表，这里我一共用到Mysql加两个SqlServer的数据库，并且整合通用Mapper。

项目结构

pom.xml

<!-- ============================= mybatis和jdbc相关========================= -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
        <!-- 通用mapper -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>RELEASE</version>
        </dependency>
        <!-- SPRINGBOOT JDBC -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- MYSQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
            <scope>runtime</scope>
        </dependency>
        <!-- SqlServer       -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>
        <!--druid 连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.6</version>
        </dependency>

其他的根据需要添加

application.yml

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://10.168.1.123:3306/camglxt?autoReconnect=true&useUnicode=true&characterEncoding=utf8
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
    esdb:
      jdbc-url: jdbc:sqlserver://10.168.1.124:1433;DatabaseName=EngineeringSystem
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    jldb:
      jdbc-url: jdbc:sqlserver://10.168.1.125:1433;DatabaseName=JingLiang
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver

改IP、数据库名称、username、password

配置类

/**
 * 主数据源配置（Mysql）
 */
@Configuration
@MapperScan(basePackages ="com.ceepcb.erp.dao.mysql", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MybatisDbMasterConfig {

	@Primary
	@Bean(name = "masterDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.master")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
		/*
			要启用druid连接池，请将配置文件中jdbc-url改成url
			本项目中Mysql连接druid正常，但sqlserver会炸掉
			所以暂时先不启用，后面排查出来再更新。
		*/
//		return new DruidDataSource();

	}

	@Primary
	@Bean(name = "masterSqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
		SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
		//设置数据源
		factoryBean.setDataSource(dataSource);
		//Mapper扫描位置
		factoryBean.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources("classpath:mapping/mysql/*Mapping.xml"));
		return factoryBean.getObject();
	}

	@Primary
	@Bean(name = "masterTransactionManager")
	public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);
	}

	@Bean(name = "masterSqlSessionTemplate")
	@Primary
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

/**
 * SqlServer EngineeringSystem数据库配置
 */
@Configuration
@MapperScan(basePackages = "com.ceepcb.erp.dao.sqlserver.es", sqlSessionFactoryRef = "esdbSqlSessionFactory")
public class MybatisESDBConfig {

	@Bean(name = "esdbDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.esdb")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
//		return new DruidDataSource();
	}

	@Bean(name = "esdbTransactionManager")
	public DataSourceTransactionManager transactionManager(@Qualifier("esdbDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "esdbSqlSessionFactory")
	public SqlSessionFactory basicSqlSessionFactory(@Qualifier("esdbDataSource") DataSource basicDataSource) throws Exception {
		SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
		factoryBean.setDataSource(basicDataSource);
		factoryBean.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources("classpath:mapping/sqlserver/es/*Mapping.xml"));
		return factoryBean.getObject();
	}

	@Bean(name = "esdbSqlSessionTemplate")
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("esdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

/**
 * SqlServer Jing_Liang数据库配置
 */
@Configuration
@MapperScan(basePackages = "com.ceepcb.erp.dao.sqlserver.jl", sqlSessionFactoryRef = "jldbSqlSessionFactory")
public class MybatisJLDBConfig {

	@Bean(name = "jldbDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.jldb")
	public DataSource dataSource() {
		return DataSourceBuilder.create().build();
//		return new DruidDataSource();
	}

	@Bean(name = "jldbTransactionManager")
	public DataSourceTransactionManager transactionManager(@Qualifier("jldbDataSource") DataSource dataSource) {
		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "jldbSqlSessionFactory")
	public SqlSessionFactory basicSqlSessionFactory(@Qualifier("jldbDataSource") DataSource basicDataSource) throws Exception {
		SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
		factoryBean.setDataSource(basicDataSource);
		factoryBean.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources("classpath:mapping/sqlserver/jl/*Mapping.xml"));
		return factoryBean.getObject();
	}

	@Bean(name = "jldbSqlSessionTemplate")
	public SqlSessionTemplate testSqlSessionTemplate(
			@Qualifier("jldbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
		return new SqlSessionTemplate(sqlSessionFactory);
	}
}

需要改的就是MapperScan中的Mapper扫描包路径和Mapping.xml路径，注意三个包要互相独立，不包含不冲突。另外MapperScan请认准导的包是

import tk.mybatis.spring.annotation.MapperScan;

Application启动类

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

Mybatis或者通用Mapper很多新手教程都会把MapperScan注解放在启动类，注意！注意！注意！因为配置类上有了，所以这里不用再添加，添加了会炸掉。我理解是优先级问题，但没有去细究，会的欢迎补充。

Mapper

@Repository
public interface DeptDao extends Mapper<Dept> {
}

Service

@Service
public class DeptServiceImpl implements DeptService {
	@Autowired
	private DeptDao deptDao;
	@Override
	public List<Dept> selectList() {
		return deptDao.selectAll();
	}
}

Controller就不写了