SpringMVC主从数据库切换

配置文件中添加数据库配置信息

<!-- 定义数据库数据源 -->
    <bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass" value="${master.spay.db.driverClass}" />
        <property name="jdbcUrl" value="${master.spay.db.url}" />
        <property name="user" value="${master.spay.db.username}" />
        <property name="password" value="${master.spay.db.password}" />
        <property name="autoCommitOnClose" value="${master.spay.db.autoCommitOnClose}" />
        <property name="initialPoolSize" value="${master.spay.db.pool.initialPoolSize}" />
        <property name="minPoolSize" value="${master.spay.db.pool.minPoolSize}" />
        <property name="maxPoolSize" value="${master.spay.db.pool.maxPoolSize}" />
        <property name="maxIdleTime" value="${master.spay.db.pool.maxIdleTime}" />
        <property name="maxIdleTimeExcessConnections" value="${master.spay.db.pool.maxIdleTimeExcessConnections}" />
        <property name="idleConnectionTestPeriod" value="${master.spay.db.pool.idleConnectionTestPeriod}" />
        <property name="acquireIncrement" value="${master.spay.db.pool.acquireIncrement}" />
        <property name="testConnectionOnCheckout" value="${master.spay.db.pool.testConnectionOnCheckout}" />
        <property name="checkoutTimeout" value="${master.spay.db.pool.checkoutTimeout}" />
        <property name="acquireRetryAttempts" value="${master.spay.db.pool.acquireRetryAttempts}" />
        <property name="maxStatements" value="${master.spay.db.pool.maxStatements}" />
        <property name="maxStatementsPerConnection" value="${master.spay.db.pool.maxStatementsPerConnection}" />
        <property name="preferredTestQuery" value="${master.spay.db.pool.preferredTestQuery}" />
    </bean>
    
    <bean id="slaveDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        .......
    </bean> 
    
    
    <bean id="dataSource" class="cn.manager.dbconfig.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <entry key="read" value-ref="slaveDataSource"></entry>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="masterDataSource"></property>
    </bean>
    
    <bean id="sqlSessionFactory" class="cn.mybatis.springext.PathSqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        .....
        <property name="plugins">
            <list>
                <bean class="cn.swiftpass.core.common.mybatis.pagehelper.PageHelper">
                    <property name="properties">
                        <value>
                            dialect=mysql
                            reasonable=true
                        </value>
                    </property>
                </bean>
            </list>
        </property> 
    </bean>
    

自定义注解

可以添加在方法上指定是走主库还是从库，不添加则根据方法名选择

/**
 * 
 * @author Honva
 * @Description 数据库切换标记注解
 */
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    public String value();
}

数据库适配类

/**
 * 
 * @author Honva
 * @Description 数据源
 */
public class DbContextHolder {
    public static final String WRITE = "write";
    public static final String READ = "read";
    private  static ThreadLocal<String> dataSource = new ThreadLocal<String>();
    
    public static void set(String name){
        dataSource.set(name);
    }
    public static String get(){
        return dataSource.get();
    }
    public static void remove(){
        dataSource.remove();
    }
}

自定义切面

自定义切面入口，在执行方法前先判断方法名，再选择主从库

/**
 * @author Honva
 * @Description 数据库切换切面
 */
@Aspect
@Component
public class DbAspect {
    private String PRE_PAGING = "paging";
    private String PRE_GET = "get";
    private String PRE_FIND = "find";

    @Before("execution(* cn.manager.mapper..*.*(..))")
    public void dbAspect(JoinPoint point) throws Exception {
        MethodSignature s = (MethodSignature) point.getSignature();
        Method method = s.getMethod();
        String methodName = method.getName();
        DataSource dataSource = method.getAnnotation(DataSource.class);
        if (dataSource != null) {
            DbContextHolder.set(dataSource.value());
        } else if (methodName.startsWith(PRE_PAGING) || methodName.startsWith(PRE_GET)
                || methodName.startsWith(PRE_FIND)) {
            DbContextHolder.set(DbContextHolder.READ);
        } else {
            DbContextHolder.set(DbContextHolder.WRITE);
        }
    }
}

动态数据库切换类（重点类）

/**
 * 
 * @author Honva
 * @Description 动态数据源切换
 */
public class DynamicDataSource extends AbstractRoutingDataSource{
    
    @Override
    protected Object determineCurrentLookupKey() {
        String key = DbContextHolder.get();
        try {
            if(key==null || key.equals(DbContextHolder.WRITE)){
                return null;
            }
            if(key.equals(DbContextHolder.READ)){
                try {
                    Field field = this.getClass().getSuperclass().getDeclaredField("targetDataSources");    field.setAccessible(true);
                    @SuppressWarnings("unchecked")
                    Map<Object,Object> targetDataSources = (Map<Object, Object>) field.get(this);
                    if(targetDataSources.isEmpty()){
                        return DbContextHolder.WRITE;
                    }
                    List<Object> list = new ArrayList<Object>(targetDataSources.keySet());
                    Object object = list.get((UUID.randomUUID().hashCode() & 0x7FFFFFFF) % list.size());
                    return object;
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
            return null;
        }finally{
            DbContextHolder.remove();
        }
    }
}

this.getClass().getSuperclass().getDeclaredField("targetDataSources")是为了获取父类targetDataSources属性。

field.setAccessible(true)，因为此变量是private修饰，所以需要改变其属性变成可获取。

散列算法:UUID.randomUUID().hashCode() & 0x7FFFFFFF),UUID随机生成哈希值会生成一个10位的随机数字（有正负值），&0x7FFFFFFF是为了避免出现负数。

主从数据库切换成功后，事务失效的话，可参考此篇文章
Spring主从数据库切换，事务失效