# db-router-spring-boot-starter
**Repository Path**: iferry/db-router-spring-boot-starter
## Basic Information
- **Project Name**: db-router-spring-boot-starter
- **Description**: 数据库分库分表组件，集成了 mybatis plugin，以spring boot starter的形式提供使用
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No
## Statistics
- **Stars**: 4
- **Forks**: 0
- **Created**: 2022-07-14
- **Last Updated**: 2023-05-06
## Categories & Tags
**Categories**: Uncategorized
**Tags**: None
## README
# 引入说明
## 一、本地安装
1. 克隆项目到本地
2. mvn clean install
## 二、导入依赖
```xml
cn.cscamp.middleware
db-router-spring-boot-starter
1.0-SNAPSHOT
```
## 三、配置文件
application.yaml/application.properties
```yaml
# 多数据源路由配置
mini-db-router:
jdbc:
datasource:
dbCount: 2
tbCount: 4
default: db00
list: db01,db02
db00:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/${database}?useUnicode=true
username: root
password: abc123456
db01:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/${database01}?useUnicode=true
username: root
password: abc123456
db02:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/${database02}?useUnicode=true
username: root
password: abc123456
```
## 四、使用案例
> - 如果一个表只分库不分表，则它的 sql 语句并不会有什么差异
> - 如果需要分表，那么则需要在表名后面加入 user_take_activity_${tbIdx} 同时入参对象需要继承 DBRouterBase 这样才可以拿到 tbIdx 分表信息
### 分库案例
第一步: 在需要使用数据库路由的DAO方法上加入注解
> @DBRouter(key = "uId") key 是入参对象中的属性，用于提取作为分库分表路由字段使用
```java
@Mapper
public interface IUserTakeActivityDao {
/**
* 插入用户领取活动信息
*
* @param userTakeActivity 入参
*/
@DBRouter(key = "uId")
void insert(UserTakeActivity userTakeActivity);
}
```
第二步: Mapper.xml中定义SQL语句
```xml
INSERT INTO user_take_activity
(u_id, take_id, activity_id, activity_name, take_date,
take_count, uuid, create_time, update_time)
VALUES
(#{uId}, #{takeId}, #{activityId}, #{activityName}, #{takeDate},
#{takeCount}, #{uuid}, now(), now())
```
第三步: 单元测试验证分库
```java
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTakeActivityDaoTest {
private Logger logger = LoggerFactory.getLogger(ActivityDaoTest.class);
@Resource
private IUserTakeActivityDao userTakeActivityDao;
@Test
public void test_insert() {
UserTakeActivity userTakeActivity = new UserTakeActivity();
userTakeActivity.setuId("Uhdgkw766120d"); // 1库：Ukdli109op89oi 2库：Ukdli109op811d
userTakeActivity.setTakeId(121019889410L);
userTakeActivity.setActivityId(100001L);
userTakeActivity.setActivityName("测试活动");
userTakeActivity.setTakeDate(new Date());
userTakeActivity.setTakeCount(10);
userTakeActivity.setUuid("Uhdgkw766120d");
userTakeActivityDao.insert(userTakeActivity);
}
}
```
> 测试中分别验证了不同的 uId 主要是为了解决数据散列到不同库表中去
### 分库分表案例
第一步: 在需要使用数据库路由的DAO方法上加入分表注解
```java
@Mapper
@DBRouterStrategy(splitTable = true)
public interface IUserStrategyExportDao {
/**
* 新增数据
* @param userStrategyExport 用户策略
*/
@DBRouter(key = "uId")
void insert(UserStrategyExport userStrategyExport);
/**
* 查询数据
* @param uId 用户ID
* @return 用户策略
*/
@DBRouter
UserStrategyExport queryUserStrategyExportByUId(String uId);
}
```
> - @DBRouterStrategy(splitTable = true) 配置分表信息，配置后会通过数据库路由组件把sql语句添加上分表字段，比如表 user 修改为 user_003
> - @DBRouter(key = "uId") 设置路由字段
> - @DBRouter 未配置情况下走默认字段，routerKey: uId
第二步: Mapper.xml中定义SQL语句
```xml
INSERT INTO user_strategy_export
(u_id, activity_id, order_id, strategy_id, strategy_mode,
grant_type, grant_date, grant_state, award_id, award_type,
award_name, award_content, uuid, create_time, update_time)
VALUES
(#{uId},#{activityId},#{orderId},#{strategyId},#{strategyMode},
#{grantType},#{grantDate},#{grantState},#{awardId},#{awardType},
#{awardName},#{awardContent},#{uuid},now(),now())
```
> 正常写 SQL 语句即可，如果你不使用注解 @DBRouterStrategy(splitTable = true) 也可以使用 `user_strategy_export_003`
第三步: 单元测试验证分库分表
```java
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserStrategyExportDaoTest {
private Logger logger = LoggerFactory.getLogger(UserStrategyExportDaoTest.class);
@Resource
private IUserStrategyExportDao userStrategyExportDao;
@Resource
private Map idGeneratorMap;
@Test
public void test_insert() {
UserStrategyExport userStrategyExport = new UserStrategyExport();
userStrategyExport.setuId("Uhdgkw766120d");
userStrategyExport.setActivityId(idGeneratorMap.get(Constants.Ids.ShortCode).nextId());
userStrategyExport.setOrderId(idGeneratorMap.get(Constants.Ids.SnowFlake).nextId());
userStrategyExport.setStrategyId(idGeneratorMap.get(Constants.Ids.RandomNumeric).nextId());
userStrategyExport.setStrategyMode(Constants.StrategyMode.SINGLE.getCode());
userStrategyExport.setGrantType(1);
userStrategyExport.setGrantDate(new Date());
userStrategyExport.setGrantState(1);
userStrategyExport.setAwardId("1");
userStrategyExport.setAwardType(Constants.AwardType.DESC.getCode());
userStrategyExport.setAwardName("IMac");
userStrategyExport.setAwardContent("奖品描述");
userStrategyExport.setUuid(String.valueOf(userStrategyExport.getOrderId()));
userStrategyExportDao.insert(userStrategyExport);
}
@Test
public void test_select() {
UserStrategyExport userStrategyExport = userStrategyExportDao.queryUserStrategyExportByUId("Uhdgkw766120d");
logger.info("测试结果：{}", JSON.toJSONString(userStrategyExport));
}
}
```
第四步: 测试结果
```text
21:05:52.050 INFO 86968 --- [ main] c.i.l.t.dao.UserStrategyExportDaoTest : Started UserStrategyExportDaoTest in 6.103 seconds (JVM running for 7.51)
21:05:52.292 INFO 86968 --- [ main] c.b.m.db.router.DBRouterJoinPoint : 数据库路由 method：queryUserStrategyExportByUId dbIdx：1 tbIdx：3
21:05:52.800 INFO 86968 --- [ main] c.i.l.t.dao.UserStrategyExportDaoTest : 测试结果：{"activityId":120405215,"awardContent":"test","awardId":"1","awardName":"IMac","awardType":1,"createTime":1633006252000,"grantDate":1633006252000,"grantState":1,"grantType":1,"id":1,"orderId":1443558966104850432,"strategyId":42480428672,"strategyMode":1,"uId":"Uhdgkw766120d","updateTime":1633006252000,"uuid":"1443558966104850432"}
```