spring-master-slave-commondao

互联网的web项目，都有个特点：请求的并发量高，其中请求最耗时的db操作，又是系统优化的重中之重。

为此，往往搭建 db的 一主多从库的 数据库架构。作为web的DAO层，要保证针对主库进行写操作，对多个从库进行读操作。当然在一些请求中，为了避免主从复制的延迟导致的数据不一致性，部分的读操作也要到主库上。（这种需求一般通过业务垂直分开，比如下单业务的代码所部署的机器，读去应该也要从主库读取数据，这块需要定制化）

结合自己在实际项目中的使用的，我分享下一个简单的DAO进行一主多从库的读(取模型负载均衡)写操作的案例：

1、实际项目中的db架构（当然这块的配置交给dba即可）
master库：
server1:common库 以及多个customer库
server2：多个customer库

slave库：
server1机器上的库对于的从库在server3,server4,server5
server2机器上的库对于的从库在server6,server7,server8

2、关于数据库连接和数据源（Connection和DataSource）
Connection ：是和服务器上的一个mysql实例的连接，可以指定库，也可以不指定。 如果不指定库，那么在db操作之前，需要先 执行：use dbname; 或者在在表的名字之前加上dbName.dbTable。
DataSource：是一个数据源，允许其实现进行数据库连接的管理和复用，考虑db连接的建立是一个相当耗时的过程，数据源能提升非常大的性能。
确定一个数据源只需要：ip+port，务必清楚这一点。

3、dao的分析和设计。
功能要求：支持一主多从，灵活配置，支持多种数据源提供商
实现思路：根据dbname->ip+port->DataSource->Connection
其中：
主库：dbname->ip+port
从库为：dbname->List<ip+port>，然后负载均衡选择其中一个ip+port

数据源配置的代码：

package com.job.db.dataservice.datasource;

import java.util.List;

import javax.sql.DataSource;

/**
 * 一主多从的配置包装类
 * @author [e-mail]
 */
public class MasterSlaveDataSourceMapping {
	
	private List<MasterSlaveDataSourceMappingItem> list;
	
	public List<MasterSlaveDataSourceMappingItem> getList() {
		return list;
	}

	public void setList(List<MasterSlaveDataSourceMappingItem> list) {
		this.list = list;
	}

	/** 主从库配置项
	 */
	public static class MasterSlaveDataSourceMappingItem{
		/** 主库数据源*/
		private DataSource master;
		/** 从库数据源列表*/
		private List<DataSource> slaveList;
		public DataSource getMaster() {
			return master;
		}
		public void setMaster(DataSource master) {
			this.master = master;
		}
		public List<DataSource> getSlaveList() {
			return slaveList;
		}
		public void setSlaveList(List<DataSource> slaveList) {
			this.slaveList = slaveList;
		}
		
	}
}

4、数据源初始化的代码。

package com.job.db.dataservice.datasource;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.job.db.dataservice.datasource.MasterSlaveDataSourceMapping.MasterSlaveDataSourceMappingItem;

/**
 * 数据源上下文
 * @author [e-mail]
 * @date 2014-7-14下午5:02:14
 */
public abstract class DataSourceContext {
	private static final Logger logger = LoggerFactory.getLogger(DataSourceContext.class);
    /**
     * 主库数据源     (ip+port->IDataSourceProvider)*/
    protected  static final Map<String, IDataSourceProvider> masterMap = new HashMap<String, IDataSourceProvider>();
    /** 主库数据库名称到ip+port的映射 (dbNmae->ip+port)*/
    protected  static final Map<String,String> masterDb2IpPortMapping = new HashMap<String,String>();
    
    /** 从库数据源  (ip+port->IDataSourceProvider)*/
    protected  static  final Map<String, IDataSourceProvider> slaveMap = new HashMap<String, IDataSourceProvider>();
    /** 从库数据库名称到ip+port的映射*/
    protected  static final Map<String,List<String>> slaveDb2IpPortListMapping = new HashMap<String,List<String>>();
    
    /** 主从数据源列表配置*/
    private MasterSlaveDataSourceMapping masterSlaveDataSourceMapping;
    
    /**
     * 初始化资源
     */
    public void init(){
    	logger.info("DataSourceContext init begin");
    	List<MasterSlaveDataSourceMappingItem> masterSlaveList = masterSlaveDataSourceMapping.getList();
    	for(MasterSlaveDataSourceMappingItem item : masterSlaveList){
    		DataSource masterConfigList = item.getMaster();
    		masterInit(masterConfigList);
    		
    		List<DataSource>  slaveConfigList = item.getSlaveList();
    		slaveInit(slaveConfigList);
    	}
    	logger.info("DataSourceContext init end");
    }
    
    /** 初始化主库资源*/
    private final void masterInit(DataSource dataSource) {
    	doMasterInit(dataSource);
    }
    
    /** 初始化从库资源*/
    private final void slaveInit(List<DataSource> dataSourceList) {
    	for(DataSource dataSource : dataSourceList){
    		doSlaveInit(dataSource);
    	}
    }
    
    protected abstract void doMasterInit(DataSource dataSource);
    
    protected abstract void doSlaveInit(DataSource dataSource);
    
    /**
     * 关闭资源
     */
    public void shutDown() {
    	logger.info("DataSourceContext shutDown begin ");
        for (IDataSourceProvider provider : masterMap.values()) {
            provider.shutdown();
        }
        Collection<IDataSourceProvider> listCollection = slaveMap.values();
        for (IDataSourceProvider provider: listCollection) {
                provider.shutdown(); 
        }
        logger.info("DataSourceContext shutDown end ");
    }

    /** 根据dbname获取主库数据源*/
    public static IDataSourceProvider getMasterDataSourceProvider(String dbName){
    	String ipPortKey = masterDb2IpPortMapping.get(dbName);
    	if(ipPortKey == null){
    		return null;
    	} else {
    		return masterMap.get(ipPortKey);
    	}
    }
    
    /** 根据数据库的名字获取多个从库数据源列表*/
    public static  List<IDataSourceProvider> getSlaveDataSourceProvider(String dbName){
    	List<String> ipPortKeyList = slaveDb2IpPortListMapping.get(dbName);
    	if(ipPortKeyList == null){
    		return null;
    	} else {
    		List<IDataSourceProvider> retList = new ArrayList<IDataSourceProvider>();
    		for(String item : ipPortKeyList){
    			IDataSourceProvider provider = slaveMap.get(item);
    			retList.add(provider);
    		}
    		return retList;
    	}
    }

	public void setMasterSlaveDataSourceMapping(
			MasterSlaveDataSourceMapping masterSlaveDataSourceMapping) {
		this.masterSlaveDataSourceMapping = masterSlaveDataSourceMapping;
	}
    
}

数据初始化的一个具体实现：

package com.job.db.dataservice.datasource.impl;

import java.util.ArrayList;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang.StringUtils;

import com.job.db.dataservice.datasource.DataSourceContext;

/**
 *  简单实现（TODO BasicDataSource 很粗糙的哈）
 * @author wangxinchun
 */
public class DefaultDataSourceContext extends DataSourceContext {

	protected void doMasterInit(DataSource dataSource) {
		URLInfo urlInfo = getDataSourceURLInfo(dataSource);
		String key = urlInfo.getDataSourceKey();
		if(StringUtils.isNotEmpty(urlInfo.getDb())){
			masterDb2IpPortMapping.put(urlInfo.getDb(), key);
		}
		if(masterMap.get(key) != null){
			return;
		}
		masterMap.put(key, new DefaultDatasourceProvider(dataSource));
	}

	protected void doSlaveInit(DataSource dataSource) {
		URLInfo urlInfo = getDataSourceURLInfo(dataSource);
		String key = urlInfo.getDataSourceKey();
		if(StringUtils.isNotEmpty(urlInfo.getDb())) {
			if(slaveDb2IpPortListMapping.get(urlInfo.getDb()) == null){
				slaveDb2IpPortListMapping.put(urlInfo.getDb(), new ArrayList<String>());
			}
			slaveDb2IpPortListMapping.get(urlInfo.getDb()).add(key);
		}
		if(slaveMap.get(key) != null){
			return;
		}
		slaveMap.put(key, new DefaultDatasourceProvider(dataSource));
	}

	private URLInfo getDataSourceURLInfo(DataSource dataSource) {
		URLInfo urlInfo = new URLInfo();
		if (dataSource instanceof BasicDataSource) {
			// jdbc:mysql://192.168.229.37:3309/tts?useUnicode=true&characterEncoding=utf8
			BasicDataSource basicDataSource = (BasicDataSource) dataSource;
			String url = basicDataSource.getUrl();
			String temp = url.substring(url.indexOf("//")+2);
			if(temp.indexOf("?") >0 ){
				temp = temp.substring(0,temp.indexOf("?"));
			}
			String[] tempArr = temp.split(":|/");
			urlInfo.setIp(tempArr[0]);
			urlInfo.setPort(tempArr[1]);
			urlInfo.setDb(tempArr[2]);
		}
		return urlInfo;
	}
	
	 private class URLInfo{
		 private String ip;
		 private String port;
		 private String db;
		 
		public String getIp() {
			return ip;
		}
		public void setIp(String ip) {
			this.ip = ip;
		}
		public String getPort() {
			return port;
		}
		public void setPort(String port) {
			this.port = port;
		}
		public String getDb() {
			return db;
		}
		public void setDb(String db) {
			this.db = db;
		}
		public String getDataSourceKey(){
			return ip+":"+port;
		}
	 }
}

package com.job.db.dataservice.datasource.impl;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.job.db.dataservice.datasource.IDataSourceProvider;

/**
 * Proxool 连接池包装类
 * @author [e-mail]
 * @date 2014-7-15下午2:14:08
 */
public class DefaultDatasourceProvider implements IDataSourceProvider {

    private static Logger log = Logger.getLogger(DefaultDatasourceProvider.class);

    private DataSource dataSource;

    
    public DefaultDatasourceProvider( DataSource dataSource){
    	this.dataSource = dataSource;
    }
    
    public DataSource getDataSource() {
        return dataSource;
    }

    public void shutdown() {
    	log.info("shutdown");
    }
    
}

5、spring 关于数据的配置。

 <bean id="parentDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" abstract="true">
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="timeBetweenEvictionRunsMillis" value="60000"/> <!-- 每隔毫秒秒检查一次连接池中空闲的连接 -->
		<property name="minEvictableIdleTimeMillis" value="600000" /> <!-- 连接池中连接可空闲的时间,毫秒 -->
		<property name="removeAbandoned" value="true" /> <!-- 是否清理removeAbandonedTimeout秒没有使用的活动连接,清理后并没有放回连接池 -->
		<property name="removeAbandonedTimeout" value="60" />  <!-- 活动连接的最大空闲时间 -->
		<property name="minIdle" value="10" /> <!-- 最小空闲连接数 -->
		<property name="maxWait" value="60000" /> <!-- 最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间 -->
	</bean>

 <!-- ******************* ******************* 公共库  （主从配置） ******************* ******************* -->
    <bean id="commonMaster" parent="parentDataSource">
		<property name="url" value="${jdbc.commonMaster.url}" />
		<property name="username" value="${jdbc.commonMaster.username}" />
		<property name="password" value="${jdbc.commonMaster.password}" />
	</bean>
	
	 <bean id="commonSlaveA" parent="parentDataSource">
		<property name="url" value="${jdbc.commonSlaveA.url}" />
		<property name="username" value="${jdbc.commonSlaveA.username}" />
		<property name="password" value="${jdbc.commonSlaveA.password}" />
	</bean>
	
	<bean id="commonSlaveB" parent="parentDataSource">
		<property name="url" value="${jdbc.commonSlaveB.url}" />
		<property name="username" value="${jdbc.commonSlaveB.username}" />
		<property name="password" value="${jdbc.commonSlaveB.password}" />
	</bean>

 <bean id="commonMasterSlaveMapping" class="com.job.db.dataservice.datasource.MasterSlaveDataSourceMapping.MasterSlaveDataSourceMappingItem">
    <property name="master">
       <ref bean="commonMaster"/>
    </property>
    <property name="slaveList">
        <list>
           <ref bean="commonSlaveA"/>
           <ref bean="commonSlaveB"/>
        </list>
    </property>
 </bean>
 <!-- *************************************** 代理商库 A 组*************************************** -->
     <bean id="customerMasterA" parent="parentDataSource">
		<property name="url" value="${jdbc.customerMasterA.url}" />
		<property name="username" value="${jdbc.customerMasterA.username}" />
		<property name="password" value="${jdbc.customerMasterA.password}" />
	</bean>
	
	 <bean id="customerSlaveAA" parent="parentDataSource">
		<property name="url" value="${jdbc.customerSlaveAA.url}" />
		<property name="username" value="${jdbc.customerSlaveAA.username}" />
		<property name="password" value="${jdbc.customerSlaveAA.password}" />
	</bean>
	
	<bean id="customerSlaveAB" parent="parentDataSource">
		<property name="url" value="${jdbc.customerSlaveAB.url}" />
		<property name="username" value="${jdbc.customerSlaveAB.username}" />
		<property name="password" value="${jdbc.customerSlaveAB.password}" />
	</bean>

 <bean id="customerMasterSlaveMappingA" class="com.job.db.dataservice.datasource.MasterSlaveDataSourceMapping.MasterSlaveDataSourceMappingItem">
    <property name="master">
       <ref bean="customerMasterA"/>
    </property>
    <property name="slaveList">
        <list>
           <ref bean="customerSlaveAA"/>
           <ref bean="customerSlaveAB"/>
        </list>
    </property>
 </bean>
    
 <!-- ***************************************代理商库 B组  *************************************** -->
      <bean id="customerMasterB" parent="parentDataSource">
		<property name="url" value="${jdbc.customerMasterB.url}" />
		<property name="username" value="${jdbc.customerMasterB.username}" />
		<property name="password" value="${jdbc.customerMasterB.password}" />
	</bean>
	
	 <bean id="customerSlaveBA" parent="parentDataSource">
		<property name="url" value="${jdbc.customerSlaveBA.url}" />
		<property name="username" value="${jdbc.customerSlaveBA.username}" />
		<property name="password" value="${jdbc.customerSlaveBA.password}" />
	</bean>
	
	<bean id="customerSlaveBB" parent="parentDataSource">
		<property name="url" value="${jdbc.customerSlaveBB.url}" />
		<property name="username" value="${jdbc.customerSlaveBB.username}" />
		<property name="password" value="${jdbc.customerSlaveBB.password}" />
	</bean>

 <bean id="customerMasterSlaveMappingB" class="com.job.db.dataservice.datasource.MasterSlaveDataSourceMapping.MasterSlaveDataSourceMappingItem">
    <property name="master">
       <ref bean="customerMasterB"/>
    </property>
    <property name="slaveList">
        <list>
           <ref bean="customerSlaveBA"/>
           <ref bean="customerSlaveBB"/>
        </list>
    </property>
 </bean>
    
 <!-- **************************************主从关系总配置***************************************** -->
   <bean id="masterSlaveDataSourceMapping" class=" com.job.db.dataservice.datasource.MasterSlaveDataSourceMapping">
      <property name="list">
         <list>
             <ref bean="commonMasterSlaveMapping"/>
             <ref bean="customerMasterSlaveMappingA"/>
             <ref bean="customerMasterSlaveMappingB"/>
         </list>
      </property>
   </bean>
   

	<bean id="dataSourceContext" class="com.job.db.dataservice.datasource.impl.DefaultDataSourceContext" init-method="init" destroy-method="shutDown">
	   <property name="masterSlaveDataSourceMapping" ref="masterSlaveDataSourceMapping"/>
	</bean>

6、数据源的使用。

package com.job.db.dataservice.connection;

import java.sql.Connection;

/**
 * 提供Connection连接
 * @author wangxinchun
 */
public interface ConnectionGetter {

	/**
	 * 根据dbName获取数据库连接
	 * @param dbName 数据库的名字
	 */
    public Connection getConnection(String dbName);

}

通过数据源获取数据库连接

package com.job.db.dataservice.connection.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import com.job.db.dao.AppDBContext;
import com.job.db.dataservice.connection.ConnectionGetter;
import com.job.db.dataservice.datasource.DataSourceContext;
import com.job.db.dataservice.datasource.IDataSourceProvider;
import com.job.db.dataservice.exception.DaoException;

/**
 * 提供根据dbName 获取读或写连接
 * @author [e-mail]
 * @date 2014-7-15下午2:26:27
 */
public abstract class AbstractConnectionGetter implements ConnectionGetter {
	
	private static Integer concount =Integer.MIN_VALUE;
	
    public Connection getDefaultConnection(String dbName, boolean isWrite) throws SQLException {
        if (dbName == null) {
            throw new DaoException("get connection error cause dbName id null");
        }
		Integer id = AppDBContext.getLastReadWriteFlag();
		// 写操作直接获得connection ,如果上一次操作是写连接，那么优先返回写连接
		if ( isWrite || (id != null && id < 0)) {
			AppDBContext.setLastReadWriteFlag(-1);
            IDataSourceProvider provider = DataSourceContext.getMasterDataSourceProvider(dbName);
            if (provider == null) {
                throw new DaoException("no write database dbName is " + dbName);
            }
            return provider.getDataSource().getConnection();
        } else {
			// 读操作取模做balance 负载均衡
            List<IDataSourceProvider> providers = DataSourceContext.getSlaveDataSourceProvider(dbName);
            if (providers == null || providers.size() == 0) {
                throw new DaoException("no slave database for clientId " + dbName);
            }
            int psize = providers.size();
			if (id == null) {
				id = Math.abs(concount++ % psize);
				AppDBContext.setLastReadWriteFlag(id);
			}
			IDataSourceProvider sdp = providers.get(id % psize);
            return sdp.getDataSource().getConnection();
        }
    }
}

评论

2 楼 王新春 2014-10-16  

james_lover 写道

如果只是主从库的话，
我想：Spring: AbstractRoutingDataSource
这个能解决问题。

你说的这个是多数据源，而不是主从库。

1 楼 james_lover 2014-07-19  

如果只是主从库的话，
我想：Spring: AbstractRoutingDataSource
这个能解决问题。