一、环境构建
1、创建一个springboot项目
2、导入如下依赖
<?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>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.cc</groupId>
<artifactId>sharding-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-demo</name>
<description>sharding-demo</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.19</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<version>RELEASE</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
遇到一些问题:我的application.properties文件中的ShardingSphere出现问题:未使用的属性
通过网上搜索答案是:如果出现未使用的属性,通常是因为该属性在当前的配置中没有被使用到。
在这种情况下,可以尝试删除该属性或者检查是否有其他地方需要使用该属性。另外,如果您使用的是较旧版本的ShardingSphere,可能会出现某些属性不再被支持的情况,建议升级到最新版本。
升级到最新版后仍然无法解决。把配置文件转为 yml的格式就可以了
二、sharding-jdbc实现水平分表
1、创建sharding_sphere数据库
2、在数据库中创建两张表,orders_1和orders_2
3、分片规则:如果订单编号是偶数添加到orders_1,如果是奇数添加到orders_2
4、创建实体类
public class Orders {
private Integer id;
private Integer orderType;
private Integer customerId;
private Double amount;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getOrderType() {
return orderType;
}
public void setOrderType(Integer orderType) {
this.orderType = orderType;
}
public Integer getCustomerId() {
return customerId;
}
public void setCustomerId(Integer customerId) {
this.customerId = customerId;
}
public Double getAmount() {
return amount;
}
public void setAmount(Double amount) {
this.amount = amount;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", orderType=" + orderType +
", customerId=" + customerId +
", amount=" + amount +
'}';
}
}
5、创建mapper类
@Repository
@Mapper
public interface OrdersMapper {
@Insert("insert into orders(id,orderType,customerId,amount) values(#{id},#{orderType},#{customerId},#{amount})")
public void insert(Orders orders);
@Select("select * from orders where id = #{id}")
@Results({
@Result(property = "id",column = "id"),
@Result(property = "orderType",column = "orderType"),
@Result(property = "customerId",column = "customerId"),
@Result(property = "amount",column = "amount")
})
public Orders selectOne(Integer id);
}
6、创建配置文件
#整合mybatis
mybatis:
type-aliases-package: com.cc.shardingdemo.mapper
spring:
shardingsphere:
datasource:
names: ds1 #定义数据源ds1
#配置数据源 ds1 的具体内容,
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/sharding_sphere?serverTimezone=UTC
username: root
password: 123456
#指定 orders 表的分布情况,配置表在哪个数据库中,表名称是什么
sharding:
tables:
orders:
actual-data-nodes: ds1.orders_$->{1..2}
key-generator:
column: id #指定orders表里主键id生成策略
type: SNOWFLAKE #雪花算法
#指定分片策略。根据id的奇偶性来判断插入到哪个表
table-strategy:
inline:
sharding-column: id
algorithm-expression: orders_${id%2+1} #id为偶数则加入 orders_(0+1)也就是orders_1;
#打开sql输出日志
props:
sql:
show:true
7、创建测试类
@SpringBootTest
class ShardingDemoApplicationTests {
@Test
void contextLoads() {
}
@Autowired
private OrdersMapper ordersMapper;
@Test
public void addOrders(){
for (int i = 1; i <=10 ; i++) {
Orders orders = new Orders();
orders.setId(i);
orders.setCustomerId(i);
orders.setOrderType(i);
orders.setAmount(1000.0*i);
ordersMapper.insert(orders);
}
}
@Test
public void queryOrders(){
Orders orders = ordersMapper.selectOne(1);
System.out.println(orders);
}
}
三、sharding-jdbc实现水平分库
1、创建不同名称的数据库:sharding_sphere_1,sharding_sphere_2
2、在两个数据库上创建相同的表orders_1,orders_2
3、分片规则,按照customer_id的奇偶性来进行分库,然后按照id的奇偶性进行分表
4、修改配置文件
#mapper文件扫描路径
mybatis:
mapper-locations: classpath:/mapper/*.xml
spring:
main:
allow-bean-definition-overriding: true
#水平分库水平分表配置,两个数据库,每个数据库中两张表
shardingsphere:
# 数据源名称 db1 、db2
datasource:
names: db1,db2
db1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/sharding_sphere_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
db2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/sharding_sphere_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
# 配置数据库的分布,表的分布
sharding:
tables:
orders:
# db1:orders_1, orders_2; db2:orders_1 ,orders_2;
actual-data-nodes: db$->{1..2}.orders_$->{1..2}
# 指定user_info表 主键id 生成策略为 SNOWFLAKE
key-generator:
column: id
type: SNOWFLAKE
# 指定数据库分片策略 约定orders 值是偶数添加到db1(sharding_sphere_1)中,奇数添加到db2中
database-strategy:
inline:
sharding-column: customer_id
algorithm-expression: db$->{customer_id%2+1}
# 指定表分片策略
table-strategy:
inline:
#约定id值是偶数添加到orders_1表,如果是奇数添加到orders_2表
sharding-column: id
algorithm-expression: orders_$->{id%2+1}
props:
sql:
show: true
# 控制台日志配置
logging:
level:
root: info
5、修改mapper类
换一种写法:
<?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.cc.shardingdemo.mapper.OrdersMapper">
<insert id="insert" parameterType="com.cc.shardingdemo.entity.Orders">
insert into orders(id,orderType,customerId,amount)
values(#{id},#{orderType},#{customerId},#{amount})
</insert>
<select id="selectUser" resultType="com.cc.shardingdemo.entity.Orders">
select * from orders
</select>
</mapper>
@Mapper
public interface OrdersMapper {
public void insert(Orders orders);
}
6、编写测试类
@Test
public void addOrdersDB(){
for (int i = 1; i <=20 ; i++) {
Orders orders = new Orders();
orders.setId(i);
orders.setCustomerId(10+i);
orders.setOrderType(i);
orders.setAmount(10.0*i);
ordersMapper.insert(orders);
}
}