一. 演示环境
windows
jdk1.8
mysql8.x
shading-jdbc 4.1.1
seata 1.6.1
二.整合sharding-jdbc
分库分表读写分离(根据id水平分离)
- sharding官方文档4.x
- 创建主库ds_0…3 创建从库 ds0slave,ds1slave
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ds_0` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `ds_0`;
/*Table structure for table `undo_log` */
DROP TABLE IF EXISTS `undo_log`;
CREATE TABLE `undo_log` (
`id` bigint NOT NULL AUTO_INCREMENT,
`branch_id` bigint NOT NULL,
`xid` varchar(100) NOT NULL,
`context` varchar(128) NOT NULL,
`rollback_info` longblob NOT NULL,
`log_status` int NOT NULL,
`log_created` datetime NOT NULL,
`log_modified` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*Table structure for table `user_0` */
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
`id` bigint NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*Table structure for table `user_1` */
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
`id` bigint NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*Table structure for table `user_2` */
DROP TABLE IF EXISTS `user_2`;
CREATE TABLE `user_2` (
`id` bigint NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*Table structure for table `user_3` */
DROP TABLE IF EXISTS `user_3`;
CREATE TABLE `user_3` (
`id` bigint NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*Table structure for table `user_4` */
DROP TABLE IF EXISTS `user_4`;
CREATE TABLE `user_4` (
`id` bigint NOT NULL,
`city` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
- 配置依赖
<properties>
<mybatisplus.version>3.4.3.1</mybatisplus.version>
<druid.version>1.2.9</druid.version>
<sharding.version>4.1.1</sharding.version>
<faster.version>1.2.83</faster.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</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.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
- 配置文件
#mybatis:
#configuration:
# map-underscore-to-camel-case: true
#config-location: classpath:mybatis-config.xml
server:
port: 8084
spring:
application:
name: sharding-service
main:
allow-bean-definition-overriding: true
allow-circular-references: true
shardingsphere:
datasource:
master0:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds_0?characterEncoding=utf-8
username: root
master0slave:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds0slave?characterEncoding=utf-8
username: root
master1:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds_1?characterEncoding=utf-8
username: root
master1slave:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds1slave?characterEncoding=utf-8
username: root
master2:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds_2?characterEncoding=utf-8
username: root
master3:
driver-class-name: com.mysql.cj.jdbc.Driver
password:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/ds_3?characterEncoding=utf-8
username: root
names: master0,master0slave,master1,master1slave,master2,master3
sharding:
default-data-source-name: ds0
default-database-strategy:
inline:
algorithm-expression: ds$->{id % 4} #分库策略
sharding-column: id
# standard: 自定义分库策略
# precise-algorithm-class-name: com.sharding.algorithm.MyPreciseShardingAlgorithm
# sharding-column: id
master-slave-rules:
ds0:
master-data-source-name: master0
slave-data-source-names: master0slave
ds1:
master-data-source-name: master1
slave-data-source-names: master1slave
ds2:
master-data-source-name: master2
slave-data-source-names: master0slave
ds3:
master-data-source-name: master3
slave-data-source-names: master1slave
tables:
user:
actual-data-nodes: ds$->{0..3}.user_$->{0..4}
# actual-data-nodes: ds$->{0..1}.user_$->{0..2},dss$->{0..1}.user_$->{0..2}
table-strategy:
inline:
algorithm-expression: user_$->{id % 5} #分表策略
sharding-column: id
props:
sql:
show: true #打印sharding sql
logging:
level:
com.sharding: info
com.sharding.repository: debug
################### mybatis-plus配置 ###################
mybatis-plus:
mapper-locations: classpath:mappers/*.xml
typeAliasesPackage: com.sharding.po
global-config:
id-type: 0 #0:数据库ID自增 1:用户输入id 2:全局唯一id(IdWorker) 3:全局唯一ID(uuid)
db-column-underline: false
refresh-mapper: true
logic-delete-value: 0
logic-not-delete-value: 1
configuration:
map-underscore-to-camel-case: false
callSettersOnNulls: true
cache-enabled: true #配置的缓存的全局开关
lazyLoadingEnabled: true #延时加载的开关
multipleResultSetsEnabled: true #开启的话,延时加载一个属性时会加载该对象全部属性,否则按需加载属性
- 创建controler
@Autowired
private UserService userService;
@GetMapping("/users")
public Object list() {
return userService.list(Wrappers.lambdaQuery());
}
@RequestMapping("/add")
public Object add() {
for (long i = 0; i <= 20; i++) {
User user = new User();
user.setId(i);
user.setCity("小日子");
user.setName("小八嘎");
userService.add(user);
}
return "success";
}
- 请求/add测试添加数据,查看主库结果
- 测试查询数据
结果为空这是我们配置了读写分离可以手动往从库添加一条数据测试
- 查询路由主库
当主从存在差异时,业务又需要实时的数据,有时候业务上不能做到妥协就可以使用sharding提供的路由
@GetMapping("/users")
public Object list() {
HintManager.getInstance().setMasterRouteOnly();
return userService.list(Wrappers.lambdaQuery());
}
自定义分库策略
- 修改配置文件
sharding:
default-database-strategy:
# inline:
# algorithm-expression: ds$->{id % 4}
# sharding-column: id
standard: #自定义分库策略
precise-algorithm-class-name: com.sharding.algorithm.MyPreciseShardingAlgorithm
sharding-column: id
- 创建类实现 PreciseShardingAlgorithm 接口设置id泛型 实现doSharding() 方法
@Slf4j
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Long> shardingValue) {
Optional<String> first = collection.stream().
filter(dbName-> dbName.endsWith(String.valueOf(shardingValue.getValue() % 4))).findFirst();
if (first.isPresent()) {
return first.get();
}
throw new IllegalArgumentException();
}
}
三.整合seata
seata:
config:
# support: nacos, consul, apollo, zk, etcd3
type: file
registry:
# support: nacos, eureka, redis, zk, consul, etcd3, sofa
type: file
store:
# support: file 、 db 、 redis
mode: db
db:
datasource: druid
dbType: mysql
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/seata-server?useUnicode=true&characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useSSL=false
user: root
password:
-
创建seata-server数据库,打开script\server\db\mysql目录下的脚本文件增加表信息
-
根据配置文件端口,打开web控制台账号密码同配置文件
-
增加maven依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding.version}</version>
<exclusions>
<exclusion>
<artifactId>antlr4-runtime</artifactId>
<groupId>org.antlr</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${faster.version}</version>
</dependency>
<dependency>
<groupId>io.seata</groupId>
<artifactId>seata-spring-boot-starter</artifactId>
<version>1.6.1</version>
<exclusions>
<exclusion>
<artifactId>druid</artifactId>
<groupId>com.alibaba</groupId>
</exclusion>
<exclusion>
<artifactId>fastjson</artifactId>
<groupId>com.alibaba</groupId>
</exclusion>
<exclusion>
<artifactId>guava</artifactId>
<groupId>com.google.guava</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-base-seata-at</artifactId>
<version>4.1.1</version>
</dependency>
- 增加项目配置文件seata.conf
client {
application.id = sharding-service
transaction.service.group = sharding-service-group
}
- 修改项目配置文件
##########seata########
seata:
# client:
# undo:
# log-serialization: fastjson
# server:
# service-port: 8091 #If not configured, the default is '${server.port} + 1000'
# enabled: true # 开启自动装配
tx-service-group: sharding-service-group # 事务组
# enable-auto-data-source-proxy: true
service:
vgroup-mapping:
sharding-service-group: default
- 修改controller
@GetMapping("/add")
@ShardingTransactionType(value = TransactionType.BASE)
@Transactional(rollbackFor = Exception.class)
public Object add() {
try {
TransactionTypeHolder.set(TransactionType.BASE);
for (long i = 100; i <= 200; i++) {
User user = new User();
user.setId(i);
user.setCity("小日子");
user.setName("小八嘎");
userService.add(user);
}
int t = 1 / 0;
} finally {
TransactionTypeHolder.clear();
}
return "success";
}
- 测试回滚
事务已经生效 - 注释异常,测试提交