Sharding-JDBC+JPA|MyBatis+Druid分库分表实现

Spring Boot入门教程(四十四): Sharding-JDBC+JPA|MyBatis+Druid分库分表实现
原创vbirdbest 最后发布于2018-07-23 22:31:08 阅读数 7834 收藏
展开
分享一个朋友的人工智能教程。比较通俗易懂，风趣幽默，感兴趣的朋友可以去看看。
一：数据库分片方案
客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现

二：Sharding-JDBC
Sharding-JDBC: https://github.com/dangdangdotcom/sharding-jdbc

Sharding-JDBC是一个开源的适用于微服务的分布式数据访问基础类库，它始终以云原生的基础开发套件为目标。

Sharding-JDBC定位为轻量级java框架，使用客户端直连数据库，以jar包形式提供服务，未使用中间层，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式，可理解为增强版的JDBC驱动，旧代码迁移成本几乎为零。

Sharding-JDBC完整的实现了分库分表，读写分离和分布式主键功能，并初步实现了柔性事务。从2016年开源至今，在经历了整体架构的数次精炼以及稳定性打磨后，如今它已积累了足够的底蕴，相信可以成为开发者选择技术组件时的一个参考。

分库分表
SQL解析功能完善，支持聚合，分组，排序，LIMIT，TOP等查询，并且支持级联表以及笛卡尔积的表查询
支持内、外连接查询
分片策略灵活，可支持=，BETWEEN，IN等多维度分片，也可支持多分片键共用，以及自定义分片策略
基于Hint的强制分库分表路由
读写分离
一主多从的读写分离配置，可配合分库分表使用
基于Hint的强制主库路由
柔性事务
最大努力送达型事务
TCC型事务(TBD)
分布式主键
统一的分布式基于时间序列的ID生成器
兼容性
可适用于任何基于java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC
可基于任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid等
理论上可支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer和PostgreSQL
灵活多样的配置
Java
YAML
Inline表达式
Spring命名空间
Spring boot starter
分布式治理能力 (2.0新功能)
配置集中化与动态化，可支持数据源、表与分片策略的动态切换(2.0.0.M1)
客户端的数据库治理，数据源失效自动切换(2.0.0.M2)
基于Open Tracing协议的APM信息输出(2.0.0.M3)
架构图

三：sharding-jdbc + jpa + druid集成

0. 数据库
— 在db0数据库上分别创建t_order_0、t_order_1表
USE db0;
DROP TABLE IF EXISTS t_order_0;
CREATE TABLE t_order_0 (
order_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS t_order_1;
CREATE TABLE t_order_1 (
order_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

— 在db1数据库上分别创建t_order_0、t_order_1表
USE db1;
DROP TABLE IF EXISTS t_order_0;
CREATE TABLE t_order_0 (
order_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS t_order_1;
CREATE TABLE t_order_1 (
order_id bigint(20) NOT NULL,
user_id bigint(20) NOT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
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
1. 引入依赖
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd”>
<modelVersion>4.0.0</modelVersion>

<groupId>com.company</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>sharding-jdbc</name>
<description>Demo project for Spring Boot</description>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath/> <!– lookup parent from repository –>
</parent>

<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</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>
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
63
64
65
66
67
68
69
70
71
72
73
74
75
注意mysql-connector-java的版本不要太高了

2. application.yml
spring:
jpa:
database: mysql
show-sql: true
hibernate:
ddl-auto: none
1
2
3
4
5
6
注意：hibernate.ddl-auto=none 是因为分表就会有多个表，例如t_order_0、t_order_1等，而ORM只能映射成一个，所以关闭自动的ddl语句。

3. domain
@Entity
@Table(name = “t_order”)
@Data
public class Order {
@Id
private Long orderId;

private Long userId;
}
1
2
3
4
5
6
7
8
9
注意：orderId上使用@Id注解并没有使用@GeneratedValue(strategy = GenerationType.AUTO)的主键生成策略，原因是分表必须要保证所有表的主键id不重复，如果使用mysql的自动生成，那么id就会重复，这里的id一般要使用分布式主键id来通过代码来生成。

4. Repository
import com.company.shardingjdbc.domain.Order;
import org.springframework.data.repository.CrudRepository;

public interface OrderRepository extends CrudRepository<Order, Long> {
}
1
2
3
4
5
5. Controller
import com.company.shardingjdbc.domain.Order;
import com.company.shardingjdbc.repository.OrderRepository;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(“/order”)
public class OrderController {

@Autowired
private OrderRepository orderRepository;

@Autowired
private KeyGenerator keyGenerator;

@RequestMapping(“/create”)
public Object add() {
for (int i = 0; i < 10; i++) {
Order order = new Order();
order.setUserId((long) i);
order.setOrderId((long) i);
orderRepository.save(order);
}
for (int i = 10; i < 20; i++) {
Order order = new Order();
order.setUserId((long) i + 1);
order.setOrderId((long) i);
orderRepository.save(order);
}

// for (int i = 0; i < 30; i++) {
// Order order = new Order();
// order.setOrderId(keyGenerator.generateKey().longValue());
// order.setUserId(keyGenerator.generateKey().longValue());
// orderRepository.save(order);
// }

return “success”;
}

@RequestMapping(“query”)
private Object queryAll() {
return orderRepository.findAll();
}
}
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
6. Configuration
package com.company.shardingjdbc.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import com.company.shardingjdbc.common.ModuleDatabaseShardingAlgorithm;
import com.company.shardingjdbc.common.ModuleTableShardingAlgorithm;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import com.mysql.jdbc.Driver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfiguration {
@Bean
public DataSource getDataSource() throws SQLException {
return buildDataSource();
}

private DataSource buildDataSource() throws SQLException {
// 设置分库映射
Map<String, DataSource> dataSourceMap = new HashMap<>(2);
// 添加两个数据库db0,db1到map里
dataSourceMap.put(“db0”, createDataSource(“db0”));
dataSourceMap.put(“db1”, createDataSource(“db1”));
// 设置默认db为db0，也就是为那些没有配置分库分表策略的指定的默认库
// 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, “db0”);

// 设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
// 0和1两个表是真实的表，t_order是个虚拟不存在的表，只是供使用。如查询所有数据就是select * from t_order就能查完0和1表的
TableRule orderTableRule = TableRule.builder(“t_order”)
.actualTables(Arrays.asList(“t_order_0”, “t_order_1”))
.dataSourceRule(dataSourceRule)
.build();

// 具体分库分表策略，按什么规则来分
ShardingRule shardingRule = ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule))
.databaseShardingStrategy(new DatabaseShardingStrategy(“user_id”, new ModuleDatabaseShardingAlgorithm()))
.tableShardingStrategy(new TableShardingStrategy(“order_id”, new ModuleTableShardingAlgorithm())).build();

DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);

return dataSource;
}

private static DataSource createDataSource(final String dataSourceName) {
// 使用druid连接数据库
DruidDataSource result = new DruidDataSource();
result.setDriverClassName(Driver.class.getName());
result.setUrl(String.format(“jdbc:mysql://localhost:3306/%s”, dataSourceName));
result.setUsername(“root”);
result.setPassword(“root123″);
return result;
}

@Bean
public KeyGenerator keyGenerator() {
return new DefaultKeyGenerator();
}
}
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
63
64
65
66
67
68
69
70
71
72
73
74
75
ModuleDatabaseShardingAlgorithm

package com.company.shardingjdbc.common;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;

import java.util.Collection;
import java.util.LinkedHashSet;

/**
* 单键数据库分片算法.
*
* 支持单键和多键策略
* <ul>
* <li>单键 SingleKeyDatabaseShardingAlgorithm</li>
* <li>多键 MultipleKeysDatabaseShardingAlgorithm</li>
* </ul>
*
* 支持的分片策略
* <ul>
* <li> = doEqualSharding 例如 where order_id = 1 </li>
* <li> IN doInSharding 例如 where order_id in (1, 2)</li>
* <li> BETWEEN doBetweenSharding 例如 where order_id between 1 and 2 </li>
* </ul>
*
* @author mengday
*/
public class ModuleDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {

/**
* 分片策略相等=
* @param availableTargetNames 可用的目标名字(这里指数据名db0、db1)
* @param shardingValue 分片值[logicTableName=”t_order” 逻辑表名, columnName=”user_id” 分片的列名, value=”20″ 分片的列名对应的值(user_id=20)]
* @return
*/
@Override
public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
for (String each : availableTargetNames) {
if (each.endsWith(shardingValue.getValue() % 2 + “”)) {
return each;
}
}
throw new IllegalArgumentException();
}

@Override
public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
for (Long value : shardingValue.getValues()) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(value % 2 + “”)) {
result.add(tableName);
}
}
}
return result;
}

@Override
public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
Range<Long> range = shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : availableTargetNames) {
if (each.endsWith(i % 2 + “”)) {
result.add(each);
}
}
}
return result;
}
}
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
63
64
65
66
67
68
69
70
71
72
73
package com.company.shardingjdbc.common;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;

import java.util.Collection;
import java.util.LinkedHashSet;

public final class ModuleTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {

/**
* doEqualSharding =
* @param tableNames 实际物理表名
* @param shardingValue [logicTableName=”t_order”, columnName=”order_id”, value=20]
*
* select * from t_order from t_order where order_id = 11
* └── SELECT * FROM t_order_1 WHERE order_id = 11
* select * from t_order from t_order where order_id = 44
* └── SELECT * FROM t_order_0 WHERE order_id = 44
*/
* select * from t_order from t_order where order_id = 11
* └── SELECT * FROM t_order_1 WHERE order_id = 11
* select * from t_order from t_order where order_id = 44
* └── SELECT * FROM t_order_0 WHERE order_id = 44
*/
public String doEqualSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
for (String each : tableNames) {
if (each.endsWith(shardingValue.getValue() % 2 + “”)) {
return each;
}
}
throw new IllegalArgumentException();
}

/**
* select * from t_order from t_order where order_id in (11,44)
* ├── SELECT * FROM t_order_0 WHERE order_id IN (11,44)
* └── SELECT * FROM t_order_1 WHERE order_id IN (11,44)
* select * from t_order from t_order where order_id in (11,13,15)
* └── SELECT * FROM t_order_1 WHERE order_id IN (11,13,15)
* select * from t_order from t_order where order_id in (22,24,26)
* └──SELECT * FROM t_order_0 WHERE order_id IN (22,24,26)
*/
public Collection<String> doInSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(tableNames.size());
for (Long value : shardingValue.getValues()) {
for (String tableName : tableNames) {
if (tableName.endsWith(value % 2 + “”)) {
result.add(tableName);
}
}
}
return result;
}
/**
* select * from t_order from t_order where order_id between 10 and 20
* ├── SELECT * FROM t_order_0 WHERE order_id BETWEEN 10 AND 20
* └── SELECT * FROM t_order_1 WHERE order_id BETWEEN 10 AND 20
*/
public Collection<String> doBetweenSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(tableNames.size());
Range<Long> range = shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : tableNames) {
if (each.endsWith(i % 2 + “”)) {
result.add(each);
}
}
}
return result;
}
}
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
63
64
65
66
67
68
69
70
71
72
73
7. localhost:8080/order/create
db0
├── t_order_0 user_id为偶数 order_id为偶数
├── t_order_1 user_id为偶数 order_id为奇数
db1
├── t_order_0 user_id为奇数 order_id为偶数
├── t_order_1 user_id为奇数 order_id为奇数

四：sharding-jdbc + mybatis + druid集成
此示例是在jap原有的集成上集成mybatis

1. 引入mybatis依赖
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
1
2
3
4
5
2. 在Application上添加注解@MapperScan
@MapperScan(“com.company.shardingjdbc.mapper”)
@SpringBootApplication
public class ShardingJdbcApplication {

public static void main(String[] args) {
SpringApplication.run(ShardingJdbcApplication.class, args);
}
}
1
2
3
4
5
6
7
8
3. application.yml
# Mybatis 配置
mybatis:
typeAliasesPackage: com.company.shardingjdbc.domain
mapperLocations: classpath:mapper/*.xml
configuration.map-underscore-to-camel-case: true

# 打印mybatis中的sql语句和结果集
logging:
level.com.company.shardingjdbc.mapper: TRACE
1
2
3
4
5
6
7
8
9
4. OrderMapper
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface OrderMapper {

void insert(Order order);

List<Order> queryById(@Param(“orderIdList”) List<Long> orderIdList);
}
1
2
3
4
5
6
7
8
9
10
5. OrderMapper.xml
<?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=”com.company.shardingjdbc.mapper.OrderMapper” >
<select id=”queryById” parameterType=”Long” resultType=”Order”>
SELECT * FROM t_order WHERE order_id IN
<foreach collection=”orderIdList” item=”orderId” open=”(” separator=”,” close=”)”>
#{orderId}
</foreach>
</select>

<insert id=”insert” parameterType=”Order”>
INSERT INTO t_order (order_id, user_id) VALUES (#{orderId}, #{userId})
</insert>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
6. OrderController
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping(“/order”)
public class OrderController {

@Autowired
private OrderMapper orderMapper;

@RequestMapping(“/insert”)
public Object insert() {
for (int i = 20; i < 30; i++) {
Order order = new Order();
order.setUserId((long) i);
order.setOrderId((long) i);
orderMapper.insert(order);
}
for (int i = 30; i < 40; i++) {
Order order = new Order();
order.setUserId((long) i + 1);
order.setOrderId((long) i);
orderMapper.insert(order);
}

return “success”;
}

@RequestMapping(“queryById”)
public List<Order> queryById(String orderIds) {
List<String> strings = Arrays.asList(orderIds.split(“,”));
List<Long> orderIdList = strings.stream().map(item -> Long.parseLong(item)).collect(Collectors.toList());
return orderMapper.queryById(orderIdList);
}
}

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
7. 插入数据
localhost:8080/order/insert

ModuleDatabaseShardingAlgorithm: 先根据分片键user_id及值来确定要操作的数据库是db0还是db1
ModuleTableShardingAlgorithm: 再根据分片键order_id及值来确定要操作的数据库对应的表是t_order_0还是t_order_1
当数据库名和表名都确定了就可以操作数据库了

localhost:8080/order/queryById?orderIds=20,31,30,21

五：示例源码下载
Springboot2+JPA|MyBatis+Sharding-JDBC示例：https://download.csdn.net/download/vbirdbest/10560767

分享一个朋友的人工智能教程。比较通俗易懂，风趣幽默，感兴趣的朋友可以去看看。
点赞 3
————————————————
版权声明：本文为CSDN博主「vbirdbest」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/vbirdbest/article/details/81176134

转载请注明：SuperIT » Sharding-JDBC+JPA|MyBatis+Druid分库分表实现