SHARDING-JDBC practice of "sub-Library sub-table"-Introduction to the Sub-Library sub-table

First, the preparatory work

1. Prepare three databases: Db0, DB1, DB2

2. Create two order tables per database: T_order_0, t_order_1

DROP TABLE IF EXISTS' t_order_x ';CREATE TABLE`t_order_x' (' ID 'bigint  not NULLauto_increment, 'user_id`bigint  not NULL, ' order_id 'bigint  not NULL, ' Order_no 'varchar( -) not NULL, ' IsActive 'tinyint  not NULL DEFAULT '1', ' Inserttime 'datetime  not NULL DEFAULT Current_timestamp, ' UpdateTime 'datetime  not NULL DEFAULT Current_timestamp  on UPDATE Current_timestamp,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

Second, sub-database table configuration

The configuration of the data source can use any link pool, this example uses Druid as an example.

1. Introduction Dependency Package:

Referencing the latest MAVEN packages

<sharding-jdbc.version>2.0.1</sharding-jdbc.version>

<dependency>
  <groupId>io.shardingjdbc</groupId>
  <artifactId>sharding-jdbc-core</artifactId>
  <version>${sharding-jdbc.version}</version>
</dependency>

2, Configuration datasource:

    @Bean(Name="Shardingdatasource", Destroymethod="Close")    @Qualifier("Shardingdatasource") PublicDataSource Getshardingdatasource () {//Configure a real data source Map<String, DataSource>Datasourcemap=New HashMap<>(3); //Configure the first data source Druiddatasource DataSource1=Createdefaultdruiddatasource ();        Datasource1.setdriverclassname ("Com.mysql.jdbc.Driver"); Datasource1.seturl ("Jdbc:mysql://localhost3306/db0 ");        Datasource1.setusername ("root");        Datasource1.setpassword ("root");        Datasourcemap.put ("Db0", DataSource1); //Configure a second data source Druiddatasource DataSource2=Createdefaultdruiddatasource ();        Datasource2.setdriverclassname ("Com.mysql.jdbc.Driver"); Datasource2.seturl ("Jdbc:mysql://localhost3306/db1 ");        Datasource2.setusername ("root");        Datasource2.setpassword ("root"); Datasource2.setname ("DB1-0001");        Datasourcemap.put ("DB1", DataSource2); //Configure a third data source Druiddatasource DataSource3=Createdefaultdruiddatasource ();        Datasource3.setdriverclassname ("Com.mysql.jdbc.Driver"); Datasource3.seturl ("Jdbc:mysql://localhost3306/DB2 ");        Datasource3.setusername ("root");        Datasource3.setpassword ("root");        Datasourcemap.put ("DB2", DataSource3); //Configure Order Table rules tableruleconfiguration Ordertableruleconfig=new Tableruleconfiguration ();        Ordertableruleconfig.setlogictable ("T_order"); Ordertableruleconfig.setactualdatanodes ("db${0..2}.t_order_${0..1}");//ordertableruleconfig.setactualdatanodes ("Db0.t_order_0,db0.t_order_1,db1.t_order_0,db1.t_order_1,db2.t_        Order_0,db2.t_order_1 "); //Configure the library policy (Groovy expression Configuration db rule) Ordertableruleconfig.setdatabaseshardingstrategyconfig (new Inlineshardingstrategyconfiguration ("user_id"," db${user_id % 3}")); //Configure a table policy (Groovy expression Configuration table routing rule) ordertableruleconfig.settableshardingstrategyconfig (new Inlineshardingstrategyconfiguration ("order_id", "t_order_${order_id% 2}")); //Configuring Shard rules shardingruleconfiguration Shardingruleconfig=new Shardingruleconfiguration (); Shardingruleconfig.gettableruleconfigs ().Add(Ordertableruleconfig); //Configure Order_items table rules ...//gets the data source object DataSource DataSource= NULL; try {DataSource=Shardingdatasourcefactory.createdatasource (Datasourcemap, Shardingruleconfig, New Concurrenthashmap (), New        Properties ());        } catch (SQLException e) {e.printstacktrace (); }        returnDataSource; }

You can use Druid to monitor the db.

iii. Example Validation 1, new data

@Slf4j @restcontroller@requestmapping ("/order") Public classOrdercontroller {@AutowiredPrivateOrdermapper Ordermapper; @RequestMapping ("/add")     Public voidAddOrder () {orderentity entity10=NewOrderEntity (); Entity10.setorderid (10000L); Entity10.setorderno ("No1000000"); Entity10.setuserid (102333001L); Ordermapper.insertselective (ENTITY10);
OrderEntity Entity11=NewOrderEntity (); Entity11.setorderid (10001L); Entity11.setorderno ("No1000000"); Entity11.setuserid (102333000L);    Ordermapper.insertselective (ENTITY11); }}

Based on the configured shard rules

• DB routing rule: user_id% 3:

102333001% 3 = 1

102333000% 3 = 0

• Table Routing rules: order_id% 2:

10000% 2 = 0

10001% 2 = 1

USERID=102333001,ORDERID=10000 's data landed to Db1.t_order_0
USERID=102333000,ORDERID=10001 's data landed to Db0.t_order_1

2. Query that does not specify a Shard rule field

    /** broadcast traversal of all libraries and tables */     @RequestMapping ("Get")    publicvoid  GetOrder () {        List New Arraylist<>();        Ids.add (4);        List<OrderEntity> orderentities = ordermapper.selectbyprimaryids (IDS);        Log.info (json.tojsonstring (orderentities));    }

By Druid Monitoring SQL, the query is broadcast to DB0, DB1, DB2 of the various tables, as shown in the following monitoring:

3. Cannot perform BULK insert operation

Bulk INSERT operations on field values of different Shard rules are not supported, similar to sql:insert into T_order values (x,x,x,x), (x,x,x,x), (x,x,x,x)

4. Carefully modify the Shard rule field

If you modify the fields of a shard rule, such as user_id or order_id in this example, because the routing rules cause the data to exist, you cannot find the data.

@RequestMapping ("/upd")     Public voidUpdate () {OrderEntity orderwhere=NewOrderEntity (); Orderwhere.setorderid (10001L); Orderwhere.setuserid (102333001L); Orderwhere.setid (4L); OrderEntity Orderset=NewOrderEntity (); Orderset.setorderid (10002L); Orderset.setorderno ("Modify Order Number");        Ordermapper.updatebypredicate (Orderset, Orderwhere); /**not found, OrderID changes can cause routing queries to fail*/orderentity predicate=NewOrderEntity (); Predicate.setorderid (10002L); OrderEntity entity=ordermapper.selectsinglebypredicate (predicate); Log.info ("After update OrderEntity:" +json.tojsonstring (entity)); }

Iv. sharding Construction of the table

3 libraries are currently configured and validated with 2 order tables per library scenario:

If you have a large number of sub-tables, simply creating a table is a tedious task. sharding query data does not specify the Shard rule field, will automatically be routed to the various tables of the library query, do not know whether people think: If you configure the routing rules to create a table, using Sharding to execute a SQL-created statement, will automatically route to the library to execute, Instead of manually to each library to build a table? Here's a look at the idea to create the T_order_items table as an example:

1, configure the rules of T_order_items:

Under configure T_order rules above, Supplement T_order_items's rule configuration:

        //Omit configuration order_item table rules ...Tableruleconfiguration Orderitemtableruleconfig =Newtableruleconfiguration (); Orderitemtableruleconfig.setlogictable ("T_order_items"); Orderitemtableruleconfig.setactualdatanodes ("Db${0..2}.t_order_items_${0..1}");//Configure a library policyOrderitemtableruleconfig.setdatabaseshardingstrategyconfig (NewInlineshardingstrategyconfiguration ("order_id", "db${order_id% 3}")); //Configure a table policyOrderitemtableruleconfig.settableshardingstrategyconfig (NewInlineshardingstrategyconfiguration ("order_id", "T_order_items_${order_id% 2} ")); Shardingruleconfig.gettableruleconfigs (). Add (orderitemtableruleconfig); 

2. T_order_items SQL statement:

    <id= "createtitemsifnotexiststable">        CREATE TABLE IF not EXISTS ' T_order_items ' (          ' id ' bigint not NULL auto_increment,          ' order_id ' bigint not NULL,          ' Unique_no ' varchar (+) NOT null,          ' quantity ' int. NOT NULL default ' 1 ',          ' is_active ' tinyint not null default 1,          ' Insertt IME ' datetime NOT NULL default Current_timestamp,          ' updatetime ' datetime NOT NULL default Current_timestamp on UPDATE Current_timestamp,          PRIMARY KEY (' id ')        ) Engine=innodb DEFAULT Charset=utf8;     </ Update >

3, Orderitemsmapper Method:

Integer createtitemsifnotexiststable ();

4, the method of implementation:

Orderitemsmapper.createtitemsifnotexiststable ();

View Db0, DB1, DB2:

The idea above has been verified and the table has been built successfully.

Appendix

If you do not configure the T_order_items rule, the SQL execution table will error:

org.mybatis.spring.MyBatisSystemException:nested exception is Org.apache.ibatis.exceptions.PersistenceException:
# # # Error updating database. Cause:io.shardingjdbc.core.exception.ShardingJdbcException: Cannot find table rule and default data source with Logi C table: ' T_order_items '
# # # The error may involve Defaultparametermap
# # # The error occurred while setting parameters
# # # sql:create TABLE IF not EXISTS ' t_order_items ' (' id ' bigint not NULL auto_increment, ' order_id ' bigint not NULL, ' unique_no ' varchar (+) NOT null, ' quantity ' int not null default ' 1 ', ' is_active ' tinyint not null default 1, ' Inserttim E ' datetime NOT NULL default Current_timestamp, ' UpdateTime ' datetime NOT NULL default Current_timestamp on UPDATE current _timestamp, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8;
# # # Cause:io.shardingjdbc.core.exception.ShardingJdbcException:Cannot Find table rule and default data source with L Ogic table: ' T_order_items '

SHARDING-JDBC practice of "sub-Library sub-table"-Introduction to the Sub-Library sub-table