基于mybatis实现多数据源
工具jdk8，idea2020.1.1，mysql8.0，maven3，springboot2.5.0
代码结构 项目依赖
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.1.0
mysql
mysql-connector-java
8.0.16
runtime
org.springframework.boot
spring-boot-starter-web
org.springframework.boot
spring-boot-starter-test
test
配置文件
#DB1 Configuration: spring.datasource.test1.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.test1.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 spring.datasource.test1.username=root spring.datasource.test1.password=123456 #DB2 Configuration: spring.datasource.test2.driverClassName=com.mysql.cj.jdbc.Driver spring.datasource.test2.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 spring.datasource.test2.username=root spring.datasource.test2.password=123456
注意：springboot 2.x 使用DataSource创建数据源时，数据库连接地址要用jdbc-url
数据源配置- ConfigDb01
package com.example.datasource.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.example.datasource.mapper1"}, sqlSessionFactoryRef = "test1SqlSessionFactory")
public class ConfigDb01 {
@Bean(name = "test1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test1")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test1SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test1SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
- ConfigDb02
package com.example.datasource.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.example.datasource.mapper2"}, sqlSessionFactoryRef = "test2SqlSessionFactory")
public class ConfigDb02 {
@Bean(name = "test2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.test2")
@Primary
public DataSource testDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "test2TransactionManager")
@Primary
public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "test2SqlSessionTemplate")
@Primary
public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
主启动类
package com.example.datasource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class DatasourceApplication {
public static void main(String[] args) {
SpringApplication.run(DatasourceApplication.class, args);
}
}
注意：这里要禁用默认的数据源加载类DataSourceAutoConfiguration
dao层（注意路径要和数据源配置中的basePackages保证一致）- TestMapper1
package com.example.datasource.mapper1;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
public interface TestMapper1 {
// 这里链接test1的数据源
@Select("select count(*) from job_test")
int countSumTotal();
@Insert("INSERT into job_test(id,name) VALUES(1, '1')")
int save();
}
- TestMapper2
package com.example.datasource.mapper2;
import org.apache.ibatis.annotations.Select;
public interface TestMapper2 {
@Select("select count(*) from test2")
int countSumTotal2();
}
测试
package com.example.datasource;
import com.example.datasource.mapper1.TestMapper1;
import com.example.datasource.mapper2.TestMapper2;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class DatasourceApplicationTests {
@Autowired
private TestMapper1 testMapper1;
@Autowired
private TestMapper2 testMapper2;
@Test
void contextLoads() {
System.out.println(testMapper2.countSumTotal2());
}
@Test
void test1() {
System.out.println(testMapper1.save());
}
}
问题
可能会遇到以下问题
- 驼峰命名不匹配导致数据出不来
参考https://blog.csdn.net/yy1209357299/article/details/125646863?spm=1001.2014.3001.5502 - jdbc-url/url的问题
- 数据源配置以后不切换，常见问题不分析
可能时事务的问题，最终结果是因为数据源没有切换，这里推荐使用aop的方式去动态加载和指定，参考后面的文章
