sharding-jdbc 分库分表

sharding官网：https://shardingsphere.apache.org/

环境：

   jdk8,  springboot + jpa +  sharding-jdbc  实现分表分库

 

导包：

<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>

重要配置：

  注意：这里的sharding.jdbc.datasource.grouptest0.jdbc-url的数据库名称是自己命名的，不能是group_test0，sharding框架本身不支持这样的命名方式,所以 这里只能grouptest0的格式

sharding.jdbc.datasource.names=grouptest1,grouptest0
# 第一个数据库
sharding.jdbc.datasource.grouptest1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.grouptest1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.grouptest1.jdbc-url=jdbc:mysql://localhost:3306/grouptest1?characterEncoding=utf-8
sharding.jdbc.datasource.grouptest1.username=
sharding.jdbc.datasource.grouptest1.password=
# 第二个数据库
sharding.jdbc.datasource.grouptest0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.grouptest0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.grouptest0.jdbc-url=jdbc:mysql://localhost:3306/grouptest0?characterEncoding=utf-8
sharding.jdbc.datasource.grouptest0.username=
sharding.jdbc.datasource.grouptest0.password=

# 分库策略  分库的维度 定义在那张表 的ID来进行分库 例如：tables.t_clientinfo
sharding.jdbc.config.sharding.tables.t_clientinfo.database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.t_clientinfo.database-strategy.inline.algorithm-expression=grouptest$->{id %2}
# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
sharding.jdbc.config.sharding.tables.t_clientinfo.actual-data-nodes=grouptest$->{0..1}.t_clientinfo_$->{0..1}
sharding.jdbc.config.sharding.tables.t_clientinfo.table-strategy.inline.sharding-column=connected
sharding.jdbc.config.sharding.tables.t_clientinfo.table-strategy.inline.algorithm-expression=t_clientinfo_$->{connected % 2}

    

实体

@Entity
@Table(name = "t_clientinfo")
public class Clientinfo {


    private  Long  id;
    private Integer connected;
    private Long mostsignbits;
    private Long leastsignbits;
    private Date lastconnecteddate;


    @Id
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getConnected() {
        return connected;
    }

    public void setConnected(Integer connected) {
        this.connected = connected;
    }

    public Long getMostsignbits() {
        return mostsignbits;
    }

    public void setMostsignbits(Long mostsignbits) {
        this.mostsignbits = mostsignbits;
    }

    public Long getLeastsignbits() {
        return leastsignbits;
    }

    public void setLeastsignbits(Long leastsignbits) {
        this.leastsignbits = leastsignbits;
    }

    public Date getLastconnecteddate() {
        return lastconnecteddate;
    }

    public void setLastconnecteddate(Date lastconnecteddate) {
        this.lastconnecteddate = lastconnecteddate;
    }
}

接口 dao

   注意：这里写sql 用逻辑表

@Repository
public interface ClientinfoDao extends  JpaRepository<Clientinfo,Long> {
    
    @Query(value = " SELECT  * FROM t_clientinfo where id = ?1",nativeQuery = true)
    Clientinfo  findClientinfo(long id);
}

实现接口

 

@Service
public class ClientinfoService {


    @Autowired
    private ClientinfoDao clientinfoDao;


    public Clientinfo save(Clientinfo clientinfo){
       return clientinfoDao.save(clientinfo);
    }

    public Clientinfo get(long id){
//        Optional<Clientinfo>  optional = clientinfoDao.findById(id);
        return clientinfoDao.findClientinfo(id);
//        return  optional.get();
    }

 

controller层

@Controller
public class ClientinfoController {


    @Autowired
    private ClientinfoService   clientinfoService;



    @GetMapping("/sharding")
    @ResponseBody
    public String save(long id,int connected){
        System.out.println("132132123");
        Clientinfo clientinfo = new Clientinfo();
        clientinfo.setId(id);
        Random random = new Random();

        clientinfo.setConnected(connected);
        clientinfo.setLastconnecteddate(new Date());
        clientinfo.setLeastsignbits(Long.parseLong(random.nextInt()+"") );
        clientinfo.setMostsignbits(Long.parseLong(random.nextInt()+"") );

        System.out.println(JSON.toJSONString(clientinfoService.save(clientinfo)));
        return "ok";
    }

    @GetMapping("/shardingjdbc")
    @ResponseBody
    public Clientinfo get(long id){
        System.out.println("132132123");
        return clientinfoService.get(id);
    }
}