sharding-jdbc读写分离

首先需要mysql数据库实现主从复制。
注意，sharding-jdbc只负责主从数据库的路由分发，不负责主从数据库之间的复制，复制由mysql实现。

1.配置文件

#分片规则
#数据源 、《《读写分离》》 数据源声明
spring.shardingsphere.datasource.names=m1,m2,m0,s0
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://192.168.230.145:3306/order_db_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://192.168.230.145:3306/order_db_2?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root
# 《《读写分离》》 数据源
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://192.168.230.145:3306/user-db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://192.168.230.147:3306/user-db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root

#分库策略
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=m$->{user_id%2+1}
# 《《读写分离》》 分库策略
spring.shardingsphere.sharding.master-slave-rules.db0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.db0.slave-data-source-names=s0

#指定表数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=m$->{1..2}.c_order_$->{1..2}
# 《《读写分离》》 指定表数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.c_user.actual-data-nodes=db0.c_user

#主键生成策略:雪花算法
spring.shardingsphere.sharding.tables.c_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE

#分表策略
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order_$->{order_id % 2 + 1}

#输出日志
spring.shardingsphere.props.sql.show=true

#公共表，广播表
spring.shardingsphere.sharding.broadcast-tables=c_dict

2.sql执行语句

@Insert("insert into c_user (id,name) value (#{id},#{name})")
void insertUser(@Param("id")Integer id, @Param("name")String name);

@Select("select * from c_user where id = #{id}")
Map selectUser(@Param("id")Integer id);

3.测试类

	@Test
    public void testMasterSlaveDistrict(){
        cUserMapper.insertUser(1,"jack");
    }

    @Test
    public void testMasterSlaveDistrictRead(){
        Map map = cUserMapper.selectUser(1);
        System.out.println(map);
    }

4.执行结果

2021-05-15 21:05:18.302 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : ==>  Preparing: insert into c_user (id,name) value (?,?)
2021-05-15 21:05:18.330 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : ==> Parameters: 1(Integer), jack(String)
2021-05-15 21:05:18.910  INFO 16748 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into c_user (id,name) value (?,?)
2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=c_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=c_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=19)], parametersIndex=2, logicSQL=insert into c_user (id,name) value (?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, name], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@498c535d, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@16ee9aa7])])
2021-05-15 21:05:18.911  INFO 16748 --- [           main] ShardingSphere-SQL                       : Actual SQL: m0 ::: insert into c_user  (id, name) VALUES (?, ?) ::: [1, jack]
2021-05-15 21:05:18.945 DEBUG 16748 --- [           main] c.dt.demo.mapper.CUserMapper.insertUser  : <==    Updates: 1

2021-05-15 21:05:51.762 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : ==>  Preparing: select * from c_user where id = ?
2021-05-15 21:05:51.793 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : ==> Parameters: 1(Integer)
2021-05-15 21:05:52.377  INFO 3704 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : Logic SQL: select * from c_user where id = ?
2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=c_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=c_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from c_user where id = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2021-05-15 21:05:52.378  INFO 3704 --- [           main] ShardingSphere-SQL                       : Actual SQL: s0 ::: select * from c_user where id = ? ::: [1]
2021-05-15 21:05:52.420 DEBUG 3704 --- [           main] c.dt.demo.mapper.CUserMapper.selectUser  : <==      Total: 1
{name=jack, id=1}