SpringBoot+Mybaits+sharding jdbc进行分库分表，亲测可行！（方法二）

• 前言

上面一篇博文介绍了基于sharding-jdbc框架管理进行分库分表，下面将介绍通过配置文件策略进行分库分表，在具体的项目中可以选择适合自己的，当然，基于配置文件的能更好的管理。

• 建表

CREATE TABLE `t_order_0` (
	`order_id` INT(11) NULL DEFAULT NULL,
	`user_id` INT(11) NULL DEFAULT NULL,
	`status` VARCHAR(50) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

 

• SpringBoot+Mybatis+sharding-jdbc框架搭建（pom文件）

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- spring boot test -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.30</version>
		</dependency>
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		<!-- for spring boot -->
		<dependency>
			<groupId>io.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>3.0.0.M1</version>
		</dependency>
	</dependencies>

• 主配置文件

mybatis.type-aliases-package=com.example.sharding.mapper
mybatis.mapper-locations=classpath:mapping/*.xml

spring.profiles.active=sharding

• application-sharding.properties（分表策略）

sharding.jdbc.datasource.names=ds0,ds1

sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/test1
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=123456

sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/test2
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=123456

#分库策略--对那个字段进行分库
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
#分库对user_id % 2进行分库选择
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2} 

#分表策略--ds$->{0..1}.t_order_$->{0..1}代表是那个库下面的那个表
sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}
#分表策略--对那个字段进行分表
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
#分表的表达式
sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
#分表对应的字段名
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_id

• 启动文件

@SpringBootApplication
public class DataBaseDivice {
	public static void main(final String[] args) {
		try (ConfigurableApplicationContext applicationContext = SpringApplication.run(DataBaseDivice.class, args)) {
			applicationContext.getBean(DemoService.class).demo();
		}
	}
}

public final class Order {

	private long orderId;

	private int userId;

	private String status;

	public long getOrderId() {
		return orderId;
	}

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

	public int getUserId() {
		return userId;
	}

	public void setUserId(final int userId) {
		this.userId = userId;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(final String status) {
		this.status = status;
	}

	@Override
	public String toString() {
		return String.format("order_id: %s, user_id: %s, status: %s", orderId, userId, status);
	}
}

• Service层 

@Service
public class DemoService {

	@Resource
	private OrderMapper orderRepository;

	public void demo() {
		for (int i = 0; i < 10; i++) {
			Order order = new Order();
			order.setOrderId(i);
			order.setUserId(55);
			order.setStatus("Tinko");
			orderRepository.insert(order);
		}
		System.out.println("Insert Success");
	}
}

• Mapper文件

@Mapper
public interface OrderMapper {
	
	Long insert(Order order);
}

• 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.example.sharding.mapper.OrderMapper">

	<insert id="insert" useGeneratedKeys="true"
		keyProperty="orderId">
		INSERT
		INTO t_order (
		order_id,user_id, status
		)
		VALUES (
		#{orderId},
		#{userId,jdbcType=INTEGER},
		#{status,jdbcType=VARCHAR}
		)
	</insert>
</mapper>

• mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeAliases>
		<package name="com.majiaxueyuan.entity" />
	</typeAliases>
	<mappers>
		<mapper resource="mapping/*.xml" />
	</mappers>
</configuration>

最后可以看到，对于user_id%2不同的数据是插入不同的库，order_id%2不同数据插入了不同的表