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

shardingsphere已經(jīng)很方便的被springboot集成了，你只要引入sharding-jdbc-spring-boot-starter這個(gè)包就可以了，而如果是原生java的話，你就需要自己去實(shí)現(xiàn)了，主要是重新定義數(shù)據(jù)源，定義規(guī)則等問(wèn)題，本文主要介紹原生環(huán)境下的shardingsphere的使用。

依賴引用

<dependencies>
        <!-- mysql 數(shù)據(jù)源 -->
        <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分庫(kù)分表 -->
        <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>

        <!-- 生產(chǎn)力工具 -->
        <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>

添加persistence.xml配置文件

• 在resources/META-INF下面添加persistence.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="lindDb">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://192.168.x.x:3306/sharding0"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value="xxx"/>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
        </properties>
    </persistence-unit>
</persistence>

建立DataSource數(shù)據(jù)源

• 添加了數(shù)據(jù)源，確定了mysql數(shù)據(jù)庫(kù)，支持同時(shí)設(shè)置多個(gè)數(shù)據(jù)源
• 定義了分庫(kù)分表的規(guī)則

  public static DataSource createDataSource() throws SQLException {
    // 配置真實(shí)數(shù)據(jù)源
    Map<String, DataSource> dataSourceMap = new HashMap<>();
    // 配置第一個(gè) MySQL 數(shù)據(jù)源
    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);

    // 配置分片規(guī)則
    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);

    // 創(chuàng)建 ShardingSphere 數(shù)據(jù)源
    return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
}

建立分表規(guī)則

/**
 * 分表算法
 */
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方式讀取數(shù)據(jù)

static void select() throws SQLException {
    // 創(chuàng)建數(shù)據(jù)源
    DataSource dataSource = createDataSource();

    // 執(zhí)行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方式，寫(xiě)入，讀取數(shù)據(jù)

定義實(shí)體

@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;
}

定義數(shù)據(jù)倉(cāng)庫(kù)

public class OrderRepository {
    // 創(chuàng)建EntityManagerFactory
    EntityManagerFactory entityManagerFactory;

    // 創(chuàng)建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();
    }
}

定義添加邏輯及測(cè)試

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);
}

根據(jù)分表的規(guī)則，這條數(shù)據(jù)會(huì)被寫(xiě)到t_order_1這張數(shù)據(jù)表里。

注意：當(dāng)采用分表規(guī)則之后，如果你的記錄主鍵沒(méi)有按著規(guī)則存儲(chǔ)，是無(wú)法獲取到時(shí)數(shù)據(jù)的，如order_id為1的記錄被存儲(chǔ)到了t_order_0里，那這條數(shù)據(jù)將無(wú)法被找到。