Sharding-Sphere:Sharding-JDBC分库分表(基于JPA)

官方文档

GitHub的地址:https://github.com/apache/incubator-shardingsphere
shardingsphere:https://github.com/apache/incubator-shardingsphere-example
中文文档地址:https://shardingsphere.apache.org/document/current/cn/overview/

代码实现(JPA)

(1)有3张表t_membert_ordert_order_item,拆分到2个数据库中,每个数据库2份,相当于每张表拆成了4张,需要注意的是每个表的主键Id尽量设置成 bigint (我开始设置为varchar,结果分表时报错),结构如下:

分库分表设计

(2)新建SpringBoot项目,引入依赖:

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
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>

(3)添加配置文件,这点很重要,否则无法实现分库分表,ds$->{0..1}.t_member$->{0..1},表达式相当于:ds0.t_member0ds0.t_member1ds1.t_member0ds1.t_member1,在配置中配置了key-generator.column,所以新增该表数据时,不需要给主键id赋值,sharding-jdbc会调用内置的SNOWFLAKE算法生成分布式id,请往下看

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
server.port=8072

spring.jpa.hibernate.ddl-auto=none
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://47.107.177.150:3306/ds0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds0.username=xnxx
spring.shardingsphere.datasource.ds0.password=Xnxx85781245!

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.107.177.150:3306/ds1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds1.username=xnxx
spring.shardingsphere.datasource.ds1.password=Xnxx85781245!

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{member_id % 2}

spring.shardingsphere.sharding.tables.t_member.actual-data-nodes=ds$->{0..1}.t_member$->{0..1}
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.algorithm-expression=t_member$->{member_id % 2}
spring.shardingsphere.sharding.tables.t_member.key-generator.column=member_id
spring.shardingsphere.sharding.tables.t_member.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order0, ds1.t_order1
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
#spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
#spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#
#spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
#spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
#spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE


#spring.shardingsphere.sharding.binding-tables=t_member,t_order,t_order_item
#spring.shardingsphere.sharding.broadcast-tables=t_config


spring.shardingsphere.props.sql.show=true

(4)Durid配置

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
@Configuration
public class DuridConfig {

@Bean
public Filter statFilter() {
StatFilter filter = new StatFilter();
filter.setSlowSqlMillis(5000);
filter.setLogSlowSql(true);
filter.setMergeSql(true);
return filter;
}

@Bean
public ServletRegistrationBean statViewServlet() {
//创建servlet注册实体
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//设置ip白名单
servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
//设置ip黑名单,如果allow与deny共同存在时,deny优先于allow
//servletRegistrationBean.addInitParameter("deny","192.168.0.19");
//设置控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "123456");
//是否可以重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}

}

(5)添加MemberService服务接口与实现

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
@Data
@Entity
@Table(name = "t_member")
public class Member {

@Id
@Column(name = "member_id")
private Long memberId;

@Column(name = "member_name")
private String memberName;

@Column(name = "nick_name")
private String nickName;

@Column(name = "account_no")
private String accountNo;

@Column(name = "password")
private String password;

@Column(name = "age")
private Integer age;

@Column(name = "birth_date")
private Date birthDate;

@Column(name = "ebl_flag")
private String eblFlag;

@Column(name = "del_flag")
private String delFlag;

@Column(name = "description")
private String description;

@Column(name = "create_time")
private Date createTime;

@Column(name = "update_time")
private Date updateTime;
}

@Repository
public interface MemberRepository extends JpaRepository<Member , Long> {

}

(6)添加MemberController

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
@RequestMapping("/member")
@RestController
public class MemberController {

@Autowired private MemberRepository memberRepository;

@RequestMapping("/health")
public String health(){
return "success";
}

@RequestMapping("/findAll")
public Object findAll(){
return memberRepository.findAll();
}

@RequestMapping("/add")
public Object add(){
Member member = addMember();
return memberRepository.save(member);
}

@RequestMapping("/findById")
public Member findById(Long memberId){
return memberRepository.findById(memberId).get();
}

private Member addMember(){
Member member = new Member();
member.setMemberId(IdWorker.getLongId());
//SnowflakeShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
//member.setMemberId(Long.parseLong(keyGenerator.generateKey().toString()));
member.setMemberName("张三");
member.setNickName("闪耀的瞬间");
member.setAccountNo(member.getMemberId()+"");
member.setPassword("123465");
member.setAge(27);
member.setBirthDate(new Date());
member.setEblFlag("1");
member.setDelFlag("0");
member.setDescription("xxx");
member.setCreateTime(new Date());
member.setUpdateTime(new Date());
return member;
}
}

(7)启动项目,访问新增接口 http://localhost:8071/member/add ,可以看到页面返回了新增后的数据

同时查看Intellij IDEA控制台,可以看到数据插入到ds1库的t_member1表了

再查看ds1数据库的t_member1表,有刚刚新增的记录

(8)访问查询接口 http://localhost:8072/member/findById?memberId=xxxxxx

同时查看控制台,sql会自动路由到ds1库去查询数据

参考:https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business/db-table-jpa

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :