1 springboot using sharding JDBC for library sub-tables

Sub-database sub-table in the data volume of the system is more commonly used, the solution has COBAR,TDDL, and so on, this time mainly take Dangdang open source Sharding-jdbc to do a small example.
Its github address is: HTTPS://GITHUB.COM/DANGDANGDOTCOM/SHARDING-JDBC
Brief introduction:
SHARDING-JDBC directly encapsulates the JDBC API, which can be understood as an enhanced version of the JDBC driver, and the legacy code migration cost is almost zero:
can be applied to any Java-based ORM framework such as JPA, Hibernate, Mybatis, Spring jdbc template or directly using JDBC.
Can be based on any third-party database connection pool, such as: DBCP, C3P0, BONECP, Druid and so on.
It is theoretically possible to support any database that implements the JDBC specification. Although only MySQL is currently supported, there are plans to support databases such as ORACLE,SQLSERVER,DB2.
SHARDING-JDBC is positioned as a lightweight Java framework that uses a direct-attached database of clients, provides services in the form of a jar package, does not use the middle tier, requires no additional deployment, has no other dependencies, and does not need to change the original operational mode. SQL parsing uses the Druid parser, which is currently the highest performing SQL parser.
The specific introduction can be on its document there to see, simply summed up is that it is an enhanced version of JDBC, the user is transparent, logic code and so on do not have to move, it to complete the operation of the sub-database table, and then it also supports distributed transactions (imperfect). Look good.
Here is a small example of the use of the Sub-database table. Using SPRINGBOOT,JPA (Hibernate), the Druid connection pool. Create a new spring boot project using idea

The pom file is as follows:

<?xml version= "1.0" encoding= "UTF-8"?> <project xmlns= "http://maven.apache.org/POM/4.0.0" xmlns:xsi= "http ://www.w3.org/2001/XMLSchema-instance "xsi:schemalocation=" http://maven.apache.org/POM/4.0.0/http Maven.apache.org/xsd/maven-4.0.0.xsd "> <modelVersion>4.0.0</modelVersion> <groupid>com.tian Yalei</groupid> <artifactId>shardingtest</artifactId> <version>0.0.1-snapshot</ version> <packaging>jar</packaging> <name>shardingtest</name> <description>d Emo project for Spring boot</description> <parent> <groupid>org.springframework.boot</gr Oupid> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.2.RELEASE< /version> <relativePath/> <!--lookup parent from repository to </parent> <prope Rties> <project.build.sourceencoding>utf-8</project.build.sourceEncoding> <project.reporting.outputencoding>utf-8</ project.reporting.outputencoding> <java.version>1.8</java.version> </properties> &lt
            ;d ependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency&gt
            ; <groupId>org.springframework.boot</groupId> <artifactid>spring-boot-starter-web</artifact
            id> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.41</version> < /dependency> <dependency> <groupId>com.dangdang</groupId> &LT;ARTIFAC Tid>sharding-jdbc-core</artifactid> <version>1.4.2</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <versi on>1.0.12</version> </dependency> <dependency> &LT;GROUPID&GT;ORG.SPRINGF Ramework.boot</groupid> <artifactId>spring-boot-starter-test</artifactId> &LT;SC 
            ope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <ar tifactid>spring-boot-maven-plugin</artifactid> </plugin> </plugins> </buil
 D> </project>

The latest sharding jdbc is 1.4.2, the Druid version is 1.0.12, this is because

Although not tested, but still according to their requirements come.
As you can see in the official documentation, there are three ways to configure sharding JDBC, which can be configured with Java code, YAML configuration, and spring XML configuration http://dangdangdotcom.github.io/sharding-jdbc/02- guide/configuration/
Different configurations require the introduction of different maven dependencies. Here I use the Java code configuration, the simplest way. Configure JPA in Application.yml

As follows:

Spring:
  JPA:
    database:mysql
    show-sql:true
    hibernate:
      ddl-auto:none

The Ddl-auto method that indicates that the JPA database is Mysql,hibernate is none.
The hairs are none. Instead of update and so on, hibernate will automatically help us build the table.
Because, since it is a sub-database table, table name is Order1,order2 and so on, hibernate can only establish an order map, is not built out of multiple sub-tables, so the table on its own build. Build Domain

Import javax.persistence.Entity;
Import Javax.persistence.Id;
Import javax.persistence.Table;

/**
 * Created by WUWF on 17/4/19.
 *
/@Entity @Table (name = "T_order") public
class Order {
    @Id
    private Long orderId;

    Private Long userId;

    Public Long Getorderid () {
        return orderId;
    }

    public void Setorderid (Long orderId) {
        This.orderid = orderId;
    }

    Public Long getUserId () {
        return userId;
    }

    public void Setuserid (Long userId) {
        This.userid = userId;
    }
}

An order table, the table named T_order, there is a primary key OrderID and UserID, this userid is not used, and later with a multi-to-one connection.
As can be seen, only on the OrderID add @id2 without adding @generatedvalue (strategy = Generationtype.auto) of the primary key generation strategy, MySQL generally use self-increment.
Why not add it. Because you can not add, you divide the table, if the primary key or self-increment, you will appear the primary key duplicates. Again, the program does not recognize the uniqueness of the data.

So this primary key needs to be created by ourselves.
and create a basic repository.

Import Com.tianyalei.domain.Order;
Import org.springframework.data.repository.CrudRepository;

/**
 * Created by WUWF on 17/4/19.
 */Public
Interface Orderrepository extends Crudrepository<order, long> {
}

configuration of the Sub-Library sub-table

Package com.tianyalei.config;
Import Com.alibaba.druid.pool.DruidDataSource;
Import Com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
Import Com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
Import Com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
Import Com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
Import Com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
Import Com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
Import Com.mysql.jdbc.Driver;
Import Org.springframework.context.annotation.Bean;

Import org.springframework.context.annotation.Configuration;
Import Javax.sql.DataSource;
Import Java.util.Arrays;
Import Java.util.HashMap;

Import Java.util.Map;
 /** * Created by WUWF on 17/4/19. */@Configuration public class Datasourceconfig {@Bean public DataSource Getdatasource () {return Buildda
    Tasource ();
   } private DataSource Builddatasource () {//Set library map     map<string, datasource> datasourcemap = new hashmap<> (2);
        Add two databases ds_0,ds_1 to map datasourcemap.put ("Ds_0", CreateDataSource ("Ds_0"));
        Datasourcemap.put ("Ds_1", CreateDataSource ("Ds_1")); Set the default DB to Ds_0, which is the default library that is specified for those that do not have a sub-list policy configured///If there is only one library, that is, if you do not need a library, the map will only have one map, only one library does not need to specify the default library, but 2 or more must specify a default library

        Otherwise, those tables that do not have a policy configured will not be able to manipulate the data datasourcerule Datasourcerule = new Datasourcerule (Datasourcemap, "ds_0"); Set up a table map, map t_order_0 and t_order_1 Two actual tables to t_order logical table//0 and 12 tables are real tables, T_order is a virtual nonexistent table, just for use. If all data is queried, select * from T_order will be able to check the tablerule ordertablerule = Tablerule.builder ("T_order") of tables 0 and 1. A Ctualtables (Arrays.aslist ("T_order_0", "t_order_1")). Datasourcerule (datasourcerule). Buil

        D (); The specific sub-database sub-table strategy, according to what rules to divide shardingrule Shardingrule = Shardingrule.builder (). Datasourcerule (Datasourceru
   Le). Tablerules (Arrays.aslist (Ordertablerule))             . Databaseshardingstrategy (New Databaseshardingstrategy ("user_id", New Modulodatabaseshardingalgorithm ()))

        . Tableshardingstrategy (New Tableshardingstrategy ("order_id", New Modulotableshardingalgorithm ())). build ();

        DataSource DataSource = Shardingdatasourcefactory.createdatasource (shardingrule);
    return dataSource; } private static DataSource CreateDataSource (final String datasourcename) {//Use Druid to connect to the database Druiddatas
        Ource result = new Druiddatasource ();
        Result.setdriverclassname (Driver.class.getName ());
        Result.seturl (String.Format ("jdbc:mysql://localhost:3306/%s", DataSourceName));
        Result.setusername ("root");
        Result.setpassword ("");
    return result;
 }
}

We do not specify the datasource of the database in the configuration file, we need to configure the DataSource in the Java code. When the general situation is not divided into libraries, only need to return the CreateDataSource method directly in the Getdatasource method, which specifies the use of Druiddatasource.
Now that we have the library, we're going to use the sharding jdbc encapsulated DataSource, which takes over the database connection.
namely DataSource DataSource = Shardingdatasourcefactory.createdatasource (shardingrule);
As you can see, the DataSource of the sharding JDBC package is primarily required to construct a shardingrule parameter.
This class is basically the construction of this rule, the comments are written in a relatively clear. Almost the process is to create a map specific strategy algorithm

In the above code, MODULODATABASESHARDINGALGORITHM and Modulotableshardingalgorithm are used respectively to specify the triage strategy for the libraries and tables.
Let's take a look at these two classes.

Import Com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
Import Com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;

Import Com.google.common.collect.Range;
Import java.util.Collection;

Import Java.util.LinkedHashSet;
 /** * Created by WUWF on 17/4/19. 
    */public class Modulodatabaseshardingalgorithm implements singlekeydatabaseshardingalgorithm<long> {@Override Public String doequalsharding (collection<string> availabletargetnames, shardingvalue<long>  Shardingvalue) {for (String each:availabletargetnames) {if (Each.endswith (Shardingvalue.getvalue ()
            % 2 + "")) {return each;
    }} throw new IllegalArgumentException (); } @Override Public collection<string> doinsharding (collection<string> availabletargetnames, ShardingV Alue<long> shardingvalue) {collection<string> result = new Linkedhashset<> (availableTargetnames.size ());
                For (Long value:shardingValue.getValues ()) {for (String tablename:availabletargetnames) {
                if (Tablename.endswith (value% 2 + "")) {Result.add (tableName);
    }}} return result;
                                                } @Override Public collection<string> dobetweensharding (collection<string> availabletargetnames, Shardingvalue<long> shardingvalue) {collection<string> re
        Sult = new Linkedhashset<> (Availabletargetnames.size ());
        Range<long> range = Shardingvalue.getvaluerange (); for (Long i = Range.lowerendpoint (), I <= range.upperendpoint (); i++) {for (String EACH:AVAILABLETARGETN
                Ames) {if (Each.endswith (i% 2 + "")) {Result.add (each);
    }}} return result; }

}

Import Com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
Import Com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;

Import Com.google.common.collect.Range;
Import java.util.Collection;

Import Java.util.LinkedHashSet;
 /** * Created by WUWF on 17/4/19.
     */Public final class Modulotableshardingalgorithm implements singlekeytableshardingalgorithm<long> {/** 
     * SELECT * from T_order to t_order where order_id = 11 *└──select * from t_order_1 where order_id = 11 * SELECT * from T_order to T_order where order_id = 44 *└──select * from T_order_0 where Order_i d = */public String doequalsharding (final collection<string> tablenames, final Shardingvalue<long&gt ; Shardingvalue) {for (String each:tablenames) {if (Each.endswith (Shardingvalue.getvalue ()% 2 + "")
            ) {return each; }} throw new IllegalargumentexceptIon (); }/** * Select * from T_order to T_order where order_id in (11,44) *├──select * from T_order _0 where order_id in (11,44) *└──select * from t_order_1 WHERE order_id in (11,44) * SELECT * FROM T_order from T_order where order_id in (11,13,15) *└──select * from t_order_1 where order_id in (11,13,15 ) * SELECT * from T_order to T_order where order_id in (22,24,26) *└──select * from T_order_0 wher E order_id in (22,24,26) */public collection<string> doinsharding (final collection<string> TableName S, final shardingvalue<long> shardingvalue) {collection<string> result = new Linkedhashset<> (ta
        Blenames.size ()); For (Long value:shardingValue.getValues ()) {for (String tablename:tablenames) {if (table
                Name.endswith (value% 2 + "")) {Result.add (tableName);
 }
            }       } return result; }/** * Select * from T_order to T_order where order_id between and 20 *├──select * from
     T_order_0 where order_id between and 20 *└──select * from t_order_1 where order_id between and 20 */Public collection<string> dobetweensharding (final collection<string> tablenames, final ShardingValu
        E<long> shardingvalue) {collection<string> result = new Linkedhashset<> (Tablenames.size ());
        Range<long> range = Shardingvalue.getvaluerange ();
                for (Long i = Range.lowerendpoint (), I <= range.upperendpoint (); i++) {for (String each:tablenames) {
                if (Each.endswith (i% 2 + "")) {Result.add (each);
    }}} return result; }
}

The main thing to see is the Doequalsharding method (such as SELECT * from T_order where user_id = 11 is equal), and Availabletargetnames is all the library names (Ds_0,ds_1), Shardingvalue is the user_id specified in the Datasourceconfig, the code is if the user_id is even even to the DS_0 database, the others put Ds_1 database. While the other two methods, Doin and Dobetween are used in such as where user_id in (1,23,7) and where user_id between (1, 6).
The policy of table is the same as the strategy of DB, and the algorithm can be determined by itself.
The above two are implementations of the Singlekeyshardingalgorithm, which is a single-row policy, you can also use multi-column strategy, such as user_id and order_id at the same time meet a certain condition, which table.
New Tableshardingstrategy (Arrays.aslist ("order_id", "Order_type", "order_date"), New Multikeyshardingalgorithm ()))

All of these are based on one or more columns to determine the sub-database of the policy, the official also provides a routing policy is not based on the column, refer to forced routing. http://dangdangdotcom.github.io/sharding-jdbc/02-guide/hint-sharding-value/ Creating DB and Tables

Goal:
Db0
├──t_order_0 user_id for even order_id
├──t_order_1 user_id for even order_id is odd
Db1
├──t_order_0 user_id is an even number for an odd order_id
├──t_order_1 user_id is an odd order_id
Start by creating two Db,ds_0 and Ds_1. The tables T_order_0 and t_order_1 are then built in each library respectively.
Build a slogan: DROP TABLE IF EXISTS t_order_0;
CREATE TABLE T_order_0 (
order_id bigint () not NULL,
user_id bigint () not NULL,
PRIMARY KEY (order_id)
) Engine=innodb DEFAULT Charset=utf8 Collate=utf8_bin;
Change the table name to 1, and then execute it in two different libraries. Remember not to tick auto_increment.
Let's write a controller to try to add and query data.

Import Com.tianyalei.domain.Order;
Import com.tianyalei.repository.OrderRepository;
Import org.springframework.beans.factory.annotation.Autowired;
Import org.springframework.web.bind.annotation.RequestMapping;