sharding-jdbc之分库分表实例

最新推荐文章于 2025-07-11 14:17:32 发布

原创

最新推荐文章于 2025-07-11 14:17:32 发布 · 362 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了如何使用sharding-jdbc进行分库分表操作，包括创建数据库、配置项目、数据库映射类的创建以及分库分表规则的设置。通过示例展示了在Spring环境中如何整合sharding-jdbc并进行数据插入测试。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、概述
之前，我们介绍了利用Mycat进行分库分表操作，Mycat分表操作是利用分库来进行的，单个库中的分表操作可结合MySQL的分区进行，这也是Mycat官方提倡的方式。那么，如何利用Mycat真正实现数据库的分库分表，可以私信我。今天，我们来看看sharding-jdbc，sharding-jdbc也是一款分库分表的“中间件”，不过，它并不向Mycat那样作为一个真正的中间件，它是一款以jar包的形式整合到业务中的插件，这就决定了它是轻量级的，用法也是十分简单的。

二、分库分表实战
接下来，我们就利用sharding-jdbc进行数据库的分库分表操作。

1、创建数据库
首先我们创建相应的数据库

create database sharding_0;
create database sharding_1;
这样我们就创建了两个数据库sharding_0和sharding_1;

接下来我们在两个库中创建相应的数据表，在两个库中分别进行如下SQL:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_student_00
-- ----------------------------
DROP TABLE IF EXISTS `t_student_00`;
CREATE TABLE `t_student_00` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_student_01
-- ----------------------------
DROP TABLE IF EXISTS `t_student_01`;
CREATE TABLE `t_student_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_user_00
-- ----------------------------
DROP TABLE IF EXISTS `t_user_00`;
CREATE TABLE `t_user_00` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_user_01
-- ----------------------------
DROP TABLE IF EXISTS `t_user_01`;
CREATE TABLE `t_user_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_user_02
-- ----------------------------
DROP TABLE IF EXISTS `t_user_02`;
CREATE TABLE `t_user_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
这样，我们的数据库就准备好了。

2、创建项目
接下来，我们就创建一个Maven项目，项目结构如下：

3、配置pom.xml
<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>com.lyz</groupId>
<artifactId>sharding-jdbc-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>sharding-jdbc-mybatis</name>
<url>http://maven.apache.org</url>

<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<spring.version>4.1.0.RELEASE</spring.version>
<mybatis.version>3.2.4</mybatis.version>
</properties>

<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.28</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.5</version>
</dependency>
</dependencies>
</project>
4、创建数据库映射类
这里，我们创建两个数据库映射类：User类和Student类。

4-1、User类

package com.lyz.sharding.entity;

import java.io.Serializable;

/**
* 用户类
* @author liuyazhuang
*
*/
public class User implements Serializable {

   private static final long serialVersionUID = 1L;

   private Integer id;

   private Integer userId;

   private String name;

   private Integer age;

   public Integer getId() {
       return id;
   }

   public void setId(Integer id) {
       this.id = id;
   }

   public Integer getUserId() {
       return userId;
   }

   public void setUserId(Integer userId) {
       this.userId = userId;
   }

   public String getName() {
       return name;
   }

   public void setName(String name) {
       this.name = name;
   }

   public Integer getAge() {
       return age;
   }

   public void setAge(Integer age) {
       this.age = age;
   }

   @Override
   public String toString() {
       return "User [id=" + id + ", userId=" + userId + ", name=" + name + ", age=" + age + "]";
   }

}
4-2、Student类

package com.lyz.sharding.entity;

import java.io.Serializable;

/**
* 学生类
* @author liuyazhuang
*
*/
public class Student implements Serializable {

   private static final long serialVersionUID = 8920597824668331209L;

   private Integer id;

   private Integer studentId;