案例需求:
环境要求
- Mysql一主二从(数据同步)
- 水平分库分表(classes班级公共表,student学生分表分库)
(1) classes班级公共表(student_db_1,student_db_2数据同步)
(2) student学生表分库(按班级id分库,偶数student_db_1库,奇数student_db_2库)
(3) student学生表分表(按学生id分表,偶数student1表,奇数student2表)
建表语句
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`c_id` int(11) NOT NULL COMMENT '班级id',
`cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级',
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
`s_id` int(11) NOT NULL COMMENT '学生id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`classes_id` int(11) NULL DEFAULT NULL COMMENT '班级id',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
DROP TABLE IF EXISTS `student2`;
CREATE TABLE `student2` (
`s_id` int(11) NOT NULL COMMENT '学生id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`classes_id` int(11) NULL DEFAULT NULL COMMENT '班级id',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
- 业务要求
(1) 班级新增
(2) 班级删除,本班级学生随机分散其他班级(分散规则自行决定)
(3) 学生新增,现有班级随机(随机规则自行决定)
(4) 根据班级名查询学生
(5) 修改学生信息
(6) 批量删除学生
学习内容:
1.首先把数据库准备好,主从搭建完成。(如何主从搭建看之前文档)
2.创建项目,导入pom依赖。
<dependencies>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
</dependency>
<dependency>
<groupId>javax.interceptor</groupId>
<artifactId>javax.interceptor-api</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-typehandlers-jsr310</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
3.修改application.properties
(配置一主一从)
server.port=56082
spring.application.name = shopping
spring.profiles.active = local
server.servlet.context-path = /shopping
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
# 真实数据源定义 m为主库 s为从库
spring.shardingsphere.datasource.names = m1,m2,s1,s2
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.200.130:3306/student_db_1?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://192.168.200.130:3306/student_db_2?useUnicode=true
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456
spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.200.130:3307/student_db_1?useUnicode=true
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = 123456
spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://192.168.200.130:3307/student_db_2?useUnicode=true
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = 123456
#spring.shardingsphere.datasource.a1.type = com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.a1.driver-class-name = com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.a1.url = jdbc:mysql://192.168.200.130:3308/student_db_1?useUnicode=true
#spring.shardingsphere.datasource.a1.username = root
#spring.shardingsphere.datasource.a1.password = 123456
#
#spring.shardingsphere.datasource.a2.type = com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.a2.driver-class-name = com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.a2.url = jdbc:mysql://192.168.200.130:3308/student_db_2?useUnicode=true
#spring.shardingsphere.datasource.a2.username = root
#spring.shardingsphere.datasource.a2.password = 123456
# 主库从库逻辑数据源定义 ds1为student_db_1 ds2为student_db_2
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2
# 默认分库策略,以classes_id为分片键,分片策略为store_info_id % 2 + 1,也就是store_info_id为双数的数据进入ds1,为单数的进入ds2
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = classes_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{classes_id % 2 + 1}
# student分表策略,分布在ds1,ds2的student1 student2表 ,分片策略为s_id% 2 + 1,s_id生成为雪花算法,为双数的数据进入student1表,为单数的进入student2表
spring.shardingsphere.sharding.tables.student.actual-data-nodes = ds$->{1..2}.student$->{1..2}
spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column =s_id
spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression =student$->{s_id % 2 + 1}
#spring.shardingsphere.sharding.tables.student.key-generator.column=s_id
#spring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE
# 设置product_info,product_descript为绑定表(配置绑定表效率更高)
#spring.shardingsphere.sharding.binding-tables[0] = student
# 设置region为广播表(公共表),每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast-tables=classes
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
#swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
4.完善基础信息
@Data
public class Student {
//学生id\
@TableId
private Integer sId;
//姓名
private String name;
//班级id
private Integer classesId;
//班级//
}
@Data
public class Classes {
//班级ID
private Integer cId;
//班级
private String cname;
}
编写dao(mapper)
@Mapper
@Component
public interface ClassesDao{
//添加班级信息
@Insert("insert into classes(c_id,cname) value(#{cId},#{cname})")
@Options(useGeneratedKeys = true,keyProperty = "cId",keyColumn = "cId")
abstract//针对insert有效,当有关联表操作的时候,可以先插入主表,然后根据主表返回的主键id去落库详情表
void insertClassesInfo(Classes classes);
//删除班级信息
@Delete("delete from classes where c_id=#{cId}")
void deleteClasses(@Param("cId")Integer cId);
@Select("select c_id from classes")
public List<Integer> classIds();
@Select("select * from classes where cname=#{name}")
public Classes findByName(@Param("name")String name);
}
@Mapper
@Component
public interface StudentDao {
//添加学生信息
@Insert("insert into student(s_id,name,classes_id) value(#{sId},#{name},#{classesId})")
@Options(useGeneratedKeys = true,keyProperty = "sId",keyColumn = "s_id")
abstract//针对insert有效,当有关联表操作的时候,可以先插入主表,然后根据主表返回的主键id去落库详情表
int insertStudentInfo(Student student);
@Select("select * from student where classes_id=#{id}")
public List<Student> findByClassesId(@Param("id")Integer id);
//修改学生
@Update("UPDATE student SET name=#{name} where s_id=#{sId}")
public void updateStudent(@Param("name")String name,@Param("sId")Integer sId);
//根据班级id把班级内所有同学全部删除
@Delete("delete FROM student where classes_id=#{cId}")
public void deleteStudent(@Param("cId")Integer cId);
//根据sid删除
@Delete("delete FROM student where s_id=#{sId}")
public void deleteStudentList(@Param("sId")Integer sId);
@Service
@Transactional
public class ClassesServiceImpl implements ClassesService {
@Resource
private ClassesDao classesDao;
@Resource
private StudentDao studentDao;
@Override
public void createClasses(Classes classes) {
//1新增班级
classesDao.insertClassesInfo(classes);
}
@Override
public void deleteClasses(Integer cId) {
classesDao.deleteClasses(cId);
List<Integer> list = classesDao.classIds();
int size = list.size();
List<Student> byClassesIds = studentDao.findByClassesId(cId);
//先删除班级下的student
studentDao.deleteStudent(cId);
//再依次重新设置cId 添加到数据库
for (Student student : byClassesIds) {
Random num=new Random();
int i = num.nextInt(size);
student.setClassesId(list.get(i));
studentDao.insertStudentInfo(student);
}
}
@Override
public List<Integer> classIds() {
return classesDao.classIds();
}
}
@Service
@Transactional
public class StudentServiceImpl implements StudentService {
@Autowired
private StudentDao studentDao;
@Autowired
private ClassesDao classesDao;
@Override
public void createStudent(Student student) {
//1、 *****************新增学生基本信息*****************
studentDao.insertStudentInfo(student);
}
@Override
public List<Student> findByClassesId(String cname) {
Classes byName = classesDao.findByName(cname);
return studentDao.findByClassesId(byName.getCId());
}
@Override
public void deleteStudent(Integer cId) {
studentDao.deleteStudent(cId);
}
@Override
public void update(String name, Integer sId) {
studentDao.updateStudent(name,sId);
}
}
public interface StudentService {
public void createStudent(Student student);
public List<Student> findByClassesId(String cname);
public void deleteStudent(Integer cId);
public void update(String name, Integer sId);
}
public interface ClassesService {
public void createClasses(Classes classes);
public void deleteClasses(Integer cId);
public List<Integer> classIds();
}
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Studentce1013Application.class)
public class ClassesTest {
@Autowired
private ClassesService classesService;
//添加班级
@Test
public void testCreateClasses() {
Classes classes = new Classes();
classes.setCId(4);
classes.setCname("JAVA34");
classesService.createClasses(classes);
}
//根据班级ID刪除班级
@Test
public void deleteClasses(){
classesService.deleteClasses(1);
}
}
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Studentce1013Application.class)
public class StudentTest {
@Autowired
StudentService studentService;
@Autowired
private StudentDao studentDao;
//学生
@Test
public void testCreateStudent() {
for (int i = 1; i < 10; i++) {
Student student = new Student();
student.setSId(i);
student.setClassesId(new Random().nextInt(5));//根据id分库
student.setName("tom" + i);
studentService.createStudent(student);
}
}
//根据班级名查询
@Test
public void findByClaId(){
List<Student> byClassesId = studentService.findByClassesId("Java34");
for (Student student : byClassesId) {
System.out.println(student);
}
}
//修改学生
@Test
public void update(){
studentService.update("222",5);
}
//根据班级Id批量删除
@Test
public void deleteStudentId(){
studentService.deleteStudent(1);
}
//根据学生id批量删
@Test
public void deleteStudentList(){
List<Integer> list =new ArrayList<>();
list.add(5);
list.add(6);
for (Integer integer : list) {
studentDao.deleteStudentList(integer);
}
}
}
上面代码仅供参考,写得不好敬请见谅