第一中方式:定义2个数据库连接,一个是MasterDataSource,另一个是SlaveDataSource。更新数据时我们读取MasterDataSource,查询数据时我们读取SlaveDataSource
该示例是基于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 source
12 * @exception SQLException if a database access error occurs
13 */
14 Connection getConnection() throws SQLException;
15
16 /**
17 * <p>Attempts to establish a connection with the data source that
18 * this <code>DataSource</code> object represents.
19 *
20 * @param username the database user on whose behalf the connection is
21 * being made
22 * @param password the user's password
23 * @return a connection to the data source
24 * @exception SQLException if a database access error occurs
25 * @since 1.4
26 */
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 @Override
4 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实现层加入设置数据源代码
第二种实现方式:基于上述的配置,只需要做部分的更改即可,主要是结合springAOP思想和反射机制去实现
第一步:基于上面的spring配置中加入aop相关配置
1 <!-- 配置数据库注解aop -->
2 <bean id="manyDataSourceAspect" class="com.he.aspect.DataSourceAspect" />
3 <aop:config>
4 <aop:aspect id="c" ref="manyDataSourceAspect">
5 <aop:pointcut id="tx" expression="execution(* com.he.dao.*.*(..))"/>
6 <aop:before pointcut-ref="tx" method="before"/>
7 </aop:aspect>
8 </aop:config>
View Code
第二步:构建DataSource和DataSourceAspect
1 import java.lang.annotation.ElementType;
2 import java.lang.annotation.Retention;
3 import java.lang.annotation.RetentionPolicy;
4 import java.lang.annotation.Target;
5
6 @Retention(RetentionPolicy.RUNTIME)
7 @Target(ElementType.METHOD)
8 public @interface DataSource {
9 String value();
10 }
DataSource
1 import java.lang.reflect.Method;
2
3 import org.aspectj.lang.JoinPoint;
4 import org.aspectj.lang.reflect.MethodSignature;
5
6 import com.he.mysql.test.DataSource;
7 import com.he.mysql.test.DynamicDataSourceHolder;
8
9
10 public class DataSourceAspect {
11 public void before(JoinPoint point)
12 {
13 Object target = point.getTarget();
14 String method = point.getSignature().getName();
15
16 Class<?>[] classz = target.getClass().getInterfaces();
17
18 Class<?>[] parameterTypes = ((MethodSignature) point.getSignature())
19 .getMethod().getParameterTypes();
20 try {
21 Method m = classz[0].getMethod(method, parameterTypes);
22 if (m != null && m.isAnnotationPresent(DataSource.class)) {
23 DataSource data = m
24 .getAnnotation(DataSource.class);
25 DynamicDataSourceHolder.putDataSource(data.value());
26 System.out.println(data.value());
27 }
28
29 } catch (Exception e) {
30 // TODO: handle exception
31 }
32 }
33
34 }
DataSourceAspect
第三步:将serviceImpl中手动植入的代码移除,在dao层接口加入注解
1 @Repository
2 public interface UserMapper {
3 @DataSource("masterdataSource")
4 public void add(User user);
5 @DataSource("masterdataSource")
6 public void updates(User user);
7 @DataSource("slavedataSource")
8 public List<User> query();
9 }
View Code
上述为实现读写分离的关键部分,只是为了简单的做一个示例,完成上面操作以后,可自行的对数据库进行新增和查询操作,查看效果