1、配置多数据源

增加druid依赖

完整pom文件

数据源配置文件

route.datasource.driver-class-name=com.mysql.jdbc.Driver

route.datasource.url= jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8

route.datasource.username=root

route.datasource.password= 123456operate.datasource.driver-class-name=com.mysql.jdbc.Driver

operate.datasource.url= jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf8

operate.datasource.username=root

operate.datasource.password= 123456

multiple-datasource.properties

初始化数据源

/*** 多数据源配置

*@authorzhouliang

* @date 2017年9月20日*/@Configuration

@PropertySource("classpath:multiple-datasource.properties")public classMyBatisConfig {

@AutowiredprivateEnvironment env;

@Autowired MybatisSpringPageInterceptor inteceptor;/*** 创建数据源(数据源的名称：方法名可以取为XXXDataSource(),XXX为数据库名称,该名称也就是数据源的名称)*/@Beanpublic DataSource operateDataSource() throwsException {

Properties props= newProperties();

props.put("driverClassName", env.getProperty("operate.datasource.driver-class-name"));

props.put("url", env.getProperty("operate.datasource.url"));

props.put("username", env.getProperty("operate.datasource.username"));

props.put("password", env.getProperty("operate.datasource.password"));returnDruidDataSourceFactory.createDataSource(props);

}

@Beanpublic DataSource routeDataSource() throwsException {

Properties props= newProperties();

props.put("driverClassName", env.getProperty("route.datasource.driver-class-name"));

props.put("url", env.getProperty("route.datasource.url"));

props.put("username", env.getProperty("route.datasource.username"));

props.put("password", env.getProperty("route.datasource.password"));returnDruidDataSourceFactory.createDataSource(props);

}/*** @Primary 该注解表示在同一个接口有多个实现类可以注入的时候，默认选择哪一个，而不是让@autowire注解报错

* @Qualifier 根据名称进行注入，通常是在具有相同的多个类型的实例的一个注入(例如有多个DataSource类型的实例)*/@Bean

@PrimarypublicDynamicDataSource dataSource(

@Qualifier("routeDataSource") DataSource routeDataSource,

@Qualifier("operateDataSource") DataSource operateDataSource) {

Map targetDataSources = new HashMap();

targetDataSources.put(DatabaseType.routeDS, routeDataSource);

targetDataSources.put(DatabaseType.operateDS, operateDataSource);

DynamicDataSource dataSource= newDynamicDataSource();

dataSource.setTargetDataSources(targetDataSources);//该方法是AbstractRoutingDataSource的方法

returndataSource;

}/*** 根据数据源创建SqlSessionFactory*/@BeanpublicSqlSessionFactory sqlSessionFactory(DynamicDataSource ds)throwsException {

SqlSessionFactoryBean fb= newSqlSessionFactoryBean();

fb.setDataSource(ds);//指定数据源(这个必须有，否则报错)//下边两句仅仅用于*.xml文件，如果整个持久层操作不需要使用到xml文件的话(只用注解就可以搞定)，则不加

fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));//指定基包

fb.setMapperLocations(newPathMatchingResourcePatternResolver()

.getResources(env.getProperty("mybatis.mapperLocations")));// fb.setPlugins(newInterceptor[]{inteceptor});returnfb.getObject();

}

}

MyBatisConfig.java

@Bean是分别注入两个数据源，

当自动注入多个同样的bean时需要指定一个默认额，所以这里指定了一个默认的抽象数据源@primary

数据源注入之后需要创建SqlSessionFactory

创建抽象数据源类集成AbstractRoutingDataSource

1 public class DynamicDataSource extendsAbstractRoutingDataSource {2

3

4 @Override5 protectedObject determineCurrentLookupKey() {6 //TODO Auto-generated method stub

7 returnDatabaseContextHolder.getDatabaseType();8 }9

10 }

DynamicDataSource.java

1 public enumDatabaseType {2 routeDS,operateDS3 }

DatabaseType.java

1 public classDatabaseContextHolder {2 private static final ThreadLocal contextHolder = new ThreadLocal();3

4 public static voidsetDatabaseType(DatabaseType type){5 contextHolder.set(type);6 }7

8 public staticDatabaseType getDatabaseType(){9 returncontextHolder.get();10 }11 public static voidclearDatabaseType(){12 contextHolder.remove();13 }

DatabaseContextHolder.java

上述配置完成之后需要在配置文件中指定mybatis映射的xml文件的位置

指定mybatis映射文件之后需要在项目的启动类上排除springboot默认的数据库的配置以及指定mybatis映射文件对应的接口

不排除springboot默认的数据库配置类，项目启动的时候会报错启动失败

@MapperScan指定了mybatis映射文件对应的接口所在的目录，这样避免了再每个接口上都加上@Mapper的注解

到此两个数据源已经配置完成

2、数据源的动态切换

这里设置了所有的实体bean都继承了一个父类，父类信息如下

1 public class BaseBean implementsSerializable{2 private int pageSize=10;3 private int pageNo=0;4 private longtotalNum;5 privateString totalMappedStatementId;6

7

8 private long shardValue = 0l;9

10 publicBaseBean() {11 super();12 //TODO Auto-generated constructor stub

13 }14

15 public BaseBean(int pageSize, int pageNo, inttotalNum) {16 super();17 this.pageSize =pageSize;18 this.pageNo =pageNo;19 this.totalNum =totalNum;20 }21

22 public intgetPageSize() {23 returnpageSize;24 }25

26 public longgetShardValue() {27 returnshardValue;28 }29

30 public void setShardValue(longshardValue) {31 this.shardValue =shardValue;32 }33

34 publicString getTotalMappedStatementId() {35 returntotalMappedStatementId;36 }37

38 public voidsetTotalMappedStatementId(String totalMappedStatementId) {39 this.totalMappedStatementId =totalMappedStatementId;40 }41

42 public void setPageSize(intpageSize) {43 this.pageSize =pageSize;44 }45

46 public intgetPageNo() {47 returnpageNo;48 }49

50 public void setPageNo(intpageNo) {51 this.pageNo =pageNo;52 }53

54 public longgetTotalNum() {55 returntotalNum;56 }57

58 public void setTotalNum(longtotalNum) {59 this.totalNum =totalNum;60 }61

62 }

BaseBean.java

BaseBean中的shardValue属性是用来指定数据源的，默认值为0，其余属性是分页相关的。

数据源的动态切换是通过spring的切面编程来实现的，通过对mybatis的映射文件对应的接口进行监控，代码如下

1 @Aspect2 @Component3 public classDataSourceAspect {4 Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);5

6 @Before("execution(* zl.mybatis.mapper.*.*(..))")7 public voidsetDataSourcePgKey(JoinPoint point) {8 Object args[] =point.getArgs();9 for(Object obj:args){10 if(obj instanceofBaseBean){11 BaseBean bean =(BaseBean) obj;12 if(Common.DB_0==bean.getShardValue()){13 logger.info("===========================使用数据源DB_route=======================");14 DatabaseContextHolder.setDatabaseType(DatabaseType.routeDS);15 }else{16 logger.info("===========================使用数据源DB_operate=======================");17 DatabaseContextHolder.setDatabaseType(DatabaseType.operateDS);18 }19 break;20 }21 }22 }23

24 }

DataSourceAspect.java

@Before("execution(* zl.mybatis.mapper.*.*(..))")是对mybatis的映射文件对应的接口进行监控，根据获取到的参数实体类判断里面的shardValue的值来决定使用哪个数据源

完成这一步之后springboot的多数据源动态切换完成了，接下来继续

3、实现自定义mybatis的分页插件

mybatis本身提供了对数据库操作的拦截器，所以实现自定义分页的时候只需要实现这个接口自定义里面的拦截方法。这里我是只拦截了查询的方法

具体代码如下

1 @Component2 @Intercepts({ @Signature(type = Executor.class, method = "query",3 args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}) })4 public class MybatisSpringPageInterceptor implementsInterceptor {5

6 private static final Logger logger = LoggerFactory.getLogger(MybatisSpringPageInterceptor.class.getName());7

8

9 @SuppressWarnings("unused")10 public Object intercept(Invocation arg0) throwsThrowable {11 MappedStatement mappedStatement = (MappedStatement) arg0.getArgs()[0];12 Object parameter = arg0.getArgs()[1];13 BoundSql boundSql =mappedStatement.getBoundSql(parameter);14 if (null == boundSql ||StringUtils.isBlank(boundSql.getSql())) {15 return null;16 }17 RowBounds rowBounds = (RowBounds) arg0.getArgs()[2];18 Object parameterObject =boundSql.getParameterObject();19 BaseBean model = null;20 if (parameterObject instanceofBaseBean) {21 model =(BaseBean) parameterObject;22 } else{23 BoundSql newBoundSql =copyFromBoundSql(mappedStatement, boundSql, boundSql.getSql());24 arg0.getArgs()[0] = copyFromMappedStatement(mappedStatement, newBoundSqlSqlSource(newBoundSql));25 returnarg0.proceed();26 }27 if (null ==model) {28 throw new Exception("无法获取分页参数.");29 }30 if (model.getPageNo() == -1) {31 BoundSql newBoundSql =copyFromBoundSql(mappedStatement, boundSql, boundSql.getSql());32 arg0.getArgs()[0] = copyFromMappedStatement(mappedStatement, newBoundSqlSqlSource(newBoundSql));33 returnarg0.proceed();34 }35 String shardSql =boundSql.getSql();36 queryTotal(mappedStatement, shardSql, parameterObject, boundSql,model);37

38 if (null == rowBounds || rowBounds ==RowBounds.DEFAULT) {39 rowBounds = new RowBounds(model.getPageSize() * (model.getPageNo() - 1), model.getPageSize());40 }41 String pagesql =getLimitSql(shardSql, rowBounds.getOffset(), rowBounds.getLimit());42 arg0.getArgs()[2] = newRowBounds(RowBounds.NO_ROW_OFFSET, RowBounds.NO_ROW_LIMIT);43 BoundSql newBoundSql =copyFromBoundSql(mappedStatement, boundSql, pagesql);44 arg0.getArgs()[0] = copyFromMappedStatement(mappedStatement, newBoundSqlSqlSource(newBoundSql));45 returnarg0.proceed();46 }47

48 public static class BoundSqlSqlSource implementsSqlSource {49 BoundSql boundSql;50

51 publicBoundSqlSqlSource(BoundSql boundSql) {52 this.boundSql =boundSql;53 }54

55 publicBoundSql getBoundSql(Object parameterObject) {56 returnboundSql;57 }58 }59 private String getLimitSql(String sql, int start, int end) throwsException{60 if(sql ==null){61 throw new Exception("execute sql is empty.");62 }63 StringBuffer sqlBuffer = new StringBuffer(sql.length()+300);64 sqlBuffer.append(sql);65 sqlBuffer.append(" LIMIT ").append(start).append(",").append(end);66 returnsqlBuffer.toString();67 }68 private void queryTotal(MappedStatement mappedStatement, String replaceSql, Object parameterObject, BoundSql boundSql,BaseBean model) throwsException{69 StringBuffer countSql = newStringBuffer();70

71 if(model.getTotalMappedStatementId()!=null && model.getTotalMappedStatementId().length()>0){72 MappedStatement totalMappedStatement=mappedStatement.getConfiguration().getMappedStatement(model.getTotalMappedStatementId());73 BoundSql totalBoundSql =totalMappedStatement.getBoundSql(parameterObject);74

75 countSql.append(totalBoundSql.getSql());76 }else{77 //未指定，自动拼装

78 countSql.append("SELECT COUNT(1) FROM (").append(replaceSql).append(") as total");79 }80

81 Connection conn = null;82 PreparedStatement ps = null;83 ResultSet rs = null;84 try{85 conn =mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();86 if(logger.isDebugEnabled()) {87 logger.debug(countSql.toString());88 }89 ps =conn.prepareStatement(countSql.toString());90 BoundSql countBS =copyFromBoundSql(mappedStatement, boundSql, countSql.toString());91 setParameters(ps, mappedStatement, countBS, parameterObject);92 rs =ps.executeQuery();93 if(rs.next()) {94 model.setTotalNum(rs.getLong(1));95 }96 } catch(Exception e) {97 logger.error(e.getMessage(), e);98 throw newException(e.getMessage(), e);99 } finally{100 try{101 if (null !=rs) {102 rs.close();103 }104 } catch(Exception e) {105 logger.error("rs.close() error!", e);106 }107 try{108 if (null !=ps) {109 ps.close();110 }111 } catch(Exception e) {112 logger.error("ps.close() error!", e);113 }114 try{115 if (null !=conn) {116 conn.close();117 }118 } catch(Exception e) {119 logger.error("conn.close() error!", e);120 }121 }122 }123 protectedMappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {124 Builder builder = newMappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());125 builder.resource(ms.getResource());126 builder.fetchSize(ms.getFetchSize());127 builder.statementType(ms.getStatementType());128 builder.keyGenerator(ms.getKeyGenerator());129 //builder.keyProperty(ms.getKeyProperties());

130 builder.timeout(ms.getTimeout());131 builder.parameterMap(ms.getParameterMap());132 builder.resultMaps(ms.getResultMaps());133 builder.cache(ms.getCache());134 MappedStatement newMs =builder.build();135 returnnewMs;136 }137

138 /**

139 *140 *@paramps141 *@parammappedStatement142 *@paramboundSql143 *@paramparameterObject144 *@throwsSQLException145 */

146 private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throwsSQLException {147 ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());148 List parameterMappings =boundSql.getParameterMappings();149 if (parameterMappings != null) {150 Configuration configuration =mappedStatement.getConfiguration();151 TypeHandlerRegistry typeHandlerRegistry =configuration.getTypeHandlerRegistry();152 MetaObject metaObject = parameterObject == null ? null: configuration.newMetaObject(parameterObject);153 for (int i = 0; i < parameterMappings.size(); i++) {154 ParameterMapping parameterMapping =parameterMappings.get(i);155 if (parameterMapping.getMode() !=ParameterMode.OUT) {156 Object value;157 String propertyName =parameterMapping.getProperty();158 if (parameterObject == null) {159 value = null;160 } else if(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {161 value =parameterObject;162 } else if(boundSql.hasAdditionalParameter(propertyName)) {163 value =boundSql.getAdditionalParameter(propertyName);164 } else{165 value = metaObject == null ? null: metaObject.getValue(propertyName);166 }167 TypeHandler typeHandler =parameterMapping.getTypeHandler();168 if (typeHandler == null) {169 throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " +mappedStatement.getId());170 }171 logger.debug(i + 1 + ":" +value);172 typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());173 }174 }175 }176 }177 @Override178 publicObject plugin(Object arg0) {179 return Plugin.wrap(arg0, this);180 }181

182 public voidsetProperties(Properties arg0) {183

184 }185

186

187 privateBoundSql copyFromBoundSql(MappedStatement ms, BoundSql boundSql, String sql) {188 BoundSql newBoundSql = newBoundSql(ms.getConfiguration(),sql, boundSql.getParameterMappings(), boundSql.getParameterObject());189 for(ParameterMapping mapping : boundSql.getParameterMappings()) {190 String prop =mapping.getProperty();191 if(boundSql.hasAdditionalParameter(prop)) {192 newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));193 }194 }195 returnnewBoundSql;196 }197

198 }

MybatisSpringPageInterceptor.java

插件重写完成之后需要在注入SqlSessionFactory的时候指定这个插件，下面的代码是在MyBatisConfig.java中

现在已经完成了多数据源的动态切换以及自定义mybatis的分页查询了，剩下的测试步骤就不再啰嗦了，排版比较乱大家就将就着看吧！