博客

需求场景：日志量过大，需要进行按年月份分表存储，用定时任务每月动态创建下个月的表，目前只是分表，单库性能不足时可以考虑分库，读写分离。

1.导入依赖

<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--Mybatis-Plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>
<!--shardingsphere-->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>3.1.0</version>
</dependency>

2.配置文件

sharding:
  jdbc:
    datasource:
      names: ds0 #库
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.218.194:3306/ds0?characterEncoding=utf-8&&serverTimezone=GMT%2B8
        username: root
        password: 90-iopjklnm,

    config:
      sharding:
        props:
          sql.show: true
        tables:
          user: #表
            actual-data-nodes: ds0.user_$->{2020..2099}0$->{1..9},ds0.user_$->{2020..2099}1$->{0..2} #表节点
            table-strategy:
              inline: #分表策略-inline
                sharding-column: sys_order_id
                algorithm-expression: user_$->{sys_order_id[2..7]} #截取该字段的2-7位决定落在哪张表

注：shardingjdbc版本不同，配置形式也会不同

3.entity

import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import groovy.transform.EqualsAndHashCode;
import lombok.Data;
import lombok.experimental.Accessors;

/**
 * @author zsy
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("user")
public class User extends Model<User> {
  
    private int id;

    private String name;

    private int age;

    private String sysOrderId;
    
}

4.dao

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.sharding.entity.User;

/**
 * user dao层
 * @author zsy
 */
public interface UserMapper extends BaseMapper<User> {

}

5.service

import com.baomidou.mybatisplus.extension.service.IService;
import com.example.sharding.entity.User;

import java.util.List;

/**
 * @author zsy
 */
public interface UserService extends IService<User> {

    /**
     * 保存用户信息
     * @param entity
     * @return
     */
    @Override
    boolean save(User entity);

    /**
     * 查询全部用户信息
     * @return
     */
    List<User> getUserList();
}

6.serviceImpl

/**
 * @author zsy
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    @Override
    public boolean save(User entity) {
        return super.save(entity);
    }

    @Override
    public List<User> getUserList() {
        return baseMapper.selectList(Wrappers.<User>lambdaQuery());
    }

}

7.controller

import java.util.List;

/**
 * @author zsy
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    @Override
    public boolean save(User entity) {
        return super.save(entity);
    }

    @Override
    public List<User> getUserList() {
        return baseMapper.selectList(Wrappers.<User>lambdaQuery());
    }

}

8.建库建表

CREATE TABLE `user_202102` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sys_order_id` varchar(50) DEFAULT NULL COMMENT '订单ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

9.在启动类加上@MapperScan(“com.example.sharding.dao”)，启动服务，成功将202102的数据插入到202102这张表。





git地址：https://github.com/woyaobianqiang/SpringBoot-demo/tree/master/sharding