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>
2.修改数据源（applocation）:2个
#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
3.设置mapper配置文件：两个（主要）
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配置，最好方法名称也改掉。
@Value 配置的数据库信息，两份不同
4.创建文件：dao、mapper下都要配置两份，必须要和mapper配置文件中位置对应，否则无法生效。
中间碰到了一些问题大部分都可以考百度摆平，当然如果你严格然后我说的操作，那就不会有问题，其中解决时间最长的是在配置文件中有一行代码，因为是复制别人的所有不是很明白为什么那么操作，有知道可以在评论里说下，谢谢。
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();
}
如果对你有用，创作不易请点个赞，谢谢。
