一、前言
springboot 为我们集成了数据库资源,只要maven引入数据库资源,在配置文件中配置数据库连接即可。但是默认数据库只能有一个,真正生产中往往我们需要在项目中配置多个数据库资源。该如何配置呢?
二、场景
现有两个数据库,test、test1,分别有一张表,sys_user以及sys_student.
我们现在要通过springboot项目,根据不同场景分别连接到不同的数据库。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<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.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.5.3</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</build>
</project>
先贴一下,maven的pom.xml。
三、准备
这里我使用了mybatis-generator插件来生产pojo以及Mapper映射,不清楚的可以了解一下maven插件自动生成pojo以及mapper映射文件
-
通过插件分别生成 pojo以及mapper文件,分别存放到不同的文件路径(方便后边区分数据源扫包地址)
-
在application.properties文件配置分别数据库地址,以及druid相关配置
这里要注意:配置数据库URL的时候,最好加上字符编码集,以及时区,方便后边连接。
springboot的默认配置数据源地址格式是下面这样的,这样配置过后,无需重新创建
DateSource又springboot默认为你进行处理。但是有时需要在启动类加上@MapperScan(“XXXX”)注解,使系统能够装配到mapper映射。
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root
如果进行自定义配置,则需要自己编写数据源加载类,获取SqlSessionFactory工厂。一般配置多数据源时,不使用默认配置,自己重新编写多个数据源加载。
spring.datasource.data2-class-name=com.mysql.jdbc.Driver
spring.datasource.data2.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.data2.username=root
spring.datasource.data2.password=root
spring.datasource.data1.class-name=com.mysql.jdbc.Driver
spring.datasource.data1.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.data1.username=root
spring.datasource.data1.password=root
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
四、配置DateSourceConfig
配置第一数据源:
package com.example.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;
/**
* 第一数据源配置
* create by c-pown on 2020-07-09
*/
@Configuration
@MapperScan(basePackages = DatasourceConfigOne.PACKAGE, sqlSessionFactoryRef = "data1SqlSessionFactory")
public class DatasourceConfigOne {
// 精确到 mapper 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.example.demo.mapper";
static final String MAPPER_LOCATION = "classpath:mapper/*.xml";
@Value("${spring.datasource.data1.url}")
private String url;
@Value("${spring.datasource.data1.username}")
private String user;
@Value("${spring.datasource.data1.password}")
private String password;
@Value("${spring.datasource.data1.class-name}")
private String driverClass;
@Bean(name = "data1DataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "data1TransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "data1SqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("data1DataSource") DataSource data1rDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(data1rDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(DatasourceConfigOne.MAPPER_LOCATION));
//开启驼峰转换
SqlSessionFactory sqlSessionFactory = sessionFactory.getObject();
org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
return sessionFactory.getObject();
}
}
配置第二数据源:
package com.example.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;
/**
* 第二数据源配置
* create by c-pown on 2020-07-09
*/
@Configuration
@MapperScan(basePackages = DatasourceConfigTwo.PACKAGE, sqlSessionFactoryRef = "data2SqlSessionFactory")
public class DatasourceConfigTwo {
// 精确到 mappera 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.example.demo.mappera";
static final String MAPPER_LOCATION = "classpath:mappera/*.xml";
@Value("${spring.datasource.data2.url}")
private String url;
@Value("${spring.datasource.data2.username}")
private String user;
@Value("${spring.datasource.data2.password}")
private String password;
@Value("${spring.datasource.data2-class-name}")
private String driverClass;
@Bean(name = "data2DataSource")
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
@Bean(name = "data2TransactionManager")
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}
@Bean(name = "data2SqlSessionFactory")
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("data2DataSource") DataSource data2DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(data2DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(DatasourceConfigTwo.MAPPER_LOCATION));
//开启驼峰转换
SqlSessionFactory sqlSessionFactory = sessionFactory.getObject();
org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
return sessionFactory.getObject();
}
}
这里设置的:
PACKAGE :是我们前边生成Mapper.java的位置。
MAPPER_LOCATION:为xml文件的位置。
@MapperScan(basePackages = DatasourceConfigTwo.PACKAGE, sqlSessionFactoryRef = “data2SqlSessionFactory”):扫描Mapper映射地址,并与给相应的sessionFactory进行关联。
@Primary:springboot要求项目必须有一个主数据库,可以使用此注解。
四、测试
在mapper映射文件分别写一个查询方法selectInfo();
@SpringBootTest
class DemoApplicationTests {
@Resource
private SysUserMapper sysUserMapper;
@Resource
private SysStudentMapper sysStudentMapper;
@Test
void test() {
List<SysUser> sysUsers = sysUserMapper.selectInfo();
sysUsers.forEach(System.out::println);
List<SysStudent> sysStudents = sysStudentMapper.selectInfo();
sysStudents.forEach(System.out::println);
}
}
没问题。