MySQL多数据源笔记5-ShardingJDBC实战

jdbc_url_1=jdbc:mysql://localhost:3306/db_1?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc_url_2=jdbc:mysql://localhost:3307/db_2?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc_url_3=jdbc:mysql://localhost:3308/db_3?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc_username=root
jdbc_password=root

<?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:aop="http://www.springframework.org/schema/aop"
       xmlns:sharding="http://shardingjdbc.io/schema/shardingjdbc/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-4.0.xsd
    http://www.springframework.org/schema/tx
    http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
    http://shardingjdbc.io/schema/shardingjdbc/sharding
    http://shardingjdbc.io/schema/shardingjdbc/sharding/sharding.xsd">


    <!--扫描注解生成bean-->
    <context:annotation-config/>
    <!--包扫描-->
    <context:component-scan base-package="com.coder520"/>

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="mapperLocations" value="classpath:com/coder520/**/**.xml"/>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.coder520.*.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

    <!--声明事务管理 采用注解方式-->
    <tx:annotation-driven transaction-manager="transactionManager"/>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="shardingDataSource"/>
    </bean>

    <!--开启切面代理-->
    <aop:aspectj-autoproxy/>
    

    <!--主数据库设置-->
    <bean id="ds_0" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close" init-method="init">
        <property name="url" value="${jdbc_url_1}"/>
        <property name="username" value="${jdbc_username}"/>
        <property name="password" value="${jdbc_password}"/>
    </bean>
    <!--从数据库设置-->
    <bean id="ds_1" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close" init-method="init">
        <property name="url" value="${jdbc_url_2}"/>
        <property name="username" value="${jdbc_username}"/>
        <property name="password" value="${jdbc_password}"/>
    </bean>
    <!--从数据库设置-->
    <bean id="ds_2" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close" init-method="init">
        <property name="url" value="${jdbc_url_3}"/>
        <property name="username" value="${jdbc_username}"/>
        <property name="password" value="${jdbc_password}"/>
    </bean>

    <!--分库策略,sharding-column这里根据user_id(这列属性不能是String,只能是整型)分库，precise-algorithm-class是分库算法，-->
    <sharding:standard-strategy id="databaseShardingStrategy" sharding-column="user_id"
                                precise-algorithm-class="com.coder520.sharding.PreciseModuloDatabaseShardingAlgorithm"/>
    <!--分表策略，sharding-column这里根据order_id(这列属性不能是String,只能是整型)分表，precise-algorithm-class是分表算法-->
    <sharding:standard-strategy id="tableShardingStrategy" sharding-column="order_id"
                                precise-algorithm-class="com.coder520.sharding.PreciseModuloTableShardingAlgorithm"/>

    <!--自己写shardingDataSource嵌入Spring里面-->
    <sharding:data-source id="shardingDataSource">
        <!--告诉shardingjdbc你所有库的名字,这里为什么要从0开始命名呢？因为shardingjdbc的分库分表策略是求余来分配的，比如user_id求余3有0，1，2，所以只能这样命名-->
        <sharding:sharding-rule data-source-names="ds_0,ds_1,ds_2">
            <sharding:table-rules>
                <!--ds_${0..2}.t_order_${0..2}，这里用到了In line表达式${0..2}，这里是用了笛卡尔乘积运算,比如ds_0.t_order_0 ds_0.t_order_1 ds_0.t_order_2-->
                <!--logic-table是逻辑表 actual-data-nodes是实际的数据源节点-->
                <!--generate-key-column生成主键列，这是一个全局序列号，是唯一的，这是shardingjdbc自动会帮我们生成的。避免我们自己自增造成id重复-->
                <sharding:table-rule logic-table="t_order" actual-data-nodes="ds_${0..2}.t_order_${0..2}"
                                     database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy"
                                        generate-key-column="order_id"/>
                <sharding:table-rule logic-table="t_order_item" actual-data-nodes="ds_${0..2}.t_order_item_${0..2}"
                                     database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy"
                                        generate-key-column="order_item_id"/>
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
    
</beans>

package com.coder520.sharding;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

public final class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

//Collection<String> availableTargetNames是我们在配置文件配置的数据源  PreciseShardingValue<Long> shardingValue是传进来配置文件逻辑表的逻辑表名和传进来user_id列名和值

@Override 
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Integer> shardingValue) { for (String each : availableTargetNames) {
　　　　　　　　//这里求余3是因为我这里在spring-cfg.xml中配置的三个数据库,这里的意思是将数据分到哪一个库中，比如user_id为51,则分配到第一个数据库中，其他数据库中没用信息
　　　　　　　　if (each.endsWith(shardingValue.getValue() % 3 + "")) { 
　　　　　　　　　　　　return each;
　　　　　　　　} 
　　　　　} 
　　　　thrownew UnsupportedOperationException(); 
　　}
}

package com.coder520.sharding;

import io.shardingjdbc.core.api.algorithm.sharding.PreciseShardingValue;
import io.shardingjdbc.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import java.util.Collection;

public final class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    
　　//Collection<String> availableTargetNames是我们在配置文件配置的数据源  PreciseShardingValue<Long> shardingValue是传进来配置文件逻辑表的逻辑表名和传进来user_id列名和值

@Override 
public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<Long> shardingValue) { for (String each : availableTargetNames) {
　　　　　　　　//这里求余3是因为我这里有三个库，分别是我们在spring-cfg.xml配置的
            if (each.endsWith(shardingValue.getValue() % 3 + "")) {
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
}

package com.coder520.order.controller;

import com.coder520.order.service.DemoService;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;

/**
 * Created by cong on 2018/3/19.
 */
public class OrderController {

    @Resource
    private DemoService demoService;

    @RequestMapping("/test")
    public void test(){
        demoService.demo();
    }

}

package com.coder520.order.service;


import com.coder520.order.dao.OrderItemRepository;
import com.coder520.order.dao.OrderRepository;
import com.coder520.order.entity.Order;
import com.coder520.order.entity.OrderItem;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class DemoService {
    
    @Resource
    private OrderRepository orderRepository;
    
    @Resource
    private OrderItemRepository orderItemRepository;
    
    public void demo() {
        orderRepository.createIfNotExistsTable();
        orderItemRepository.createIfNotExistsTable();
    }
}

public final class Order {
    
    private long orderId;
    
    private int userId;
    
    private String status;
    
    public long getOrderId() {
        return orderId;
    }
    
    public void setOrderId(final long orderId) {
        this.orderId = orderId;
    }
    
    public int getUserId() {
        return userId;
    }
    
    public void setUserId(final int userId) {
        this.userId = userId;
    }
    
    public String getStatus() {
        return status;
    }
    
    public void setStatus(final String status) {
        this.status = status;
    }
    
    @Override
    public String toString() {
        return String.format("order_id: %s, user_id: %s, status: %s", orderId, userId, status);
    }
}

package com.coder520.order.entity;

public final class OrderItem {
    
    private long orderItemId;
    
    private long orderId;
    
    private int userId;
    
    private String status;
    
    public long getOrderItemId() {
        return orderItemId;
    }
    
    public void setOrderItemId(final long orderItemId) {
        this.orderItemId = orderItemId;
    }
    
    public long getOrderId() {
        return orderId;
    }
    
    public void setOrderId(final long orderId) {
        this.orderId = orderId;
    }
    
    public int getUserId() {
        return userId;
    }
    
    public void setUserId(final int userId) {
        this.userId = userId;
    }
    
    public String getStatus() {
        return status;
    }
    
    public void setStatus(final String status) {
        this.status = status;
    }
    
    @Override
    public String toString() {
        return String.format("order_item_id:%s, order_id: %s, user_id: %s, status: %s", orderItemId, orderId, userId, status);
    }
}

package com.coder520.order.service;


import com.coder520.order.dao.OrderItemRepository;
import com.coder520.order.dao.OrderRepository;
import com.coder520.order.entity.Order;
import com.coder520.order.entity.OrderItem;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class DemoService {
    
    @Resource
    private OrderRepository orderRepository;
    
    @Resource
    private OrderItemRepository orderItemRepository;
    
    public void demo() {

        List<Long> orderIds = new ArrayList<>(10);
        System.out.println("1.Insert--------------");
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setUserId(51);
            order.setStatus("INSERT_TEST");
            orderRepository.insert(order);
            long orderId = order.getOrderId();
            orderIds.add(orderId);
            
            OrderItem item = new OrderItem();
            item.setOrderId(orderId);
            item.setUserId(51);
            item.setStatus("INSERT_TEST");
            orderItemRepository.insert(item);
        }
        System.out.println(orderItemRepository.selectAll());
        System.out.println("2.Delete--------------");
        for (Long each : orderIds) {
            orderRepository.delete(each);
            orderItemRepository.delete(each);
        }
        System.out.println(orderItemRepository.selectAll());
        orderItemRepository.dropTable();
        orderRepository.dropTable();
    }
}