ShardingSphere-读写分离

LiJunYi 2/2/2023 ShardingSphere

# 读写分离代码示例

# 引入依赖

<?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.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>sharding-jdbc读写分离示例</description>
    <properties>
        <java.version>1.8</java.version>
        <mybatis-spring-boot-starter.version>2.1.4</mybatis-spring-boot-starter.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <druid.version>1.2.5</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot-starter.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-core-common -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

</project>

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

# application-masterslave.yml配置

# 读写分离配置
spring:
  shardingsphere:
    #显示sql
    props:
      sql:
        show: true
    #配置数据源
    datasource:
      # 取别名
      names: ds1,ds2,ds3
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
      #配置从库ds2
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
      #配置从库ds3
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: admin123
        maxPoolSize: 100
        minPoolSize: 5
    sharding:
      default-data-source-name: ds1
    masterslave:
      name: ms
      master-data-source-name: ds1
      slave-data-source-names: ds2,ds3
      load-balance-algorithm-type: round_robin

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
38
39
40
41
42
43
44
45

# 业务代码

# UserController

@RestController
@RequestMapping("/user")
public class UserController
{
    @Autowired
    private IUserServer userService;

    /**
     * 写操作
     *
     * @return {@link String}
     */
    @GetMapping("/save")
    public String insert()
    {
        UserModel userModel = UserModel.builder()
                .name("zhangsan")
                .passWord("121212")
                .sex(1).build();
        userService.insert(userModel);
        return "success";
    }

    @GetMapping("/insert/{sex}")
    public String insert(@PathVariable("sex") int sex)
    {
        UserModel userModel = UserModel.builder()
                .name("zhangsan")
                .passWord("121212")
                .sex(sex).build();
        userService.insert(userModel);
        return "success";
    }

    /**
     * 用户列表
     * 读操作
     * @return {@link List<UserModel>}
     */
    @GetMapping("/list")
    public List<UserModel> listUsers()
    {
        return userService.getUsers();
    }
}

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
38
39
40
41
42
43
44
45

# Server方法

@Service
public class UserService implements IUserServer{

    @Autowired
    private UserDao userDao;

    @Transactional(rollbackFor = Exception.class)
    @ShardingTransactionType(TransactionType.XA)
    @Override
    public int insert(UserModel record) {
        return userDao.insert(record);
    }

    @Override
    public List<UserModel> getUsers() {
        return userDao.getUsers();
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 遇见的问题

# 错误详情

shardingsphere 启动的时候报错 Property ‘sqlSessionFactory‘ or ‘sqlSessionTemplate‘ are required

# 解决方法

如果使用了druid，依赖必须使用druid，而不能是 druid-spring-boot-starter

<!-- druid -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
    <version>1.1.22</version>
</dependency>

1
2
3
4
5
6

我的开发笔记

Choose mode