springBoot集成ShardingSphere实现单库分表
我们在开发项⽬的时候，会遇到有⼀个或者⼏个表的数据量特别⼤的情况。⽐如商城中的订单表。这个是随着时间的推进越来越多。所以
需要进⾏分表来处理。让单表的数量不要太多。所以这个时候需要进⾏分表操作。我们使⽤⽐较流⾏的shardingsphere来实现。
⼀：引⼊jar包
<!--shardingsphere start-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
⼆：在application.properties中设置
# 数据源 master （当前只有⼀个）
spring.shardingsphere.datasource.names=master
# 数据源主库
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.sql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=
###分表策略其中user为逻辑表分表主要取决于id⾏
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_$->{0..1}
spring.shardingsphere.sharding.tables.lumn=id
spring.shardingsphere.sharding.tables.pe=SNOWFLAKE
### ⾏表达式分⽚策略  begin （如果没有下⾯的这两⾏。user_0和user_1都会存相同的数据） ###
# 根据这个列分表
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
# 分表规则为：对 asset_package_id 取模
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 2}
### ⾏表达式分⽚策略  end###
# 绑定表规则列表, 多个⽤逗号隔开
spring.shardingsphere.sharding.binding-tables[0]=user
#spring.shardingsphere.sharding.binding-tables[1]=test
#打印sql
spring.shardingsphere.props.sql.show=true
三：创建表
CREATE TABLE `user_0` (
`id` bigint(32) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_1` (
`id` bigint(32) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
注意：
1：这两个表名要根据application.properties中的设置⼀样。
spring.shardingsphere.sharding.tables.user.actual-data-nodes=master.user_$->{0..1} 2：由于⽣成的id是雪花算法。所以id应该要⾜够⼤，⽤bigint(32)就⾜够了
spring.shardingsphere.sharding.tables.lumn=id
spring.shardingsphere.sharding.tables.pe=SNOWFLAKE 四：实现功能
按照⼀般的⽅式dao,service,controller实现创建。
entity
public class User implements Serializable {
private long id;
private String name;
private Integer age;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name == null ? null : im();
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
mapper
public interface UserMapper {
int insert(User record);
List<User> selectByExample(UserExample example);
}
xml
<insert id="insert" parameterType="ity.User">
insert into user (name, age)
values ( #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})  </insert>
特别注意：当insert的时候id千万不要写。否则会⼀直是0。service
@Service
public class TestService {
@Resource
private UserMapper userMapper;
@Transactional
public Result add(String name) {
springboot推荐算法
try {
User user=new User();
user.setName(name);
user.setAge(12);
userMapper.insert(user);
return Result.sendSuccess("新增成功");
} catch (Exception e) {
e.printStackTrace();
return Result.sendFailure("新增失败");
}
}
public Result query() {
UserExample example=new UserExample();
UserExample.Criteria ateCriteria();
List<User> list= userMapper.selectByExample(example);
return Result.sendSuccess("查询成功",list);
}
}
controller
@RestController
@RequestMapping(value = "/test")
public class TestController {
@Resource
private TestService testService;
@RequestMapping(value = "/add", method = RequestMethod.GET)    public Result add(@RequestParam(value = "name") String name) {        return testService.add(name);
}
@RequestMapping(value = "/query", method = RequestMethod.GET)    public Result query() {
return testService.query();
}
}
可能出现问题:
参考：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。