Sharding-JDBC的SpringBoot使用

本文使用的sharding-jdbc版本为3.1.0，采用springboot的配置。

不同的sharding-jdbc版本，会有配置上的差异。请大家注意下版本问题。

要求：模拟订单数据库，有两个数据库demo_ds_0、demo_ds_1，依据于uid进行分库。每个库中，将t_order表依据order_id进行拆分为t_order_0，t_order_1两个表。

1. 数据库及表创建

use demo_ds_0;
CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

use demo_ds_1;
CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. pom文件引入

<dependencies>
      <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
      </dependency>

      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
      </dependency>

      <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>3.1.0</version>
      </dependency>

      <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>3.1.0</version>
      </dependency>

      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-dependencies</artifactId>
        <version>2.1.6.RELEASE</version>
        <type>pom</type>
      </dependency>

      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
        <version>2.1.6.RELEASE</version>
      </dependency>

      <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <version>2.1.6.RELEASE</version>
      </dependency>

    </dependencies>

3. application.properties配置

sharding.jdbc.datasource.names=ds0,ds1

# 数据源ds_0
sharding.jdbc.datasource.ds0.type=com.mysql.cj.jdbc.MysqlDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/demo_ds_0
sharding.jdbc.datasource.ds0.user=test
sharding.jdbc.datasource.ds0.password=!1A@2b#3c

# 数据源ds_1
sharding.jdbc.datasource.ds1.type=com.mysql.cj.jdbc.MysqlDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/demo_ds_1
sharding.jdbc.datasource.ds1.user=test
sharding.jdbc.datasource.ds1.password=!1A@2b#3c

sharding.jdbc.config.sharding.props.sql.show=true

# 分表
sharding.jdbc.config.sharding.tables.t_order.logic-table=t_order
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id

# 分库
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

4. service编写

@Service
public class OrderService {

    @Autowired
    private DataSource dataSource;

    public void insert() throws SQLException {
        String sql = "insert into t_order(`user_id`, `status`) values(?, ?)";

        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 11114);
        preparedStatement.setString(2, "order init");
        preparedStatement.executeUpdate();
    }

    public void select() throws SQLException {
        String sql = "SELECT * FROM t_order WHERE user_id = 11114 AND order_id = 360811169792917505";

        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        Object row;
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                row = resultSet.getObject(i);
                System.out.print(row);
                System.out.print("\t");
            }
            System.out.println();
        }
    }
}

5. 编写测试

public class OrderServiceTest extends ShardingSpringbootDemoApplicationTest {

    @Autowired
    private OrderService orderService;

    @Test
    public void insert() throws SQLException {
        orderService.insert();
    }

    @Test
    public void select() throws SQLException {
        orderService.select();
    }
}