shardingsphere读写分离+分库分表

首先创建4个数据库，ds0,ds1,ds2,ds3,然后创建4张表msg_1,msg_2,t_order_1,t_order_2,图中的people是上篇文章用的，可以不用管。看下表的结构，

msg_1和msg_2表的结构：

t_order_1和t_order_2表结构如下：

创建一个新的SpringBoot项目，pom.xml文件如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.8.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc</name>
<description>Demo project for Spring Boot</description>

<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC2</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

创建Msg和Order的实体类:

package com.coco.entity;

public class Msg {

private int id;
private Long userId;
private String name;

public int getId() {
    return id;
}

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

public Long getUserId() {
    return userId;
}

public void setUserId(Long userId) {
    this.userId = userId;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}
}

==============================

package com.coco.entity;

import java.io.Serializable;
import java.math.BigDecimal;

public class Order implements Serializable {

private Long id;
private BigDecimal orderAmount;
private int orderStatus;
private int userId;

public Long getId() {
    return id;
}

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

public BigDecimal getOrderAmount() {
    return orderAmount;
}

public void setOrderAmount(BigDecimal orderAmount) {
    this.orderAmount = orderAmount;
}

public int getOrderStatus() {
    return orderStatus;
}

public void setOrderStatus(int orderStatus) {
    this.orderStatus = orderStatus;
}

public int getUserId() {
    return userId;
}

public void setUserId(int userId) {
    this.userId = userId;
}
}

创建Msg和Order的dao层：

 package com.coco.dao;

import com.coco.entity.Msg;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface MsgMapper {

void addMsg(Msg msg);

List<Msg> getData();
}

==============================

package com.coco.dao;

import com.coco.entity.Order;
import org.springframework.stereotype.Repository;

@Repository
public interface OrderMapper {

void saveData(Order order);

}

在resources目录下创建mappers目录放置xml配置文件：

msg.xml:

   <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.coco.dao.MsgMapper">

    <resultMap id="userMap" type="com.coco.entity.Msg">
        <result column="user_id" property="userId" jdbcType="BIGINT"/>
        <result column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </resultMap>

   <insert id="addMsg" parameterType="com.coco.entity.Msg">
       insert into msg (id,name) values (#{id},#{name})
   </insert>

    <select id="getData" resultMap="userMap">
        select * from msg
    </select>

</mapper>

order.xml:

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.coco.dao.OrderMapper">

    <select id="getDataById" resultType="com.coco.entity.Order">
        select id,order_amount,order_status,user_id from t_order where id = #{id} and user_id = #{user_id}
    </select>

    <insert id="saveData" parameterType="com.coco.entity.Order">
        insert into t_order (order_amount,order_status,user_id) values (#{orderAmount},#{orderStatus},#{userId})
    </insert>

</mapper>

启动类：

 package com.coco;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.coco.dao")
public class ShardingJdbcApplication {

public static void main(String[] args) {
    SpringApplication.run(ShardingJdbcApplication.class, args);
}

}

自定义路由：

 package com.coco.sharding;


import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

public class MySharding implements PreciseShardingAlgorithm<Long>{


    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {

        Long id = preciseShardingValue.getValue();
        System.out.println("user_id = "+id);
        long model = id % collection.size();
        String[] strings = collection.toArray(new String[0]);
        System.out.println("strings = "+strings);
        return strings[(int)model];
    }
}

配置文件：

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.coco.entity

spring.shardingsphere.datasource.names=m0,m1,s0,s1

spring.shardingsphere.datasource.m0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

spring.shardingsphere.datasource.m1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.s1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.jdbc-url=jdbc:mysql://localhost:3306/ds2
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root

spring.shardingsphere.datasource.s0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.jdbc-url=jdbc:mysql://localhost:3306/ds3
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root


# 基础的分片配置
#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}
#spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{id % 2 + 1}

#设置主键为分布式
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=m$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.coco.sharding.MySharding
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=345
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10


spring.shardingsphere.sharding.tables.msg.actual-data-nodes=m$->{0..1}.msg_$->{1..2}
spring.shardingsphere.sharding.tables.msg.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.msg.database-strategy.inline.algorithm-expression=m$->{id % 2}
spring.shardingsphere.sharding.tables.msg.table-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.msg.table-strategy.standard.precise-algorithm-class-name=com.coco.sharding.MySharding
spring.shardingsphere.sharding.tables.msg.key-generator.column=user_id
spring.shardingsphere.sharding.tables.msg.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.msg.key-generator.props.worker.id=345
spring.shardingsphere.sharding.tables.msg.key-generator.props.max.tolerate.time.difference.milliseconds=10

#配置读写分离
spring.shardingsphere.sharding.master-slave-rules.m0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.m0.slave-data-source-names=s0
spring.shardingsphere.sharding.master-slave-rules.m1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.m1.slave-data-source-names=s1
#配置绑定表
#sharding.jdbc.config.sharding.binding-tables[0]=t_order,t_order_item
#sharding.jdbc.config.sharding.binding-tables[1]=t_order,t_order_item

# 全局表
#spring.shardingsphere.sharding.broadcast-tables=area

#控制台打印出shardingsphere的sql语句
spring.shardingsphere.props.sql.show=true

然后插入数据或者查询可以观看控制台的打印信息,完整代码：gitee.com/baojh123/sh…