SpringBoot集成Sharding-JDBC实现主从同步

1.mysql主从配置

详细内容请参考上一篇文章：MySQL8.0以上实现主从同步配置

2.application.properties文件配置

# ShardingSphere configuration
spring.shardingsphere.props.sql.show=true


spring.shardingsphere.datasource.names=m0,s0

# master DataSource configuration m0
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

# slave DataSource configuration  s0
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://localhost:3307/user_db?useUnicode=true&characterEncoding=utf8&useSSL=false
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root

# set master and slave
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0


#
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=m$->{user_id % 2 +1}


spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

# point common table t_dict
spring.shardingsphere.sharding.broadcast-tables=t_dict
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

本文中主库：m0，从库：s0

3.测试

3.1 查询数据

xml的sql语句实现条件查询：

  <select id="selectOrdersByuserID" resultType="com.test.sharding.domain.pojo.User">
    select *
    from t_user
    where user_id in
    <foreach collection="userIds" item="index" open="(" separator="," close=")">
      #{index}
    foreach>
  select>

1
2
3
4
5
6
7
8
9

可以看到查询结果：

Logic SQL: select *
    from t_user
    where user_id in
     (  
      ?
     )
Actual SQL: s0 ::: select *
                  from t_user
							   where user_id in
							    (  
							     ?
							    ) ::: [1]
1
2
3
4
5
6
7
8
9
10
11
12

可看看到上面的查询语句实际上是去从库s0查了

3.2 添加数据

dao层插入语句：

    @Insert("insert into t_user(user_id,fullname,user_type) values (#{userId},#{fullname},#{userType})")
    int insertUser(User user);
1
2