基于SpringBoot多数据源解决方案

最近在学习SpringBoot的时候，需要同时用两个不同的数据库连接服务，在网上学习了之后，下文以连接一个MySQL数据库和一个SqlServer数据库为例。

配置数据源连接信息

在配置文件中，配置对应的数据库连接信息，相比于单数据源时连接信息的url属性在多数据源时应该为jdbc-url，请注意下图红色部分：

application.yml

spring:datasource:webproject:type: mysqldriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://localhost:3306/webproject?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghaiusername: password: workcontent:type: sqlServerdriver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc-url: jdbc:sqlserver://localhost:1433;databaseName=myDatabase;encrypt=true;trustServerCertificate=true;characterEncoding=utf8;username: password: 

Maven配置数据库驱动

由于新版的官方Sqlserver驱动不支持TLSv1, TLSv1.1，我选择了较老的数据库驱动程序。

<!--    sqlserver驱动    -->
<dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>7.4.1.jre11</version>
</dependency>
<!--	MYSQL驱动	-->
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version>
</dependency>

创建数据源配置类

以下文中的 WorkContentDataSourceConfig 为例，基本的流程如下：

配置类声明

配置要该数据源相关的Mapper所在的的包扫描和要注入的Sql工厂实例

创建数据源DataSource

@ConfigurationProperties 用于注入application.yml中配置的数据库连接信息

@Primary 默认指定当前数据库，多数据源下需要配置@Primary，不然SpringBoot会找不到数据源注入，后续步骤最好也加上@Primary

创建Sql工厂，注册DataSource

@Qualifier 用于选择注入的bean对象

创建事务管理器

创建Sql模版对象

具体配置类信息如下：

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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @author yamu* @version 1.0* @description 工作库* @date 2025/1/23 10:49*/
@Configuration
@MapperScan(basePackages = {"org.cqw.baseproject.dao.workcontent"},sqlSessionFactoryRef = "workContentSqlSessionFactory")
public class WorkContentDataSourceConfig {@Bean(name = "workContentDataSource")@ConfigurationProperties(prefix = "spring.datasource.workcontent")@Primarypublic DataSource dataSource() {return DataSourceBuilder.create().build();}@Bean(name = "workContentSqlSessionFactory")@Primarypublic SqlSessionFactory sqlSessionFactory(@Qualifier("workContentDataSource") DataSource dataSource)throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/workcontent/*.xml"));return bean.getObject();}@Bean(name = "workContentTransactionManager")@Primarypublic DataSourceTransactionManager transactionManager(@Qualifier("workContentDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean(name = "workContentSqlSessionTemplate")@Primarypublic SqlSessionTemplate sqlSessionTemplate(@Qualifier("workContentSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @author yamu* @version 1.0* @description 项目库	* @date 2025/1/23 10:49*/
@Configuration
@MapperScan(basePackages = {"org.cqw.baseproject.dao.webproject"}, sqlSessionFactoryRef = "webProjectSqlSessionFactory")
public class WebProjectDataSourceConfig {@Bean(name = "webProjectDataSource")@ConfigurationProperties(prefix = "spring.datasource.webproject")public DataSource dataSource() {return DataSourceBuilder.create().build();}@Bean(name = "webProjectSqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("webProjectDataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean();bean.setDataSource(dataSource);bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/webproject/*.xml"));return bean.getObject();}@Bean(name = "webProjectTransactionManager")public DataSourceTransactionManager transactionManager(@Qualifier("webProjectDataSource") DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Bean(name = "webProjectSqlSessionTemplate")public SqlSessionTemplate sqlSessionTemplate(@Qualifier("webProjectSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {return new SqlSessionTemplate(sqlSessionFactory);}
}

创建实体

实体所在的包需要和创建配置类中的第一步的 配置类声明的@MapperScan的basePackages值相对应。

package org.cqw.baseproject.dao.webproject;
@Repository
public interface FunctionMenuDao {FunctionMenu queryById(int id);List<FunctionMenu> queryAll();
}

创建Mapper

由于有多个数据源，所以在/resources/mapper里面需要区分不同的数据库（即创建不同的文件夹，每个数据源扫描自己的mapper

.xml文件），如下图所示，注意和第三步创建Sql工厂，注册DataSource的SqlSessionFactory中bean.setMapperLocations()里面的路径对应上，对于Mapper.xml里面的配置这里就不具体说明了。

FunctionMenuMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.cqw.baseproject.dao.webproject.FunctionMenuDao"><select id="queryById" resultType="org.cqw.baseproject.entity.FunctionMenu">SELECT * FROM FunctionMenu WHERE id = #{id}</select><select id="queryAll" resultType="org.cqw.baseproject.entity.FunctionMenu">SELECT * FROM FunctionMenu WHERE isDel = '0' AND status = '1' ORDER BY depth, id</select>
</mapper>

测试

关于 Service 和 ServiceImpl 这里就省略了

FunctionMenuController

@RestController
@RequestMapping("/functionMenu")
public class FunctionMenuController {@Autowiredpublic FunctionMenuService functionMenuService;@GetMapping("getAllMenuList")public String getAllMenuList() {var data = functionMenuService.getAllMenuList();return R.success(data);//消息响应}
}

结果：

    public FunctionMenuService functionMenuService;@GetMapping("getAllMenuList")public String getAllMenuList() {var data = functionMenuService.getAllMenuList();return R.success(data);//消息响应}
}

结果：