Spring 实现数据库读写分离
随着互联网的大型网站系统访问量的增高，数据库访问压力方面不断的显现而出，所以许多公司在数据库层面采用读写分离技术，也就是一个master，多个slave。master负责数据的实时更新或实时查询，而slave负责非实时数据的查询。实际应用当中，数据库通常读多写少，而读取数据通常耗时长，占用数据库服务器资源高，所以解决的办法是查询从主库抽取出来，采用多个从库，使用nginx，减轻每个从库的压力。
实现数据库分离有多种方式，这里介绍一下动态数据源切换的方式，使用的技术框架为：Spring+Mybatis+DruidDataSource数据连接池（目前市面上常使用的连接池为：DBCP连接池、C3P0连接池、tomcat内置连接池、DruidDataSource连接池），主要使用的技术：annotation,spring aop,反射。
先来准备一下必要知识，spring的AbstractRoutingDataSource类，AbstractRoutingDataSource这个类 是spring2.0以后增加的，我们先来看下AbstractRoutingDataSource的定义：
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {}
AbstractRoutingDataSource继承了AbstractDataSource ，而AbstractDataSource 又是DataSource 的子类。DataSource 是javax.sql 的数据源接口，定义如下：
public interface DataSource extends CommonDataSource,Wrapper { /** * <p>Attempts to establish a connection with the data source that * this <code>DataSource</code> object represents. * * @return a connection to the data source * @exception SQLException if a database access error occurs */ Connection getConnection() throws SQLException; /** * <p>Attempts to establish a connection with the data source that * this <code>DataSource</code> object represents. * * @param username the database user on whose behalf the connection is * being made * @param password the user's password * @return a connection to the data source * @exception SQLException if a database access error occurs * @since 1.4 */ Connection getConnection(String username, String password) throws SQLException; }
DataSource 接口定义了2个方法，都是获取数据库连接。我们在看下AbstractRoutingDataSource 如何实现了DataSource接口：
public Connection getConnection() throws SQLException { return determineTargetDataSource().getConnection(); } public Connection getConnection(String username, String password) throws SQLException { return determineTargetDataSource().getConnection(username, password); }
很显然就是调用自己的determineTargetDataSource() 方法获取到connection。determineTargetDataSource方法定义如下：
protected DataSource determineTargetDataSource() { Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); Object lookupKey = determineCurrentLookupKey(); DataSource dataSource = this.resolvedDataSources.get(lookupKey); if (dataSource == null && (this.lenientFallback || lookupKey == null)) { dataSource = this.resolvedDefaultDataSource; } if (dataSource == null) { throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); } return dataSource; }
其中最主要的两句话：
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
determineCurrentLookupKey方法返回lookupKey,resolvedDataSources方法就是根据lookupKey从Map中获得数据源。resolvedDataSources 和determineCurrentLookupKey定义如下：
private Map<Object, DataSource> resolvedDataSources;
protected abstract Object determineCurrentLookupKey()
其中resolvedDataSources是一个Map类型，
我们可以把MasterDataSource和SlaveDataSource存到Map中，如下：
key value
master MasterDataSource
slave SlaveDataSource
我们在写一个类DynamicDataSource 继承AbstractRoutingDataSource，实现其determineCurrentLookupKey() 方法，该方法返回Map的key，master或slave。
具体实现如下：
1）定义annotation：
import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.METHOD) public @interface DataSource { String value(); }
2）实现spring的抽象类AbstractRoutingDataSource，就是实现determineCurrentLookupKey方法：
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DynamicDataSourceHolder.getDataSouce(); } }
3）当然我们还需定义一个DynamicDataSourceHolder
public class DynamicDataSourceHolder { public static final ThreadLocal<String> holder = new ThreadLocal<String>(); public static void putDataSource(String name) { holder.set(name); } public static String getDataSouce() { return holder.get(); } }
注意：使用ThreadLocal维护变量时，ThreadLocal为每个使用该变量的线程提供独立的变量副本，所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
4）定义一个切面(Aspect)
import org.apache.log4j.Logger; import java.lang.reflect.Method; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.reflect.MethodSignature; import sy.annotation.DataSource; import sy.common.db.DynamicDataSourceHolder; public class DataSourceAspect { /** * Logger for this class */ private static final Logger logger = Logger.getLogger(DataSourceAspect.class); public void before(JoinPoint point) { Object target = point.getTarget(); String method = point.getSignature().getName(); Class<?>[] classz = target.getClass().getInterfaces(); Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes(); try { Method m = classz[0].getMethod(method, parameterTypes); if (m != null && m.isAnnotationPresent(DataSource.class)) { DataSource data = m.getAnnotation(DataSource.class); DynamicDataSourceHolder.putDataSource(data.value()); logger.info(data.value()); } } catch (Exception e) { // TODO: handle exception } } }
5)其中spring中mybatis的配置如下：
<!-- 配置数据源 -->
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="url" value="${jdbc_url}" />
<property name="username" value="${jdbc_username}" />
<property name="password" value="${jdbc_password}" />
<!-- 初始化连接大小 -->
<property name="initialSize" value="0" />
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="20" />
<!-- 连接池最大空闲 -->
<property name="maxIdle" value="20" />
<!-- 连接池最小空闲 -->
<property name="minIdle" value="0" />
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="60000" />
<!-- <property name="poolPreparedStatements" value="true" /> <property name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
<property name="validationQuery" value="${validationQuery}" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="true" />
<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="25200000" />
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="true" />
<!-- 1800秒，也就是30分钟 -->
<property name="removeAbandonedTimeout" value="1800" />
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="true" />
<!-- 监控数据库 -->
<!-- <property name="filters" value="stat" /> -->
<property name="filters" value="mergeStat" />
</bean>
<bean id="dataSourceMaster" class="sy.common.db.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<!-- write -->
<entry key="master" value-ref="dataSource"/>
<!-- read -->
<entry key="slave" value-ref="dataSource"/>
</map>
</property>
<property name="defaultTargetDataSource" ref="dataSource"/>
</bean>
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSourceMaster" />
<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
<property name="mapperLocations" value="classpath:sy/mapping/*.xml" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="sy.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 注解方式配置事物 -->
<!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->
<!-- 拦截器方式配置事物 -->
<tx:advice id="transactionAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="append*" propagation="REQUIRED" />
<tx:method name="insert*" propagation="REQUIRED" />
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="update*" propagation="REQUIRED" />
<tx:method name="modify*" propagation="REQUIRED" />
<tx:method name="edit*" propagation="REQUIRED" />
<tx:method name="delete*" propagation="REQUIRED" />
<tx:method name="remove*" propagation="REQUIRED" />
<tx:method name="repair" propagation="REQUIRED" />
<tx:method name="delAndRepair" propagation="REQUIRED" />
<tx:method name="get*" propagation="SUPPORTS" />
<tx:method name="find*" propagation="SUPPORTS" />
<tx:method name="load*" propagation="SUPPORTS" />
<tx:method name="search*" propagation="SUPPORTS" />
<tx:method name="datagrid*" propagation="SUPPORTS" />
<tx:method name="*" propagation="SUPPORTS" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="transactionPointcut" expression="execution(* sy.service..*Impl.*(..))" />
<aop:advisor pointcut-ref="transactionPointcut" advice-ref="transactionAdvice" />
</aop:config>
<!-- 配置druid监控spring jdbc -->
<bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor">
</bean>
<bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
<property name="patterns">
<list>
<value>sy.service.*</value>
</list>
</property>
</bean>
<aop:config>
<aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut" />
</aop:config>
<!-- 配置数据库注解aop -->
<bean id="manyDataSourceAspect" class="sy.aspect.DataSourceAspect" />
<aop:config>
<aop:aspect id="c" ref="manyDataSourceAspect">
<aop:pointcut id="tx" expression="execution(* sy.dao..*Mapper.*(..))"/>
<aop:before pointcut-ref="tx" method="before"/>
</aop:aspect>
</aop:config>
<!-- 配置数据库注解aop -->
注：我这里都是用的同一个数据源，用时可以更换一下数据源
5）其中以一个mybatis映射接口举例
import java.util.List; import org.apache.ibatis.annotations.Param; import sy.annotation.DataSource; import sy.model.Tuser; public interface TuserMapper { int deleteByPrimaryKey(String id); int insert(Tuser record); int insertSelective(Tuser record); @DataSource("master") Tuser selectByPrimaryKey(String id); int updateByPrimaryKeySelective(Tuser record); int updateByPrimaryKey(Tuser record); Tuser selectBySelective(Tuser record); @DataSource("master") Tuser selectByTest(String id); List<String> selectResourcesByPrimaryKey(String id); @DataSource("master") List<Tuser> selectByName(@Param("name") String name,@Param("start") Integer start,@Param("end") Integer end,@Param("sort") String sort,@Param("order") String order); Long selectCountByName(String name); }
6）写一个测试单元如下
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = { "classpath:spring.xml", "classpath:spring-mybatis.xml","classpath:spring-config-mongodb.xml" }) public class TestUser { /** * Logger for this class */ private static final Logger logger = Logger.getLogger(TestUser.class); @Resource private MongoTemplate mongoTemplate; private UserServiceI userService; public UserServiceI getUserService() { return userService; } @Autowired public void setUserService(UserServiceI userService) { this.userService = userService; } @Test public void Test1(){ Tuser t=userService.getTuserTest("0"); logger.info(JSON.toJSONStringWithDateFormat(t, "yyyy-MM-dd HH:mm:ss")); } @Test public void Test2(){ List<String> t=userService.resourceList("0"); logger.info(JSON.toJSONStringWithDateFormat(t, "yyyy-MM-dd HH:mm:ss")); }
}
