Springboot-Sharding-Jdbc集成(二)读写分离

我遇见你，我记得你，这座城市天生就适合谈恋爱，你天生就适合我的灵魂。

---

一.pom.xml 引入

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.2</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.yy</groupId>
	<artifactId>springboot-sharding-jdbc</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springboot-sharding-jdbc</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
		<spring-cloud.version>2020.0.1</spring-cloud.version>
		<spring-cloud-alibab.version>2021.1</spring-cloud-alibab.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
 
		<dependency>
			<groupId>org.springframework.cloud</groupId>
			<artifactId>spring-cloud-starter-bootstrap</artifactId>
		</dependency>
 
		<!--nacos 服务发现-->
		<dependency>
			<groupId>com.alibaba.cloud</groupId>
			<artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
		</dependency>
		<!--nacos 配置管理-->
		<dependency>
			<groupId>com.alibaba.cloud</groupId>
			<artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId>
		</dependency>
 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
			<version>2.5.6</version>
		</dependency>
 
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
 
		<!--shaeding-jdbc-->
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>4.0.0-RC1</version>
		</dependency>
		<dependency>
			<groupId>io.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-namespace</artifactId>
			<version>3.1.0</version>
		</dependency>
 
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.20</version>
			<scope>compile</scope>
		</dependency>
 
		<!--mybatis-plu-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-generator</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>org.freemarker</groupId>
			<artifactId>freemarker</artifactId>
			<version>2.3.28</version>
		</dependency>
 
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.5.8</version>
		</dependency>
 
		<!--swagger生成在线接口文档-->
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger2</artifactId>
			<version>2.7.0</version>
		</dependency>
		<dependency>
			<groupId>io.springfox</groupId>
			<artifactId>springfox-swagger-ui</artifactId>
			<version>2.7.0</version>
		</dependency>
 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
 
 
 
	</dependencies>
 
	<dependencyManagement>
		<!--如果要使用Spring Cloud 的组件都需要在pom.xml中添加如下的配置；-->
		<dependencies>
			<dependency>
				<groupId>org.springframework.cloud</groupId>
				<artifactId>spring-cloud-dependencies</artifactId>
				<version>${spring-cloud.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
			<!--如果要使用Spring Cloud Alibaba 的组件都需要在pom.xml中添加如下的配置；-->
			<dependency>
				<groupId>com.alibaba.cloud</groupId>
				<artifactId>spring-cloud-alibaba-dependencies</artifactId>
				<version>${spring-cloud-alibab.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
 
 
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
 
	<repositories>
		<repository>
			<id>public</id>
			<name>aliyun nexus</name>
			<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
			<releases>
				<enabled>true</enabled>
			</releases>
		</repository>
	</repositories>
 
	<pluginRepositories>
		<pluginRepository>
			<id>public</id>
			<name>aliyun nexus</name>
			<url>http://maven.aliyun.com/nexus/content/groups/public//</url>
			<releases>
				<enabled>true</enabled>
			</releases>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</pluginRepository>
	</pluginRepositories>
 
</project>

二.项目文件

Mapper

public interface UserMapper extends BaseMapper<User> {
 
}

domain 实体类

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_user")
@ApiModel(value="User对象", description="用户表")
public class User implements Serializable {
 
    @ApiModelProperty(value = "用户编号")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
 
    @ApiModelProperty(value = "用户昵称")
    @TableField(value = "nickname")
    private String nickname;
 
    @ApiModelProperty(value = "用户密码")
    @TableField(value = "password")
    private String password;
 
    @ApiModelProperty(value = "性别")
    @TableField(value = "sex")
    private Integer sex;
 
    @ApiModelProperty(value = "年龄")
    @TableField(value = "age")
    private Integer age;
 
    @ApiModelProperty(value = "生日")
    @TableField(value = "birthday")
    private String birthday;
}

Controller

@RestController
@RequestMapping("/api/user")
public class UserController {
 
    @Autowired
    private UserMapper userMapper;
 
    @GetMapping("/save")
    public String addUser() {
        User user = new User();
        user.setNickname("JueDream" + new Random().nextInt());
        user.setPassword("123456");
        user.setAge(18);
        user.setSex(1);
        user.setBirthday("1997-12-03");
        userMapper.insert(user);
        return "success";
    }
 
    @GetMapping("/findUsers")
    public List<User> findUsers() {
        return userMapper.selectList(new LambdaQueryWrapper<User>());
    }
}

Application 启动类

@Slf4j
@MapperScan("com.yy.ssj.mapper")
@SpringBootApplication
@EnableDiscoveryClient
public class SpringbootShardingJdbcApplication {
 
	public static void main(String[] args) throws UnknownHostException {
		ConfigurableApplicationContext application = SpringApplication.run(SpringbootShardingJdbcApplication.class, args);
		Environment env = application.getEnvironment();
		String ip = InetAddress.getLocalHost().getHostAddress();
		String port = env.getProperty("server.port");
		String path = env.getProperty("server.servlet.context-path");
		log.info("\n----------------------------------------------------------\n\t" +
				"Application springboot-sharding-jdbc is running! Access URLs:\n\t" +
				"Local: \t\thttp://localhost:" + port + path + "\n\t" +
				"External: \thttp://" + ip + ":" + port + path + "\n\t" +
				"----------------------------------------------------------");
	}
}

bootstrap.yml 配置文件

spring:
  # Nacos 注册 配置
  cloud:
    nacos:
      config:
        server-addr: IP:8848
        file-extension: yaml
        group: sharding-jdbc
        prefix: ${spring.application.name}-${spring.profiles.active}.yaml
      discovery:
        server-addr: IP:8848
        group: sharding-jdbc
  application:
    name: springboot-sharding-jdbc
  profiles:
    active: rw
dream:
  active: local-nacos

application.yml 配置文件

server:
  port: 10012
  servlet:
    context-path: /
spring:
  application:
    name: springboot-sharding-jdbc
  main:
    allow-bean-definition-overriding: true
  profiles:
    active: rw
# mybatis-plus 配置
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  global-config:
    # 逻辑删除配置
    db-config:
      # 删除前
      logic-not-delete-value: 0
      # 删除后
      logic-delete-value: 1

application-rw.yml 配置文件

# sharding-jdbc 4.0 配置读写分离
spring:
  shardingsphere:
    # 显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      names: ds0,ds1,ds2
      # master-ds0数据库连接信息
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://IP:13311/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
        maxPoolSize: 100
        minPoolSize: 5
      # slave-ds1数据库连接信息
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://IP:13312/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
        maxPoolSize: 100
        minPoolSize: 5
      # slave-ds2数据库连接信息
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://IP:13312/sharding-jdbc-db?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
        minPoolSize: 5
      ds:
        maxPoolSize: 100
    # 配置默认数据源ds0 默认数据源,主要用于写
    sharding:
      default-data-source-name: ds0
    # 读写分离 单个读写分离
    masterslave:
      # 配置主从名称
      name: ds-ms
      # 配置主库master,负责数据的写入
      master-data-source-name: ds0
      # 配置从库slave节点
      slave-data-source-names: ds1,ds2
      # 配置slave节点的负载均衡均衡策略,采用轮询机制
      load-balance-algorithm-type: round_robin
 

三.验证读写分离

依次访问controler 接口; ds0 ->master ds1,ds2->slave

-- slave 从库查询
http://localhost:10012/api/user/findUsers
 
-- master 主库写入
http://localhost:10012/api/user/save

1.主库查询

2.从库更新 

---

 你是往事的遗书 是日落的余情未了 是路人脚下不停生长的风