Spring实现数据库读写分离
转载自:http://blog.csdn.net/zero__007/article/details/48711017
在数据库层面需要采用读写分离技术,就是一个master数据库,多个slave数据库。master库负责数据更新和实时数据查询,slave库负责非实时数据查询。因为在实际的应用中,数据库都是读多写少(读取数据的频率高,更新数据的频率相对较少),而读取数据通常耗时比较长,占用数据库服务器的CPU较多。
采用读写分离技术的目标:有效减轻master库的压力,又可以把用户查询数据的请求分发到不同的slave库,从而保证系统的健壮性。
如何方便的实现读写分离呢?
1.第一种方式是最简单的方式,就是定义2个数据库连接,一个是masterDataSource,另一个是slaveDataSource。对DAO的dataSource属性注入是,根据需求分别如入不同的DataSource。有时由于在同一个DAO中可能既有select又有insert,那样就需要对同一类型的DAO写两个DAO,比如就可能需要将UserDaoImp拆分为UserDaoImp_w与UserDaoImp_r。
2. 第二种方式,不将UserDaoImp拆分为UserDaoImp_w与UserDaoImp_r。在UserDaoImp的方法调用前使用this.setDataSource()来切换DataSource,但是存在线程安全问题。例如,线程一中,调用了f1(),将datasource切换为masterDataSource ,准备写数据到主数据库,但是这时线程二,调用了f2(),将datasource切换为slaveDataSource,准备从从数据库读数据,那么线程一继续执行f1()的方法就会出现问题。原因在于线程一、线程二使用的是一个UserDaoImp实例(spring加载的bean默认都是单例),共用的一个datasource。当然可不让UserDaoImp为单例,并结合ThreadLocal来避免线程安全问题,但是不推荐。
3. 第三种方式动态数据源切换,就是在程序运行时,把数据源动态织入到程序中,从而选择读取主库还是从库。 这里Spring的AbstractRoutingDataSource提供了很好的支持。
- public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
- private Map<Object, Object> targetDataSources;
- private Object defaultTargetDataSource;
- private boolean lenientFallback = true;
- private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
- private Map<Object, DataSource> resolvedDataSources;
- private DataSource resolvedDefaultDataSource;
- ......
AbstractRoutingDataSource继承了AbstractDataSource ,而AbstractDataSource 又是DataSource 的子类。DataSource是javax.sql的数据源接口,定义如下:
- public interface DataSource extends CommonDataSource, Wrapper {
- Connection getConnection() throws SQLException;
- 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);
- }
AbstractRoutingDataSource通过调用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;
- }
determineCurrentLookupKey()方法返回lookupKey, resolvedDataSources()方法就是根据 lookupKey从Map中获得数据源。
示例:
beans.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- 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">
- <bean id="m_dataSource"
- class="org.springframework.jdbc.datasource.DriverManagerDataSource">
- <property name="driverClassName" value="com.mysql.jdbc.Driver" />
- <property name="url" value="jdbc:mysql://127.0.0.1:3306/zero" />
- <property name="username" value="root/>
- <property name="password" value="123456" />
- </bean>
- <bean id="s_dataSource"
- class="org.springframework.jdbc.datasource.DriverManagerDataSource">
- <property name="driverClassName" value="com.mysql.jdbc.Driver" />
- <property name="url"
- value="jdbc:mysql://127.0.0.1:3306/zero_test" />
- <property name="username" value="root" />
- <property name="password" value="123456" />
- </bean>
- <bean id="dynamicDataSource" class="com.zero.springjdbc.DynamicDataSource">
- <property name="targetDataSources">
- <map key-type="java.lang.String">
- <!-- write -->
- <entry key="master" value-ref="m_dataSource" />
- <!-- read -->
- <entry key="slave" value-ref="s_dataSource" />
- </map>
- </property>
- <property name="defaultTargetDataSource" ref="m_dataSource" />
- </bean>
- <bean id="zeroDaoImpl" class="com.zero.springjdbc.ZeroDaoImpl">
- <property name="dataSource" ref="dynamicDataSource"></property>
- </bean>
- </beans>
以上的beans.xml中,配置了两个DataSource,m_dataSource与s_dataSource,然后将这两个DataSource交给com.zero.springjdbc.DynamicDataSource管理,由于com.zero.springjdbc.DynamicDataSource是继承了AbstractRoutingDataSource,所以给它的两个属性targetDataSources、defaultTargetDataSource注入值。而DAO层的dataSource属性所使用的是dynamicDataSource。
DynamicDataSourceHolder.java
- package com.zero.springjdbc;
- public class DynamicDataSourceHolder {
- public static final ThreadLocal<String> holder = new ThreadLocal<String>();
- public static void setDataSource(String name) {
- holder.set(name);
- }
- public static String getDataSouce() {
- return holder.get();
- }
- }
这里利用了ThreadLocal,来指明每个线程在进行数据库操作时所用到的数据库。
DynamicDataSource.java
- package com.zero.springjdbc;
- import java.sql.SQLFeatureNotSupportedException;
- import java.util.logging.Logger;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- public class DynamicDataSource extends AbstractRoutingDataSource{
- @Override
- protected Object determineCurrentLookupKey() {
- // TODO Auto-generated method stub
- return DynamicDataSourceHolder.getDataSouce();
- }
- @Override
- public Logger getParentLogger() throws SQLFeatureNotSupportedException {
- // TODO Auto-generated method stub
- return null;
- }
- }
ZeroDaoImpl.java
- package com.zero.springjdbc;
- import org.springframework.jdbc.core.JdbcTemplate;
- public class ZeroDaoImpl extends JdbcTemplate {
- public void update1() {
- DynamicDataSourceHolder.setDataSource("master");
- String sql01 = "update lineitem_record set fee=111111 where lineitemId='11238'";
- System.out.println("update1 lineitemId='11238' : "
- + this.update(sql01));
- String sql02 = "update lineitem_record set fee=111111 where lineitemId='111'";
- System.out.println("update1 lineitemId='111' : "
- + this.update(sql02));
- }
- public void update2() {
- try {
- DynamicDataSourceHolder.setDataSource("slave");
- String sql01 = "update lineitem_record set fee=111111 where lineitemId='11238'";
- System.out.println("update2 lineitemId='11238' : "
- + this.update(sql01));
- String sql02 = "update lineitem_record set fee=222222 where lineitemId='111'";
- System.out.println("update2 lineitemId='111' : "
- + this.update(sql02));
- } catch (Exception e) {
- // TODO: handle exception
- e.printStackTrace();
- }
- }
- }
这里的每个方法都DynamicDataSourceHolder.setDataSource(),来指明接下来的操作将使用的是什么数据库,由于被ThreadLocal隔离了,所以是线程安全的。
Test.java
- package com.zero.springjdbc;
- import org.springframework.context.ApplicationContext;
- import org.springframework.context.support.ClassPathXmlApplicationContext;
- public class Test {
- public static void main(String[] args) {
- // TODO Auto-generated method stub
- ApplicationContext ctx = new ClassPathXmlApplicationContext(
- "/com/zero/springjdbc/beans.xml");
- ZeroDaoImpl zeroDaoImpl = (ZeroDaoImpl) ctx.getBean("zeroDaoImpl");
- for (int i = 0; i < 5; i++) {
- new Thread(new Runnable() {
- @Override
- public void run() {
- // TODO Auto-generated method stub
- zeroDaoImpl.update1();
- }
- }, "thread1-" + i).start();
- ;
- new Thread(new Runnable() {
- @Override
- public void run() {
- // TODO Auto-generated method stub
- zeroDaoImpl.update2();
- }
- }, "thread2-" + i).start();
- ;
- }
- }
- }
这里建了10个线程,每个线程执行zeroDaoImpl.update1()或zeroDaoImpl.update2()方法来检验是否是线程安全的。
PS:master的lineitem_record表中有lineitemId='11238',没有lineitemId='111';而slave的lineitem_record表中没有lineitemId='11238',有lineitemId='111'。预期结果是update1 lineitemId='11238' 的值是1,而update1 lineitemId='111'的值是0,update2 lineitemId='11238' 的值是0,而update2 lineitemId='111'的值是1。如果不是这样,那么就说明在多线程环境下,依然会出现方式二所面临的问题。
测试结果:
- update2 lineitemId='11238' : 0
- update1 lineitemId='11238' : 1
- update2 lineitemId='11238' : 0
- update1 lineitemId='11238' : 1
- update2 lineitemId='11238' : 0
- update1 lineitemId='11238' : 1
- update1 lineitemId='11238' : 1
- update1 lineitemId='11238' : 1
- update2 lineitemId='11238' : 0
- update1 lineitemId='111' : 0
- update2 lineitemId='111' : 1
- update1 lineitemId='111' : 0
- update1 lineitemId='111' : 0
- update2 lineitemId='111' : 1
- update2 lineitemId='111' : 1
- update1 lineitemId='111' : 0
- update2 lineitemId='11238' : 0
- update2 lineitemId='111' : 1
- update1 lineitemId='111' : 0
- update2 lineitemId='111' : 1
由结果可知,利用Spring的AbstractRoutingDataSource可以解决多数据源的问题。
贴上AbstractRoutingDataSource源码:
- package org.springframework.jdbc.datasource.lookup;
- import java.sql.Connection;
- import java.sql.SQLException;
- import java.util.HashMap;
- import java.util.Map;
- import javax.sql.DataSource;
- import org.springframework.beans.factory.InitializingBean;
- import org.springframework.jdbc.datasource.AbstractDataSource;
- import org.springframework.util.Assert;
- public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
- private Map<Object, Object> targetDataSources;
- private Object defaultTargetDataSource;
- private boolean lenientFallback = true;
- private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
- private Map<Object, DataSource> resolvedDataSources;
- private DataSource resolvedDefaultDataSource;
- public void setTargetDataSources(Map<Object, Object> targetDataSources) {
- this.targetDataSources = targetDataSources;
- }
- public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
- this.defaultTargetDataSource = defaultTargetDataSource;
- }
- public void setLenientFallback(boolean lenientFallback) {
- this.lenientFallback = lenientFallback;
- }
- public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
- this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
- }
- public void afterPropertiesSet() {
- if (this.targetDataSources == null) {
- throw new IllegalArgumentException("Property 'targetDataSources' is required");
- }
- this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
- for (Map.Entry entry : this.targetDataSources.entrySet()) {
- Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
- DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
- this.resolvedDataSources.put(lookupKey, dataSource);
- }
- if (this.defaultTargetDataSource != null) {
- this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
- }
- }
- protected Object resolveSpecifiedLookupKey(Object lookupKey) {
- return lookupKey;
- }
- protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
- if (dataSource instanceof DataSource) {
- return (DataSource) dataSource;
- }
- else if (dataSource instanceof String) {
- return this.dataSourceLookup.getDataSource((String) dataSource);
- }
- else {
- throw new IllegalArgumentException(
- "Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
- }
- }
- public Connection getConnection() throws SQLException {
- return determineTargetDataSource().getConnection();
- }
- public Connection getConnection(String username, String password) throws SQLException {
- return determineTargetDataSource().getConnection(username, password);
- }
- @Override
- @SuppressWarnings("unchecked")
- public <T> T unwrap(Class<T> iface) throws SQLException {
- if (iface.isInstance(this)) {
- return (T) this;
- }
- return determineTargetDataSource().unwrap(iface);
- }
- @Override
- public boolean isWrapperFor(Class<?> iface) throws SQLException {
- return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
- }
- 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;
- }
- protected abstract Object determineCurrentLookupKey();
- }