SHARDING-JDBC combined with MyBatis to realize the function of sub-database table

First, the required dependencies are introduced in the Pom file

<dependency>            <groupId>com.dangdang</groupId>            <artifactid>sharding-jdbc-core </artifactId>            <version>1.4.2</version>        </dependency>        <dependency>            <groupId>com.dangdang</groupId>            <artifactid>sharding-jdbc-config-spring</ artifactid>            <version>1.4.0</version>        </dependency>

Second, create a new Sharding-jdbc.xml file, to achieve the configuration of the sub-database sub-table

<?xml version= "1.0" encoding= "UTF-8"? ><beans xmlns= "Http://www.springframework.org/schema/beans" xmlns:     Xsi= "Http://www.w3.org/2001/XMLSchema-instance" xmlns:context= "Http://www.springframework.org/schema/context" xmlns:tx= "Http://www.springframework.org/schema/tx" xmlns:rdb= "Http://www.dangdang.com/schema/ddframe/rdb" xsi: schemalocation= "Http://www.springframework.org/schema/beans http://www.springframework.org/schema/b Eans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.                         Springframework.org/schema/tx/spring-tx.xsd Http://www.springframework.org/schema/context Http://www.springframework.org/schema/context/spring-context.xsd Http://www.dangdang                    . Com/schema/ddframe/rdb Http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd "> <rdb:strategy id= "TableshardingstrateGy "sharding-columns=" user_id "algorithm-class=" Com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm "/> <rdb:data-source id="                Shardingdatasource "> <rdb:sharding-rule data-sources=" DataSource "> <rdb:table-rules>  <rdb:table-rule logic-table= "Member_index" actual-tables= "member_index_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}" table-strategy= "Tableshardingstrategy"/> <rdb:table-rule logic-table= "Member_details" actual-tables= "Member_details_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}" table-strategy= "Tableshardingstrategy"/> </rdb:tabl e-rules> </rdb:sharding-rule> </rdb:data-source> <bean id= "TransactionManager" class= "O Rg.springframework.jdbc.datasource.DataSourceTransactionManager "> <property name=" DataSource "ref=" Shardingdatasource "/> </bean></beans>

Here I briefly introduce the meanings of some properties,

<rdb:strategy id= "Tableshardingstrategy" sharding-columns= "user_id" algorithm-class= " Com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm "/> Configuration table Rule sharding-columns: Table rule

Dependent name (according to USER_ID), Algorithm-class: implementation class for Table rules

<rdb:sharding-rule data-sources= "DataSource" > here fill in the associated data source (multiple data sources separated by commas),

<rdb:table-rule logic-table= "Member_index" actual-tables= "member_index_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}" table-strategy= "Tableshardingstrategy"/> logic-table: Logical table name (replaced in MyBatis table name) Actual-tables:

The actual table name of the database, where inline expressions are supported, such as: member_index_tbl_${0..2} will parse into Member_index_tbl_0,member_index_tbl_1,member_index_tbl_ 2;MEMBER_INDEX_TBL_${[A,B,C]} will be parsed into

Member_index_tbl_a,member_index_tbl_b and Member_index_tbl_c, when used in conjunction with two expressions, will take the Cartesian product way: member_index_tbl_${[a,b]}${ 0..2} resolves to member_index_tbl_a0,member_index_tbl_a1 member_index_tbl_a2,member_index_tbl _b0,member_index_tbl_b1,member_index_tbl_b2;table-strategy: The table rule defined previously;

Third, the configuration to change the file, we need to modify the previous spring-datasource of several places, Sqlsessionfactory and TransactionManager The original associated datasource modified to Shardingdatasource (this step is to host the data source to sharding to manage)

　　

Four, the realization of the Sub-table (sub-Library) logic, our table logic class needs to implement the Singlekeytableshardingalgorithm interface three methods dobetweensharding, doequalsharding, doinsharding

/** * Sub-table logic * @author Zhangwentao * */public class Membersinglekeytableshardingalgorithm implements Singlekeytablesharding algorithm<long> {/** * sql between rule */public collection<string> dobetweensharding (Collec Tion<string> tablenames, shardingvalue<long> shardingvalue) {collection<string> result = new Lin        Kedhashset<string> (Tablenames.size ());        Range<long> range = (range<long>) shardingvalue.getvaluerange ();            for (Long i = range.lowerendpoint (); I <= range.upperendpoint (); i++) {Long modvalue = i% 100; String modstr = Modvalue < 10?            "0" + modValue:modValue.toString ();                for (String each:tablenames) {if (Each.endswith (MODSTR)) {result.add (each);    }}} return result; }/** * sql = = Rule */public String doequalsharding (collection<string> tablenames, Shardingvalue<Long> shardingvalue) {Long modvalue = shardingvalue.getvalue ()% 100; String modstr = Modvalue < 10?        "0" + modValue:modValue.toString ();            for (String each:tablenames) {if (Each.endswith (MODSTR)) {return each;    }} throw new IllegalArgumentException (); }/** * SQL in Rule */public collection<string> doinsharding (collection<string> tablenames, Shard Ingvalue<long> shardingvalue) {collection<string> result = new Linkedhashset<string> (tableNames.        Size ());            For (Long Value:shardingValue.getValues ()) {long Modvalue = value% 100; String modstr = Modvalue < 10?            "0" + modValue:modValue.toString (); for (String tablename:tablenames) {if (Tablename.endswith (MODSTR)) {Result.add (table                Name);    }}} return result; }    }

Five or more four steps, we have completed the construction of SHARDING-JDBC, we can write a test demo to check our results

<select id= "Getdetailsbyid" resulttype= "Com.meiren.member.dataobject.MemberDetailsDO"        parametertype= " Java.lang.Long ">        select user_id userId, Qq,email from Member_details where     user_id =#{userid} limit 1    </select>

123456789101112131415161718192021

privatestaticfinalString SERVICE_PROVIDER_XML = "/spring/member-service.xml";      privatestaticfinalString BEAN_NAME = "idcacheService";            privateClassPathXmlApplicationContext context = null;      IdcacheServiceImpl bean = null;      IdcacheDao idcacheDao;            @Before      publicvoidbefore() {          context= new ClassPathXmlApplicationContext(                  newString[] {SERVICE_PROVIDER_XML});         idcacheDao=context.getBean("IdcacheDao", IdcacheDao.class);      }             @Test      publicvoidgetAllCreditActionTest() {       // int id = bean.insertIdcache();          Long s=100l;        MemberDetailsDO memberDetailsDO=idcacheDao.getDetailsById(s);        System.out.println("QQ---------------------"+memberDetailsDO.getQq());      }

Print SQL statement, output: QQ-------------------------------------100, prove successful!

Note: The construction process, I have encountered a small pit, is the execution of the time will be error:, the official document is a solution: the introduction of <context:property-placeholder location= "Classpath:/member_ Service.properties "ignore-unresolvable=" true "/>, when this line of code is introduced, it is necessary to remove the bean that manages the configuration file here, in other words, That is, the spring container allows only a maximum of one propertyplaceholderconfigurer (or <context:property-placeholder/>) to be defined, and the rest will be ignored by spring (I was doing it for a long time.)

SHARDING-JDBC combined with MyBatis to realize the function of sub-database table