shardingJDBC分库分表

ShardingJDBC基础环境

pom文件配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.datang</groupId>
    <artifactId>shardingjdbc1</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

    </dependencies>

</project>

View Code

基础数据

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `sd`;

/*Table structure for table `t_order` */

DROP TABLE IF EXISTS `t_order`;

CREATE TABLE `t_order` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order` */

insert  into `t_order`(`user_id`,`order_id`,`product_name`) values (1,1,'芒果1'),(2,2,'芒果2'),(3,3,'芒果3'),(4,4,'芒果4'),(5,5,'芒果5'),(6,6,'芒果6'),(7,7,'芒果7'),(8,8,'芒果8'),(9,9,'芒果9'),(10,10,'芒果10');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

不分库，不分表

下边代码片段只是引入了ShardingJDBC但未分库分表。一个简单的查询。

package sjfp;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

//数据分片,不分库，不分表
public class SJFPDemo1 {
    public static void main(String[] args) throws Exception {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        DruidDataSource druidDataSource1 = new DruidDataSource();
        druidDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource1.setUrl("jdbc:mysql://39.105.59.232:3306/sd");
        druidDataSource1.setUsername("root");
        druidDataSource1.setPassword("1");
        dataSourceMap.put("sd", druidDataSource1);


        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "sd.t_order");

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int userId = resultSet.getInt(1);
            int orderId = resultSet.getInt(2);
            String productName = resultSet.getString(3);
            System.out.println(userId + "----" + orderId + "----" + productName);
        }
    }
}

View Code

不分库，只分表

使用ShardingJDBC分库分表，并不能自动的帮我们创建数据库或者表，也就是说对于生产环境已经产生的数据需要我们手动自己迁移数据。

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `sd`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order0` */

insert  into `t_order0`(`user_id`,`order_id`,`product_name`) values (1,1,'芒果1'),(2,2,'芒果2'),(3,3,'芒果3'),(4,4,'芒果4'),(5,5,'芒果5'),(6,6,'芒果6'),(7,7,'芒果7'),(8,8,'芒果8'),(9,9,'芒果9'),(10,10,'芒果10');

/*Table structure for table `t_order1` */

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order1` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

现在t_order被分成了两个表，但是t_order0的数据还是之前的我们暂且不修改。一下代码还是一个简单的查询。

package sjfp;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

//数据分片,分表
public class SJFPDemo2 {
    public static void main(String[] args) throws Exception {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        DruidDataSource druidDataSource1 = new DruidDataSource();
        druidDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource1.setUrl("jdbc:mysql://39.105.59.232:3306/sd");
        druidDataSource1.setUsername("root");
        druidDataSource1.setPassword("1");
         dataSourceMap.put("sd", druidDataSource1);


        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "sd.t_order${0..1}");

        // 配置分表策略
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int userId = resultSet.getInt(1);
            int orderId = resultSet.getInt(2);
            String productName = resultSet.getString(3);
            System.out.println(userId + "----" + orderId + "----" + productName);
        }
    }
}

View Code

需要注意的是TableRuleConfiguration配置了t_order表的分表规则，t_order被拆成了多少个小表。setTableShardingStrategyConfig则配置了新增的数据使用什么规则放到不同的小表中。按照order_id%2来区分数据放到哪个表中，order_id是int类型当然不是0就是1。

下边我们对数据进行新增，看看效果是否和配置规则一样。

package sjfp;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

//数据分片,分表
public class SJFPDemo3 {
    public static void main(String[] args) throws Exception {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        DruidDataSource druidDataSource1 = new DruidDataSource();
        druidDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource1.setUrl("jdbc:mysql://39.105.59.232:3306/sd");
        druidDataSource1.setUsername("root");
        druidDataSource1.setPassword("1");
         dataSourceMap.put("sd", druidDataSource1);


        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "sd.t_order${0..1}");

        // 配置分表策略
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement1 = connection.prepareStatement("insert into t_order (order_id,product_name) values(11,'芒果11')");
        preparedStatement1.execute();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int userId = resultSet.getInt(1);
            int orderId = resultSet.getInt(2);
            String productName = resultSet.getString(3);
            System.out.println(userId + "----" + orderId + "----" + productName);
        }
    }
}

View Code

数据是成功的插入了，和预计的一样插入到了t_order1表中，但有一点意料之外但是又必然的结果，在t_order1中的插入的数据id是1，也就是说自增id这里并没有跨表自增。所以我们需要注意，当分库分表时，你的唯一id是否需要调整。

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `sd`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order0` */

insert  into `t_order0`(`user_id`,`order_id`,`product_name`) values (1,1,'芒果1'),(2,2,'芒果2'),(3,3,'芒果3'),(4,4,'芒果4'),(5,5,'芒果5'),(6,6,'芒果6'),(7,7,'芒果7'),(8,8,'芒果8'),(9,9,'芒果9'),(10,10,'芒果10');

/*Table structure for table `t_order1` */

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order1` */

insert  into `t_order1`(`user_id`,`order_id`,`product_name`) values (1,11,'芒果11');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

接下来我们来测试删除，既然新增的数据有id重复的，那么我们看看删除时，会不会把两个表中的id相同的数据都删除掉。

package sjfp;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

//数据分片,分表
public class SJFPDemo4 {
    public static void main(String[] args) throws Exception {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        DruidDataSource druidDataSource1 = new DruidDataSource();
        druidDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource1.setUrl("jdbc:mysql://39.105.59.232:3306/sd");
        druidDataSource1.setUsername("root");
        druidDataSource1.setPassword("1");
         dataSourceMap.put("sd", druidDataSource1);


        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "sd.t_order${0..1}");

        // 配置分表策略
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        Connection connection = dataSource.getConnection();

        PreparedStatement preparedStatement1 = connection.prepareStatement("delete from t_order where user_id=1");
        preparedStatement1.execute();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int userId = resultSet.getInt(1);
            int orderId = resultSet.getInt(2);
            String productName = resultSet.getString(3);
            System.out.println(userId + "----" + orderId + "----" + productName);
        }
    }
}

View Code

下面是执行删除后的数据。

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `sd`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order0` */

insert  into `t_order0`(`user_id`,`order_id`,`product_name`) values (2,2,'芒果2'),(3,3,'芒果3'),(4,4,'芒果4'),(5,5,'芒果5'),(6,6,'芒果6'),(7,7,'芒果7'),(8,8,'芒果8'),(9,9,'芒果9'),(10,10,'芒果10');

/*Table structure for table `t_order1` */

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order1` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

分库，分表

分库又分表，证明我们对数据的操作来自于多个数据库的多个表。首先我们先创建空的数据结构。以下是两个数据库，没个数据库中有两个order表

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd0
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd0` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `sd0`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/*Data for the table `t_order0` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd0
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `sd1`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/*Data for the table `t_order0` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

对于分库分表来说，首先我们需要指定两个数据源，因为有两个数据库。并且我们要指定分库分表的策略，一条数据的插入到底是根据什么规则插入到哪个库的哪张表中。

package sjfp;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

//数据分片,不分库，不分表
public class SJFPDemo5 {
    public static void main(String[] args) throws Exception {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();

        DruidDataSource druidDataSource1 = new DruidDataSource();
        druidDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource1.setUrl("jdbc:mysql://39.105.59.232:3306/sd0");
        druidDataSource1.setUsername("root");
        druidDataSource1.setPassword("1");
        dataSourceMap.put("sd0", druidDataSource1);

        DruidDataSource druidDataSource2 = new DruidDataSource();
        druidDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource2.setUrl("jdbc:mysql://39.105.59.232:3306/sd1");
        druidDataSource2.setUsername("root");
        druidDataSource2.setPassword("1");
        dataSourceMap.put("sd1", druidDataSource2);


        // 配置Order表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "sd${0..1}.t_order${0..1}");

        // 配置分库 + 分表策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "sd${user_id % 2}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

        Connection connection = dataSource.getConnection();

        for (int i = 1; i < 20; i++) {
            PreparedStatement preparedStatement = connection.prepareStatement("insert into t_order (user_id,order_id,product_name) values (" + i + "," + i + ",'芒果" + i + "')");
            preparedStatement.execute();
        }

        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int userId = resultSet.getInt(1);
            int orderId = resultSet.getInt(2);
            String productName = resultSet.getString(3);
            System.out.println(userId + "----" + orderId + "----" + productName);
        }
    }
}

View Code

从结果来看，这个结果将数据插入到sd0中的t_order0表和sd1中的t_order1表中，好吧我承认这个规则是从官网抄的。user_id%2为0的插入sd0库，所以sd0中只可能有双数id的，sd1中只能有单数。接下来就该分表了，对于order_id%2为0的数据插入t_order0中，不为0的插入t_order1中，也就是说。sd0中只会是t_order0中有数据，而sd1中只会有t_order1中有数据。

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd0
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd0` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `sd0`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order0` */

insert  into `t_order0`(`user_id`,`order_id`,`product_name`) values (2,2,'芒果2'),(4,4,'芒果4'),(6,6,'芒果6'),(8,8,'芒果8'),(10,10,'芒果10'),(12,12,'芒果12'),(14,14,'芒果14'),(16,16,'芒果16'),(18,18,'芒果18');

/*Table structure for table `t_order1` */

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order1` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code

/*
SQLyog v10.2 
MySQL - 5.7.24 : Database - sd1
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sd1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `sd1`;

/*Table structure for table `t_order0` */

DROP TABLE IF EXISTS `t_order0`;

CREATE TABLE `t_order0` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order0` */

/*Table structure for table `t_order1` */

DROP TABLE IF EXISTS `t_order1`;

CREATE TABLE `t_order1` (
  `user_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_name` varchar(10) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*Data for the table `t_order1` */

insert  into `t_order1`(`user_id`,`order_id`,`product_name`) values (1,1,'芒果1'),(3,3,'芒果3'),(5,5,'芒果5'),(7,7,'芒果7'),(9,9,'芒果9'),(11,11,'芒果11'),(13,13,'芒果13'),(15,15,'芒果15'),(17,17,'芒果17'),(19,19,'芒果19');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

View Code