官方文档

https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/usage/read-write-splitting/

一、环境

1.软件环境

SpringBoot2.3 + MyBatis-Plus + Sharding-jdbc+MySQL读写分离实验

2.MySQL环境

CentOS7虚拟机，MySQL一主一从，提前准备好主从，确保主从同步没问题。
主库：192.168.140.152
从库：192.168.140.151

二、Java

定位到官方文档中：使用Spring 部分

1.配置

maven依赖

核心部分

  1. <dependencies>
  2.         <!--集成springmvc框架并实现自动配置 -->
  3.         <dependency>
  4.             <groupId>org.springframework.boot</groupId>
  5.             <artifactId>spring-boot-starter-web</artifactId>
  6.             <exclusions><!-- 去掉springboot默认配置 -->
  7.                 <exclusion>
  8.                     <groupId>org.springframework.boot</groupId>
  9.                     <artifactId>spring-boot-starter-logging</artifactId>
  10.                 </exclusion>
  11.             </exclusions>
  12.         </dependency>
  13.         <!-- 引入log4j2依赖 -->
  14.         <dependency>
  15.             <groupId>org.springframework.boot</groupId>
  16.             <artifactId>spring-boot-starter-log4j2</artifactId>
  17.         </dependency>
  20.         <dependency>
  21.             <groupId>org.springframework.boot</groupId>
  22.             <artifactId>spring-boot-starter-test</artifactId>
  23.             <scope>test</scope>
  24.         </dependency>
  25.         <dependency>
  26.             <groupId>org.springframework.boot</groupId>
  27.             <artifactId>spring-boot-starter-aop</artifactId>
  28.         </dependency>
  29.         <dependency>
  30.             <groupId>org.springframework.boot</groupId>
  31.             <artifactId>spring-boot-starter-actuator</artifactId>
  32.         </dependency>
  34.         <!-- Lombok -->
  35.         <dependency>
  36.             <groupId>org.projectlombok</groupId>
  37.             <artifactId>lombok</artifactId>
  38.         </dependency>
  40.         <!-- mybatis-plus -->
  41.         <dependency>
  42.             <groupId>com.baomidou</groupId>
  43.             <artifactId>mybatis-plus-boot-starter</artifactId>
  44.             <version>${mybatis-plus.version}</version>
  45.         </dependency>
  47.         <dependency>
  48.             <groupId>com.baomidou</groupId>
  49.             <artifactId>mybatis-plus-generator</artifactId>
  50.             <version>${mybatis-plus.version}</version>
  51.         </dependency>
  52.         <dependency>
  53.             <groupId>org.apache.velocity</groupId>
  54.             <artifactId>velocity-engine-core</artifactId>
  55.             <version>2.1</version>
  56.         </dependency>
  57.         <dependency>
  58.             <groupId>org.freemarker</groupId>
  59.             <artifactId>freemarker</artifactId>
  60.             <version>2.3.29</version>
  61.         </dependency>
  63.         <!-- druid -->
  64.         <dependency>
  65.             <groupId>com.alibaba</groupId>
  66.             <artifactId>druid-spring-boot-starter</artifactId>
  67.             <version>${druid.version}</version>
  68.         </dependency>
  71.         <!--mysql-->
  72.         <dependency>
  73.             <groupId>mysql</groupId>
  74.             <artifactId>mysql-connector-java</artifactId>
  75.             <version>5.1.47</version>
  76.             <scope>runtime</scope>
  77.         </dependency>
  80.         <dependency>
  81.             <groupId>io.shardingsphere</groupId>
  82.             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  83.             <version>3.1.0</version>
  84.         </dependency>
  85.         <!-- for spring namespace -->
  86.         <dependency>
  87.             <groupId>io.shardingsphere</groupId>
  88.             <artifactId>sharding-jdbc-spring-namespace</artifactId>
  89.             <version>3.1.0</version>
  90.         </dependency>
  93.     </dependencies>

application.yml



server:
  port: 9999

spring:
  application:
    name: xxoo
  main:
    allow-bean-definition-overriding: true #设置为true，表示后发现的bean会覆盖之前相同名称的bean。
#  datasource:
#    driver-class-name:
#    url:  jdbc:mysql://192.168.140.141:3306/xxoo?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
#    data-username: root
#    data-password: 123456


sharding:
  jdbc:
    datasource:
      names: master,salve #主从数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver #数据库新的驱动，下面连接url一定要加区时
        url: jdbc:mysql://192.168.140.152:3306/xxoo?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 123456
      salve:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.140.151:3306/xxoo?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 123456
    config:  #读写分离配置
      masterslave:
        name: ms
        master-data-source-name: master #主数据库配置
        slave-data-source-names: salve #从数据库配置（多个以逗号隔开）
        #load-balance-algorithm-type: round_robin # 提供轮询与随机(random)，这里选择用轮询，
      props:
        sql:
          show: true #打印sql日志


#mybatis plus 设置
mybatis-plus:
  mapper-locations: classpath*:mapper/*Mapper.xml
  global-config:
    # 关闭MP3.0自带的banner
    banner: false
    db-config:
      #主键类型  0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
      id-type: ASSIGN_ID
      # 默认数据库表下划线命名
      table-underline: true
  configuration:
    # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用
    #log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 返回类型为Map,显示null对应的字段
    call-setters-on-nulls: true
#enable swagger
swagger:
  enable: true

这一行必定配上
spring.application.main.allow-bean-definition-overriding=true
数据源可以使用DruidDataSource ，sharding-jdbc 不care你用的啥
启动后，会报错：

org.springframework.dao.InvalidDataAccessApiUsageException: ConnectionCallback; isValid; nested exception is java.sql.SQLFeatureNotSupportedException: isValid

是Spring Boot 2.3数据源健康检查引起。解决办法是继承 DataSourceHealthContributorAutoConfiguration 重写 createIndicator 方法
需要如下写个配置类，即可。

@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {

    @Value("${spring.datasource.dbcp2.validation-query:select 1}")
    private String defaultQuery;


    public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
        super(dataSources, metadataProviders);
    }

    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
        DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
        if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery(defaultQuery);
        }
        return indicator;
    }
}

无需其他配置，对用户透明，select就走从库，增删改就走主库：

--------->NbBase Started <---------
19:52:07.090 [RMI TCP Connection(1)-192.168.140.1] INFO  ShardingSphere-SQL - Rule Type: master-slave
19:52:07.091 [RMI TCP Connection(1)-192.168.140.1] INFO  ShardingSphere-SQL - SQL: select 1 ::: DataSources: salve
19:52:08.744 [RMI TCP Connection(3)-192.168.140.1] INFO  org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/] - Initializing Spring DispatcherServlet 'dispatcherServlet'
19:52:08.744 [RMI TCP Connection(3)-192.168.140.1] INFO  org.springframework.web.servlet.DispatcherServlet - Initializing Servlet 'dispatcherServlet'
19:52:08.750 [RMI TCP Connection(3)-192.168.140.1] INFO  org.springframework.web.servlet.DispatcherServlet - Completed initialization in 5 ms
20:10:31.191 [http-nio-9999-exec-3] INFO  ShardingSphere-SQL - Rule Type: master-slave
20:10:31.191 [http-nio-9999-exec-3] INFO  ShardingSphere-SQL - SQL: SELECT id,name,age FROM user WHERE id=?  ::: DataSources: salve
20:10:34.406 [http-nio-9999-exec-4] INFO  ShardingSphere-SQL - Rule Type: master-slave
20:10:34.406 [http-nio-9999-exec-4] INFO  ShardingSphere-SQL - SQL: SELECT id,name,age FROM user WHERE id=?  ::: DataSources: salve
20:10:44.030 [http-nio-9999-exec-7] INFO  ShardingSphere-SQL - Rule Type: master-slave
20:10:44.030 [http-nio-9999-exec-7] INFO  ShardingSphere-SQL - SQL: INSERT INTO user  ( name,
age )  VALUES  ( ?,
? ) ::: DataSources: master