36°

SpringBoot+Mybatis+ShardingJDBC实现数据分片

研究了一番ShardingShpere, 用SpringBoot+Mybatis+ShardingJDBC搭建了一个最简单的数据分区的构架,在此记录一下。

ShardingJDBC是基于Mybatis等ORM框架和数据库之间的一层中间件,简单地说它包装了数据库中分库分表,读写分离,数据脱敏等操作,向上给ORM框架提供数据源。对于应用开发者而言,无需关心自己的数据应该存在哪个库哪张表,他/她的直接操作对象还是ORM框架,ORM框架的处理对象是逻辑表,ShardingJDBC的工作就是处理逻辑表和物理表之间的操作关系。它的工作流程可以简单理解为:SQL解析->查询优化->SQL路由->SQL改写->SQL执行->结果的归并。

以下例子是基于ShardingJDBC结合Mybatis在SpringBoot中实现简单的分库分表的插入操作。操作大致可氛围两步:

  1. SpringBoot+ShardingJDBC提供数据源;
  2. 引入Mybatis实现对象的插入操作。

配置ShardingJDBC的主要操作步骤有:(参考

a. 在SpringBoot工程中添加maven依赖:

<!-- for spring boot -->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>4.0.0-RC1</version>
</dependency>

<!-- for spring namespace --> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>4.0.0-RC1</version> </dependency>

由于使用了dbcp2连接池,还需要添加依赖:

<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-dbcp2</artifactId>
	<version>2.7.0</version>
</dependency>

<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-pool2</artifactId> <version>2.8.0</version> </dependency>

b. 在application.properties配置好Sharding数据源,如:

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0 spring.shardingsphere.datasource.ds0.username=root spring.shardingsphere.datasource.ds0.password=Zkong_1234

spring.shardingsphere.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1 spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=Zkong_1234

spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order$->{0..1} spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item$->{0..1} spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}

这里实现了一个简单的数据分区规则,对于一个Order(对应于逻辑表t_order)来说,根据它的user_id对2取模来找到对应的库,再根据它的order_id对2取模来找到对应的表。所以,在本地数据库中,新建两个数据库ds0和ds1,并在它们中分别创建两张表t_order0, t_order1, 如:

至此数据源就已经配好了,可以写一个简单的单测来做下测试:

package com.stan.sharding;

import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.SQLException;

@SpringBootTest class ShardingApplicationTests {

@Resource
private DataSource dataSource;

@Test
void testDataSource() {
	try {
		System.out.println(dataSource.getConnection().toString());
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

}

使用Mybatis实现一个Order的插入操作:(在SpringBoot中使用Mybatis可参考上一篇

在pom文件中引入Mybatis相关的依赖后,在application.properties中添加Mybatis配置(与直接使用Mybatis不同,在此已经无需单独配置数据源):

mybatis.mapper-locations=classpath:/mybatis-mapper/*Mapper.xml
mybatis.type-aliases-package=com.stan.sharding.entity

创建Order对象:

package com.stan.sharding.entity;

public class Order {

private Integer id;
private Integer userId;
private Integer orderId;
private String name;

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 Integer getOrderId() {
    return orderId;
}

public void setOrderId(Integer orderId) {
    this.orderId = orderId;
}

public String getName() {
    return name;
}

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

}

创建OrderMapper接口:

package com.stan.sharding.mapper;

import com.stan.sharding.entity.Order; import org.springframework.stereotype.Repository;

@Repository public interface OrderMapper {

void add(Order order);

}

创建Order的映射文件OrderMapper.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.stan.sharding.mapper.OrderMapper">
&lt;resultMap id="BaseResultMap" type="com.stan.sharding.entity.Order"&gt;
    &lt;result column="id" jdbcType="INTEGER" property="id" /&gt;
    &lt;result column="user_id" jdbcType="INTEGER" property="userId" /&gt;
    &lt;result column="order_id" jdbcType="INTEGER" property="orderId" /&gt;
    &lt;result column="name" jdbcType="VARCHAR" property="name" /&gt;
&lt;/resultMap&gt;

&lt;insert id="add" parameterType="com.stan.sharding.entity.Order" useGeneratedKeys="true" keyProperty="id"&gt;
    insert into t_order (
    id, user_id, order_id, name
    ) values (
    #{id}, #{userId} ,#{orderId} ,#{name}
    )
&lt;/insert&gt;

</mapper>

在启动程序添加MapperScan和EntityScan

package com.stan.sharding;

import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.domain.EntityScan;

@MapperScan("com.stan.sharding.mapper") @EntityScan("com.stan.sharding.entity") @SpringBootApplication public class ShardingApplication {

public static void main(String[] args) {
	SpringApplication.run(ShardingApplication.class, args);
}

}

各个文件的相对位置如:

写一个单测试一下OrderMapper:

package com.stan.sharding;

import com.stan.sharding.entity.Order; import com.stan.sharding.mapper.OrderMapper; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest class ShardingApplicationTests {

@Autowired
OrderMapper orderMapper;

@Test
void testOrderMapper() {
	Order order = new Order();
	order.setId(1);
	order.setOrderId(1);
	order.setUserId(1);
	order.setName("haha");
	orderMapper.add(order);
}

}

根据之前所配置的分区规则,单测中的order,userId为1,orderId为1,应该被映射到ds1库,t_order1表,执行单测后有:

到此最简单的一个分库分表功能已经实现了,ShardingSphere还有很多高级功能,值得慢慢探索。

本文由【莫在全】发布于开源中国,原文链接:https://my.oschina.net/u/4042451/blog/3160611

全部评论: 0

    我有话说: