导读

现在大多环境都是采用的前后端分离，数据库也采用分库分表。简单举例：用户模块对应一个用户库，订单模块对应一个订单库，分别存储在不同数据库中，下面简单介绍下。

使用

配置数据源

我这里配置了三个数据源，默认base数据源，另外有firstdb数据源和seconddb数据源。

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3.        xsi:schemaLocation="http://www.springframework.org/schema/beans
  4.        classpath:/org/springframework/beans/factory/xml/spring-beans-3.0.xsd"
  5.        default-autowire="byName">
  7.     <!-- firstDataSource -->
  8.     <bean id="firstDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  9.         <property name="url" value="${firstDatasource.url}"/>
  10.         <property name="username" value="${firstDatasource.name}"/>
  11.         <property name="password" value="${firstDatasource.password}"/>
  12.         <property name="filters" value="log4j"/>
  13.         <property name="maxActive" value="${firstDatasource.maxActive}"/>
  14.         <property name="initialSize" value="${firstDatasource.initialiSize}"/>
  15.         <property name="maxWait" value="60000"/>
  16.         <property name="minIdle" value="1"/>
  17.         <property name="timeBetweenEvictionRunsMillis" value="2000"/>
  18.         <property name="minEvictableIdleTimeMillis" value="200000"/>
  19.         <property name="poolPreparedStatements" value="false"/>
  20.         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
  21.         <property name="blockingTimeoutMillis" value="500"/>
  22.         <property name="idleTimeoutMinutes" value="30"/>
  23.         <property name="preparedStatementCacheSize" value="0"/>
  24.         <property name="queryTimeout" value="30"/>
  25.         <property name="prefill" value="true"/>
  26.         <property name="maxReadThreshold" value="100"/>
  27.         <property name="maxWriteThreshold" value="100"/>
  28.         <property name="newConnectionSQL" value="set names utf8mb4;"/>
  29.     </bean>
  31.     <!-- secondDataSource -->
  32.     <bean id="secondDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
  33.           destroy-method="close">
  34.         <property name="url" value="${secondDatasource.url}"/>
  35.         <property name="username" value="${secondDatasource.name}"/>
  36.         <property name="password" value="${secondDatasource.password}"/>
  37.         <property name="filters" value="log4j"/>
  38.         <property name="maxActive" value="${secondDatasource.maxActive}"/>
  39.         <property name="initialSize" value="${secondDatasource.initialiSize}"/>
  40.         <property name="maxWait" value="60000"/>
  41.         <property name="minIdle" value="1"/>
  42.         <property name="timeBetweenEvictionRunsMillis" value="2000"/>
  43.         <property name="minEvictableIdleTimeMillis" value="200000"/>
  44.         <property name="poolPreparedStatements" value="false"/>
  45.         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
  46.         <property name="blockingTimeoutMillis" value="500"/>
  47.         <property name="idleTimeoutMinutes" value="30"/>
  48.         <property name="preparedStatementCacheSize" value="0"/>
  49.         <property name="queryTimeout" value="30"/>
  50.         <property name="prefill" value="true"/>
  51.         <property name="maxReadThreshold" value="100"/>
  52.         <property name="maxWriteThreshold" value="100"/>
  53.         <property name="newConnectionSQL" value="set names utf8mb4;"/>
  54.     </bean>
  56.     <!-- baseDataSource -->
  57.     <bean id="baseDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
  58.           destroy-method="close">
  59.         <property name="url" value="${baseDatasource.url}"/>
  60.         <property name="username" value="${baseDatasource.name}"/>
  61.         <property name="password" value="${baseDatasource.password}"/>
  62.         <property name="filters" value="log4j"/>
  63.         <property name="maxActive" value="${baseDatasource.maxActive}"/>
  64.         <property name="initialSize" value="${baseDatasource.initialiSize}"/>
  65.         <property name="maxWait" value="60000"/>
  66.         <property name="minIdle" value="1"/>
  67.         <property name="timeBetweenEvictionRunsMillis" value="2000"/>
  68.         <property name="minEvictableIdleTimeMillis" value="200000"/>
  69.         <property name="poolPreparedStatements" value="false"/>
  70.         <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
  71.         <property name="blockingTimeoutMillis" value="500"/>
  72.         <property name="idleTimeoutMinutes" value="30"/>
  73.         <property name="preparedStatementCacheSize" value="0"/>
  74.         <property name="queryTimeout" value="30"/>
  75.         <property name="prefill" value="true"/>
  76.         <property name="maxReadThreshold" value="100"/>
  77.         <property name="maxWriteThreshold" value="100"/>
  78.         <property name="newConnectionSQL" value="set names utf8mb4;"/>
  79.     </bean>
  80. </beans>

AOP切面拦截

由于我是在Service层的某一个方法内需要调用其他两个库的表，所以我在mapper层做aop切面设置，如果是不同的Service层的不同方法，切面可以设置成Service层，这个由自己的业务决定。

  2. import com.alibaba.common.logging.Logger;
  3. import com.alibaba.common.logging.LoggerFactory;
  4. import com.demo.second.common.dal.support.datasource.DdsDsModeEnum;
  5. import com.demo.second.common.dal.support.datasource.DynamicRoutingDataSourceFactory;
  6. import org.aspectj.lang.JoinPoint;
  7. import org.aspectj.lang.annotation.After;
  8. import org.aspectj.lang.annotation.Aspect;
  9. import org.aspectj.lang.annotation.Before;
  10. import org.aspectj.lang.annotation.Pointcut;
  11. import org.aspectj.lang.reflect.MethodSignature;
  12. import org.springframework.core.annotation.Order;
  13. import org.springframework.stereotype.Component;
  15. import java.lang.reflect.Method;
  18. /**
  19.  * 切面配置数据源
  20.  */
  22. @Aspect
  23. @Component
  24. @Order(1) //@order注解控制切面的执行顺序，如果有多个切面配置，可以设置order的大小
  25. public class DataSourceAspect {
  26.     private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceAspect.class);
  28.     //定义切面，当执行某个mapper时，就会拦截，切换到相应的数据源，这里切换firstdb数据源
  29.     @Pointcut("execution(* com.demo.second.common.dal.mapper.first.*.*(..))")
  30.     public void firstDataSource() {
  31.     }
  33.     //定义切面，当执行某个mapper时，就会拦截，切换到相应的数据源，这里切换seconddb数据源
  34.     @Pointcut("execution(* com.demo.second.common.dal.mapper.second.*.*(..))")
  35.     public void secondDataSource() {
  36.     }
  38.     @Before("firstDataSource()")
  39.     public void beforefirst(JoinPoint point) {
  40.         LOGGER.info("切面捕获到修改数据源信息");
  41.         MethodSignature signa = (MethodSignature) point.getSignature();
  42.         Method method = signa.getMethod();
  43.         //切换到firstdb数据源
  44.         DynamicRoutingDataSourceFactory.switchCurrDataSource(DdsDsModeEnum.FIRSTDB);
  45.         LOGGER.info("AOP动态切换数据源，className" + point.getTarget().getClass().getName() + ";methodName:" + method.getName());
  46.     }
  48.     @Before("secondDataSource()")
  49.     public void beforesecond(JoinPoint point) {
  50.         LOGGER.info("切面捕获到修改数据源信息");
  51.         MethodSignature signa = (MethodSignature) point.getSignature();
  52.         Method method = signa.getMethod();
  53.          //切换到seconddb数据源
  54.         DynamicRoutingDataSourceFactory.switchCurrDataSource(DdsDsModeEnum.SECONDDB);
  55.         LOGGER.info("AOP动态切换数据源，className" + point.getTarget().getClass().getName() + ";methodName:" + method.getName());
  56.     }
  58.     /**
  59.      * 清理掉当前设置的数据源，让默认的base数据源不受影响
  60.      */
  61.     @After("firstDataSource()")
  62.     public void afterfirst(JoinPoint point) {
  63.         DynamicRoutingDataSourceFactory.releaseCurrDataSource();
  64.     }
  66.     /**
  67.      * 清理掉当前设置的数据源，让默认的数据源不受影响
  68.      */
  69.     @After("secondDataSource()")
  70.     public void aftersecond(JoinPoint point) {
  71.         DynamicRoutingDataSourceFactory.releaseCurrDataSource();
  72.     }
  74. }

注： 我这里使用的切面拦截的 ，还有一种使用注解方式，没有列出了，和这个类似，只是需要在使用的时候在指定方法上面添加注解，指定下数据源即可。
_

动态数据源工厂

  1. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  3. import java.util.logging.Logger;
  5. /**
  6.  * 动态数据源工厂.
  7.  */
  8. public class DynamicRoutingDataSourceFactory extends AbstractRoutingDataSource {
  10.     private static final ThreadLocal<String> currentDataSourceKey = new ThreadLocal<>();
  12.      @Override
  13.     public Logger getParentLogger() {
  14.         return null;
  15.     }
  17.     @Override
  18.     protected Object determineCurrentLookupKey() {
  19.         return currentDataSourceKey.get();
  20.     }
  22.     /**
  23.      * 让外部业务设置当前线程所用数据源的方法
  24.      */
  25.     public static void switchCurrDataSource(DdsDsModeEnum ds) {
  26.         if (ds == null) {
  27.             throw new RuntimeException("设置数据源失败, 目标数据源为 [null].");
  28.         }
  29.         currentDataSourceKey.set(ds.getDsName());
  30.     }
  32.     /**
  33.      * 扩展: 释放当前线程设置的数据源
  34.      */
  35.     public static void releaseCurrDataSource() {
  36.         currentDataSourceKey.remove();
  37.     }
  38. }

业务数据源枚举类定义

  1. /**
  2.  * 业务数据源枚举类定义.
  3.  */
  4. public enum DdsDsModeEnum {
  6.     BASE("base"),
  8.     FIRSTDB("firstdb"),
  10.     SECONDDB("seconddb");
  12.     private final String dsName;
  14.     DdsDsModeEnum(String dsName) {
  15.         this.dsName = dsName;
  16.     }
  18.     public String getDsName() {
  19.         return dsName;
  20.     }
  21. }

在service层中使用

  1. @Service
  2. public class TransferFailedServiceImpl implements TransferFailedService {
  3.     @Autowired
  4.     private TransferOrderMapper transferOrderMapper;
  6.     @Autowired
  7.     private ChannelInvokeRecordMapper channelInvokeRecordMapper;
  9.     @Autowired
  10.     private UserMapper userMapper;
  12.     @Override
  13.     public PageVO<TransferOrderVO> listTransferOrder(TransferOrderFilterParams params) {
  15.         //判断userId是否为空
  16.         if (StringUtils.isNotBlank(params.getUserId())) {
  17.             User userInfo = userMapper.selectUserByUserId(params.getUserId());
  18.             if (userInfo != null) {
  19.                 params.setUserId(userInfo.getUid());
  20.             }
  21.         }
  23.         PageHelper.startPage(params.getPageNum(), params.getPageSize());
  24.         //查询数据库firstdb
  25.         List<TransferOrder> transferOrders = transferOrderMapper.listTransferOrder(params.getBizOrderNo(), params.getUserId());
  26.         PageInfo pageInfo = new PageInfo<>(transferOrders);
  27.         List<TransferOrderVO> voList = OrikaMapperUtils.mapList(transferOrders, TransferOrder.class,
  28.                 TransferOrderVO.class);
  29.         for (TransferOrderVO order : voList) {
  30.             order.setUserId(order.getAccount());
  31.             //查询数据库firstdb
  32.             ChannelInvokeRecord channelInvokeRecord = channelInvokeRecordMapper.selectCirByBizOrderNo(order.getBizOrderNo());
  33.             if (channelInvokeRecord != null) {
  34.                 //失败原因
  35.                 String remark = JSON.parseObject(channelInvokeRecord.getRemark()).getString("subMsg");
  36.                 order.setSubMsg(remark);
  37.             }
  38.         }
  40.         for (TransferOrderVO order : voList) {
  41.             //查询数据库seconddb
  42.             User user = userMapper.selectUserByUserId(order.getAccount());
  43.             if (user != null) {
  44.                 order.setUserId(String.valueOf(user.getUserId()));
  45.             }
  46.         }
  47.         return new PageVO(params.getPageSize(), params.getPageNum(), pageInfo.getTotal(), voList);
  48.     }
  49. }

参考

更多其他的方式可以查看其他博主记录的，如下：