分布式数据库-最终案例-ShardingJDBC

1：新建一个springboot工程

2：创建两个数据库order1，order2，分别创建t_address表如下：

DROP TABLE IF EXISTS `t_address`;
CREATE TABLE `t_address` (
  `id` bigint(20) NOT NULL,
  `code` varchar(64) DEFAULT NULL COMMENT '编码',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `pid` varchar(64) NOT NULL DEFAULT '0' COMMENT '父id',
  `type` int(11) DEFAULT NULL COMMENT '1国家2省3市4县区',
  `lit` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3： 开始整合SpringBoot

这种方式比较简单只要加入sharding-jdbc-spring-boot-starter依赖，在application.yml中配置数据源，分片策略即可使用，这种方式简单，方便。pom.xml

3.1pom

<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>3.0.0</version>
</dependency>

3.2appplication.yml

mybatis:
  configuration:
    mapUnderscoreToCamelCase: true
spring:
  main:
    allow-bean-definition-overriding: true
# shardingjdbc分库分表
sharding:
  jdbc:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order1?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
        username: root
        password: root
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/order2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
        username: root
        password: root
    config:
      sharding:
        props:
          sql.show: true
        tables:
          t_user: #t_user表【即分库，又分表】
            key-generator-column-name: id # 主键
            actual-data-nodes: ds${0..1}.t_user${0..1} #数据节点
            database-strategy: #分库策略
              inline:
                sharding-column: city_id
                algorithm-expression: ds${city_id % 2}
            table-strategy: #分表策略
              inline:
                shardingColumn: sex
                algorithm-expression: t_user${sex % 2}
          t_address: #t_address表【只分库】
            key-generator-column-name: id
            actual-data-nodes: ds${0..1}.t_address
            database-strategy:
              inline:
                shardingColumn: lit
                algorithm-expression: ds${lit % 2}

3.3编写Vo

package com.itheima.springbootshardingpro.vo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class AddressVo {
    private Long id;
    private String code;
    private String name;
    private String pid;
    private Integer type;
    private Integer lit;
}

3.4 编写Dao

package com.itheima.springbootshardingpro.dao;
import com.itheima.springbootshardingpro.vo.AddressVo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface IndexDao {
    @Options(useGeneratedKeys = true)
    @Insert("insert into t_address (code,name,pid,type,lit)values(#{code},#{name},#{pid},#{type},#{lit})")
    int insertAddress(AddressVo addressVo);
    @Select("select * from t_address order by lit")
    List<AddressVo> listAddress();
}

3.5 编写controller

package com.itheima.springbootshardingpro.web;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.itheima.springbootshardingpro.dao.IndexDao;
import com.itheima.springbootshardingpro.vo.AddressVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class IndexController {
    @Autowired
    private IndexDao indexDao;
    @PostMapping("/addAddress")
    public int addAddress(AddressVo addressVo){
        int row = indexDao.insertAddress(addressVo);
        return row;
    }
    @GetMapping("/listAddress")
    public PageInfo<AddressVo> listAddress(@RequestParam(required=false,defaultValue="1")Integer pageNum,
                                @RequestParam(required=false,defaultValue="5")Integer pageSize){
        PageHelper.startPage(pageNum,pageSize);
        List<AddressVo> list =  indexDao.listAddress();
        PageInfo<AddressVo> info = new PageInfo<>(list);
        return info;
    }
}

此时，启动项目，用postman访问插入接口：