Code Configuration of mysql+spring+mybatis to Realize the Separation of Reading and Writing Database

Scenario: 1 read data source and 1 read and write data source.

Principle: With the help of spring's " org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource "This abstract class implementation, look at the name can understand is a routing data source of things, this class has a method

/** 
 * Determine the current lookup key. This will typically be 
 * implemented to check a thread-bound transaction context. 
 * <p>Allows for arbitrary keys. The returned key needs 
 * to match the stored lookup key type, as resolved by the 
 * {@link #resolveSpecifiedLookupKey} method. 
 */ 
protected abstract Object determineCurrentLookupKey(); 

spring will call this method to find the corresponding data source every time it connects to the database. The return value is the LookUpKey of the corresponding data source. Where is this LookUpKey defined? Look at the following dataBase.xml Configuration of

<!-- Data source   Read and write  --> 
<bean id="dataSourceRW" class="org.logicalcobwebs.proxool.ProxoolDataSource"> 
  <property name="alias" value="ihotelRW"></property> 
  <property name="delegateProperties"> 
    <value>user=${jdbc.username},password=${jdbc.password} 
    </value> 
  </property> 
  <property name="user" value="${jdbc.username}" /> 
  <property name="password" value="${jdbc.password}" /> 
  <property name="driver" value="${jdbc.driverClassName}" /> 
  <property name="driverUrl" value="${jdbc.url}" /> 
  <property name="maximumConnectionCount" value="${jdbc.maximumConnectionCount}"></property> 
  <property name="maximumActiveTime" value="${jdbc.maximumActiveTime}"></property> 
  <property name="maximumConnectionLifetime" value="${jdbc.maximumConnectionLifetime}"></property> 
  <property name="prototypeCount" value="${jdbc.prototypeCount}"></property> 
  <property name="houseKeepingSleepTime" value="${jdbc.houseKeepingSleepTime}"></property> 
  <property name="simultaneousBuildThrottle" value="${jdbc.simultaneousBuildThrottle}"></property> 
  <property name="houseKeepingTestSql" value="${jdbc.houseKeepingTestSql}"></property> 
  <property name="verbose" value="${jdbc.verbose}"></property> 
  <property name="statistics" value="${jdbc.statistics}"></property> 
  <property name="statisticsLogLevel" value="${jdbc.statisticsLogLevel}"></property> 
</bean> 
  <!-- Data source   Read --> 
  <bean id="dataSourceR" class="org.logicalcobwebs.proxool.ProxoolDataSource"> 
    <property name="alias" value="ihotelR"></property> 
    <property name="delegateProperties"> 
      <value>user=${jdbc.r.username},password=${jdbc.r.password} 
      </value> 
    </property> 
    <property name="user" value="${jdbc.r.username}" /> 
    <property name="password" value="${jdbc.r.password}" /> 
    <property name="driver" value="${jdbc.r.driverClassName}" /> 
    <property name="driverUrl" value="${jdbc.r.url}" /> 
    <property name="maximumConnectionCount" value="${jdbc.maximumConnectionCount}"></property> 
    <property name="maximumActiveTime" value="${jdbc.maximumActiveTime}"></property> 
    <property name="maximumConnectionLifetime" value="${jdbc.maximumConnectionLifetime}"></property> 
    <property name="prototypeCount" value="${jdbc.prototypeCount}"></property> 
    <property name="houseKeepingSleepTime" value="${jdbc.houseKeepingSleepTime}"></property> 
    <property name="simultaneousBuildThrottle" value="${jdbc.simultaneousBuildThrottle}"></property> 
    <property name="houseKeepingTestSql" value="${jdbc.houseKeepingTestSql}"></property> 
    <property name="verbose" value="${jdbc.verbose}"></property> 
    <property name="statistics" value="${jdbc.statistics}"></property> 
    <property name="statisticsLogLevel" value="${jdbc.statisticsLogLevel}"></property> 
  </bean> 
  <!--  Dynamic data source  --> 
  <bean id="dynamicDataSource" class="com.dao.datasource.DynamicDataSource"> 
    <!--  Pass key-value Associated data source  --> 
    <property name="targetDataSources"> 
      <map> 
        <entry value-ref="dataSourceRW" key="dataSourceKeyRW"></entry> 
        <entry value-ref="dataSourceR" key="dataSourceKeyR"></entry> 
      </map> 
    </property> 
    <property name="defaultTargetDataSource" ref="dataSourceRW" />   
  </bean> 
<!--mybatis And Spring Integration   Begin  --> 
<bean id="sqlSessionFactory" name="sqlSessionFactory" 
  class="org.mybatis.spring.SqlSessionFactoryBean"> 
  <property name="configLocation" value="classpath:conf/core/sqlMapConfig.xml" /> 
  <property name="dataSource" ref="dynamicDataSource" /> 
</bean> 

dataSourceKeyRW and dataSourceKeyR in the dynamic data source dynamicDataSource are

protected abstract Object determineCurrentLookupKey(); 

The value to be returned by this method. So how to set the return value of this method to return dataSourceKeyRW and dataSourceKeyR according to our needs? Because this method has no parameters and is automatically called by spring, consider using static variables to store key of dataSource, setting the value of static variables before calling sql statement, and then getting the value of static variables in this method and returning. Considering multithreading, there may be many requests at the same time, in order to avoid mutual interference between threads, consider using threadLocal.

Let's start with the container class that stores dataSourceKey.

public class DBContextHolder { 
  /** 
   *  Thread threadlocal 
   */ 
  private static ThreadLocal<String> contextHolder = new ThreadLocal<>(); 
  private String DB_TYPE_RW = "dataSourceKeyRW"; 
  private String DB_TYPE_R = "dataSourceKeyR"; 
  public String getDbType() { 
    String db = contextHolder.get(); 
    if (db == null) { 
      db = DB_TYPE_RW;//  The default is read-write library  
    } 
    return db; 
  } 
  /** 
   *  Set the dbtype 
   * @param str 
   * @see [ Correlation class / Method ]( Optional ) 
   * @since [ Products / Module version ]( Optional ) 
   */ 
  public void setDbType(String str) { 
    contextHolder.set(str); 
  } 
  /** 
   * clearDBType 
   * @Title: clearDBType 
   * @Description:  Clean up connection types  
   */ 
  public static void clearDBType() { 
    contextHolder.remove(); 
  } 
} 

Implementation class of dynamic data source.

public class DynamicDataSource extends AbstractRoutingDataSource { 
  /* 
   * (non-Javadoc) 
   * @see javax.sql.CommonDataSource#getParentLogger() 
   */ 
  @Override 
  public Logger getParentLogger() throws SQLFeatureNotSupportedException { 
    // TODO Auto-generated method stub 
    return null; 
  } 
  /** 
   * override determineCurrentLookupKey 
   * <p> 
   * Title: determineCurrentLookupKey 
   * </p> 
   * <p> 
   * Description:  Automatic search datasource 
   * </p> 
   * @return 
   */ 
  @Override 
  protected Object determineCurrentLookupKey() { 
    return DBContextHolder.getDbType(); 
  } 
} 

Set the database type in the DAO layer.

/** 
   *  Add Mail  
   * @param sms 
   * @return 
   */ 
  public boolean insertEmail(Email email) { 
    // Set up different databases according to specific needs  
    DBContextHolder.setDbType(DBContextHolder.DB_TYPE_RW); 
    //DBContextHolder.setDbType(DBContextHolder.DB_TYPE_R); 
    int result = this.getSqlSession().insert(STATEMENT + ".addEntity", 
        email); 
    return result == 1; 
  } 

In this case, we specify the database in DAO, or we can specify the DB type in service or controller as needed, bearing in mind that setDbType is for the thread dimension. Consider multithreading.

Summarize