Sharding-JDBC不分库，只分表例子
上一篇介绍的了Sharding-jdbc的读写分离，接下来我们写demo，介绍SpringBoot使用Sharding-JDBC不分库，只分表例子。话不多说，直接写代码。
准备
- SpringBoot 2.1.12
- Sharding-JDBC 4.0.0
- Mybatis 3.x
- Mysql 8.0
- lombok
本文场景介绍
一个数据库，将user表分表分为四个一样的表，根据取模算法分别向user0-3的表里插入数据。
POM文件
pom文件引入如下相关依赖：
<?xml version="1.0" encoding="UTF-8"?>
4.0.0
cn.cicoding
shardingsphere-example
1.0-SNAPSHOT
cn.cicoding
sharding-jdbc-table
0.0.1-SNAPSHOT
sharding-jdbc-table
Demo project for Spring Boot
1.8
org.springframework.boot
spring-boot-starter-web
org.apache.shardingsphere
sharding-jdbc-spring-boot-starter
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.0.1
mysql
mysql-connector-java
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-test
test
org.springframework.boot
spring-boot-maven-plugin
相关代码实现
Controller代码
package cn.cicoding.controller;
import cn.cicoding.model.User;
import cn.cicoding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/users")
public Object list() {
return userService.list();
}
@GetMapping("/add")
public Object add() {
for (long i = 0; i < 100; i++) {
User user = new User();
user.setId(i);
user.setCity("深圳");
user.setName("李四"+ i);
userService.add(user);
}
return "success";
}
@GetMapping("/users/{id}")
public Object get(@PathVariable Long id) {
return userService.findById(id);
}
@GetMapping("/users/query")
public Object get(String name) {
return userService.findByName(name);
}
}
Service代码
package cn.cicoding.service;
import cn.cicoding.model.User;
import java.util.List;
public interface UserService {
List list();
Long add(User user);
User findById(Long id);
User findByName(String name);
}
ServiceImpl代码
package cn.cicoding.service.impl;
import cn.cicoding.dao.UserDaoMapper;
import cn.cicoding.model.User;
import cn.cicoding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDaoMapper userDao;
public List list() {
List list = userDao.list();
return list;
}
public Long add(User user) {
return userDao.addUser(user);
}
@Override
public User findById(Long id) {
return userDao.findById(id);
}
@Override
public User findByName(String name) {
return userDao.findByName(name);
}
}
Dao代码
package cn.cicoding.dao;
import cn.cicoding.model.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserDaoMapper {
Long addUser(User user);
List list();
User findById(Long id);
User findByName(String name);
}
Mapper.xml代码实现
<?xml version="1.0" encoding="UTF-8" ?>
INSERT INTO user (
id, city, name
)
VALUES (
#{id,jdbcType=INTEGER},
#{city,jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR}
)
实体类
package cn.cicoding.model;
import lombok.Data;
import java.io.Serializable;
@Data
public class User implements Serializable {
private static final long serialVersionUID = -1205226416664488559L;
private Long id;
private String city = "";
private String name = "";
}
到这我们完成了基本的代码编写，由于sharding-jdbc是jar包，我们来看主要的配置信息
#使用取模的方式来实现表分片
server.port=8084
# mybatis对应的映射文件路径
mybatis.mapper-locations=classpath:mapper/*.xml
# mybatis对应的实体类
mybatis.type-aliases-package=cn.cicoding.model
spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url=jdbc:mysql://localhost:3309/ds_0?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root
# 展示sql打印
spring.shardingsphere.props.sql.show=true
# 自定义分表算法
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.tables.user.table-strategy.standard.precise-algorithm-class-name=com.example.MyCustomShardingAlgorithm
# 分表配置
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds.user_$->{0..3}
# inline 表达式 （id类型转换 -> id.longValue() -> user_${id.longValue() % 4}）
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
启动类
package cn.cicoding;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class ShardingJdbcDemoApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcDemoApplication.class, args);
}
}
测试演示
启动启动类，访问http://localhost:8084/add
查看数据库中user_0，user_1，user_2，user_3中每个数据库库中都有数据，且id都相差4
我们看到DataSources是master节点主库
我们再次访问http://localhost:8084/users
看到数据库中的数据user_0，user_1，user_2，user_3中都查询出来了，实现了不分库分表策略！
到此我们就实现了sharding-jdbc主从读写分离实现，更多配置请参考此处！