我们的主从复制已经提前搭建好： mysql-搭建主从复制：https://blog.csdn.net/m0_65152767/article/details/142214434

1、application.yml

spring:datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver# 驱动类：使用shardingjdbc提供的驱动类driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
#    username: root
#    password: 123456
#    url: jdbc:mysql://192.168.74.148:3306/mydb2?serverTimezone=GMT%2B8url: jdbc:shardingsphere:classpath:shardingsphere.yaml

2、shardingsphere.yaml

# 模式配置
mode:# shardingjdbc：使用的是单机模式type: Standalonerepository:type: JDBC
# 数据源配置
dataSources:write_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3306/mydb2username: rootpassword: 123456read_ds_0:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.74.148:3316/mydb2username: rootpassword: 123456# 读写分离配置：指定读交给哪个数据源 写交给哪个
rules:- !READWRITE_SPLITTINGdataSources:readwrite_ds:writeDataSourceName: write_dsreadDataSourceNames:- read_ds_0transactionalReadQueryStrategy: PRIMARY # 事务内读请求的路由策略，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC# 配置读操作的负载均衡策略loadBalancerName: randomloadBalancers:random:type: RANDOMround_robin:type: ROUND_ROBINweight:type: WEIGHTprops:read_ds_0: 1
# 输出sql：
props:# 打印 sharding 的sql日志sql-show: true

3、创建实体类 User

package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_user")
public class User {private Long id;private String name;}

4、创建 UserMapper

package com.atguigu.sharding.jdbc.demo.mapper;
import com.atguigu.sharding.jdbc.demo.bean.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

5、添加依赖

    <dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--操作数据库--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.30</version></dependency><!--mybatisPlus--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.1</version></dependency><!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>5.4.0</version></dependency><!--兼容jdk17和spring boot3--><dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>1.33</version></dependency><dependency><groupId>org.glassfish.jaxb</groupId><artifactId>jaxb-runtime</artifactId><version>2.3.8</version></dependency></dependencies>

6、读写分离测试

package com.atguigu.sharding.jdbc.demo;import com.atguigu.sharding.jdbc.demo.bean.User;
import com.atguigu.sharding.jdbc.demo.mapper.UserMapper;
import jakarta.annotation.Resource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest
class ShardingJdbcDemoApplicationTests {@ResourceUserMapper userMapper;@Testvoid contextLoads() {for (int i = 0; i < 3; i++) {//User user1 = userMapper.selectById(1);userMapper.selectList(null).forEach(System.out::println);}}}

2024-09-24T10:10:43.199+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name  FROM t_user
2024-09-24T10:10:43.199+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Actual SQL: read_ds_0 ::: SELECT  id,name  FROM t_user
User(id=1, name=张三)
User(id=2, name=李四)
User(id=3, name=3316)
2024-09-24T10:10:43.277+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name  FROM t_user
2024-09-24T10:10:43.277+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Actual SQL: read_ds_0 ::: SELECT  id,name  FROM t_user
User(id=1, name=张三)
User(id=2, name=李四)
User(id=3, name=3316)
2024-09-24T10:10:43.282+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT  id,name  FROM t_user
2024-09-24T10:10:43.282+08:00  INFO 1436 --- [           main] ShardingSphere-SQL                       : Actual SQL: read_ds_0 ::: SELECT  id,name  FROM t_user
User(id=1, name=张三)
User(id=2, name=李四)
User(id=3, name=3316)

7、事务测试

transactionalReadQueryStrategy: PRIMARY

事务内读请求的路由策略，可选值：

PRIMARY（路由至主库）

FIXED（同一事务内路由至固定数据源）

DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC

测试1：

不添加@Transactional：insert对主库操作，select对从库操作

测试2：

添加@Transactional：则insert和select按照transactionalReadQueryStrategy的配置执行

/*** 事务测试*/
@Transactional//开启事务
@Test
public void testTrans(){User user = new User();user.setUname("铁锤");userMapper.insert(user);List<User> users = userMapper.selectList(null);
}

注意： 在JUnit环境下的@Transactional注解，默认情况下就会对事务进行回滚（即使在没加注解@Rollback，也会对事务回滚）
常见错误

ShardingSphere-JDBC远程连接的方式默认的密码加密规则是：mysql_native_password

因此需要在服务器端修改服务器的密码加密规则，如下：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';