一直做了互联网的小项目,感觉小公司的性能瓶颈主要在数据库端。大公司没去过,不清楚
~
一般用MySQL数据库做主从,读写分离,减少主库的压力。假设1主4从。4个从库每次的访问是随机,压力平摊。
先把搜来的贴出来。先记录下,再去code实验~
采用spring的AbstractRoutingDataSource就可以简单的解决这个问题。下面是用ibatis的。单独的spring mvc 实现也是用AbstractRoutingDataSource类
AbstractRoutingDataSource实现了javax.sql.DataSource接口,因此可以理解为一个虚拟的动态DataSource,在需要的时候根据上下文Context动态决定使用哪个数据源。
strut2 spring ibatis整合项目代码下载
下面这个是ibatis 的 没实验只是简单看看,不过每次 service都要
- DbContextHolder.setDbType("2");
从库多了哥们都设置晕了。稍后学习奉上根据事务管理直接动态随机分配的~
(9.21更新 。在最下面的第2部分是根据公司的项目整理出来的。测试通过,可以动态分配)
/******************************一 、复制来的例子 开始**********************************************/
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:aop="http://www.springframework.org/schema/aop"
- xmlns:tx="http://www.springframework.org/schema/tx"
- xmlns:jee="http://www.springframework.org/schema/jee"
- xsi:schemaLocation="
- http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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
- http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd">
-
-
-
- <jee:jndi-lookup id="ds0" jndi-name="jdbc/ds0"/>
- <jee:jndi-lookup id="ds1" jndi-name="jdbc/ds1"/>
- <jee:jndi-lookup id="ds2" jndi-name="jdbc/ds2"/>
-
- <bean id="dataSource" class="com.xxx.xxx.util.DynamicDataSource">
- <property name="targetDataSources">
- <map key-type="java.lang.String">
- <entry key="0" value-ref="ds0"/>
- <entry key="1" value-ref="ds1"/>
- <entry key="2" value-ref="ds2"/>
- </map>
- </property>
- <property name="defaultTargetDataSource" ref="1"/>
- </bean>
-
- <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
- <property name="dataSource" ref="dataSource"/>
- <property name="configLocation" value="classpath:com/xxx/xxx/dao/sqlmap/sql-map-config.xml"/>
- </bean>
- <bean id="testDAO" class="com.xxx.xxx.dao.impl.TestDAO">
- <property name="sqlMapClient" ref="sqlMapClient"/>
- </bean>
- <bean id="testService" class="com.xxx.xxx.service.impl.TestService">
- <property name="testDAO" ref="testDAO"/>
- </bean>
- </beans>
其核心是DynamicDataSource,代码如下
- package com.xxx.xxx.util;
-
- import org.apache.log4j.Logger;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
-
- public class DynamicDataSource extends AbstractRoutingDataSource {
-
- static Logger log = Logger.getLogger("DynamicDataSource");
- @Override
- protected Object determineCurrentLookupKey() {
-
- return DbContextHolder.getDbType();
- }
-
- }
上下文DbContextHolder为一线程安全的ThreadLocal,如下
- package com.xxx.xxx.util;
-
- public class DbContextHolder {
- private static final ThreadLocal contextHolder = new ThreadLocal();
-
- public static void setDbType(String dbType) {
- contextHolder.set(dbType);
- }
-
- public static String getDbType() {
- return (String) contextHolder.get();
- }
-
- public static void clearDbType() {
- contextHolder.remove();
- }
- }
sql-map-config.xml如下
- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
- <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
- "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
-
- <sqlMapConfig>
-
- <sqlMap resource="com/xxx/xxx/dao/sqlmap/Object.xml"/>
-
- </sqlMapConfig>
这样在调用service之前只需要设置一下上下文即可调用相应的数据源,如下:
- DbContextHolder.setDbType("2");
-
-
dao如下
- package com.xxx.xxx.dao.impl;
-
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
-
- import org.apache.log4j.Logger;
- import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
-
- import com.xxx.xxx.vo.TestObj;
-
- public class TestDAO extends SqlMapClientDaoSupport implements ITestDAO {
-
- static Logger log = Logger.getLogger(TestDAO.class);
-
- public TestObj getTestObj(String objID) throws Exception {
- return (TestObj) getSqlMapClientTemplate().queryForObject("getTestObj", objID);
- }
- }
/******************************一 、复制来的例子 结束**********************************************/
/********************************************二、动态分配数据源 spring2.5 +ibatis 开始*************************************************/
application中db的配置
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
- xmlns:aop="http://www.springframework.org/schema/aop"
- xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">
-
- <bean id="propertyConfigurer"
- class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
- <property name="locations">
- <list>
- <value>classpath:/jdbc.properties</value>
- </list>
- </property>
- </bean>
-
-
- <bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
- destroy-method="close" abstract="true">
- <property name="driverClass">
- <value>${jdbc.driverClassName}</value>
- </property>
- <property name="jdbcUrl">
- <value>${jdbc.url}</value>
- </property>
- <property name="user">
- <value>${jdbc.username}</value>
- </property>
- <property name="password">
- <value>${jdbc.password}</value>
- </property>
- <property name="maxPoolSize">
- <value>${jdbc.maxPoolSize}</value>
- </property>
- <property name="minPoolSize">
- <value>${jdbc.minPoolSize}</value>
- </property>
- <property name="initialPoolSize">
- <value>${jdbc.initialPoolSize}</value>
- </property>
- <property name="idleConnectionTestPeriod">
- <value>${jdbc.idleConnectionTestPeriod}
- </value>
- </property>
- <property name="maxIdleTime">
- <value>${jdbc.maxIdleTime}</value>
- </property>
- </bean>
-
- <bean id="writedb" parent="parentDataSource"></bean>
-
- <bean id="read02" destroy-method="close" parent="parentDataSource">
- <property name="jdbcUrl">
- <value>${jdbc.read.db02.url}</value>
- </property>
- <property name="user">
- <value>${jdbc.read.db02.username}</value>
- </property>
- <property name="password">
- <value>${jdbc.read.db02.password}</value>
- </property>
- </bean>
-
- <bean id="read03" destroy-method="close" parent="parentDataSource">
- <property name="jdbcUrl">
- <value>${jdbc.read.db03.url}</value>
- </property>
- <property name="user">
- <value>${jdbc.read.db03.username}</value>
- </property>
- <property name="password">
- <value>${jdbc.read.db03.password}</value>
- </property>
- </bean>
-
-
-
- <bean id="dataSource" class="com.share.common.database.DataSourceRouter">
- <property name="targetDataSources">
- <map key-type="java.lang.String">
- <entry key="writedb" value-ref="writedb" />
- <entry key="read02" value-ref="read02" />
- <entry key="read03" value-ref="read03" />
- </map>
- </property>
- <property name="defaultTargetDataSource" ref="writedb" />
- <property name="dataSourceKey">
- <ref local="dataSourceKey" />
- </property>
- </bean>
-
-
- <bean id="dataSourceKey" class="com.share.common.database.DataSourceKeyImpl">
- <property name="readDateSourceMap">
- <map key-type="java.lang.String">
- <entry key="read02" value="read02" />
- <entry key="read03" value="read03" />
-
- </map>
- </property>
- <property name="writedbKey">
- <value>writedb</value>
- </property>
- </bean>
-
-
- <bean id="transactionManager"
- class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
- <property name="dataSource" ref="dataSource"></property>
- </bean>
-
-
- <bean id="transactionInterceptor"
- class="org.springframework.transaction.interceptor.TransactionInterceptor">
- <property name="transactionManager" ref="transactionManager" />
- <property name="transactionAttributes">
- <props>
- <prop key="query*">PROPAGATION_SUPPORTS,-Exception</prop>
- <prop key="select*">PROPAGATION_SUPPORTS,-Exception</prop>
- <prop key="find*">PROPAGATION_SUPPORTS,-Exception</prop>
- <prop key="get*">PROPAGATION_SUPPORTS,-Exception</prop>
-
- <prop key="save*">PROPAGATION_REQUIRED,-Exception</prop>
- <prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>
- <prop key="delete*">PROPAGATION_REQUIRED,-Exception</prop>
- <prop key="add*">PROPAGATION_REQUIRED,-Exception</prop>
- <prop key="edit*">PROPAGATION_REQUIRED,-Exception</prop>
- <prop key="*">PROPAGATION_SUPPORTS,-Exception</prop>
- </props>
- </property>
- </bean>
-
-
-
- <bean id="dataSourceInterceptor" class="com.share.common.database.DataSourceInterceptor">
- <property name="attributes">
- <props>
- <prop key="query*">readdb</prop>
- <prop key="select*">readdb</prop>
- <prop key="find*">readdb</prop>
- <prop key="get*">readdb</prop>
- <prop key="save*">writedb</prop>
- <prop key="update*">writedb</prop>
- <prop key="delete*">writedb</prop>
- <prop key="add*">writedb</prop>
- <prop key="edit*">writedb</prop>
- <prop key="*">readdb</prop>
- </props>
- </property>
- <property name="dataSourceKey">
- <ref bean="dataSourceKey" />
- </property>
- </bean>
-
-
- <bean
- class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
- <property name="beanNames">
- <list>
- <value>*Service</value>
- </list>
- </property>
- <property name="interceptorNames">
- <list>
- <value>dataSourceInterceptor</value>
- </list>
- </property>
- </bean>
-
- <bean id="sqlMapClient"
- class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
- <property name="configLocation"
- value="classpath:/config/ibatis/SqlMapConfig.xml"/>
- <property name="dataSource" ref="dataSource"/>
- </bean>
-
- <bean id="simpleDao" class="com.share.common.dao.IbatisSimpleDaoImpl">
- <property name="sqlMapClient" ref="sqlMapClient"/>
- </bean>
-
- </beans>
jdbc.properties
- jdbc.driverClassName=com.mysql.jdbc.Driver
- jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
- jdbc.username=root
- jdbc.password=root
- jdbc.maxPoolSize=100
- jdbc.minPoolSize=10
- jdbc.initialPoolSize=10
- jdbc.idleConnectionTestPeriod=900
- jdbc.maxIdleTime=1800
-
- jdbc.read.db02.url=jdbc:mysql://127.0.0.1:3306/test02?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
- jdbc.read.db02.username=root
- jdbc.read.db02.password=root
-
- jdbc.read.db03.url=jdbc:mysql://127.0.0.1:3306/test03?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
- jdbc.read.db03.username=root
- jdbc.read.db03.password=root
最近忙没时间都贴出来,有时间再上传下全部的。
主要原理:
1.写自己的Rout类继承AbstractRoutingDataSource类
@Override determineCurrentLookupKey()方法
此方法返回dataSource的key.我们只要控制这个key.(例子中有3个writedb、 readdb02、readdb03 ),就是控制了每次使用的那个数据源。
2.拦截器
写自己的拦截器实现MethodInterceptor。在此拦截器中根据请求的方法名字设置key.(例子中有3个writedb、 readdb02、readdb03 )
然后在自己的Rout类中返回就可以了。
最终执行测试的结果:
循环10次查询。10次读的库是随机的,我测的时候配置了10个从库.
/********************************************二、动态分配数据源 spring2.5 +ibatis*************************************************/
0 0