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">

    <resultMap id="BaseResultMap" type="com.stan.sharding.entity.Order">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="user_id" jdbcType="INTEGER" property="userId" />
        <result column="order_id" jdbcType="INTEGER" property="orderId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
    </resultMap>

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

</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还有很多高级功能,值得慢慢探索。