shardingsphere分库分表多表关联查询

shardingsphere分库分表多表关联查询的应用

1.准备工具mysql
2.sql语句如下;
demo_ds_0数据源表结构

CREATE DATABASE demo_ds_0 CHARSET=utf8;
use demo_ds_0;
DROP TABLE IF EXISTS t_user_0;

CREATE TABLE t_user_0 (
user_id bigint(20) AUTO_INCREMENT,
username varchar(30) NOT NULL,
password varchar(30) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_user_1;

CREATE TABLE t_user_1 (
user_id bigint(20) AUTO_INCREMENT,
username varchar(30) NOT NULL,
password varchar(30) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_order_0;

CREATE TABLE t_order_0 (
order_id bigint(20) AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
title varchar(30) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_order_1;

CREATE TABLE t_order_1 (
order_id bigint(20) AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
title varchar(30) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

demo_ds_1数据源表结构`

CREATE DATABASE demo_ds_1 CHARSET=utf8;
use demo_ds_1;
DROP TABLE IF EXISTS t_user_0;

CREATE TABLE t_user_0 (
user_id bigint(20) AUTO_INCREMENT,
username varchar(30) NOT NULL,
password varchar(30) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_user_1;

CREATE TABLE t_user_1 (
user_id bigint(20) AUTO_INCREMENT,
username varchar(30) NOT NULL,
password varchar(30) DEFAULT NULL,
PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_order_0;

CREATE TABLE t_order_0 (
order_id bigint(20) AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
status varchar(30) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t_order_1;

CREATE TABLE t_order_1 (
order_id bigint(20) AUTO_INCREMENT,
user_id bigint(20) NOT NULL,
status varchar(30) DEFAULT NULL,
PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

spring.shardingsphere.datasource.names=ms0,ms1

spring.shardingsphere.datasource.ms0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ms0.jdbcUrl=jdbc:mysql://192.168.159.132:3306/demo_ds_0
spring.shardingsphere.datasource.ms0.username=root
spring.shardingsphere.datasource.ms0.password=root

spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.159.132:3306/demo_ds_1
spring.shardingsphere.datasource.ms1.username=root
spring.shardingsphere.datasource.ms1.password=root

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{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=ms$->{user_id % 2}
#分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
#主键生成策略
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.key-generator.props.worker.id=345
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10

#t_order_item
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ms$->{0..1}.t_order_item_$->{1..2}
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=user_id
#分数据库
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ms$->{user_id % 2}
#分表策略
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
#主键生成策略
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.worker.id=345
spring.shardingsphere.sharding.tables.t_order_item.key-generator.props.max.tolerate.time.difference.milliseconds=10
#全表/广播表
spring.shardingsphere.sharding.broadcast-tables=area
#绑定表规则列表
sharding.jdbc.config.sharding.binding-tables[0]=t_order,t_order_item

mybatis.mapper-locations=/mybatis/*.xml

logging.pattern.dateformat=HH:mm:ss```


 @Test
    public void testSelectOrderItem(){
        Order orderItem=orderMapper.selectByPrimaryKey(459460589959614465L);
        List<Map<String,Object>> result=orderMapper.selectOrderAndOrderItem();
        log.info("result是:{}",result);
    }

  <select id="selectOrderAndOrderItem"  resultType="java.util.Map">
    SELECT * FROM  t_order t1 join t_order_item t2 on t1.user_id=t2.user_id
  </select>

这样就可以去关联表进行查询，其中需要注意的是：
两个表关联的字段是user_id,其中user_id也是
分数据库的关键字，不然会查不出来结果的

项目链接地址:https://gitee.com/szy125980/shardingsphereExcemple.git