Spring example of database read and write separation

Nowadays, large-scale e-commerce systems mostly use read and write separation technology at the database level, which is a Master database and multiple Slave databases. The Master library is responsible for data updates and real-time data query, and the Slave library is responsible for non-real-time data query. Because in actual applications, the database reads more and writes less (the frequency of reading data is high and the frequency of updating data is relatively small), and reading data is usually longer and occupies more CPUs on the database server, which affects the user experience. Our usual approach is to extract the query from the main library, use multiple slave libraries, and use load balancing to reduce the query pressure of each slave library.

The goal of using read and write separation technology is to effectively reduce the pressure on the Master library, and to distribute user query data requests to different Slave libraries, thereby ensuring the robustness of the system. Let's take a look at the background of using read-write separation.

As the website's business continues to expand, data continues to increase, and more users, the pressure on the database becomes greater and greater. Traditional methods, such as: database or SQL optimization basically cannot meet the requirements. At this time, the reading and writing separation strategy can be used to change the status quo.

Specifically in development, how to easily achieve read and write separation? There are two commonly used methods:

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

2 The second method of dynamic data source switching is to dynamically weave the data source into the program when the program is running, so as to choose to read the master library or the slave library. The main technologies used are: annotation, Spring AOP, reflection. The implementation method will be described in detail below.

Before introducing the implementation method, we will prepare some necessary knowledge, the AbstractRoutingDataSource class of spring

AbstractRoutingDataSource class has been added after spring 2.0. Let’s first look at the definition of AbstractRoutingDataSource:

The code copy is as follows:
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {}

AbstractRoutingDataSource inherits AbstractDataSource, which is a subclass of DataSource. DataSource is the data source interface of javax.sql, 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 side 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, both of which are obtaining 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, it is to call your own determineTargetDataSource() method to get the connection. 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; }

What we care most about is the following two sentences:

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

determineCurrentLookupKey method returns lookupKey, resolvedDataSources method is to obtain the data source from the map based on lookupKey. resolvedDataSources and determinedCurrentLookupKey are defined as follows:

 private Map<Object, DataSource> resolvedDataSources; protected abstract Object determineCurrentLookupKey()

After seeing the above definition, do we have some ideas? resolvedDataSources is the Map type. We can save MasterDataSource and SlaveDataSource to the Map, as follows:

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

Okay, after saying so much, I'm a little annoying. Let's see how to achieve 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 spring's 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, it returns the DynamicDataSourceHolder.getDataSouce() value. We need to call the DynamicDataSourceHolder.putDataSource() method when the program is running and assign a value to it. The following is the core part of our implementation, that is, the AOP part. The 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 } }}

For the convenience of testing, I defined 2 databases, shop mock Master library, test mock Slave library, shop and test table structures are the same, but the data are different, and the database configuration is as follows:

 <bean id="masterdataSource" > <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" > <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"> <property name="targetDataSources"> <map key-type="java.lang.String"> <!-- 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" > <property name="dataSource" ref="dataSource" /> </bean> <!-- Configure SqlSessionFactoryBean --> <bean id="sqlSessionFactory"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:config/mybatis-config.xml" /> </bean>

Add aop configuration to spring configuration

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

The following is the definition of MyBatis UserMapper. For the convenience of testing, login reads the Master library and the user list reads the Slave library:

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

OK, run our Eclipse to see the effect, enter the username admin and log in to see the effect

The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.