以下是一个基于
shardingsphere-
sharding-boot-starter 5.1.2
版本实现分库
分表的例子：
1. 添加依赖
在pom.xml文件中添加以下依赖：
```xml
<dependency>
<groupId>org.apache.
shardingsphere</groupId>
<artifactId>
shardingsphere-jdbc-core</artifactId>
<version>5.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.
shardingsphere</groupId>
<artifactId>
shardingsphere-proxy-backend-common</artifactId>
<version>5.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.
shardingsphere</groupId>
<artifactId>
shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.
shardingsphere</groupId>
<artifactId>
shardingsphere-proxy-frontend-common</artifactId>
<version>5.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.
shardingsphere</groupId>
<artifactId>
shardingsphere-proxy-bootstrap</artifactId>
<version>5.1.2</version>
</dependency>
```
2. 配置数据源
在application.yml文件中配置数据源信息，例如：
```yaml
spring:
datasource:
sharding:
datasource:
ds_0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
username: root
password: root
ds_1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
username: root
password: root
# 配置分库规则
shardingRule:
# 分库策略
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
#
分表策略
defaultTableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
# 绑定表
bindingTables:
- t_order
# 分库
分表算法
shardingAlgorithms:
inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
algorithm-expression: t_order_${order_id % 2}
# 配置默认数据源
url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=UTF8&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
```
3. 创
建实体类
创
建一个简单的实体类，例如：
```java
public class Order {
private Long orderId;
private Long userId;
private String status;
// getter and setter
}
```
4. 创
建DAO
创
建一个简单的DAO类，通过注解来指定表名和主键，例如：
```java
@Repository
@Mapper
public interface OrderDao {
@Insert("insert into t_order (order_id, user_id, status) values (#{orderId}, #{userId}, #{status})")
@Options(useGeneratedKeys = true, keyProperty = "orderId")
int insert(Order order);
@Select("select * from t_order where order_id = #{orderId}")
@Results({
@Result(column = "order_id", property = "orderId"),
@Result(column = "user_id", property = "userId"),
@Result(column = "status", property = "status")
})
Order selectByPrimaryKey(Long orderId);
}
```
5. 编写测试用例
编写一个简单的测试用例，例如：
```java
@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderDaoTest {
@Autowired
private OrderDao orderDao;
@Test
public void testInsert() {
Order order = new Order();
order.setUserId(1L);
order.setStatus("INIT");
orderDao.insert(order);
System.out.println(order.getOrderId());
}
@Test
public void testSelectByPrimaryKey() {
Order order = orderDao.selectByPrimaryKey(1L);
System.out.println(order);
}
}
```
6. 运行测试用例
通过运行测试用例来验证分库
分表是否正常工作。
本文介绍了如何在Sharding Sphere 4.0.0-RC1版本中实现按年自动分表的功能。通过系统初始化时查询并缓存现有表，确保在插入和查询时只操作实际存在的表，避免了手动建表的需要。在插入数据时，如果发现指定年份的表不存在，会先创建表后再进行操作。而在范围查询时，仅从缓存的表中选择，防止查询超出实际范围。
最低0.47元/天 解锁文章
2496
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
