application-test.properties
#datasource -- mysql multiple.datasource.master.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true multiple.datasource.master.username=root multiple.datasource.master.password=pypua multiple.datasource.master.driverClassName=com.mysql.jdbc.Driver multiple.datasource.master.InitialSize=10 multiple.datasource.master.MinIdle=10 multiple.datasource.master.MaxActive=100 multiple.datasource.slave.url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true multiple.datasource.slave.username=root multiple.datasource.slave.password=pypua multiple.datasource.slave.driverClassName=com.mysql.jdbc.Driver multiple.datasource.slave.InitialSize=10 multiple.datasource.slave.MinIdle=10 multiple.datasource.slave.MaxActive=100
创建核心枚举类型 （多个数据源的名称）DataSourceKey
package com.pupeiyuan.core; public enum DataSourceKey { DB_MASTER, DB_SLAVE1, }
创建动态数据源DynamicRoutingDataSource，代表当前数据源
package com.pupeiyuan.core; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicRoutingDataSource extends AbstractRoutingDataSource{ @Override protected Object determineCurrentLookupKey() { logger.info("当前数据源：{}"+ DynamicDataSourceContextHolder.get()); return DynamicDataSourceContextHolder.get(); } }
创建动态数据源上下文取值类 DynamicDataSourceContextHolder.java
package com.pupeiyuan.core; import org.apache.log4j.Logger; public class DynamicDataSourceContextHolder { private static final Logger LOG = Logger.getLogger(DynamicDataSourceContextHolder.class); private static final ThreadLocal<DataSourceKey> currentDatesource = new ThreadLocal<>(); /** * 清除当前数据源 */ public static void clear() { currentDatesource.remove(); } /** * 获取当前使用的数据源 * * @return 当前使用数据源的ID */ public static DataSourceKey get() { return currentDatesource.get(); } /** * 设置当前使用的数据源 * * @param value 需要设置的数据源ID */ public static void set(DataSourceKey value) { currentDatesource.set(value); } /** * 设置从从库读取数据 * 采用简单生成随机数的方式切换不同的从库 */ /*public static void setSlave() { if (RandomUtils.nextInt(0, 2) > 0) { DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE2); } else { DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE1); } }*/ }
最主要的配置 mybatisConfig
package com.pupeiyuan.config; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.Properties; import javax.sql.DataSource; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.annotation.Order; import org.springframework.core.io.Resource; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.core.io.support.ResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.PlatformTransactionManager; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import com.github.pagehelper.PageHelper; import com.pupeiyuan.core.DataSourceKey; import com.pupeiyuan.core.DynamicRoutingDataSource; import tk.mybatis.spring.annotation.MapperScan; import tk.mybatis.spring.mapper.MapperScannerConfigurer; @Configuration @MapperScan("com.pupeiyuan.mapper") public class MyBatisConfig { @Bean //此处的"multiple.datasource.master"需要你在application.properties中配置，详细信息看下面贴出的application.properties文件。 @ConfigurationProperties(prefix = "multiple.datasource.master") public DataSource dbMaster() { DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build(); //配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 try { druidDataSource.setFilters("stat,wall,log4j"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 通过connectProperties属性来打开mergeSql功能；慢SQL记录 druidDataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000"); //合并多个DruidDataSource的监控数据 druidDataSource.setUseGlobalDataSourceStat(true); return druidDataSource; } @Bean @ConfigurationProperties(prefix = "multiple.datasource.slave") public DataSource dbSlave1() { DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build(); //配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 try { druidDataSource.setFilters("stat,wall,log4j"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 通过connectProperties属性来打开mergeSql功能；慢SQL记录 druidDataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000"); //合并多个DruidDataSource的监控数据 druidDataSource.setUseGlobalDataSourceStat(true); return druidDataSource; } /** * 核心动态数据源 * * @return 数据源实例 */ @Bean public DataSource dynamicDataSource() { DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource(); dataSource.setDefaultTargetDataSource(dbMaster()); Map<Object, Object> dataSourceMap = new HashMap<>(4); dataSourceMap.put(DataSourceKey.DB_MASTER, dbMaster()); dataSourceMap.put(DataSourceKey.DB_SLAVE1, dbSlave1()); dataSource.setTargetDataSources(dataSourceMap); return dataSource; } @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dynamicDataSource()); //此处设置为了解决找不到mapper文件的问题 //sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate() throws Exception { return new SqlSessionTemplate(sqlSessionFactory()); } /** * 事务管理 * * @return 事务管理实例 */ @Bean public PlatformTransactionManager platformTransactionManager() { return new DataSourceTransactionManager(dynamicDataSource()); } }
如果要配置自动切换数据源可以考虑AOP方式或者拦截器的方式进行配置，我这里采用手动切换
controller中手动切换
package com.pupeiyuan.controller; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Scope; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.servlet.ModelAndView; import com.github.pagehelper.PageHelper; import com.pupeiyuan.bean.NhReportStatusHistory; import com.pupeiyuan.common.controller.BaseController; import com.pupeiyuan.core.DataSourceKey; import com.pupeiyuan.core.DynamicDataSourceContextHolder; import com.pupeiyuan.services.NhReportService; /** * @author pypua * @date 2018年8月30日 上午9:21:20 * */ @Controller @RequestMapping("burket") @Scope("prototype") public class BurketController extends BaseController { //services层注入 @Autowired NhReportService nhReportService; @RequestMapping(value = "/burketList", method = {RequestMethod.GET,RequestMethod.POST}) public ModelAndView burketList(HttpServletRequest request, HttpServletResponse response ) throws Exception { System.out.println("hello,springboot"); //参数容器 Map<String, Object> params = new HashMap<String, Object>(); PageHelper.startPage(1, 2); DynamicDataSourceContextHolder.set(DataSourceKey.DB_SLAVE1); List<NhReportStatusHistory> findList = nhReportService.findList(params); ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("burketList"); modelAndView.addObject("list", findList); return modelAndView; } }
-----------------------------------------------亲测有效------------------------------------------------------