java：使用shardingSphere访问mysql的分库分表数据

# 创建分库与分表

创建两个数据库【order_db_1、order_db_2】。
然后在两个数据库下分别创建三个表【orders_1、orders_2、orders_3】。
建表sql请参考：

CREATE TABLE `orders_1`  (
  `id` bigint NOT NULL,
  `order_type` varchar(255) NULL DEFAULT NULL,
  `customer_id` bigint NULL DEFAULT NULL,
  `amount` double NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) 
ENGINE = InnoDB

# 下面开始写测试代码：

【pom.xml】

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
    <version>2.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.0</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.3.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-core</artifactId>
    <version>3.4.3.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.22</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba.fastjson2</groupId>
    <artifactId>fastjson2</artifactId>
    <version>2.0.49</version>
</dependency>

【application.properties】

server.port=8080
spring.application.name=myShardingSphere

management.server.port=8080
management.endpoints.web.exposure.include=*

########################################

management.health.db.enabled=false
management.health.elasticsearch.enabled=false

mybatis-plus.mapper-locations=classpath:mapper/*.xml
mybatis-plus.type-aliases-package=test/persistence/mapper

##########################################################

spring.shardingsphere.datasource.names=ds-order-db-1, ds-order-db-2

######################################################
# 数据源

spring.shardingsphere.datasource.ds-order-db-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-order-db-1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-order-db-1.url=jdbc:mysql://192.168.44.228:3306/order_db_1
spring.shardingsphere.datasource.ds-order-db-1.username=root
spring.shardingsphere.datasource.ds-order-db-1.password=root

spring.shardingsphere.datasource.ds-order-db-2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-order-db-2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-order-db-2.url=jdbc:mysql://192.168.44.228:3306/order_db_2
spring.shardingsphere.datasource.ds-order-db-2.username=root
spring.shardingsphere.datasource.ds-order-db-2.password=root

#################################################
# table

spring.shardingsphere.sharding.tables.orders.actual-data-nodes=ds-order-db-${1..2}.orders_${1..3}

spring.shardingsphere.sharding.tables.orders.database-strategy.inline.sharding-column=customer_id
spring.shardingsphere.sharding.tables.orders.database-strategy.inline.algorithm-expression=ds-order-db-$->{customer_id % 2 + 1}

spring.shardingsphere.sharding.tables.orders.table-strategy.inline.sharding-column=customer_id
spring.shardingsphere.sharding.tables.orders.table-strategy.inline.algorithm-expression=orders_${customer_id % 3 + 1}

##################################################

spring.shardingsphere.props.sql.show=true

################################################

【mapper/OrdersMapper.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.chz.myShardingSphere.persistence.mapper.OrdersMapper">

    <resultMap id="orders" type="com.chz.myShardingSphere.persistence.po.Orders">
        <id column="id" property="id" />
        <result column="orderType" property="order_type" />
        <result column="customerId" property="customer_id" />
        <result column="amount" property="amount" />
    </resultMap>

    <select id="testSelectOrder" parameterType="long" resultMap="orders">
        SELECT *
        FROM orders
        <where>
            <if test="customerId!=null">
                and customer_id = #{customerId}
            </if>
        </where>
        order by id
        LIMIT 10 OFFSET 0
    </select>

</mapper>

【TestController.java】

package com.chz.myShardingSphere.controller;

@Slf4j
@RestController
@RequestMapping("/test")
public class TestController {

    @Value("${spring.application.name}")
    private String applicationName;

    @Autowired
    private TestService testService;

    @GetMapping("/testAddOrder")
    public String testAddOrder(
            @RequestParam(value = "id", defaultValue = "1") Long id
    ) {
        testService.testAddOrder(id);
        return "testAddOrder";
    }

    @GetMapping("/testSelectOrder")
    public String testSelectOrder(
            @RequestParam(value = "customerId", required = false) Long customerId
    ) {
        List<Orders> orders = testService.testSelectOrder(customerId);
        return JSON.toJSONString(orders);
    }

    @GetMapping("/testDeleteOrder")
    public String testDeleteOrder() {
        testService.testDeleteOrder();
        return "success";
    }
}

【OrdersMapper.java】

package com.chz.myShardingSphere.persistence.mapper;

@Repository
@Mapper
public interface OrdersMapper extends BaseMapper<Orders>
{
    List<Orders> testSelectOrder(@Param("customerId") Long customerId);
}

【Orders.java】

package com.chz.myShardingSphere.persistence.po;

@Getter
@Setter
public class Orders {

    private Long id;
    private Long orderType;
    private Long customerId;
    private Double amount;

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderType=" + orderType +
                ", customerId=" + customerId +
                ", amount=" + amount +
                '}';
    }
}

【TestService.java】

package com.chz.myShardingSphere.server;

@Service
public class TestService {

    @Autowired
    private OrdersMapper ordersMapper;

    @Transactional
    public void testAddOrder(long id)
    {
        Orders orders = new Orders();
        orders.setId(id);
        orders.setCustomerId(id);
        orders.setOrderType(id);
        orders.setAmount((double)id);
        ordersMapper.insert(orders);
    }

    @Transactional
    public List<Orders> testSelectOrder(Long customerId)
    {
        List<Orders> orders = ordersMapper.testSelectOrder(customerId);
        return orders;
    }

    @Transactional
    public void testDeleteOrder()
    {
        LambdaQueryWrapper<Orders> queryWrapper = new LambdaQueryWrapper<>();
        ordersMapper.delete(queryWrapper);
    }
}

【MyShardingSphereTest.java】

package com.chz.myShardingSphere;

@SpringBootApplication
public class MyShardingSphereTest {

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

启动【MyShardingSphereTest】

# 下面进行测试：

访问以下url创建6条数据：

http://localhost:8080/test/testAddOrder?id=1
http://localhost:8080/test/testAddOrder?id=2
http://localhost:8080/test/testAddOrder?id=3
http://localhost:8080/test/testAddOrder?id=4
http://localhost:8080/test/testAddOrder?id=5
http://localhost:8080/test/testAddOrder?id=6

然后访问【http://localhost:8080/test/testSelectOrder】看下数据能不能查出来：

可以看到数据都查出来了。

增加个条件试试，访问【http://localhost:8080/test/testSelectOrder?customerId=1】

也运行正常