java實現mysql資料庫讀寫分離之定義多資料來源方式

標籤：display   分離   功能   oca   aop   root   擷取   pen   exception   

 

該樣本是基於spring提供的AbstractRoutingDataSource，實現了一個動態資料源的功能，在spring配置中定義多個資料庫分為主、從資料庫，實現效果為當進行儲存和修改記錄時則對主表操作，查詢則對從表進行操作，從而實現對資料庫表的讀寫分離。這樣做有利於提高網站的效能，特別是在資料庫這一層。因為在實際的應用中，資料庫都是讀多寫少（讀取資料的頻率高，更新資料的頻率相對較少），而讀取資料通常耗時比較長，佔用資料庫伺服器的CPU較多，從而影響使用者體驗。我們通常的做法就是把查詢從主庫中抽取出來，採用多個從庫，使用負載平衡，減輕每個從庫的查詢壓力。該樣本並未對資料庫同步進行說明，只對讀寫操作的分離實現：

在進行操作之前，先簡單說一下AbstractRoutingDataSource相關的東西：

 1 AbstractRoutingDataSource繼承了AbstractDataSource ，而AbstractDataSource 又是DataSource 的子類。DataSource   是javax.sql 的資料來源介面，定義如下： 2  3 public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean  {} 4  5 public interface DataSource  extends CommonDataSource,Wrapper { 6  7   /** 8    * <p>Attempts to establish a connection with the data source that 9    * this <code>DataSource</code> object represents.10    *11    * @return  a connection to the data source12    * @exception SQLException if a database access error occurs13    */14   Connection getConnection() throws SQLException;15 16   /**17    * <p>Attempts to establish a connection with the data source that18    * this <code>DataSource</code> object represents.19    *20    * @param username the database user on whose behalf the connection is21    *  being made22    * @param password the user‘s password23    * @return  a connection to the data source24    * @exception SQLException if a database access error occurs25    * @since 1.426    */27   Connection getConnection(String username, String password)28     throws SQLException;29 30 }31 32 33 public Connection getConnection() throws SQLException {34         return determineTargetDataSource().getConnection();35     }36 37     public Connection getConnection(String username, String password) throws SQLException {38         return determineTargetDataSource().getConnection(username, password);39     }40 41 protected DataSource determineTargetDataSource() {42         Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");43         Object lookupKey = determineCurrentLookupKey();44         DataSource dataSource = this.resolvedDataSources.get(lookupKey);45         if (dataSource == null && (this.lenientFallback || lookupKey == null)) {46             dataSource = this.resolvedDefaultDataSource;47         }48         if (dataSource == null) {49             throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");50         }51         return dataSource;52     }

View Code

從上面的代碼中不難看出，擷取資料來源首先是通過對determineCurrentLookupKey（）的調用擷取resolvedDataSources對應key的值，故執行建立一個動態資料源類繼承AbstractRoutingDataSource，複寫determineCurrentLookupKey（）去自訂設定和擷取resolvedDataSources的key就可以實現了

具體步驟如下：

第一步：

<?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:aop="http://www.springframework.org/schema/aop"    xmlns:context="http://www.springframework.org/schema/context"    xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"    xmlns:jpa="http://www.springframework.org/schema/data/jpa"    xsi:schemaLocation="        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd"    default-lazy-init="true">    <!-- 引入設定檔 -->      <context:component-scan base-package="com.he" />       <bean id="masterdataSource"       class="org.apache.commons.dbcp.BasicDataSource"          destroy-method="close">          <property name="driverClassName" value="com.mysql.jdbc.Driver" />        <property name="url" value="jdbc:mysql://localhost:3306/test" />        <property name="username" value="root" />        <property name="password" value="111111" />    </bean>    <bean id="slavedataSource"      class="org.apache.commons.dbcp.BasicDataSource"          destroy-method="close">          <property name="driverClassName" value="com.mysql.jdbc.Driver" />        <property name="url" value="jdbc:mysql://localhost:3306/test2" />        <property name="username" value="root" />        <property name="password" value="111111" />    </bean>        <bean id="dataSource" class="com.he.mysql.test.DynamicDataSource">        <property name="targetDataSources">                <map key-type="java.lang.String">                    <!-- write -->                 <entry key="masterdataSource" value-ref="masterdataSource"/>                   <!-- read -->                 <entry key="slavedataSource" value-ref="slavedataSource"/>                </map>                        </property>          <property name="defaultTargetDataSource" ref="masterdataSource"/>      </bean>           <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">          <property name="dataSource" ref="dataSource" />          <property name="mapperLocations" value="classpath:com/he/dao/*.xml"></property>      </bean>      <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">          <property name="basePackage" value="com.he.dao" />          <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>      </bean>          <bean id="transactionManager"          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">          <property name="dataSource" ref="dataSource" />      </bean>        <!-- 註解式交易管理，需要在Service類上標註@Transactional -->    <tx:annotation-driven transaction-manager="transactionManager" />     </beans>  

View Code

 

第二步：

1 public class DynamicDataSource extends AbstractRoutingDataSource {2 3     @Override4     protected Object determineCurrentLookupKey() {5      6         return DynamicDataSourceHolder.getDataSouce();7     }8 9 }

建立動態資料源類繼承AbstractRoutingDataSource

第三步：

 1 public class DynamicDataSourceHolder { 2     public static final ThreadLocal<String> holder = new ThreadLocal<String>(); 3  4     public static void putDataSource(String name) { 5         holder.set(name); 6     } 7  8     public static String getDataSouce() { 9         return holder.get();10     }11 }

設定及擷取每個線程訪問的哪個資料來源

第四步：

 1 @Service("userService")   2 @Transactional 3 public class UserServiceImpl implements UserService{ 4  5     @Autowired 6     private UserMapper userDao;public void add(User user) { 7          8         DynamicDataSourceHolder.putDataSource("masterdataSource"); 9         userDao.add(user);10     }11 12     public void update(User user) {13         14         DynamicDataSourceHolder.putDataSource("masterdataSource");15         userDao.updates(user);16     17         18     }19 20     @Transactional(propagation = Propagation.NOT_SUPPORTED)21     public List<User> query() {22         23         DynamicDataSourceHolder.putDataSource("slavedataSource");24         List<User> user = userDao.query();25         return user;26         27     }28 29     30     31     32 }

對service實現層加入設定資料來源代碼

 

上述為實現讀寫分離的關鍵區段，只是為了簡單的做一個樣本，完成上面操作以後，可自行的對資料庫進行新增和查詢操作，查看效果

java實現mysql資料庫讀寫分離之定義多資料來源方式