在线制作书封网站,建德建设局官方网站,网站空间免,济南建设集团有限公司最近在学习SpringBoot的时候#xff0c;需要同时用两个不同的数据库连接服务#xff0c;在网上学习了之后#xff0c;下文以连接一个MySQL数据库和一个SqlServer数据库为例。
配置数据源连接信息
在配置文件中#xff0c;配置对应的数据库连接信息#xff0c;相比于单数…最近在学习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?useUnicodetruecharacterEncodingUTF-8serverTimezoneAsia/Shanghaiusername: password: workcontent:type: sqlServerdriver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc-url: jdbc:sqlserver://localhost:1433;databaseNamemyDatabase;encrypttrue;trustServerCertificatetrue;characterEncodingutf8;username: password: Maven配置数据库驱动
由于新版的官方Sqlserver驱动不支持TLSv1, TLSv1.1我选择了较老的数据库驱动程序。
!-- sqlserver驱动 --
dependencygroupIdcom.microsoft.sqlserver/groupIdartifactIdmssql-jdbc/artifactIdversion7.4.1.jre11/version
/dependency
!-- MYSQL驱动 --
dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.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);ListFunctionMenu queryAll();
}创建Mapper
由于有多个数据源所以在/resources/mapper里面需要区分不同的数据库即创建不同的文件夹每个数据源扫描自己的mapper
.xml文件如下图所示注意和第三步创建Sql工厂注册DataSource的SqlSessionFactory中bean.setMapperLocations()里面的路径对应上对于Mapper.xml里面的配置这里就不具体说明了。 FunctionMenuMapper.xml
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceorg.cqw.baseproject.dao.webproject.FunctionMenuDaoselect idqueryById resultTypeorg.cqw.baseproject.entity.FunctionMenuSELECT * FROM FunctionMenu WHERE id #{id}/selectselect idqueryAll resultTypeorg.cqw.baseproject.entity.FunctionMenuSELECT * 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);//消息响应}
}结果
