Spring implements database read/write splitting and spring Database

Spring implements database read/write splitting and spring Database

Nowadays, large e-commerce systems mostly adopt read/write splitting technology at the database level, which is a Master database and multiple Slave databases. The Master database is responsible for data updates and real-time data queries. The Slave database is also responsible for non-real-time data queries. In actual applications, databases only read more and write less (the frequency of reading data is high, and the frequency of updating data is relatively small), while reading data usually takes a long time, the CPU usage of the database server is high, which affects the user experience. The common practice is to extract the query from the master database, use multiple slave databases, and use Server Load balancer to relieve the query pressure on each slave database.

The objective of using read/write splitting technology is to effectively reduce the pressure on the Master database, and distribute user data query requests to different Slave databases to ensure system robustness. Let's take a look at the background of using read/write splitting.

As the business of the website continues to expand, the data increases, and the number of users increases, the pressure on the database increases. The traditional methods, such as database or SQL optimization, have basically not met the requirements, in this case, the policy of read/write splitting can be used to change the status quo.

Specifically, how can I implement read/write splitting conveniently during development? Currently, there are two common methods:

1. The first method is our most commonly used method, which is to define two database connections, one is MasterDataSource and the other is SlaveDataSource. When updating data, we read MasterDataSource, and when querying data, we read SlaveDataSource. This method is simple and I will not go into details.

2. The second method is dynamic data source switching. When the program is running, the data source is dynamically woven into the program to choose whether to read the master database or slave database. The main technologies used are annotation, Spring AOP, and reflection. The implementation method is described in detail below.

Before introducing the implementation method, we should first prepare some necessary knowledge, spring's AbstractRoutingDataSource class.

The AbstractRoutingDataSource class is added after spring2.0. Let's take a look at the definition of AbstractRoutingDataSource:

Public abstract class implements actroutingdatasource extends actdatasource implements InitializingBean {}

AbstractRoutingDataSource inherits AbstractDataSource, which is a subclass of DataSource. DataSource is the data source interface of javax. SQL, which is defined as follows:

 

 

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;}

 

 

The DataSource interface defines two methods to obtain database connections. Let's take a look at how AbstractRoutingDataSource implements the DataSource interface:

 

public Connection getConnection() throws SQLException {        return determineTargetDataSource().getConnection();    }    public Connection getConnection(String username, String password) throws SQLException {        return determineTargetDataSource().getConnection(username, password);    }

 

 

Obviously, you can call your own determineTargetDataSource () method to obtain the connection. The determineTargetDataSource method is defined as follows:

 

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;    }

 

 

We are most concerned with the following two sentences:

Object lookupKey = determineCurrentLookupKey ();
DataSource dataSource = this. resolvedDataSources. get (lookupKey );

The determineCurrentLookupKey method returns the lookupKey. The resolvedDataSources method obtains the data source from the Map based on the lookupKey. ResolvedDataSources and determineCurrentLookupKey are defined as follows:

Private Map <Object, DataSource> resolvedDataSources;

Protected abstract Object determineCurrentLookupKey ()

If we see the above definition, is it a bit of thought? resolvedDataSources is of the Map type. We can save MasterDataSource and SlaveDataSource to Map, as shown below:

Key value

Master MasterDataSource

Slave SlaveDataSource

We are writing a DynamicDataSource class that inherits the AbstractRoutingDataSource and implements its determineCurrentLookupKey () method. This method returns the Map key, master, or slave.

 

Well, it's a bit annoying to say so much. Let's take a look at how to implement it.

The technology we want to use has been mentioned above. Let's first look at the definition of annotation:

@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface DataSource {    String value();}

 

We also need to implement the spring abstract class AbstractRoutingDataSource, which is to implement the determineCurrentLookupKey method:

 

public class DynamicDataSource extends AbstractRoutingDataSource {    @Override    protected Object determineCurrentLookupKey() {        // TODO Auto-generated method stub        return DynamicDataSourceHolder.getDataSouce();    }}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();    }}

 

 

From the definition of DynamicDataSource, the returned value is DynamicDataSourceHolder. getDataSouce (). We need to call the DynamicDataSourceHolder. putDataSource () method when the program is running and assign values to it. The following is the core part of our implementation, that is, the AOP part. DataSourceAspect is defined as follows:

 

public class DataSourceAspect {    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());                System.out.println(data.value());            }                    } catch (Exception e) {            // TODO: handle exception        }    }}

 

 

 

To facilitate the test, I have defined two databases: shop simulation Master database and test simulation Slave database. The table structure of shop and test is the same, but the data is different. The database configuration is as follows:

<Bean id = "masterdataSource" 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/shop "/> <property name =" username "value =" root "/> <property name =" password "value =" yangyanping0615 "/> </bean> <bean id = "slavedataSource" 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/test "/> <property name =" username "value =" root "/> <property name =" password "value =" yangyanping0615 "/> </bean> <beans: bean id = "dataSource" class = "com. air. shop. common. db. dynamicDataSource "> <property name =" targetDataSources "> <map key-type =" java. lang. S Tring "> <! -- Write --> <entry key = "master" value-ref = "masterdataSource"/> <! -- Read --> <entry key = "slave" value-ref = "slavedataSource"/> </map> </property> <property name = "defaultTargetDataSource" ref = "masterdataSource" /> </beans: bean> <bean id = "transactionManager" class = "org. springframework. jdbc. datasource. dataSourceTransactionManager "> <property name =" dataSource "ref =" dataSource "/> </bean> <! -- Configure SqlSessionFactoryBean --> <bean id = "sqlSessionFactory" class = "org. mybatis. spring. sqlSessionFactoryBean "> <property name =" dataSource "ref =" dataSource "/> <property name =" configLocation "value =" classpath: config/mybatis-config.xml "/> </bean>

 

 

Add the aop configuration in spring Configuration

<! -- Configure the database annotation aop --> <aop: aspectj-autoproxy> </aop: aspectj-autoproxy> <beans: bean id = "manyDataSourceAspect" class = "com. air. shop. proxy. performanceaspect "/> <aop: config> <aop: aspect id =" c "ref =" manyperformanceaspect "> <aop: pointcut id = "tx" expression = "execution (* com. air. shop. mapper. *. *(..)) "/> <aop: before pointcut-ref =" tx "method =" before "/> </aop: aspect> </aop: config> <! -- Configure the database annotation aop -->

 

 

The following is the definition of UserMapper of MyBatis. To facilitate the test, the Master database is read during login and the Slave database is read from the user list:

public interface UserMapper {    @DataSource("master")    public void add(User user);    @DataSource("master")    public void update(User user);    @DataSource("master")    public void delete(int id);    @DataSource("slave")    public User loadbyid(int id);    @DataSource("master")    public User loadbyname(String name);        @DataSource("slave")    public List<User> list();}

 

 

Okay. Run Eclipse to check the effect. Enter the User Name admin to log on to the system to check the effect.

　　

　　

We can see that the data in the logon user list is different from that in the user list. It also verifies our implementation. The logon reads the Master database, and the user list reads the Slave database.

【]