SpringBoot 整合 ShardingSphere
前言
ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。
POM依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.4</version>
</parent>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.com.xiaobai.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
移除Drud自动配置
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class )
public class ShardingsphereApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingsphereApplication.class, args);
}
}
水平分表
创建数据库lg_order，建表语句
-- ----------------------------
-- Table structure for pay_order_1
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_1`;
CREATE TABLE `pay_order_1` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NULL DEFAULT NULL,
`product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for pay_order_2
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_2`;
CREATE TABLE `pay_order_2` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NULL DEFAULT NULL,
`product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置代码
mybatis:
configuration:
map-underscore-to-camel-case: true
server:
servlet:
context-path: /
encoding:
charset: UTF-8
enabled: true
force: true
spring:
application:
description: 水平分表 （单库）
name: sharding-jdbc-one
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
db1:
driver-class-name: com.mysql.cj.jdbc.Driver
password: 123456;a
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/lg_order?characterEncoding=UTF-8&useSSL=false
username: root
names: db1
props:
sql:
show: true
sharding:
tables:
pay_order:
actual-data-nodes: db1.pay_order_$->{1..2}
key-generator:
type: SNOWFLAKE
column: order_id
tablestrategy:
inline:
algorithm-expression: pay_order_$->{order_id % 2 + 1}
sharding-column: order_id
编写Mapper
@Component
@Mapper
public interface OrderMapper {
@Insert("insert into pay_order(user_id,product_name,count) values(#{userId},#{productName},#{count})")
Long insert(OrderVO orderVO);
/**
* 查询订单
*/
@Select({"<script>" +
"select * from pay_order p where p.order_id in " +
"<foreach collection='orderIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +
"limit 1" +
"</script>"})
List<Map<String, Object>> findOrderByIds(@Param("orderIds") List<Long> orderIds);
/**
* 查询订单
*/
@Select("select * from pay_order order by user_id asc")
List<Map<String, Object>> list();
}
测试
@SpringBootTest()
public class OrderServiceTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void test1() {
for (long i = 11; i < 20; i++) {
OrderVO orderVO = new OrderVO();
orderVO.setCount(1);
orderVO.setUserId(i);
orderVO.setProductName("产品" + i);
orderMapper.insert(orderVO);
}
}
@Test
public void test2() {
List<Long> ids = List.of(786541544898297857L,786541545091235841L,786541544923463680L);
System.out.println(JSON.toJSONString(orderMapper.findOrderByIds(ids)));
}
@Test
public void test3() {
System.out.println(JSON.toJSONString(orderMapper.list()));
}
}
insert结果
水平分库
创建数据库lg_order1, lg_order2
-- ----------------------------
-- Table structure for pay_order_1
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_1`;
CREATE TABLE `pay_order_1` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NULL DEFAULT NULL,
`product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for pay_order_2
-- ----------------------------
DROP TABLE IF EXISTS `pay_order_2`;
CREATE TABLE `pay_order_2` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NULL DEFAULT NULL,
`product_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
配置代码
mybatis:
configuration:
map-underscore-to-camel-case: true
server:
servlet:
context-path: /
encoding:
charset: UTF-8
enabled: true
force: true
spring:
application:
description: 水平分库
name: sharding-jdbc-two
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
db1:
driver-class-name: com.mysql.cj.jdbc.Driver
password: 123456;a
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/lg_order_1?characterEncoding=UTF-8&useSSL=false
username: root
db2:
driver-class-name: com.mysql.cj.jdbc.Driver
password: 123456;a
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/lg_order_2?characterEncoding=UTF-8&useSSL=false
username: root
names: db1,db2
props:
sql:
show: true
sharding:
tables:
pay_order:
databasestrategy:
inline:
algorithm-expression: db$->{user_id % 2 + 1}
sharding-column: user_id
actual-data-nodes: db$->{1..2}.pay_order_$->{1..2}
key-generator:
type: SNOWFLAKE
column: order_id
tablestrategy:
inline:
algorithm-expression: pay_order_$->{order_id % 2 + 1}
sharding-column: order_id
测试
@SpringBootTest()
public class OrderServiceTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void test1() {
for (long i = 11; i < 20; i++) {
OrderVO orderVO = new OrderVO();
orderVO.setCount(1);
orderVO.setUserId(i);
orderVO.setProductName("产品" + i);
orderMapper.insert(orderVO);
}
}
@Test
public void test2() {
List<Long> ids = List.of(786541544898297857L,786541545091235841L,786541544923463680L);
System.out.println(JSON.toJSONString(orderMapper.findOrderByIds(ids)));
}
@Test
public void test3() {
System.out.println(JSON.toJSONString(orderMapper.list()));
}
}
insert 结果
写累了，，，后面再写
