sharding-jdbc在目前微服务和各种saas软件等数据量日益加大的情况下变得使用的人多了起来。
sharding-jdbc读写分离测试
数据库实体
@Data
@Table(name = "city")
@Entity
public class City implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "province")
private String province;
}
Jpa操作类
public interface CityRepository extends JpaRepository<City,Long> {
}
SpringBoot启动类
@SpringBootApplication
@EnableTransactionManagement
public class RunBoot {
public static void main(String[] args) {
SpringApplication.run(RunBoot.class,args);
}
}
具体读写分离配置
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.datasource.names=master,slave0
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/test1
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=351848327
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql:/localhost:3306/test2
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=351848327
#master-slave
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
测试类和方法
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestMasterSlave {
@Resource
CityRepository cityRepository;
@Test
public void testMasterSlave(){
City city = new City();
city.setName("shanghai");
city.setProvince("shanghai");
cityRepository.save(city);
}
@Test
public void findAll(){
List<City> list = cityRepository.findAll();
list.forEach(c->{
System.out.println(c.getId()+" "+c.getName()+" "+c.getProvince());
});
}
}
分别执行两个方法可以看到日志,实际操作写的时候是在master库,操作读的时候是在slave库
file
file
sharding-jdbc读写分离强制某库执行操作
这种方法主要是针对那些数据量大,数据无法及时同步到从库,可以直接从主库读取
具体配置
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test1
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=351848327
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/test2
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=351848327
spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.zhao.hint.MyHint
具体Hint的规则
public class MyHint implements HintShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> targetNames, HintShardingValue<Long> hintShardingValue) {
Collection<String> results = new ArrayList<>();
for (String each :targetNames){
for (Long value:hintShardingValue.getValues()){
if (each.endsWith(String.valueOf(value%2))){
results.add(each);
}
}
}
return results;
}
}
Hint测试类
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestHint {
@Resource
CityRepository cityRepository;
@Test
public void testHint(){
HintManager hintManager =HintManager.getInstance();
hintManager.setDatabaseShardingValue(0L);
List<City> list = cityRepository.findAll();
System.out.println("主库表大小"+list.size());
hintManager.setDatabaseShardingValue(1L);
List<City> list1 = cityRepository.findAll();
System.out.println("从库表大小"+list1.size());
}
}
执行后可以看到两次查询分别从主库和从库中获取了数据
file
sharding-jdbc分库分表
分库分表中要处理的包括id生成,分表列,广播表,字表跟随父表进行分表等配置
测试试题类
@Data
@Table(name = "position")
@Entity
@ToString
public class Position implements Serializable {
@Id
@Column(name = "Id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long Id;
@Column(name = "name")
private String name;
@Column(name = "salary")
private String salary;
@Column(name = "city")
private String city;
}
@Data
@Table(name = "position_detail")
@Entity
public class PositionDetail implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@Column(name = "pid")
private Long pid;
@Column(name = "description")
private String description;
}
@Data
@Table(name = "b_order")
@Entity
public class Border implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name="is_del")
private Boolean isDel;
@Column(name = "company_id")
private Integer companyId;
@Column(name = "position_id")
private long positionId;
@Column(name = "user_id")
private Integer userId;
@Column(name = "publish_user_id")
private Integer publishUserId;
@Column(name = "resume_type")
private Integer resumeType;
@Column(name = "status")
private String status;
@Column(name = "create_time")
private Date createTime;
@Column(name = "operate_time")
private Date operateTime;
@Column(name = "work_year")
private String workYear;
@Column(name = "name")
private String name;
@Column(name = "position_name")
private String positionName;
@Column(name = "resume_id")
private Integer resumeId;
}
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test1
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=351848327
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://rlocalhost:3306/test2
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=351848327
#sharding
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
spring.shardingsphere.sharding.tables.position.key-generator.column=id
#spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.position.key-generator.type=zhao-sharding-key
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{pid % 2}
spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
#spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=zhao-sharding-key
#broadcast
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
#sharding-database-table
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order$->{id % 2}
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE
自定义id生成器
@Slf4j
public class MyShardingId implements ShardingKeyGenerator {
private SnowflakeShardingKeyGenerator shardingKeyGenerator = new SnowflakeShardingKeyGenerator();
@Override
public Comparable<?> generateKey() {
log.info("执行了自定义的id生成器");
return shardingKeyGenerator.generateKey();
}
@Override
public String getType() {
return "zhao-sharding-key";
}
@Override
public Properties getProperties() {
return null;
}
@Override
public void setProperties(Properties properties) {
}
}
执行的测试方法
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestingShardingDatabase {
@Resource
PositionRepository positionRepository;
@Resource
PositionDetailRepository positionDetailRepository;
@Resource
CityRepository cityRepository;
@Resource
BorderRepository borderRepository;
/**
* 测试是否分库
*/
@Test
public void testAdd(){
for (long i=1;i<=20;i++){
Position position = new Position();
//position.setId(i);
position.setCity("shanghai");
position.setName("zhao");
position.setSalary("100.86");
positionRepository.save(position);
}
}
/**
* 分库时主表和字表配置的规则一样,所以这里测试的是主表和字表在同库对应
*/
@Test
public void testAddDetail(){
for (long i=1;i<=20;i++){
Position position = new Position();
//position.setId(i);
position.setCity("shanghai");
position.setName("zhao");
position.setSalary("100.86");
positionRepository.save(position);
PositionDetail detail = new PositionDetail();
detail.setPid(position.getId());
detail.setDescription("This is message "+i);
positionDetailRepository.save(detail);
}
}
/**
* 测试加载数据分库时走的那个库
*/
@Test
public void testLoadData(){
Object object = positionRepository.findPositionLoadById(607972934107004929L);
Object[] objects = (Object[]) object;
System.out.println(objects[0]+" "+objects[1]);
}
/**
* 广播表会写入多个库
*/
@Test
public void testBroadCast(){
City city = new City();
city.setName("shanghai");
city.setProvince("shanghai");
cityRepository.save(city);
}
/**
* 测试分表时如何插入
*/
@Test
@Repeat(100)
public void testShardingBorder(){
Random random = new Random();
int companyId = random.nextInt(10);
Border border = new Border();
border.setIsDel(false);
border.setCompanyId(companyId);
border.setCreateTime(new Date());
border.setName("zhao");
border.setWorkYear("2");
borderRepository.save(border);
}
}
以上操作依赖的pom文件为
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.2.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>2.2.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.2.5.RELEASE</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>
<!--<dependency>-->
<!--<groupId>org.apache.shardingsphere</groupId>-->
<!--<artifactId>sharding-transaction-xa-core</artifactId>-->
<!--<version>4.1.0</version>-->
<!--</dependency>-->
<!--<dependency>-->
<!--<groupId>org.apache.shardingsphere</groupId>-->
<!--<artifactId>sharding-transaction-base-saga</artifactId>-->
<!--</dependency>-->
</dependencies>
以上就是sharding-jdbc的一些基础使用的介绍。本文github地址https://github.com/zhendiao/deme-code
欢迎关注和点赞,以及总结的分类面试题https://github.com/zhendiao/JavaInterview
网友评论