springboot+mybaties实现分库分表

1.导包必须有的

<!--jdbc-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--Web依赖-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--MySQL数据库配置-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

#mysql
db.url = 127.0.0.1
db.username = dev
db.password = dev




# mysql datasource 数据库访问配置 默认
spring.shardingsphere.datasource.saas.url = jdbc:mysql://${db.url}:3306/sol-badge-platform-saas?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&nullCatalogMeansCurrent=true
spring.shardingsphere.datasource.saas.username = ${db.username}
spring.shardingsphere.datasource.saas.password = ${db.password}

spring.shardingsphere.datasource.saas.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.saas.initialSize = 10
spring.shardingsphere.datasource.saas.minIdle = 5
spring.shardingsphere.datasource.saas.maxActive = 50
# 配置获取连接等待超时的时间
spring.shardingsphere.datasource.saas.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.shardingsphere.datasource.saas.timeBetweenEvictionRunsMillis = 60000
spring.shardingsphere.datasource.saas.minEvictableIdleTimeMillis = 60000
# 检测连接是否有效的sql
spring.shardingsphere.datasource.saas.validationQuery = SELECT 1 FROM DUAL
# 检测连接是否有效
spring.shardingsphere.datasource.saas.testWhileIdle = true
# 申请连接时执行validationQuery检测连接是否有效
spring.shardingsphere.datasource.saas.testOnBorrow = false
# 归还连接时执行validationQuery检测连接是否有效
spring.shardingsphere.datasource.saas.testOnReturn = false
spring.shardingsphere.datasource.saas.filters = stat,wall,log4j
spring.shardingsphere.datasource.saas.logSlowSql = true



# mysql datasource 数据库访问配置
spring.shardingsphere.datasource.gps.url = jdbc:mysql://${db.url}:3306/sol-badge-gps?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&nullCatalogMeansCurrent=true
spring.shardingsphere.datasource.gps.username = ${db.username}
spring.shardingsphere.datasource.gps.password = ${db.password}

spring.shardingsphere.datasource.gps.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.gps.initialSize = 10
spring.shardingsphere.datasource.gps.minIdle = 5
spring.shardingsphere.datasource.gps.maxActive = 50
# 配置获取连接等待超时的时间
spring.shardingsphere.datasource.gps.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.shardingsphere.datasource.gps.timeBetweenEvictionRunsMillis = 60000
spring.shardingsphere.datasource.gps.minEvictableIdleTimeMillis = 60000
# 检测连接是否有效的sql
spring.shardingsphere.datasource.gps.validationQuery = SELECT 1 FROM DUAL
# 检测连接是否有效
spring.shardingsphere.datasource.gps.testWhileIdle = true
# 申请连接时执行validationQuery检测连接是否有效
spring.shardingsphere.datasource.gps.testOnBorrow = false
# 归还连接时执行validationQuery检测连接是否有效
spring.shardingsphere.datasource.gps.testOnReturn = false
spring.shardingsphere.datasource.gps.filters = stat,wall,log4j
spring.shardingsphere.datasource.gps.logSlowSql = true

package com.macro.mall.tiny.demo.dbconfig;

import com.alibaba.druid.pool.DruidDataSource;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * mapper设置
 * @author YZS
 */
@Configuration
@MapperScan(basePackages = SmartDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "smartSqlSessionFactory")
public class SmartDataSourceConfig {

    static final String PACKAGE = "com.macro.mall.tiny.demo.dao.saas";
    static final String MAPPER_LOCATION = "classpath*:mapping/saas/*.xml";

    @Value("${spring.shardingsphere.datasource.saas.url}")
    private String url;

    @Value("${spring.shardingsphere.datasource.saas.username}")
    private String user;

    @Value("${spring.shardingsphere.datasource.saas.password}")
    private String password;

    @Value("${spring.shardingsphere.datasource.saas.driver-class-name}")
    private String driverClass;

    @Bean(name = "smartDataSource")
    @Primary
    public DataSource smartDataSource() {

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "smartTransactionManager")
    @Primary
    public DataSourceTransactionManager paasTransactionManager() {
        return new DataSourceTransactionManager(smartDataSource());
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.shardingsphere.datasource.druid")
    public MybatisConfiguration globalConfiguration() {
        return new MybatisConfiguration();
    }

    @Bean(name = "smartSqlSessionFactory")
    @Primary
    public SqlSessionFactory paasSqlSessionFactory(@Qualifier("smartDataSource") DataSource smartDataSource,
                                       MybatisConfiguration config) throws Exception {
        //final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        final MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(smartDataSource);
        // TODO: 2022/3/12  导致多数据源切换失效
        //sessionFactory.setConfiguration(config);
        sessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(SmartDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }



}

另一份配置文件复制一份即可，不过有些参数需要改变，讲解：

@Primary  代表默认数据库，第二份需要去掉

@MapperScan  @Bean  代表sql工程，dao层位置等，需要改掉不能和第一份一样吗，具体看你的applocation配置，最好方法名称也改掉。

中间碰到了一些问题大部分都可以考百度摆平，当然如果你严格然后我说的操作，那就不会有问题，其中解决时间最长的是在配置文件中有一行代码，因为是复制别人的所有不是很明白为什么那么操作，有知道可以在评论里说下，谢谢。

bug：数据库无法切换查询第二数据库时，debug中数据库值都已拿到，但是仍然查的是默认数据库，原因是在mapper配置文件中赋值了一个MybatisConfiguration，源代码如下：

@Bean(name = "smartSqlSessionFactory")
@Primary
public SqlSessionFactory paasSqlSessionFactory(@Qualifier("smartDataSource") DataSource smartDataSource,
                           MybatisConfiguration config) throws Exception {
    //final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    final MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
    sessionFactory.setDataSource(smartDataSource);
    // TODO: 2022/3/12  导致多数据源切换失效
    sessionFactory.setConfiguration(config);
    sessionFactory.setMapperLocations(
            new PathMatchingResourcePatternResolver().getResources(SmartDataSourceConfig.MAPPER_LOCATION));
    return sessionFactory.getObject();
}

如果对你有用，创作不易请点个赞，谢谢。