springBoot + Mysql中配置多种数据源

springBoot + Mysql中配置多种数据源

业务背景

由于业务的繁杂，项目中需要引入多个数据库，比如订单库，商品库，积分库等，这个时候需要在项目中配置多个数据源，查询不同的sql，sql可以根据配置去不同的数据库进行查询并返回数据。

实现思路

1. 定义数据源bean，该bean中要处理数据源的连接信息
2. 将数据源的bean和mapper.xml文件进行关联
3. 定义mapper扫描bean，加入到容器中

目录结构

这其中只是一个数据库的配置方式（webdb的配置方式），如果需要配置其他数据库，在config中新建一个orderdb的文件夹，还是这三个类，只不过在数据源的连接方式上，以及mapper的文件地址和mapper的包名路径上有所改变。

实现代码

① 定义数据源bean

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.util.Map;
import java.util.Properties;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;

@Configuration
@Slf4j
public class DBConfig {	
	
    // 获取application中配置的environment（dev, test , prd），通过不同的编译环境，获取不同的连接
	@Value("${environment}")
	private String environment;

	@Resource(name = "propertiesMap")
	private Map<String, Map<String,String>> propertiesMap;

	@Bean(name = "webdbDataSource")
	public DataSource webdbDataSource() throws PropertyVetoException, IOException {
		if ("dev".equals(environment)) {
			ComboPooledDataSource dataSource = new ComboPooledDataSource();

			Map<String, String> devDbMap = propertiesMap.get("devDb");
			dataSource.setDriverClass(devDbMap.get("webdb_driverClass"));
			dataSource.setJdbcUrl(devDbMap.get("webdb_jdbcUrl"));
			dataSource.setUser(devDbMap.get("webdb_user"));
			dataSource.setPassword(devDbMap.get("webdb_password"));
			dataSource.setMaxPoolSize(20);
			dataSource.setMinPoolSize(5);
			dataSource.setInitialPoolSize(10);
			dataSource.setMaxIdleTime(300);
			dataSource.setAcquireIncrement(5);
			dataSource.setIdleConnectionTestPeriod(60);
			return dataSource;
		} else if ("test".equals(environment)) {
			ComboPooledDataSource dataSource = new ComboPooledDataSource();

			Map<String, String> devDbMap = propertiesMap.get("testDb");
			dataSource.setDriverClass(devDbMap.get("webdb_driverClass"));
			dataSource.setJdbcUrl(devDbMap.get("webdb_jdbcUrl"));
			dataSource.setUser(devDbMap.get("webdb_user"));
			dataSource.setPassword(devDbMap.get("webdb_password"));
			dataSource.setMaxPoolSize(20);
			dataSource.setMinPoolSize(5);
			dataSource.setInitialPoolSize(10);
			dataSource.setMaxIdleTime(300);
			dataSource.setAcquireIncrement(5);
			dataSource.setIdleConnectionTestPeriod(60);
			return dataSource;
		} else {
			ComboPooledDataSource dataSource = new ComboPooledDataSource();

			Map<String, String> devDbMap = propertiesMap.get("prdDb");
			dataSource.setDriverClass(devDbMap.get("webdb_driverClass"));
			dataSource.setJdbcUrl(devDbMap.get("webdb_jdbcUrl"));
			dataSource.setUser(devDbMap.get("webdb_user"));
			dataSource.setPassword(devDbMap.get("webdb_password"));
			dataSource.setMaxPoolSize(20);
			dataSource.setMinPoolSize(5);
			dataSource.setInitialPoolSize(10);
			dataSource.setMaxIdleTime(300);
			dataSource.setAcquireIncrement(5);
			dataSource.setIdleConnectionTestPeriod(60);
			return dataSource;
		}
	}
}

import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.io.IOException;
import java.util.*;

@Configuration
@Slf4j
public class LoadPropertiesConfigAction {

    @Bean(name = "propertiesMap")
    public Map<String, Map<String,String>> getPropertiesMap () throws IOException {
        Map<String, Map<String,String>> allMap = new HashMap<>();

        // 加载 db-dev.properties
        Map<String, String> devDbMap = this.loadData("db-dev.properties");
        allMap.put("devDb", devDbMap);
        
        // 加载 db-test.properties
        Map<String, String> testDBMap = this.loadData("db-test.properties");
        allMap.put("testDb", testDBMap);
        
        // 加载 db-prd.properties
        Map<String, String> prdDbMap = this.loadData("db-prd.properties");
        allMap.put("prdDb", prdDbMap);

        return allMap;
    }


    private Map<String,String> loadData (String propertiesName) throws IOException {
        Properties properties = new Properties();
        properties.load(LoadPropertiesConfigAction.class.getClassLoader().getResourceAsStream(propertiesName));

        Map<String,String> dataMap = new HashMap<>();
        Iterator<Map.Entry<Object, Object>> iteratorDev = properties.entrySet().iterator();
        while (iteratorDev.hasNext()) {
            Map.Entry<Object, Object> next = iteratorDev.next();
            // CommonUtil中的方法，可以参考我的java工具类封装这篇文件
            dataMap.put(CommonUtil.safeToString(next.getKey(),""), CommonUtil.safeToString(next.getValue(),""));
        }
        return dataMap;
    }
}

② 将bean和mapper.xml文件进行关联

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {

	@Resource(name="webdbDataSource")
    private DataSource webdbDataSource;
	
	@Bean(name = "webdbSqlSessionFactory")
    public SqlSessionFactoryBean webDbSqlSessionFactory(ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setConfigLocation(applicationContext.getResource("classpath:myBatis-log-cfg.xml"));
        sessionFactory.setDataSource(webdbDataSource);
        // 如果有其他的数据库，例如orderdb，将webdb换成orderdb
        sessionFactory.setMapperLocations(applicationContext.getResources("classpath:mapper/webdb/*.xml"));
        return sessionFactory;
    }	
}

myBatis-log-cfg.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>  
	<settings>
        <!-- 打印查询语句 -->
        <setting name="logImpl" value="LOG4J" />
    </settings>
</configuration>

③ 扫描mapper文件

import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisScannerConfig {

	@Bean(name="webdbMapperScannerConfigurer")
	public MapperScannerConfigurer webdbMapperScannerConfigurer() {
		MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        // 由于包名的保密性，用*进行隐蔽，各位根据实际包名进行替换
        // 如果有其他的数据库，例如orderdb，将webdb换成orderdb
		mapperScannerConfigurer.setBasePackage("com.*.*.*.dao.webdb");
		mapperScannerConfigurer
				.setSqlSessionFactoryBeanName("webdbSqlSessionFactory");
		return mapperScannerConfigurer;
	}
}