Fork me on GitHub

Sharding-JDBC不分库,只分表例子

目录

上一篇介绍的了Sharding-jdbc的读写分离,接下来我们写demo,介绍SpringBoot使用Sharding-JDBC不分库,只分表例子。话不多说,直接写代码。

准备

  • SpringBoot 2.1.12
  • Sharding-JDBC 4.0.0
  • Mybatis 3.x
  • Mysql 8.0
  • lombok

本文场景介绍

一个数据库,将user表分表分为四个一样的表,根据取模算法分别向user0-3的表里插入数据。

POM文件

pom文件引入如下相关依赖:

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
<?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>cn.cicoding</groupId>
<artifactId>shardingsphere-example</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<groupId>cn.cicoding</groupId>
<artifactId>sharding-jdbc-table</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-table</name>
<description>Demo project for Spring Boot</description>

<properties>
<java.version>1.8</java.version>
</properties>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>

<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

相关代码实现

Controller代码

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
package cn.cicoding.controller;

import cn.cicoding.model.User;
import cn.cicoding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

@Autowired
private UserService userService;

@GetMapping("/users")
public Object list() {
return userService.list();
}

@GetMapping("/add")
public Object add() {
for (long i = 0; i < 100; i++) {
User user = new User();
user.setId(i);
user.setCity("深圳");
user.setName("李四"+ i);
userService.add(user);
}
return "success";
}

@GetMapping("/users/{id}")
public Object get(@PathVariable Long id) {
return userService.findById(id);
}

@GetMapping("/users/query")
public Object get(String name) {
return userService.findByName(name);
}

}

Service代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package cn.cicoding.service;

import cn.cicoding.model.User;

import java.util.List;

public interface UserService {

List<User> list();

Long add(User user);

User findById(Long id);

User findByName(String name);

}

ServiceImpl代码

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
package cn.cicoding.service.impl;

import cn.cicoding.dao.UserDaoMapper;
import cn.cicoding.model.User;
import cn.cicoding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

@Autowired
private UserDaoMapper userDao;

public List<User> list() {
List<User> list = userDao.list();
return list;
}

public Long add(User user) {
return userDao.addUser(user);
}

@Override
public User findById(Long id) {
return userDao.findById(id);
}

@Override
public User findByName(String name) {
return userDao.findByName(name);
}

}

Dao代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package cn.cicoding.dao;

import cn.cicoding.model.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserDaoMapper {
Long addUser(User user);

List<User> list();

User findById(Long id);

User findByName(String name);
}

Mapper.xml代码实现

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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.cicoding.dao.UserDaoMapper">

<insert id="addUser">
INSERT INTO user (
id, city, name
)
VALUES (
#{id,jdbcType=INTEGER},
#{city,jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR}
)
</insert>

<select id="list" resultType="cn.cicoding.model.User">
SELECT u.* FROM user u
</select>

<select id="findById" resultType="cn.cicoding.model.User">
SELECT u.* FROM user u WHERE u.id=#{id,jdbcType=INTEGER}
</select>

<select id="findByName" resultType="cn.cicoding.model.User">
SELECT u.* FROM user u WHERE u.name=#{name,jdbcType=VARCHAR}
</select>

</mapper>

实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package cn.cicoding.model;

import lombok.Data;

import java.io.Serializable;

@Data
public class User implements Serializable {

private static final long serialVersionUID = -1205226416664488559L;

private Long id;

private String city = "";

private String name = "";


}

到这我们完成了基本的代码编写,由于sharding-jdbc是jar包,我们来看主要的配置信息

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
#使用取模的方式来实现表分片
server.port=8084

# mybatis对应的映射文件路径
mybatis.mapper-locations=classpath:mapper/*.xml
# mybatis对应的实体类
mybatis.type-aliases-package=cn.cicoding.model

spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://localhost:3309/ds_0?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root

# 展示sql打印
spring.shardingsphere.props.sql.show=true

# 自定义分表算法
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.example.MyCustomShardingAlgorithm

# 分表配置
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds.user_$->{0..3}
# inline 表达式 (id类型转换 -> id.longValue() -> user_${id.longValue() % 4})
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE

启动类

1
2
3
4
5
6
7
8
9
10
11
12
13
package cn.cicoding;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ShardingJdbcDemoApplication {

public static void main(String[] args) {
SpringApplication.run(ShardingJdbcDemoApplication.class, args);
}

}

测试演示

启动启动类,访问http://localhost:8084/add

查看数据库中user_0,user_1,user_2,user_3中每个数据库库中都有数据,且id都相差4

我们看到DataSources是master节点主库

我们再次访问http://localhost:8084/users

看到数据库中的数据user_0,user_1,user_2,user_3中都查询出来了,实现了不分库分表策略!

到此我们就实现了sharding-jdbc主从读写分离实现,更多配置请参考此处

相关文章