使用spring的动态路由实现数据库负载均衡

最新推荐文章于 2022-11-13 22:07:40 发布

slimina

最新推荐文章于 2022-11-13 22:07:40 发布

阅读量2.2k

点赞数

分类专栏： Mysql Spring 文章标签： mysql spring

Spring 同时被 2 个专栏收录

29 篇文章 0 订阅

订阅专栏

Mysql

27 篇文章 1 订阅

订阅专栏

在spring2.0.1发布之前，各个项目中可能存在多种针对这种情况下的多数据源管理方式，不过，spring2.0.1发布之后，引入了AbstractRoutingDataSource，可以通过集成org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource类，自定义动态数据源。

配置如下： datasource-config.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beansxmlns="http://www.springframework.org/schema/beans"xmlns:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xmlns:aop="http://www.springframework.org/schema/aop"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans  
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd  
http://www.springframework.org/schema/context  
 http://www.springframework.org/schema/context/spring-context-2.5.xsd  
http://www.springframework.org/schema/aop   
http://www.springframework.org/schema/aop/spring-aop-2.5.xsd  
http://www.springframework.org/schema/tx   
http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
	<!-- 数据源配置 -->
	<beanid="dataSourceFirst"class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close">
		<propertyname="driverClassName"value="oracle.jdbc.driver.OracleDriver"/>
		<propertyname="url"value="jdbc:oracle:thin:@10.20.151.4:1521:ptdev"/>
		<propertyname="username"value="pt"/>
		<propertyname="password"value="pt"/>
		<propertyname="maxActive"value="200"/>
		<propertyname="maxIdle"value="5"/>
		<propertyname="poolPreparedStatements"value="true"/>
		<propertyname="removeAbandoned"value="true"/>
		<propertyname="removeAbandonedTimeout"value="300"/>
	</bean>
	<beanid="dataSourceSecond"class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close">
		<propertyname="driverClassName"value="oracle.jdbc.driver.OracleDriver"/>
		<propertyname="url"value="jdbc:oracle:thin:@10.20.151.12:1521:pt10g"/>
		<propertyname="username"value="pt"/>
		<propertyname="password"value="pt"/>
		<propertyname="maxActive"value="200"/>
		<propertyname="maxIdle"value="5"/>
		<propertyname="poolPreparedStatements"value="true"/>
		<propertyname="removeAbandoned"value="true"/>
		<propertyname="removeAbandonedTimeout"value="300"/>
	</bean>
	<beanid="dataSource"class="com.common.bean.RoutingDataSource">
		<propertyname="targetDataSources">
			<map>
				<entrykey="1"value-ref="dataSourceFirst"/>
				<entrykey="2"value-ref="dataSourceSecond"/>
			</map>
		</property>
		<propertyname="defaultTargetDataSource">
			<reflocal="dataSourceFirst"/>
		</property>
	</bean>
	<!--配置事物-->
	<beanid="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<propertyname="dataSource">
			<reflocal="dataSource"/>
		</property>
	</bean>
	<beanid="lobHandler"class="org.springframework.jdbc.support.lob.DefaultLobHandler"lazy-init="true"/>
	<beanid="sqlMapClient"class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<propertyname="dataSource"ref="dataSource"/>
		<propertyname="lobHandler"ref="lobHandler"/>
		<propertyname="configLocations"value="classpath*:/ibatis/config/sql-map.xml"/>
	</bean>
	<beanid="txAttributeSource"class="org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource">
		<propertyname="properties">
			<props>
				<propkey="add*">PROPAGATION_REQUIRED,-PtServiceException</prop>
				<propkey="update*">PROPAGATION_REQUIRED,-PtServiceException</prop>
				<propkey="delete*">PROPAGATION_REQUIRED,-PtServiceException</prop>
				<propkey="batch*">PROPAGATION_REQUIRED,-PtServiceException</prop>
				<propkey="get*">PROPAGATION_REQUIRED,-PtServiceException</prop>
			</props>
		</property>
	</bean>
	<beanid="transactionManagerProxy"class="org.springframework.aop.framework.ProxyFactoryBean">
		<propertyname="proxyTargetClass">
			<value>true</value>
		</property>
		<propertyname="target">
			<refbean="transactionManager"/>
		</property>
	</bean>
	<beanid="transactionDefinition"class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"abstract="true">
		<propertyname="transactionManager">
			<refbean="transactionManagerProxy"/>
		</property>
		<propertyname="transactionAttributeSource">
			<refbean="txAttributeSource"/>
		</property>
	</bean>
	<beanid="baseDao"class="com.common.dao.impl.BaseDaoImpl">
		<propertyname="sqlMapClient">
			<refbean="sqlMapClient"/>
		</property>
		<propertyname="dataSource">
			<refbean="dataSource"/>
		</property>
	</bean>
	<beanid="dbInfoService"parent="transactionDefinition">
		<propertyname="target">
			<beanclass="com.service.impl.DbInfoServiceImpl">
				<propertyname="baseDao"ref="baseDao"/>
			</bean>
		</property>
	</bean>
	<beanid="test"class="com.common.bean.Test">
		<propertyname="dbInfoService"ref="dbInfoService">
		</property>
	</bean>
</beans>

com.common.bean.RoutingDataSource类：

package com.common.bean;import java.sql.Connection;import java.sql.SQLException;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;publicclassRoutingDataSourceextendsAbstractRoutingDataSource{protectedObject determineCurrentLookupKey(){//获取当前线程处理的账号对应分片信息Shard shard =ThreadInfoHolder.getCurrentThreadShard();//动态选定DataSourceString dbId = shard ==null?null:String.valueOf(shard.getDbId());return dbId;}@OverridepublicString toString(){//获取当前线程处理的账号对应分片信息Shard shard =ThreadInfoHolder.getCurrentThreadShard();//动态选定DataSourceString dbId = shard ==null?null:String.valueOf(shard.getDbId());return"DB ID "+ dbId +":"+super.toString();}publicString getTargetDbId()throwsSQLException{Connection conn =null;try{//jdbc:oracle:thin:@10.20.151.4:1521:ptdev, UserName=xx, Oracle JDBC driver
            conn = determineTargetDataSource().getConnection();if(conn !=null){String connectionDesc = conn.getMetaData().getURL();int beginIdx = connectionDesc.indexOf("@")+1;int endIdx = connectionDesc.indexOf(":", beginIdx);return connectionDesc.substring(beginIdx, endIdx);}}finally{if(conn !=null){
                conn.close();}}returnnull;}}

com.common.bean.ThreadInfoHolder类如下：package com.common.bean;publicclassThreadInfoHolder{// thread local， 获取、存储本线程处理的账号对应分片信息privatestaticfinalThreadLocal<Shard> shardLocal =newThreadLocal<Shard>();/**
     * 获取当前线程处理的账号对应分片信息
     * 
     * @return
     */publicstaticShard getCurrentThreadShard(){returnThreadInfoHolder.shardLocal.get();}/**
     * 在当前线程存储账号对应分片信息
     * 
     * @param shard
     */publicstaticvoid addCurrentThreadShard(Shard shard){ThreadInfoHolder.shardLocal.set(shard);}/**
     * 清空前线程存储分片信息
     * 
     * @param shard
     */publicstaticvoid cleanCurrentThreadShard(){ThreadInfoHolder.shardLocal.remove();}}
com.common.bean.Shard类如下：publicclassShard{//存放Account数据的DB_IDprivateInteger            dbId;/**
     * @return the dbId
     */publicInteger getDbId(){return dbId;}/**
     * @param dbId the dbId to set
     */publicvoid setDbId(Integer dbId){this.dbId = dbId;}}

com.service.DbInfoService接口：package com.service;import java.util.List;import java.util.Map;import com.common.dao.model.User;publicinterfaceDbInfoService{publicList<Map<String,Object>> getUserInfo(User user);}

com.service.impl.DbInfoServiceImpl实现类：package com.service.impl;import java.util.List;import java.util.Map;import com.common.bean.Shard;import com.common.bean.ThreadInfoHolder;import com.common.dao.BaseDao;import com.common.dao.model.User;import com.service.DbInfoService;publicclassDbInfoServiceImplimplementsDbInfoService{publicBaseDao baseDao;publicvoid setBaseDao(BaseDao baseDao){this.baseDao = baseDao;}publicList<Map<String,Object>> getUserInfo(User user){

        baseDao.add("login.addUser", user);List<Map<String,Object>> result=baseDao.getList("login.getUserInfo",user.getName());return result;}}

com.common.bean.Test类：package com.common.bean;import java.util.List;import java.util.Map;import com.common.dao.model.User;import com.service.DbInfoService;publicclassTest{publicDbInfoService dbInfoService;publicvoid setDbInfoService(DbInfoService dbInfoService){this.dbInfoService = dbInfoService;}publicList<Map<String,Object>> getInfo(User user){List<Map<String,Object>> result=dbInfoService.getUserInfo(user);return result;}}

测试main方法如下：

package com.transaction;import java.util.List;import java.util.Map;import org.springframework.beans.factory.xml.XmlBeanFactory;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.core.io.ClassPathResource;import com.common.bean.Shard;import com.common.bean.Test;import com.common.bean.ThreadInfoHolder;import com.common.dao.model.User;import com.service.impl.DbInfoServiceImpl;publicclassTransactionTest{publicstaticvoid main(String[] args){ApplicationContext ctx =newClassPathXmlApplicationContext("classpath*:spring/datasource-config.xml");Test t=(Test)ctx.getBean("test");User user=newUser();
        user.setName("xj");
        user.setPassword("123");Shard shard=newShard();
        shard.setDbId(1);//使用datasource1ThreadInfoHolder.addCurrentThreadShard(shard);List<Map<String,Object>> result =t.getInfo(user);
        shard.setDbId(2);//使用datasource2ThreadInfoHolder.addCurrentThreadShard(shard);List<Map<String,Object>> result1 =t.getInfo(user);System.out.println(result);}}

运行结果是分别向数据库1和数据库2中插入了1调记录。

注意：

由于对DbInfoService配置了事物，如果将切换数据源的代码ThreadInfoHolder.addCurrentThreadShard(shard);放在在DbInfoServiceImpl类的getUserInfo方法中，如下：

publicList<Map<String,Object>> getUserInfo(User user){Shard shard=newShard();
    shard.setDbId(1);ThreadInfoHolder.addCurrentThreadShard(shard);
    baseDao.add("login.addUser", user);

    shard.setDbId(2);ThreadInfoHolder.addCurrentThreadShard(shard);
    baseDao.add("login.addUser", user);List<Map<String,Object>> result=baseDao.getList("login.getUserInfo",user.getName());return result;}

main方法改为：

publicstaticvoid main(String[] args){ApplicationContext ctx =newClassPathXmlApplicationContext("classpath*:spring/datasource-config.xml");Test t=(Test)ctx.getBean("test");User user=newUser();
    user.setName("xj");
    user.setPassword("123");List<Map<String,Object>> result =t.getInfo(user);System.out.println(result);}

则运行结果将是向数据库1中插入2条相同的记录，而不是分别想数据库1,2各插一条记录，产生该结果的原因是应为由于DbInfoServiceImpl配置了事物，所以在getUserInfo方法中的第一次连数据库会新建一个连接，而后将该连接绑定在线程的本地变量即ThreadLoad中，当以后在需要访问数据库时不在新建连接而是使用这个绑定了老连接，在本例子中，即第一次的数据库连接是连数据库1，当第二次访问数据库时，使用的还是这个数据库1的连接，即切换数据源设置代码shard.setDbId(2);ThreadInfoHolder.addCurrentThreadShard(shard);失效。同理我们可以退出，如果将切换数据源代码放在Test类的getInfo方法中，即：

publicList<Map<String,Object>> getInfo(User user){Shard shard=newShard();
    shard.setDbId(1);ThreadInfoHolder.addCurrentThreadShard(shard);List<Map<String,Object>> result=dbInfoService.getUserInfo(user);
    shard.setDbId(2);ThreadInfoHolder.addCurrentThreadShard(shard);List<Map<String,Object>> result1=dbInfoService.getUserInfo(user);return result;}

这样是能正确运行的，应为在调用事物前我们已经切换了数据源。

转至： http://outofmemory.cn/code-snippet/1459/usage-spring-dongtai-route-switch-zhucong-library

1. spring 使用AbstractRoutingDataSource自定义动态数据源时的事务处理

slimina

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
使用spring的动态路由实现数据库负载均衡

在spring2.0.1发布之前，各个项目中可能存在多种针对这种情况下的多数据源管理方式，不过，spring2.0.1发布之后，引入了AbstractRoutingDataSource，可以通过集成org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource类，自定义动态数据源。配置如下： datasource-
复制链接

扫一扫