mysql数据库读写分离

基于Spring mvc + Spring + Mybatis实现数据库读写分离

1.配置主从数据库,进行读写分离设置xml中配置数据库连接信息时使用 ${jdbc.master.password}，参考ssm项目中配置属性获取，也可直接写在xml中

<bean id="masterDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.master.username}"/>
        <property name="password" value="${jdbc.master.password}"/>
        <property name="driverClass" value="${jdbc.master.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.master.url}"/>
        <property name="maxPoolSize" value="20"/>
		<property name="minPoolSize" value="2"/>
        <property name="initialPoolSize" value="2"/>
        <property name="maxIdleTime" value="30"/>
        <property name="checkoutTimeout" value="5000"/>
        <property name="acquireIncrement" value="2"/>
		<property name="acquireRetryAttempts" value="0"/>
		<property name="acquireRetryDelay" value="1000" />
		<property name="autoCommitOnClose" value="false"/>
  		<property name="preferredTestQuery" value="SELECT 1" />
        <property name="breakAfterAcquireFailure" value="false" />
		<property name="idleConnectionTestPeriod" value="60" />
        <property name="maxStatements" value="100" />
     </bean>

<bean id="slaveDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.slave.username}"/>
        <property name="password" value="${jdbc.slave.password}"/>
        <property name="driverClass" value="${jdbc.slave.driverClassName}"/>
        <property name="jdbcUrl" value="${jdbc.slave.url}"/>
        <property name="maxPoolSize" value="20"/>
		<property name="minPoolSize" value="2"/>
        <property name="initialPoolSize" value="2"/>
        <property name="maxIdleTime" value="30"/>
        <property name="checkoutTimeout" value="5000"/>
        <property name="acquireIncrement" value="2"/>
		<property name="acquireRetryAttempts" value="0"/>
		<property name="acquireRetryDelay" value="1000" />
		<property name="autoCommitOnClose" value="false"/>
  		<property name="preferredTestQuery" value="SELECT 1" />
        <property name="breakAfterAcquireFailure" value="false" />
		<property name="idleConnectionTestPeriod" value="60" />
        <property name="maxStatements" value="100" />
     </bean>

2.进行dataSource配置

<bean id="dataSource" class="com.ihxlife.database.db.DynamicDataSource">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
				<!-- key值不能修改,value-ref可以修改 -->
				<!-- master -->
				<entry key="master" value-ref="masterDataSource" />
				<!-- slave -->
				<entry key="slave" value-ref="slaveDataSource" />
			</map>
		</property>
		<property name="defaultTargetDataSource" ref="masterDataSource" />
	</bean>

3.使用Spring的AbstractRoutingDataSource类来实现读写分离

1) 继承AbstractRoutingDataSource类,重写determineCurrentLookupKey方法

public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		 return DynamicDataSourceHolder.getDataSouce();  
	}
}

2) 编写DynamicDataSourceHolder类,使用ThreadLocal实现数据库绑定

public class DynamicDataSourceHolder {
	 public static final ThreadLocal<String> holder = new ThreadLocal<String>();  
	    
	    public static void setDataSource(String dataSourceValue) {
	    	holder.set(dataSourceValue);
	    }
	  
	    public static String getDataSouce() {  
	        return holder.get();  
	    }  
}

4.使用Spring的AOP实现读写数据库的动态切换

1) 自定义datasource注解

@Target({ElementType.PARAMETER, ElementType.METHOD})  
@Retention(RetentionPolicy.RUNTIME) 
@Documented
public @interface DataSourceAnnotation {
	String dataSourceValue(); 
}

2)配置AOP实现读写数据库的动态绑定

@Aspect
@Component
public class DataSourceAspect {
	
	// 记录日志
	private static final Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);
	
	// Service层切点，根据需要修改
	@Pointcut("execution(* com.example.*.dao.*.mapper.*.*(..))")
	public void dataSourceAspect() {
	}
	
	/**
	 * 调用前选择查询、读写数据库
	 * @param point
	 */
	@Before("dataSourceAspect()")
	public void doBefore(JoinPoint joinPoint) throws Throwable {  
        Object target = joinPoint.getTarget();  
        String method = joinPoint.getSignature().getName();  
        String className = joinPoint.getSignature().getDeclaringTypeName();
        Class<?>[] classz = target.getClass().getInterfaces();  
        Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();  
        String dataSourceValue = "";
        Method m = classz[0].getMethod(method, parameterTypes);  
        if (m != null && m.isAnnotationPresent(DataSourceAnnotation.class)) {  
        	dataSourceValue = m.getAnnotation(DataSourceAnnotation.class).dataSourceValue();  
            logger.info("类【{}】中的方法:【{}】使用数据源【{}】",className, method,dataSourceValue);
          //mybatis自动生成类判断
        } else if(method.toUpperCase().indexOf("INSERT") == 0 || method.toUpperCase().indexOf("DELETE") == 0 ||method.toUpperCase().indexOf("UPDATE") == 0){
        	dataSourceValue = "master";
        }else if(method.toUpperCase().indexOf("QUERY") == 0 || method.toUpperCase().indexOf("SELECT") == 0 || method.toUpperCase().indexOf("COUNT") == 0 || method.toUpperCase().indexOf("FIND") == 0){
        	dataSourceValue = "slave";  
        }else{
        	dataSourceValue = "";
        }
        if(!dataSourceValue.equals("")){
        	DynamicDataSourceHolder.setDataSource(dataSourceValue);
        }else{
        	throw new RuntimeException("类【{" + className +"}】中的方法:【{" + method + "}】未获取到数据源");
        }        
    } 
}