# rws1

**Repository Path**: weishaoying/rws1

## Basic Information

- **Project Name**: rws1
- **Description**: 数据库读写分离常用的2种方案
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2018-05-05
- **Last Updated**: 2020-12-18

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 读写分离

可以作为Spring MVC项目的简单模板

[读写分离笔记](https://gitee.com/weishaoying/codes/mv9busowkgy8ic12xt6dh54)

<br/>

### 方案1 手工控制

两个数据源，写两套mapper，一套用于读，一套用于只写   

Service中[读方法调用readMapper]，[写方法调用writeMapper]  

配置两套数据源：读数据源、写数据源

sqlSessionFactoryReadWrite

MapperScannerConfigurer


dao分2个目录 daor_r, dao_rw

UserReadMapper / UserWriteMapper 

```java
public class UserService {

    @Autowired
    private UserReadMapper userReadMapper;

    @Autowired
    private UserWriteMapper userWriteMapper;
```
<br/>

## 测试：

准备工作：
```shell
打包：mvn clean package
部署到Tomcat中，并启动
```
<br/>

注释掉：RwsController及Service上的注解

引入这个配置
```xml
<import resource="applicationContext-dao.xml"/>
```

<br/>

数据源1：
```text
http://localhost:8080/rws/queryAllUser

返回：
[{"id":1,"username":"weixiaodou","password":"WXD","nickname":"魏小豆","roles":"ROLE_CHILDREN"},{"id":2,"username":"admin","password":"admin","nickname":"admin","roles":"ROLE_USER,ROLE_ADMIN"},{"id":3,"username":"weishaoying","password":"abc123","nickname":"魏韶颖","roles":"ROLE_USER"},{"id":4,"username":"root","password":"root","nickname":"系统管理员","roles":"ROLE_USER"},{"id":5,"username":"wsy","password":"$2a$10$xgW7sWpDtNj6K6wS8jef9.NzbbUS7QwgisR8Nrh3BqbDbx5ru6nW2","nickname":"韶颖.魏","roles":"ROLE_USER"},{"id":7,"username":"weixiaodou","password":"wxd123456","nickname":"魏小豆","roles":"ROLE_CHILDREN"},{"id":8,"username":"wanchen","password":"wwc123","nickname":"魏菀辰","roles":"ROLE_CHILDREN"},{"id":11,"username":"BaiFeiFei","password":"AJBF","nickname":"白飞","roles":"ROLE_SINGER"},{"id":12,"username":"EEE","password":"WXD","nickname":"魏小豆","roles":"ROLE_CHILDREN"}]



http://localhost:8080/rws/queryAllBook
返回：
[{"id":1000,"name":"Java程序设计","number":0},{"id":1001,"name":"数据结构","number":10},{"id":1002,"name":"设计模式","number":9},{"id":1003,"name":"编译原理","number":10},{"id":1004,"name":"操作系统原理","number":100},{"id":1005,"name":"JavaWeb开发","number":25},{"id":1006,"name":"Spring mvc教程","number":25}]
```

<br/>
<br/>

数据源2：
```text
http://localhost:8080/rws/queryAllDog
返回：
[{"name":"泰迪","color":"棕色"},{"name":"哈士奇","color":"黑白色"},{"name":"腊肠","color":"棕红色"},{"name":"斗牛","color":"灰色"}]


新增、修改用户
```
    
<hr/>    
<br/>
    
## 方案2 实现AbstractRoutingDataSource类来动态管理数据源

applicationContext-daoRWS.xml中 配置了methodType属性
```xml
<bean id="dataSource" class="com.weishaoying.rws.aspect.ChooseDataSource" lazy-init="true">
        <property name="targetDataSources">
            <map key-type="java.lang.String" value-type="javax.sql.DataSource">
                <!-- 写数据源 -->
                <entry key="write" value-ref="dataSourceWrite"/>
                <!-- 读数据源 -->
                <entry key="read" value-ref="dataSourceRead"/>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="dataSourceWrite"/>
        <property name="methodType">
            <map key-type="java.lang.String">
                <!-- 读方法 -->
                <entry key="read" value=",get,select,count,list,query"/>
                <!-- 写方法 -->
                <entry key="write" value=",add,save,create,update,delete,remove,"/>
            </map>
        </property>
    </bean>
```
所以
Spring容器启动创建这个bean时，就会调用setMethodType(Map<String, String> map) 方法

<br/>

```java
public class ChooseDataSource extends AbstractRoutingDataSource {

    //key=read, value=get,select,count,list,query
    //key=write, value=add,save,create,update,delete,remove
    public static Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<>();

    //Spring容器启动创建这个bean时，就会调用这个方法，为属性赋值
    public void setMethodType(Map<String, String> map) {
        for (String key : map.keySet()) {
            List<String> list = new ArrayList<>();
            String[] methods = map.get(key).split(",");

            for (String type : methods) {
                if (StringUtils.isNotBlank(type)) {
                    list.add(type);
                }
            }
            System.out.println("========ChooseDataSource.key = " + key);
            System.out.println("========ChooseDataSource.list = " + list);
            METHOD_TYPE_MAP.put(key, list);
        }
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceHolder.getDataSource();
    }
}
```

<br/>

核心是切面
```java
@Aspect
@Component
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DataSourceAspect {

    protected Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);

    @Pointcut("execution(* com.weishaoying.rws.service.*.*(..))")
    public void rws() {
    }

    @Before("rws()")
    public void before(JoinPoint point) {
        System.out.println("========Aspect before");
        String className = point.getTarget().getClass().getName();
        String methodName = point.getSignature().getName();
        System.out.println("========" + className + "." + methodName + "(" + StringUtils.join(point.getArgs(), ",") + ")");

        try {
            //遍历Map,其实只有2个Read/Write
            for (String key : ChooseDataSource.METHOD_TYPE_MAP.keySet()) {
                //通过key获得value，value是List所以遍历
                for (String type : ChooseDataSource.METHOD_TYPE_MAP.get(key)) {
                    //如果方法名等于type
                    if (methodName.startsWith(type)) {
                        System.out.println("========" + methodName + " start with " + type + ", Use " + key + " DataSource");
                        //把read/write放到ThreadLocal中
                        DataSourceHolder.putDataSource(key);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //另外一种方式,通过方法上的注解来判断使用哪种数据源
    public void before2(JoinPoint point) {
        Object target = point.getTarget();
        System.out.println(target.toString());
        String method = point.getSignature().getName();
        System.out.println(method);
        Class<?> classz = target.getClass();
        Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
        try {
            Method m = classz.getMethod(method, parameterTypes);
            System.out.println(m.getName());
            if (m != null && m.isAnnotationPresent(DataSource.class)) {
                DataSource dataSource = m.getAnnotation(DataSource.class);
                DataSourceHolder.putDataSource(dataSource.value());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
```


只有一套Mapper即可
```java
public interface UserMapper {
    User queryById(Long id);
    List<User> queryAll();
    int saveUser(User user);
    int updateUser(User user);
    int deleteById(Long id);
}
```

<br/>

### 测试：

```text

测试查询：
http://localhost:8080/query/12
http://localhost:8080/queryAll

删除
http://localhost:8080/delete/31

新增
POST:
http://localhost:8080/save
参数：
{
  "username": "BaiFeiFei",
  "password": "AJ@B123F",
  "nickname": "白飞飞",
  "roles": "ROLE_SINGER"
}

修改
http://localhost:8080/update
参数：
{
  "id": 27,
  "username": "A27",
  "password": "WAS",
  "nickname": "王安石",
  "roles": "ROLE_CHILDREN"
}

```