sharding-JDBC 实现分库

中间件

需求

按照业务线Id分库，每个业务线一个库。

sql

-- 建立112业务线库
create database sharding_112 ;
-- 在112业务库里建立 t_order表
CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `business_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
-- 建立113业务线库
create database sharding_113 ;
-- 在113业务库里建立 t_order表
CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `business_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

pom.xml（使用sharding-jdbc 2.0）

<!-- https://mvnrepository.com/artifact/io.shardingjdbc/sharding-jdbc-core -->
<dependency>
    <groupId>io.shardingjdbc</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>2.0.0.M2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.dangdang/sharding-jdbc-config-spring -->
<dependency>
    <groupId>com.dangdang</groupId>
    <artifactId>sharding-jdbc-config-spring</artifactId>
    <version>1.4.0</version>
</dependency>
<dependency>
    <groupId>io.shardingjdbc</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>2.0.0.M2</version>
</dependency>

Spring相关配置

<bean id="ds_112" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
      destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="${jdbc.url_112}"></property>
    <property name="username" value="${jdbc.username_112}"></property>
    <property name="password" value="${jdbc.password_112}"></property>
    <property name="maxActive" value="100"/>
    <property name="initialSize" value="10"/>
    <property name="maxWait" value="60000"/>
    <property name="minIdle" value="5"/>
</bean>
<bean id="ds_113" class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
      destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="${jdbc.url_113}"></property>
    <property name="username" value="${jdbc.username_113}"></property>
    <property name="password" value="${jdbc.password_113}"></property>
    <property name="maxActive" value="100"/>
    <property name="initialSize" value="10"/>
    <property name="maxWait" value="60000"/>
    <property name="minIdle" value="5"/>
</bean>
<sharding:standard-strategy id="databaseShardingStrategy" sharding-column="business_id" precise-algorithm-class="com.boothsun.util.sharding.PreciseModuloDatabaseShardingAlgorithm" />
<sharding:data-source id="shardingDataSource">
    <sharding:sharding-rule data-source-names="ds_112,ds_113">
        <sharding:table-rules>
            <sharding:table-rule logic-table="t_order" database-strategy-ref="databaseShardingStrategy"  />
        </sharding:table-rules>
    </sharding:sharding-rule>
</sharding:data-source>

sharding-jdbc 相关标签含义 参见官方文档：配置手册

官方demo：github

分库规则类

/**
 * 精确匹配
 */
public final class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
    private static  final Map<Integer,String> dataSourceMap = new HashMap<>();
    static {
        dataSourceMap.put(112,"ds_112");
        dataSourceMap.put(113,"ds_113");
    }
    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) {
        return dataSourceMap.get(shardingValue.getValue());
    }
}

单测类

@Autowired
OrderMapper orderMapper ;
/**
 * 测试插入
 * @throws Exception
 */
@Test
public void insertSelective() throws Exception {
    Order order = new Order();
    order.setOrderId(1231);
    order.setUserId(222);
    order.setBusinessId(113);
    Boolean result = orderMapper.insert(order) > 0;
    System.out.println(result?"插入成功":"插入失败");
}
/**
 * 测试 in 的查询操作
 * @throws Exception
 */
@Test
public void selectByExample2() throws Exception {
    List<Integer> values = new ArrayList<>();
    values.add(112);
    values.add(113);
    OrderExample example = new OrderExample() ;
    example.createCriteria().andBusinessIdIn(values);
    List<Order> orderList = orderMapper.selectByExample(example) ;
    System.out.println(JSONObject.toJSONString(orderList));
}
/**
 * 测试between的查询操作
 * @throws Exception
 */
@Test
public void selectByExample3() throws Exception {
    OrderExample example = new OrderExample() ;
    example.createCriteria().andBusinessIdBetween(112,113);
    List<Order> orderList = orderMapper.selectByExample(example) ;
    System.out.println(JSONObject.toJSONString(orderList));
}