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

Recently busy project has been a long time no blog, the first 2 articles I gave you to build the foundation Springmvc+mybatis MAVEN Project, this simple framework has been able to deal with the general small projects. But there are many complex scenarios in our actual project, such as how to ensure performance when the data is very large. Today I will introduce to you the database sub-list optimization, this paper introduces MyBatis combined with Dangdang SHARDING-JDBC sub-database sub-table technology (principle here does not introduce)

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"?><Beansxmlns= "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/beans/sprin G-beans.xsd Http://www.springframework.org/schema/tx Http://www.springfram                         Ework.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/schem A/ddframe/rdb http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd ">                    <Rdb:strategyID= "Tableshardingstrategy"Sharding-columns= "user_id"Algorithm-class= "Com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm"/>        <Rdb:data-sourceID= "Shardingdatasource">        <Rdb:sharding-ruledata-sources= "DataSource">            <Rdb:table-rules>                <Rdb:table-rulelogic-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-rulelogic-table= "Member_details"Actual-tables= "member_details_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}"Table-strategy= "Tableshardingstrategy"/>            </Rdb:table-rules>        </Rdb:sharding-rule>    </Rdb:data-source>        <BeanID= "TransactionManager"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager">        < Propertyname= "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 "/> Configure the table rule sharding-columns : Sub-table rules

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 *@authorZhangwentao **/ Public classMembersinglekeytableshardingalgorithmImplementsSinglekeytableshardingalgorithm<long> {        /*** SQL between rules*/     PublicCollection<string> dobetweensharding (collection<string> tablenames, ShardingValue<Long>shardingvalue) {Collection<String> result =NewLinkedhashset<string>(Tablenames.size ()); Range<Long> range = (range<long>) Shardingvalue.getvaluerange ();  for(Longi = 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); }            }        }        returnresult; }    /*** sql = = Rule*/     PublicString 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)) {returnEach ; }        }        Throw Newillegalargumentexception (); }    /*** SQL in Rule*/     PublicCollection<string> doinsharding (collection<string> tablenames, shardingvalue<long>shardingvalue) {Collection<String> result =NewLinkedhashset<string>(Tablenames.size ());  for(Longvalue:shardingValue.getValues ()) {Long Modvalue= value% 100; String Modstr= Modvalue < 10? "0" +modValue:modValue.toString ();  for(String tablename:tablenames) {if(Tablename.endswith (modstr)) {Result.add (tableName); }            }        }        returnresult; }    }

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} limi T 1  </ select  >  

  private static final String Service_provider_xml = "/spring/member-service.xml";    private static final String Bean_name = "Idcacheservice";        Private Classpathxmlapplicationcontext context = null;    Idcacheserviceimpl bean = null;    Idcachedao Idcachedao;        @Before public    Void before () {        context= new Classpathxmlapplicationcontext (                new string[] {Service_ Provider_xml});       Idcachedao=context.getbean ("Idcachedao", Idcachedao.class);    }        @Test public    void Getallcreditactiontest () {     //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.)

Summary: This time to share the SHARDING-JDBC configuration is to solve the large data volume of the Sub-Library sub-table architecture, the next, I will introduce the split business needs of the Duboo+zookeeper configuration (distributed), welcome attention!

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