欢迎访问中国建设银行网站个人客户6,wordpress访问仪表盘,自己做电影网站可以赚钱吗,企业网站模板html目录一、ShardingSphere简介二、ShardingSphere-分库分表1、垂直拆分#xff08;1#xff09;垂直分库#xff08;2#xff09;垂直分表2、水平拆分#xff08;1#xff09;水平分库#xff08;2#xff09;水平分表三、水平分库操作1、创建数据库和表2、配置分片的规则…
目录一、ShardingSphere简介二、ShardingSphere-分库分表1、垂直拆分1垂直分库2垂直分表2、水平拆分1水平分库2水平分表三、水平分库操作1、创建数据库和表2、配置分片的规则3、测试类四、水平分表操作1、引入依赖2、创建数据库、表3、分片策略4、编写代码实现1实体类2编写Mapper3启动类修改添加扫描mapper的入口4测试类采用springboot给我们提供的五、垂直分库/分表操作1、创建数据库、表2、分片策略3、编写代码实现1创建User实体类2Mapper4、测试六、公共表1、概念2、实现1在三个数据库中创建公共表2公共表的配置3测试代码3、相关问题及解决方法1Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named null in class com.chenxin.shardingsphere.entity.Udict一、ShardingSphere简介
Apache ShardingSphere 是一个开源的分布式数据库中间件解决方案组成的生态圈且它的产品有Sharding-JDBC和Sharding-Proxy组成他们两个之间是独立的同时又能混合部署组合起来一起使用。它们都提供了标准化的数据分片、分布式事务和数据库的治理能力可适用如Java、云原生开发的应用场景。
ShardingSphere定位是关系型数据库中间件目的是充分为了合理地在分布式的场景下利用关系型数据库的计算能力和存储能力而不是实现一个全新的关系型数据库。
二、ShardingSphere-分库分表
1、垂直拆分
1垂直分库
垂直分库把单一的数据库按照业务的不同进行划分专库专表
2垂直分表
操作数据库中的某张表我们把这张表里面的一部分字段拿出来存储到一张新的表里剩下的字段放在另一张表里。 2、水平拆分
1水平分库
水平分库相当于把数据库水平切割原来一个表中的数据可能会分配到不同的数据库中这就是水平分库。
2水平分表
水平分表就是把表中的数据进行了水平切割意味着按照行进行切割也就是说不同行的数据被切割后可能在不同的表中。
三、水平分库操作
1、创建数据库和表
-- 创建两个数据库
CREATE DATABASE edu_db_1;
CREATE DATABASE edu_db_2;
-- 需要在两个数据库中都执行下面的脚本
CREATE TABLE course_1(cid BIGINT(20) PRIMARY KEY,cname VARCHAR(20) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus VARCHAR(20) NOT NULL
);CREATE TABLE course_2(cid BIGINT(20) PRIMARY KEY,cname VARCHAR(20) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus VARCHAR(20) NOT NULL
);2、配置分片的规则
spring.shardingsphere.datasource.namesds-1,ds-2spring.shardingsphere.datasource.ds-1.jdbc-urljdbc:mysql://localhost:3306/edu_db_1?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-1.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.usernameroot
spring.shardingsphere.datasource.ds-1.passwordrootspring.shardingsphere.datasource.ds-2.jdbc-urljdbc:mysql://localhost:3306/edu_db_2?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-2.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.usernameroot
spring.shardingsphere.datasource.ds-2.passwordrootspring.main.allow-bean-definition-overridingtruespring.shardingsphere.sharding.tables.course.actual-data-nodesds-$-{1..2}.course_$-{1..2}spring.shardingsphere.sharding.tables.course.key-generator.columncid
spring.shardingsphere.sharding.tables.course.key-generator.typeSNOWFLAKEspring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-columncid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expressioncourse_$-{cid % 2 1}spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-columnuser_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expressionds-$-{user_id % 2 1}spring.shardingsphere.props.sql.showtrue3、测试类
// ------------- 测试分库 ---------------------Testvoid addCorseFpdb() {Course course new Course();course.setCname(java01);// 根据user_id进行分库偶数是在edu_db_1基数是在edu_db_2course.setUserId(100L);course.setCstatus(已启用);courseMapper.insert(course);}四、水平分表操作
1、引入依赖
dependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter/artifactId/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.1.17/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactId/dependencydependencygroupIdorg.apache.shardingsphere/groupIdartifactIdsharding-jdbc-spring-boot-starter/artifactIdversion4.0.0/version/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.0.5/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependency
/dependencies2、创建数据库、表
create table course_1(cid bigint(20) primary key,cname varchar(20) not null,user_id bigint(20) not null,cstatus varchar(20) not null
);create table course_2(cid bigint(20) primary key,cname varchar(20) not null,user_id bigint(20) not null,cstatus varchar(20) not null
);3、分片策略
spring.shardingsphere.datasource.namesds-0spring.shardingsphere.datasource.ds-0.jdbc-urljdbc:mysql://localhost:3306/course_db?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-0.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.usernameroot
spring.shardingsphere.datasource.ds-0.passwordrootspring.main.allow-bean-definition-overridingtruespring.shardingsphere.sharding.tables.course.actual-data-nodesds-0.course_$-{1..2}spring.shardingsphere.sharding.tables.course.key-generator.columncid
spring.shardingsphere.sharding.tables.course.key-generator.typeSNOWFLAKEspring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-columncid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expressioncourse_$-{cid % 2 1}spring.shardingsphere.props.sql.showtrue4、编写代码实现
1实体类
import lombok.Data;
import lombok.ToString;Data
ToString
public class Course {private Long cid;private String cname;private Long userId;private String cstatus;
}2编写Mapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.chenxin.shardingsphere.entity.Course;
import org.apache.ibatis.annotations.Mapper;Mapper
public interface CourseMapper extends BaseMapperCourse {
}3启动类修改添加扫描mapper的入口
SpringBootApplication
MapperScan(com.chenxin.shardingsphere.mapper)
public class ShardingsphereApplication {public static void main(String[] args) {SpringApplication.run(ShardingsphereApplication.class, args);}}4测试类采用springboot给我们提供的
SpringBootTest
class ShardingsphereApplicationTests {ResourceCourseMapper courseMapper;Testvoid addCourse() {Course course null;for (int i 0; i 100; i) {course new Course();course.setCname(Java);course.setUserId(1000L);course.setCstatus(Nor1);courseMapper.insert(course);}}}五、垂直分库/分表操作
1、创建数据库、表
CREATE DATABASE user_db;
USE user_db;
CREATE TABLE T_USER(user_id BIGINT(20) NOT NULL PRIMARY KEY,username VARCHAR(100) NOT NULL,ustatus VARCHAR(50) NOT NULL
);2、分片策略
spring.shardingsphere.datasource.namesds-1,ds-2,ds-3spring.shardingsphere.datasource.ds-1.jdbc-urljdbc:mysql://localhost:3306/edu_db_1?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-1.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.usernameroot
spring.shardingsphere.datasource.ds-1.passwordrootspring.shardingsphere.datasource.ds-2.jdbc-urljdbc:mysql://localhost:3306/edu_db_2?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-2.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.usernameroot
spring.shardingsphere.datasource.ds-2.passwordrootspring.shardingsphere.datasource.ds-3.jdbc-urljdbc:mysql://localhost:3306/user_db?serverTimezoneAsia/ShanghaiuseSSLfalse
spring.shardingsphere.datasource.ds-3.typecom.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-3.driver-class-namecom.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-3.usernameroot
spring.shardingsphere.datasource.ds-3.passwordrootspring.main.allow-bean-definition-overridingtrue# spring.shardingsphere.sharding.tables.course.actual-data-nodesds-$-{1..2}.course_$-{1..2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodesds-$-{3}.t_userspring.shardingsphere.sharding.tables.t_user.key-generator.columnuser_id
spring.shardingsphere.sharding.tables.t_user.key-generator.typeSNOWFLAKEspring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-columnuser_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expressiont_userspring.shardingsphere.props.sql.showtrue3、编写代码实现
1创建User实体类
import lombok.Data;
import lombok.ToString;// 实体类添加注解
TableName(value t_user)
Data
ToString
public class User {private Long userId;private String username;private String ustatus;
}2Mapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.chenxin.shardingsphere.entity.User;
import org.apache.ibatis.annotations.Mapper;Mapper
public interface UserMapper extends BaseMapperUser {
}4、测试
// ------------- 测试垂直分库 ----------------------
Test
void addUser() {User user new User();user.setUsername(chenxin);user.setUstatus(a);userMapper.insert(user);
}六、公共表
1、概念
存储固定数据的表表里面的数据很少发生变化查询的时候经常进行关联查询
在每个数据库中创建出相同的结构的表我们将每个数据库中共有的表成为公共表
2、实现
1在三个数据库中创建公共表
CREATE TABLE t_udict(dicid bigint(20) primary key,ustatus varchar(20) not null,uvalue varchar(100) not null
);2公共表的配置
spring.shardingsphere.sharding.broadcast-tablest_udict
spring.shardingsphere.sharding.tables.t_udict.key-generator.columndicid
spring.shardingsphere.sharding.tables.t_udict.key-generator.typeSNOWFLAKE3测试代码
创建实体类
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import lombok.ToString;Data
ToString
TableName(value t_udict)
public class Udict {private Integer dicid;private String ustatus;private String uvalue;
}Mapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.chenxin.shardingsphere.entity.Udict;
import org.apache.ibatis.annotations.Mapper;Mapper
public interface UdictMapper extends BaseMapperUdict {
}测试代码
Test
void addDict() {Udict udict new Udict();udict.setUstatus(0);udict.setUvalue(已启用);udictMapper.insert(udict);
}Test
void updateDict() {Udict udict new Udict();udict.setDicid(792033627201339393L);udict.setUstatus(1);udict.setUvalue(未启用);udictMapper.updateById(udict);
}3、相关问题及解决方法
1Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named ‘null’ in ‘class com.chenxin.shardingsphere.entity.Udict’
问题原因 数据库中字段名和实体类中相差太大程序自己对应不上
解决方法
// 需要在实体类指定主键是哪一个属性
Data
ToString
TableName(value t_udict)
public class Udict {TableIdprivate Long dicid;private String ustatus;private String uvalue;
}
