- 背景
最近在研究Mysql的分库分表,前面的博客已经详细介绍了分库分表!
由于sharding-jdbc是不支持动态进行建库的SQL,那么就需要一次把需要的数据库和数据表都建好
-
建库、建表
考虑到这只是一个测试的demo,所以,只建了两个库和两个表
CREATE TABLE `t_order_0` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`order_id` VARCHAR(32) NULL DEFAULT NULL COMMENT '顺序编号',
`user_id` VARCHAR(32) NULL DEFAULT NULL COMMENT '用户编号',
`userName` VARCHAR(32) NULL DEFAULT NULL COMMENT '用户名',
`passWord` VARCHAR(32) NULL DEFAULT NULL COMMENT '密码',
`nick_name` VARCHAR(32) NULL DEFAULT NULL,
- SpringBoot+Mybatis+sharding-jdbc框架搭建(pom文件)
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--sharding-jdbc -->
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.5.4</version>
</dependency>
</dependencies>
- 配置文件
mybatis.config-locations=classpath:mybatis/mybatis-config.xml
#datasource
spring.devtools.remote.restart.enabled=false
#data source1
spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg1?serverTimezone=UTC
spring.datasource.test1.username=root
spring.datasource.test1.password=123456
#data source2
spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test_msg2?serverTimezone=UTC
spring.datasource.test2.username=root
spring.datasource.test2.password=123456
- 启动文件
@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion
@EnableTransactionManagement(proxyTargetClass = true) //开启事物管理功能
public class ShardingJdbcApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcApplication.class, args);
}
}
- 实体类(Data注解可免去set/get方法)
@Data
public class User {
private Long id;
private Long order_id;
private Long user_id;
private String userName;
private String passWord;
private String nickName;
}
- Service层
@Slf4j
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public void insert(User user) {
userMapper.insert(user);
}
}
- Mapper层
public interface UserMapper {
void insert(User user);
}
- 数据源配置和Mybatis配置和分库分表规则(重要)
这里,我们是将多个数据源交给sharding-jdbc进行管理,并且有默认的数据源,当没有设置分库分表规则的时候就可以使用默认的数据源
分表:user_id%2 = 0的数据存储到test_msg1,为1的存储到test_msg0
分表:order_id%2 = 0的数据存储到t_order_0,为1的存储到t_order_1
/**
* @Auther: Tinko
* @Date: 2018/12/19 16:27
* @Description: 数据源配置和Mybatis配置和分库分表规则
*/
@Configuration
@MapperScan(basePackages = "com.example.shardingjdbc.mapper", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class DataSourceConfig {
/**
* 配置数据源0,数据源的名称最好要有一定的规则,方便配置分库的计算规则
* @return
*/
@Bean(name="dataSource0")
@ConfigurationProperties(prefix = "spring.datasource.test1")
public DataSource dataSource0(){
return DataSourceBuilder.create().build();
}
/**
* 配置数据源1,数据源的名称最好要有一定的规则,方便配置分库的计算规则
* @return
*/
@Bean(name="dataSource1")
@ConfigurationProperties(prefix = "spring.datasource.test2")
public DataSource dataSource1(){
return DataSourceBuilder.create().build();
}
/**
* 配置数据源规则,即将多个数据源交给sharding-jdbc管理,并且可以设置默认的数据源,
* 当表没有配置分库规则时会使用默认的数据源
* @param dataSource0
* @param dataSource1
* @return
*/
@Bean
public DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0,
@Qualifier("dataSource1") DataSource dataSource1){
Map<String, DataSource> dataSourceMap = new HashMap<>(); //设置分库映射
dataSourceMap.put("dataSource0", dataSource0);
dataSourceMap.put("dataSource1", dataSource1);
return new DataSourceRule(dataSourceMap, "dataSource0"); //设置默认库,两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一
}
/**
* 配置数据源策略和表策略,具体策略需要自己实现
* @param dataSourceRule
* @return
*/
@Bean
public ShardingRule shardingRule(DataSourceRule dataSourceRule){
//具体分库分表策略
TableRule orderTableRule = TableRule.builder("t_order")
.actualTables(Arrays.asList("t_order_0", "t_order_1"))
.tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
.dataSourceRule(dataSourceRule)
.build();
//绑定表策略,在查询时会使用主表策略计算路由的数据源,因此需要约定绑定表策略的表的规则需要一致,可以一定程度提高效率
List<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>();
bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule)));
return ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule))
.bindingTableRules(bindingTableRules)
.databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))
.tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
.build();
}
/**
* 创建sharding-jdbc的数据源DataSource,MybatisAutoConfiguration会使用此数据源
* @param shardingRule
* @return
* @throws SQLException
*/
@Bean(name="dataSource")
public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException {
return ShardingDataSourceFactory.createDataSource(shardingRule);
}
/**
* 需要手动配置事务管理器
* @param dataSource
* @return
*/
@Bean
public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory testSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
return bean.getObject();
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
- 分库规则
/**
* @Auther: Tinko
* @Date: 2018/12/19 16:31
* @Description: 分库规则
*/
public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
@Override
public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
for (String each : databaseNames) {
if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(databaseNames.size());
for (Long value : shardingValue.getValues()) {
for (String tableName : databaseNames) {
if (tableName.endsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(databaseNames.size());
Range<Long> range = (Range<Long>) shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : databaseNames) {
if (each.endsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
- 分表规则
/**
* @Auther: Tinko
* @Date: 2018/12/19 16:30
* @Description: 分表规则
*/
public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
@Override
public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
for (String each : tableNames) {
if (each.endsWith(shardingValue.getValue() % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(tableNames.size());
for (Long value : shardingValue.getValues()) {
for (String tableName : tableNames) {
if (tableName.endsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(tableNames.size());
Range<Long> range = (Range<Long>) shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : tableNames) {
if (each.endsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
- 与Mysql交互的配置的文件
<?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.shardingjdbc.mapper.UserMapper" >
<sql id="Base_Column_List" >
id, userName, passWord, user_sex, nick_name
</sql>
<insert id="insert" parameterType="com.example.shardingjdbc.entity.User" >
INSERT INTO
t_order
(order_id,user_id,userName,passWord)
VALUES
(#{order_id},#{user_id},#{userName}, #{passWord})
</insert>
</mapper>
然后,测试可行!!!