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

技术人小冯

已于 2022-07-19 10:07:19 修改

阅读量4.5k

点赞数 1

于 2022-07-04 16:44:57 首次发布

本文链接：https://blog.csdn.net/f88069684/article/details/125296471

版权

SpringBoot 同时被 2 个专栏收录

21 篇文章 18 订阅

订阅专栏

Mysql

5 篇文章 1 订阅

订阅专栏

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最终项目结构

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

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

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

二、配置pom.xml文件

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
        <!-- Druid 数据连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.16</version>
            <scope>compile</scope>
        </dependency>
</dependencies>

三、配置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

<?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="com.duoduo.mybatis.mapper.user.UserMapper">

    <resultMap id="BaseResultMap" type="com.duoduo.mybatis.entity.User">
        <result column="id" jdbcType="INTEGER" property="id" />
        <result column="userName" jdbcType="VARCHAR" property="userName" />
        <result column="passWord" jdbcType="VARCHAR" property="passWord" />
        <result column="realName" jdbcType="VARCHAR" property="realName" />
    </resultMap>


    <select id="findUser" resultType="com.duoduo.mybatis.entity.User">
        select * from user where id = #{id}
    </select>

</mapper>

resource/mapper/order/OrderMapper.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="com.duoduo.mybatis.mapper.order.OrderMapper">

    <resultMap id="BaseResultMap" type="com.duoduo.mybatis.entity.Order">
        <result column="orderId" jdbcType="INTEGER" property="orderId" />
        <result column="orderName" jdbcType="VARCHAR" property="orderName" />
    </resultMap>

    <select id="findOrder" resultType="com.duoduo.mybatis.entity.Order">
        select * from `order` where orderId = #{id}
    </select>


 </mapper>

六、书写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

技术人小冯

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot 2.7教程：SpringBoot 整合 mybatis 多数据源连接方法-2022年最新图文版本

本章学习目标：学习 springboot-mybatis 多数据源的整合方法项目开发过程中会遇到单个项目要连接过个数据库实例的情况，如业务需要访问用户库和订单库。目录:一、项目mybatis-muilt-database最终项目结构二、配置pom.xml文件三、配置properties四、配置数据源类五、书写mapper and xml六、书写service七、书写controller八、Mysql 数据库文件九、测试 ...............
复制链接

扫一扫