springboot~shardingsphere在非spring框架中的使用

shardingsphere已经很方便的被springboot集成了，你只要引入sharding-jdbc-spring-boot-starter这个包就可以了，而如果是原生java的话，你就需要自己去实现了，主要是重新定义数据源，定义规则等问题，本文主要介绍原生环境下的shardingsphere的使用。

依赖引用

<dependencies>
        <!-- mysql 数据源 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>

        <!-- HikariCP连接池 -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>4.0.3</version>
        </dependency>

        <!-- shardingsphere分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.1</version> <!-- 替换成最新版本 -->
        </dependency>

        <!-- ORM -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.15.Final</version>
        </dependency>

        <!-- 生产力工具 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
        </dependency>
    </dependencies>

建立DataSource数据源

• 添加了数据源，确定了mysql数据库，支持同时设置多个数据源
• 定义了分库分表的规则

  public static DataSource createDataSource() throws SQLException {
    // 配置真实数据源
    Map<String, DataSource> dataSourceMap = new HashMap<>();
    // 配置第一个 MySQL 数据源
    HikariDataSource ds0 = new HikariDataSource();
    ds0.setDriverClassName("com.mysql.jdbc.Driver");
    ds0.setJdbcUrl("jdbc:mysql://192.168.xx.xx:3306/sharding0");
    ds0.setUsername("root");
    ds0.setPassword("123456");
    dataSourceMap.put("ds0", ds0);

    // 配置分片规则
    TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order", "ds0.t_order_${0..2}");
    ShardingStrategyConfiguration shardingStrategyConfiguration = new StandardShardingStrategyConfiguration("order_id", new ModuloShardingAlgorithm());
    tableRuleConfig.setTableShardingStrategyConfig(shardingStrategyConfiguration);

    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);

    // 创建 ShardingSphere 数据源
    return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
}

建立分表规则

/**
 * 分表算法
 */
public class ModuloShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(String.valueOf(shardingValue.getValue() % 2))) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

源生SQL方式读取数据

static void select() throws SQLException {
    // 创建数据源
    DataSource dataSource = createDataSource();

    // 执行SQL
    try (Connection conn = dataSource.getConnection()) {
        String sql = "SELECT * FROM t_order WHERE order_id = ?";
        try (PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
            preparedStatement.setLong(1, 11L);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    System.out.println(resultSet.getString("order_id"));
                }
            }
        }
    }
}

Hibernate方式，写入，读取数据

定义实体

@Entity
@Data
@Table(name = "t_order")
@NamedQueries({
        @NamedQuery(
                name = "findById",
                query = "FROM Order t WHERE t.orderId = :orderId"
        )
})
public class Order {
    @Id
    @Column(name = "order_id", nullable = false)
    private Long orderId;
    @Column(name = "user_id", nullable = false)
    private Long userId;
    @Column(name = "amount", nullable = false)
    private Double amount;
}

定义数据仓库

public class OrderRepository {
    // 创建EntityManagerFactory
    EntityManagerFactory entityManagerFactory;

    // 创建EntityManager
    EntityManager entityManager;

    public OrderRepository() throws SQLException {
        this.entityManagerFactory = Persistence.createEntityManagerFactory("lindDb", getProperties(Main.createDataSource()));
        this.entityManager = entityManagerFactory.createEntityManager();
    }

    private static Map<String, Object> getProperties(DataSource dataSource) {
        Map<String, Object> properties = new HashMap<>();
        properties.put("javax.persistence.nonJtaDataSource", dataSource);
        return properties;
    }

    public List<Order> findById(Long id) {
        return entityManager.createQuery("findById", Order.class).setParameter("orderId", id).getResultList();
    }

    public void save(Order order) {
        entityManager.getTransaction().begin();
        entityManager.persist(order);
        entityManager.getTransaction().commit();
        entityManager.close();
        entityManagerFactory.close();
    }
}

定义添加逻辑及测试

static void HihernateInsert() throws SQLException {
    OrderRepository orderRepository = new OrderRepository();
    Order order = new Order();
    order.setAmount(100d);
    order.setOrderId(11L);
    order.setUserId(6L);
    orderRepository.save(order);
}

根据分表的规则，这条数据会被写到t_order_1这张数据表里。

注意：当采用分表规则之后，如果你的记录主键没有按着规则存储，是无法获取到时数据的，如order_id为1的记录被存储到了t_order_0里，那这条数据将无法被找到。