SpringBoot 2.7教程：SpringBoot + Web 项目搭建及实践应用-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot + Web 项目搭建，异常捕获处理-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot + Mysql 项目应用-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 集成 jsp 页面开发 -2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 实现文件上传，图片上传并显示功能-2022年最新图文版本

SpringBoot 2.7教程：springboot 设置全局字符编码，解决乱码问题-2022年最新图文版

SpringBoot 2.7教程：SpringBoot mybatis 多数据源的整合方法-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 整合 RocketMQ 项目搭建-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 整合 RabbitMQ 项目搭建-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 整合 MongoDB 项目搭建-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 整合 Redisson 项目搭建-2022年最新图文版本

SpringBoot 2.7教程：SpringBoot 整合 Redis 项目搭建-2022年最新图文版本

本章学习目标：

学习 springboot-mybatis 多数据源的整合方法

项目开发过程中会遇到单个项目要连接过个数据库实例的情况，如业务需要访问 用户库 和订单库。

目录

一、项目mybatis-muilt-database最终项目结构

二、配置pom.xml文件

三、配置properties

四、配置数据源类

五、书写mapper  and  xml

六、书写service

七、书写controller

八、Mysql 数据库文件

九、测试

当前最常用的版本2.2.x、2.3.x将在2022年停止维护了。

下面我们以最新的版本2.7进行系统搭建。

一、项目mybatis-muilt-database最终项目结构

二、配置pom.xml文件



org.springframework.boot
spring-boot-starter-web
2.7.0


org.mybatis.spring.boot
mybatis-spring-boot-starter
2.1.4


mysql
mysql-connector-java
8.0.29



com.alibaba
druid
1.1.8


log4j
log4j
1.2.16
compile



三、配置properties

# 应用名称
spring.application.name=mybatis-multi-datasource
# 应用服务 WEB 访问端口
server.port=8080
datasource.user.url=jdbc:mysql://mysql_host:3306/user?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
datasource.user.username=root
datasource.user.password=password
datasource.user.driverClassName=com.mysql.cj.jdbc.Driver
datasource.order.url=jdbc:mysql://mysql_host:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
datasource.order.username=root
datasource.order.password=password
datasource.order.driverClassName=com.mysql.cj.jdbc.Driver
# 连接池的配置信息
# 初始化大小，最小，最大
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictiOnRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validatiOnQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerCOnnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.cOnnectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
## 打印SQL语句
logging.level.com.duoduo.mybatis.mapper=debug

四、配置数据源类

user数据库连接配置类

@Configuration
@MapperScan(basePackages = UserDataSourceConfig.PACKAGE, sqlSessiOnFactoryRef= "userSqlSessionFactory")
public class UserDataSourceConfig {
static final String PACKAGE = "com.duoduo.mybatis.mapper.user";
static final String MAPPER_LOCATION = "classpath:mapper/user/*.xml";
@Value("${datasource.user.url}")
private String url;
@Value("${datasource.user.username}")
private String username;
@Value("${datasource.user.password}")
private String password;
@Value("${datasource.user.driverClassName}")
private String driverClassName;

@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean(name = "userDatasource")
public DataSource userDatasource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);
//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
@Bean(name = "userTransactionManager")
public DataSourceTransactionManager userTransactionManager() {
return new DataSourceTransactionManager(userDatasource());
}
@Bean(name = "userSqlSessionFactory")
public SqlSessionFactory userSqlSessionFactory(@Qualifier("userDatasource") DataSource userDatasource)
throws Exception {
final SqlSessionFactoryBean sessiOnFactory= new SqlSessionFactoryBean();
sessionFactory.setDataSource(userDatasource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(UserDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}

order数据库连接配置类

@Configuration
@MapperScan(basePackages = OrderDataSourceConfig.PACKAGE, sqlSessiOnFactoryRef= "orderSqlSessionFactory")
public class OrderDataSourceConfig {
static final String PACKAGE = "com.duoduo.mybatis.mapper.order";
static final String MAPPER_LOCATION = "classpath:mapper/order/*.xml";
@Value("${datasource.order.url}")
private String url;
@Value("${datasource.order.username}")
private String username;
@Value("${datasource.order.password}")
private String password;
@Value("${datasource.order.driverClassName}")
private String driverClass;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean(name = "orderDataSource")
public DataSource orderDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClass);
//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
@Bean(name = "orderTransactionManager")
public DataSourceTransactionManager orderTransactionManager() {
return new DataSourceTransactionManager(orderDataSource());
}
@Bean(name = "orderSqlSessionFactory")
public SqlSessionFactory orderSqlSessionFactory(@Qualifier("orderDataSource") DataSource orderDataSource)
throws Exception {
final SqlSessionFactoryBean sessiOnFactory= new SqlSessionFactoryBean();
sessionFactory.setDataSource(orderDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(OrderDataSourceConfig.MAPPER_LOCATION));
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(OrderDataSourceConfig.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}

五、书写mapper  and  xml

 com.duoduo.mybatis.mapper.user.UserMapper.java

package com.duoduo.mybatis.mapper.user;
import com.duoduo.mybatis.entity.User;
import org.springframework.stereotype.Repository;
/***
* @date: 2022/7/1
* @author: 微信公众号：深入技术架构
* @description: TODO
*/
@Repository
public interface UserMapper {
User findUser(int id);
}

 com.duoduo.mybatis.mapper.order.OrderMapper.java

package com.duoduo.mybatis.mapper.order;
import com.duoduo.mybatis.entity.Order;
import org.springframework.stereotype.Repository;
/***
* @date: 2022/7/1
* @author: 微信公众号：深入技术架构
* @description: TODO
*/
@Repository
public interface OrderMapper {
Order findOrder(int id);
}


resource/mapper/user/UserMapper.xml











select * from user where id = #{id}



resource/mapper/order/OrderMapper.xml









select * from `order` where orderId = #{id}



六、书写service

com.duoduo.mybatis.service.UserService.java

package com.duoduo.mybatis.service;
import com.duoduo.mybatis.entity.User;
import com.duoduo.mybatis.mapper.user.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/***
* @date: 2022/7/1
* @author: fenghaikuan
* @description: TODO
*/
@Service
public class UserService {
@Autowired
UserMapper userMapper;
public User findUser(int id){
return userMapper.findUser(id);
}
}

com.duoduo.mybatis.service.OrderService.java

package com.duoduo.mybatis.service;
import com.duoduo.mybatis.entity.Order;
import com.duoduo.mybatis.mapper.order.OrderMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/***
* @date: 2022/7/1
* @author: fenghaikuan
* @description: TODO
*/
@Service
public class OrderService {
@Autowired
OrderMapper orderMapper;
public Order findOrder(int id){
return orderMapper.findOrder(id);
}
}


七、书写controller

com.duoduo.mybatis.controller.UserController.java

package com.duoduo.mybatis.controller;
import com.duoduo.mybatis.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/***
* @date: 2022/7/1
* @author: fenghaikuan
* @description: TODO
*/
@RestController
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/getUser/{id}")
public String getUser(@PathVariable int id){
return userService.findUser(id).toString();
}
}

com.duoduo.mybatis.controller.OrderController.java

package com.duoduo.mybatis.controller;
import com.duoduo.mybatis.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/***
* @date: 2022/7/1
* @author: fenghaikuan
* @description: TODO
*/
@RestController
public class OrderController {
@Autowired
private OrderService orderService;
@RequestMapping("getOrder/{id}")
public String getOrder(@PathVariable int id){
return orderService.findOrder(id).toString();
}
}


八、Mysql 数据库文件

user数据库执行：
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`userName` varchar(32) NOT NULL,
`passWord` varchar(50) NOT NULL,
`realName` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;
INSERT INTO `user` VALUES (1, '张三', 'password', '小张');
order数据库执行：
CREATE TABLE `order` (
`orderId` int NOT NULL AUTO_INCREMENT,
`orderName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`orderId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;
INSERT INTO `order` VALUES (1, '订单名称');

九、测试

http://localhost:8080/getUser/1

http://localhost:8080/getOrder/1