mysql的读写分离

1、搭建好mysql集群分别为：192.168.227.129（master）     192.168.227.129（slave）    192.168.227.129(slave)

2、构建多个连接池

  <!--主库-->
    <bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://192.168.227.129:3306/lch"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        </bean>
    <!--从库1-->
    <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://192.168.227.130:3306/lch"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    </bean>

    <!--从库2-->
    <bean id="dataSource3" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://192.168.227.131:3306/lch"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    </bean>

3、DynamicDataSource动态分配连接池该类为自定义类继承自AbstractRoutingDataSource //determineCurrentLookupKey方法返回的结果就是要原则的库

<!--动态分配连接池-->
    <bean id="dataSource" class="dynamicDataSource.DynamicDataSource">
        <property name="targetDataSources">
            <map>
                <entry key="slave_db1" value-ref="dataSource2"/>
                <entry key="slave_db2" value-ref="dataSource3"/>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="dataSource1"/>
    </bean>

3、DynamicDataSource 

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // TODO Auto-generated method stub
        return DataSourceHelper.determine();
    }
}

4、DataSourceHelper(辅助类  从线程中获取当前操作类型，若是读操作 随机返回一个slave(负载策略可以自定义)否则返回master)

public class DataSourceHelper {
    private static List<String> slaveDBKeys;
    static{
        slaveDBKeys=new ArrayList<String>();
        slaveDBKeys.add("slave_db1");
        slaveDBKeys.add("slave_db2");
    }
    public static Object determine() {
        //获取当前上下文操作类型
        OperType currentOperType = OperTypeHolder.getCurrentOperType();
        if(currentOperType==null)
            return "NONE";
        if(currentOperType.equals(OperType.READ)){//如果是从库返回从
            return slaveDBKeys.get(new Random().nextInt(slaveDBKeys.size()));
        }else{//如果是非读 返回 "NONE"
            return "NONE";
        }
    }
}

5、OperTypeHolder(绑定现场工具类 提供绑定线程，获取绑定的对象以及清空绑定方法)

public class OperTypeHolder {
    private  static  final  ThreadLocal<OperType>  THREADLOCAL_LOCAL=new ThreadLocal<OperType>();
    public static void setCurrentOperType(OperType ot){
        THREADLOCAL_LOCAL.set(ot);
    }
    public static OperType getCurrentOperType(){
        return THREADLOCAL_LOCAL.get();
    }
    public static void clear(){
        THREADLOCAL_LOCAL.remove();
    }
}

6、OperType（枚举类型 有两个常量 READ ERITE）

public enum OperType {
    READ("read"),WRITE("write");
    private String operType;
    private OperType(String oper){
        this.operType=oper;
    }
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return operType;
    }
}

7、SlaveDB(自定义注解 一个标记 用于标记读操作)

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface SlaveDB { }

8、aop（切方法 所有方法 执行前 先获取注解看是否有@SlaveDB注解若有 线程绑定OperType.READ 否则 线程绑定 OperType.WRITE 执行方法 ==》【若访问数据库会执行DynamicDataSource的determineCurrentLookupKey方法选择数据库】执行方法 清空线程 返回结果 ）

@Aspect
@Component
public class DataSourceDeterminAOP {
    @Around("execution(* com.lch.service..*.*(..))")
    public Object around(ProceedingJoinPoint proceed)throws Throwable{

        System.out.println("开始拦截===============================");

        MethodSignature signature=(MethodSignature)proceed.getSignature();
        Method method = signature.getMethod();
        if (method.isAnnotationPresent(SlaveDB.class)){
            OperTypeHolder.setCurrentOperType(OperType.READ);
        }else {
            OperTypeHolder.setCurrentOperType(OperType.WRITE);
        }
        Object proceed1 = proceed.proceed();

        System.out.println("方法通过========================");

        OperTypeHolder.clear();
        return proceed1;
    }
}