前言
在一个项目体量不断增大的情况下,数据量随之增大,那么为了分散数据库的压力,可能会通过分库分表来进行减缓操作。这就与我们单一的数据源就不一样了,而是要配置多数据源了,本文就通过简单的示例,来示范多数据源实现的过程
准备环境
jdk8,idea2020.1.1,mysql8.0,maven3,springboot2.5.0
建表语句
这两张表要建在不同的数据库下
CREATE TABLE `job_test` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `test2` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
代码
项目结构
依赖
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<!--mysql依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
配置
application.properties
#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
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);
}
}
dao
注意包名
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());
}
}
这个本身并不难,但是一般用到多数据源情况的话,项目也不可能再是单体了,所以后面会对事务的补偿机制进行学习。