查询

在前面的案例中，如果进行查询，会有一个问题，数据分布在两个库中，也就是水平分库，那么在查询时不带上分库标识，就会出现查询错误

mybatis.configuration.map-underscore-to-camel-case=true
spring.main.allow-bean-definition-overriding=true

#数据源名称

spring.shardingsphere.datasource.names=m1,m2

#连接池（上面名称叫啥这里就要写啥 m1 就写m1）
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/order_db_2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

#数据分布情况，数据节点
#t_order可以任意，但是在SQL插入的时候就要跟这里对应
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2}

#指定t_order表的主键生成策略为SNOWFLAKE
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.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}


# 指定t_order表的分片策略，分片策略包括分片和分片算法
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 + 1}

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

logging.level.root=info
logging.level.com.g.shardingjdbc=debug
logging.level.druid.sql=debug
logging.level.org.springframework.web=info

@Test
public void list() {
    ArrayList<Long> objects = new ArrayList<>();
    objects.add(959582334099652609L);
    objects.add(959581835946360833L);
    List<Map> ids = tOrderMapper.getIds(objects);
    System.out.println(ids);
    System.out.println(ids.size());
}

<select id="getIds" resultType="java.util.Map">
    SELECT 
        * 
    FROM
        t_order 
    WHERE
        order_id in
    <foreach collection="orderIds" item="item" separator="," open="(" close=")">
        #{item}
    </foreach>
</select>

这样只会查询到m1的库中，因为在数据节点中配置的只有m1，他是不知道查询m2的，需要在数据分布中增加变量

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.t_order_$->{1..2}