When the library is divided into the table middleware-SHARDING-JDBC

Use guide

Before you read this guide, read the Quick start. This document uses more complex scenarios to further describe the SHARDING-JDBC capabilities of the library. Database Schema

Two data sources Db0 and DB1 are available in this document, and each data source contains two sets of tables T_order_0 and T_order_1,t_order_item_0 and t_order_item_1. The table statements for these two sets of tables are:

CREATE TABLE IF not EXISTS ' t_order_x ' (
  ' order_id ' int not null,
  ' user_id '  int not null,
  PRIMARY KEY (' or der_id ')
);
CREATE TABLE IF not EXISTS ' t_order_item_x ' (
  ' item_id '  int not null,
  ' order_id ' int not null,
  ' user_id '  INT not NULL,
  PRIMARY KEY (' item_id ')
;

mapping relationship between logical table and actual table Evenly distributed

A situation in which a data table is uniformly distributed within each data source

Db0
  ├──t_order_0 
  └──t_order_1 
db1
  ├──t_order_0 
  └──t_order_1

Table rules can use the default configuration

Tablerule ordertablerule = new Tablerule ("T_order", Arrays.aslist ("T_order_0", "t_order_1"), Datasourcerule);

Custom Distribution

The data table renders a specific rule distribution

Db0
  ├──t_order_0 
  └──t_order_1 
db1
  ├──t_order_2
  ├──t_order_3

Table rules can specify the distribution of each table in the data source

Tablerule ordertablerule = new Tablerule ("T_order", Arrays.aslist ("Db0.t_order_0", "db0.t_order_1", "db1.t_order_2", " Db1.t_order_3 "," Db1.t_order_4 "), Datasourcerule);

Examples of data distributions used in this tutorial

Db0
  ├──t_order_0               user_id is even   order_id for even
  ├──t_order_1 user_id as even order_id for   odd numbers
  ├──t_ Order_item_0          user_id is even   order_id for even
  └──t_order_item_1 user_id for even-numbered order_id   for odd
-numbered DB1
  ├──t_order_0               user_id is odd   order_id for even
  ├──t_order_1               user_id for
  odd-numbered order_id ├──t_order_item_0          user_id is odd   order_id for even
  └──t_order_item_1          user_id for odd-numbered   order_id

logical tables and actual tables

The purpose of configuring the split-table is to spread the data from one table to the different tables in different libraries without changing the original SQL statement to use this table. Then the mapping relationship from one table to multiple uses the concepts of logical tables and actual tables. Here is an example to explain. If you are using PreparedStatement to access the database, SQL is as follows:

SELECT * from t_order where user_id =? and order_id =?;

When User_id=0 and order=0, SHARDING-JDBC converts the SQL statement to the following form:

SELECT * from db0.t_order_0 where user_id =? and order_id =?;

The t_order in the original SQL is the logical table, and the converted Db0.t_order_0 is the actual table rule configuration

The form of the above SHARDING-JDBC is described by the rule configuration, and the following sections describe the detailed configuration of the rule:

Shardingrule shardingrule = new Shardingrule (Datasourcerule, Arrays.aslist (Ordertablerule, orderItemTableRule),
                Arrays.aslist (New Bindingtablerule (Arrays.aslist (Ordertablerule, Orderitemtablerule)),
                New Databaseshardingstrategy ("user_id", New Modulodatabaseshardingalgorithm ()),
                new Tableshardingstrategy ("Order_ ID ", new Modulotableshardingalgorithm ()));

Data Source Configuration

First we construct the Datasourcerule object, which is to describe the distribution rules of the data source.

Datasourcerule datasourcerule = new Datasourcerule (DATASOURCEMAP);

Here the constructor requires an entry parameter: the mapping relationship between the name of the data source and the real data source, and the relationship is constructed as follows

map<string, datasource> datasourcemap = new hashmap<> (2);
Datasourcemap.put ("Ds_0", CreateDataSource ("Ds_0"));
Datasourcemap.put ("Ds_1", CreateDataSource ("Ds_1"));

The real data source can use any kind of database connection pool, here use DBCP to illustrate

private Static DataSource CreateDataSource (final String datasourcename) {basicdatasource result = new BASICDATASOURC
    E ();
    Result.setdriverclassname (Com.mysql.jdbc.Driver.class.getName ());
    Result.seturl (String.Format ("jdbc:mysql://localhost:3306/%s", DataSourceName));
    Result.setusername ("root");
    Result.setpassword ("");
return result;