Sharding-Sphere：Sharding-JDBC分库分表（基于JPA）

官方文档

GitHub的地址：https://github.com/apache/incubator-shardingsphere
shardingsphere：https://github.com/apache/incubator-shardingsphere-example
中文文档地址：https://shardingsphere.apache.org/document/current/cn/overview/

代码实现（JPA）

（1）有3张表t_member、t_order、t_order_item，拆分到2个数据库中，每个数据库2份，相当于每张表拆成了4张，需要注意的是每个表的主键Id尽量设置成 bigint （我开始设置为varchar，结果分表时报错），结构如下：

（2）新建SpringBoot项目，引入依赖：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>${sharding-sphere.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.16</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

（3）添加配置文件，这点很重要，否则无法实现分库分表，ds$->{0..1}.t_member$->{0..1}，表达式相当于：ds0.t_member0、ds0.t_member1、ds1.t_member0、ds1.t_member1，在配置中配置了key-generator.column，所以新增该表数据时，不需要给主键id赋值，sharding-jdbc会调用内置的SNOWFLAKE算法生成分布式id，请往下看

server.port=8072

spring.jpa.hibernate.ddl-auto=none
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://47.107.177.150:3306/ds0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds0.username=xnxx
spring.shardingsphere.datasource.ds0.password=Xnxx85781245!

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://47.107.177.150:3306/ds1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds1.username=xnxx
spring.shardingsphere.datasource.ds1.password=Xnxx85781245!

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{member_id % 2}

spring.shardingsphere.sharding.tables.t_member.actual-data-nodes=ds$->{0..1}.t_member$->{0..1}
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.sharding-column=member_id
spring.shardingsphere.sharding.tables.t_member.table-strategy.inline.algorithm-expression=t_member$->{member_id % 2}
spring.shardingsphere.sharding.tables.t_member.key-generator.column=member_id
spring.shardingsphere.sharding.tables.t_member.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1}
#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order0, ds1.t_order1
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
#spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
#spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#
#spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1}
#spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
#spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
#spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE

#spring.shardingsphere.sharding.binding-tables=t_member,t_order,t_order_item
#spring.shardingsphere.sharding.broadcast-tables=t_config

spring.shardingsphere.props.sql.show=true

（4）Durid配置

@Configuration
public class DuridConfig {

    @Bean
    public Filter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(5000);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }

    @Bean
    public ServletRegistrationBean statViewServlet() {
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //设置ip白名单
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
        //设置ip黑名单，如果allow与deny共同存在时,deny优先于allow
        //servletRegistrationBean.addInitParameter("deny","192.168.0.19");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

}

（5）添加MemberService服务接口与实现

@Data
@Entity
@Table(name = "t_member")
public class Member {

    @Id
    @Column(name = "member_id")
    private Long memberId;

    @Column(name = "member_name")
    private String memberName;

    @Column(name = "nick_name")
    private String nickName;

    @Column(name = "account_no")
    private String accountNo;

    @Column(name = "password")
    private String password;

    @Column(name = "age")
    private Integer age;

    @Column(name = "birth_date")
    private Date birthDate;

    @Column(name = "ebl_flag")
    private String eblFlag;

    @Column(name = "del_flag")
    private String delFlag;

    @Column(name = "description")
    private String description;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;
}

@Repository
public interface MemberRepository extends JpaRepository<Member , Long> {

}

（6）添加MemberController

@RequestMapping("/member")
@RestController
public class MemberController {

    @Autowired private MemberRepository memberRepository;

    @RequestMapping("/health")
    public String health(){
        return "success";
    }

    @RequestMapping("/findAll")
    public Object findAll(){
        return memberRepository.findAll();
    }

    @RequestMapping("/add")
    public Object add(){
        Member member = addMember();
        return memberRepository.save(member);
    }

    @RequestMapping("/findById")
    public Member findById(Long memberId){
        return memberRepository.findById(memberId).get();
    }

    private Member addMember(){
        Member member = new Member();
        member.setMemberId(IdWorker.getLongId());
        //SnowflakeShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
        //member.setMemberId(Long.parseLong(keyGenerator.generateKey().toString()));
        member.setMemberName("张三");
        member.setNickName("闪耀的瞬间");
        member.setAccountNo(member.getMemberId()+"");
        member.setPassword("123465");
        member.setAge(27);
        member.setBirthDate(new Date());
        member.setEblFlag("1");
        member.setDelFlag("0");
        member.setDescription("xxx");
        member.setCreateTime(new Date());
        member.setUpdateTime(new Date());
        return member;
    }
}

（7）启动项目，访问新增接口 http://localhost:8071/member/add ，可以看到页面返回了新增后的数据

同时查看Intellij IDEA控制台，可以看到数据插入到ds1库的t_member1表了

再查看ds1数据库的t_member1表，有刚刚新增的记录

（8）访问查询接口 http://localhost:8072/member/findById?memberId=xxxxxx

同时查看控制台，sql会自动路由到ds1库去查询数据

参考：https://gitee.com/zhuyu1991/spring-cloud/tree/master/sharding-business/db-table-jpa