手把手教你SpringBoot、Sharding-JDBC、MyBatis实现多库分表

最新推荐文章于 2022-03-10 16:20:16 发布

chuancuanzuo8795

最新推荐文章于 2022-03-10 16:20:16 发布

阅读量298

点赞数

文章标签： java 数据库

原文链接：https://my.oschina.net/u/4129361/blog/3097868

版权

MySQL设计

在一个物理机的mysql上创建两个库ds0,ds1,每个库里面创建三个分表的用户表

create database `ds0`;

use `ds0`;

CREATE TABLE `t_user_0`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_1`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_2`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




create database `ds1`;

use `ds1`;

CREATE TABLE `t_user_0`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_1`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user_2`(
	id bigint(64) not null  auto_increment,
    user_id bigint(64) not null,
	city varchar(20) not null,
	name varchar(20) not null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

新建SpringBoot web项目

https://start.spring.io通过创建SpringBoot项目,引入Mysql、MyBatis、SpringWeb依赖.

除此以外还要添加apache sharding-jdbc、Alibaba Druid数据库连接池依赖.

        <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>2.1.0</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>


        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>

添加配置类，配置ShardingJDBC的数据源以及分库分表逻辑

@Configuration
@MapperScan(basePackages = {"com.yzy.sharding.mapper"}, sqlSessionFactoryRef = "DbDataPlatformConfig.SqlSessionFactory")
public class ShardingDataSourceConfig {
    @Bean(name = "DbDataPlatformConfig.SqlSessionFactory")
    public SqlSessionFactory sqlSessionFacstory(@Qualifier("shardingDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath*:mappers/*.xml"));
        sessionFactory.setDataSource(dataSource);
        return sessionFactory.getObject();
    }
    @Bean(name = "DbDataPlatformConfig.SqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("DbDataPlatformConfig.SqlSessionFactory")
            SqlSessionFactory sqlSessionFactoryBean) {
        return new SqlSessionTemplate(sqlSessionFactoryBean);
    }
    @Bean(name = "shardingDataSource")
    @Qualifier("shardingDataSource")
    public DataSource getShardingDataSource() {
        // 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        // 配置第一个数据源
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0");
        dataSource1.setUsername("root");
        dataSource1.setPassword("root");
        dataSourceMap.put("db0", dataSource1);
        // 配置第二个数据源
        DruidDataSource dataSource2 = new DruidDataSource();
        dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1");
        dataSource2.setUsername("root");
        dataSource2.setPassword("root");
        dataSourceMap.put("db1", dataSource2);
        // 配置用户表规则
        TableRuleConfiguration  userTableRuleConfig = new TableRuleConfiguration("t_user" , "db${0..1}.t_user_${0..2}");
        // 配置分库策略（Groovy表达式配置db规则）
        userTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "db${user_id % 2}"));
        // 配置分表策略（Groovy表达式配置表路由规则）
        userTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "t_user_${user_id % 3}"));
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(userTableRuleConfig);
        DataSource dataSource = null;
        try {
            dataSource = ShardingDataSourceFactory
                    .createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataSource;
    }
}

这个配置类相当重要，它多了如下的事情:

通过@MapperScan注解配置MyBatis Interface所在的包路径
通过sessionFactory.setMapperLocations设置MyBatis xml文件所在路径
创建ShardingJDBC数据源并且设置分库、分表的逻辑 (user_id取余)

MyBatis使用逻辑表写SQL

com.yzy.sharding.mapper.UserMapper.java

@Repository
public interface UserMapper {
    void insert(Integer userId ,String city,String name);
}

resources/mappers/UserMapper.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.yzy.sharding.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.yzy.sharding.User">
        <result column="user_id" jdbcType="INTEGER" property="userId" />
        <result column="city" jdbcType="VARCHAR" property="city" />
        <result column="name" jdbcType="VARCHAR" property="name" />
    </resultMap>
    <insert id="insert">
        insert into t_user (`user_id`,`city`, `name`)
        values  (#{userId, jdbcType=INTEGER}, #{city,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR})
    </insert>
</mapper>

com.yzy.sharding.User.java

package com.yzy.sharding;
public class User {
    private Integer userId;
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    private String city;
    private String name;
}

测试的Controller

@RestController
@RequestMapping("/")
public class HelloController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("user/{userId}/{city}/{name}")
    public String GetUser(@PathVariable int userId, @PathVariable String city, @PathVariable String name) {
        userMapper.insert(userId,city,name);
         return "OK";
    }
}

通过不停的调整http://127.0.0.1:8080/user/2/tianjin/xiaohong 的参数，测试看看是否可以根据sharding的分库分表的配置来实现正确的insert路由。

转载于:https://my.oschina.net/u/4129361/blog/3097868