sharding-jdbc在目前微服务和各种saas软件等数据量日益加大的状况下变得应用的人多了起来。
sharding-jdbc读写拆散测试
数据库实体
@Data@Table(name = "city")@Entitypublic 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@EnableTransactionManagementpublic class RunBoot { public static void main(String[] args) { SpringApplication.run(RunBoot.class,args); }}具体读写拆散配置
spring.shardingsphere.props.sql.show=truespring.shardingsphere.datasource.names=master,slave0spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/test1spring.shardingsphere.datasource.master.username=rootspring.shardingsphere.datasource.master.password=351848327spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql:/localhost:3306/test2spring.shardingsphere.datasource.slave0.username=rootspring.shardingsphere.datasource.slave0.password=351848327#master-slavespring.shardingsphere.masterslave.name=datasourcespring.shardingsphere.masterslave.master-data-source-name=masterspring.shardingsphere.masterslave.slave-data-source-names=slave0spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBINspring.shardingsphere.sharding.tables.city.key-generator.column=idspring.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库
sharding-jdbc读写拆散强制某库执行操作
这种办法次要是针对那些数据量大，数据无奈及时同步到从库，能够间接从主库读取
具体配置
spring.shardingsphere.props.sql.show=truespring.shardingsphere.datasource.names=ds0,ds1spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test1spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=351848327spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/test2spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=351848327spring.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()); }}执行后能够看到两次查问别离从主库和从库中获取了数据
sharding-jdbc分库分表
分库分表中要解决的包含id生成，分表列，播送表,字表追随父表进行分表等配置
测试试题类
@Data@Table(name = "position")@Entity@ToStringpublic 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")@Entitypublic 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")@Entitypublic 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,ds1spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/test1spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=351848327spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://rlocalhost:3306/test2spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=351848327#shardingspring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=idspring.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=SNOWFLAKEspring.shardingsphere.sharding.tables.position.key-generator.type=zhao-sharding-keyspring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pidspring.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=SNOWFLAKEspring.shardingsphere.sharding.tables.position_detail.key-generator.type=zhao-sharding-key#broadcastspring.shardingsphere.sharding.broadcast-tables=cityspring.shardingsphere.sharding.tables.city.key-generator.column=idspring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE#sharding-database-tablespring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_idspring.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=idspring.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=idspring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE自定义id生成器
@Slf4jpublic 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/d...
欢送搜寻关注自己与敌人共同开发的微信面经小程序【大厂面试助手】和公众号【微瞰技术】