Sharding-JDBC实战

 

1、快速入门

1.1、分库分表数据库架构

以订单中心为例，无论库还是表，都是以卖家ID或者买家ID去分片。假设有两个数据库 order_center_0和order_center_1，每个库有两张表t_order_0，t_order_1以及t_order_item_0和t_order_item_1，于是我们得到下面的数据库架构：

1.2、Sharding-JDBC的配置

1.2.1、maven依赖

<dependency>

<groupId>com.dangdang</groupId>

<artifactId>sharding-jdbc-core</artifactId>

<version>1.4.1</version>

</dependency>

 

<dependency>

<groupId>com.dangdang</groupId>

<artifactId>sharding-jdbc-config-common</artifactId>

<version>1.4.1</version>

</dependency>

 

<dependency>

<groupId>com.dangdang</groupId>

<artifactId>sharding-jdbc-config-spring</artifactId>

<version>1.4.1</version>

</dependency>

 

<dependency>

<groupId>com.dangdang</groupId>

<artifactId>sharding-self-id-generator</artifactId>

<version>1.4.1</version>

</dependency>

 

<dependency>

<groupId>org.codehaus.groovy</groupId>

<artifactId>groovy-all</artifactId>

<version>2.4.7</version>

</dependency>

 

<!-- druid最好用1.0.12，因为当当官网也是基于这个开发的，最新版本的druid对某些sql的解析有问题-->

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid</artifactId>

<version>1.0.12</version>

</dependency>

 

1.2.2、spring配置      

    

<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:rdb="http://www.dangdang.com/schema/ddframe/rdb" 

    xsi:schemaLocation="http://www.springframework.org/schema/beans 

                        http://www.springframework.org/schema/beans/spring-beans.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 ">

  <context:property-placeholder location="classpath:setting-test.properties" ignore-unresolvable="true" />

 <bean id="order_center_0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 

        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

        <property name="url" value="jdbc:mysql://120.26.123.50:3306/order_center_0"/>

        <property name="username" value="developer"/>

        <property name="password" value="developer_meiyou_01"/>

    </bean>

    <bean id="order_center_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

        <property name="url" value="jdbc:mysql://120.26.123.50:3306/order_center_1"/>

        <property name="username" value="developer"/>

        <property name="password" value="developer_meiyou_01"/>

    </bean> 

    <rdb:strategy id="orderTableStrategy" sharding-columns="order_id" algorithm-expression="t_order_${order_id.longValue() % 2}"/> 

    <rdb:strategy id="orderItemTableStrategy" sharding-columns="order_id" algorithm-expression="t_order_item_${order_id.longValue() % 2}"/> 

    <rdb:strategy id="databaseStrategy" sharding-columns="order_id&#