【Mybatis-plus】SpringBoot项目多数据源配置（解决日志无法打印和分页问题）

0. 前言

在使用SpringBoot项目中，有时可能会用到多个不同的数据库，这种情况下就要配置多数据源。本文以同一个MySQL服务器中的两个不同数据库db1和db2为例，数据库中包含的表如下：

整个项目工程的文件目录如下（由于业务代码较为简单，省略了service层）：

1. 数据表

用户表

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL COMMENT '用户名称',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `sex` char(4) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

学生表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(5) NOT NULL,
  `gender` int(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 配置文件-application.properties

spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?serverTimezone=UTC&useSSL=false
spring.datasource.db1.username=root
spring.datasource.db1.password=xxx

spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/db2?serverTimezone=UTC&useSSL=false
spring.datasource.db2.username=root
spring.datasource.db2.password=xxx

# MyBatis
## 多数据源下，这段语句即使配置了也不会生效
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

3. 实体类

学生类

package com.whut.multisource.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@TableName(value = "student")
@Data
public class Student {
    /**
     * id
     */
    @TableId(type = IdType.AUTO)
    @TableField("id")
    private Integer id;
    /**
     * 姓名
     */
    @TableField("name")
    private String name;
    /**
     * 年龄
     */
    @TableField("age")
    private Integer age;

    /**
     * 性别
     */
    @TableField("gender")
    private String gender;
}

用户类

package com.whut.multisource.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.util.Date;

@TableName(value = "tb_user")
@Data
public class User {
    /**
     * id
     */
    @TableId(type = IdType.AUTO)
    @TableField("id")
    private Integer id;
    /**
     * 姓名
     */
    @TableField("username")
    private String username;

    /**
     * 出生日期
     */
    @TableField("birthday")
    private Date birthday;

    /**
     * 性别
     */
    @TableField("sex")
    private String sex;

    /**
     * 地址
     */
    @TableField("address")
    private String address;
}

4. mapper接口及对应xml文件

学生：

package com.whut.multisource.mapper.db2;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.whut.multisource.domain.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;
public interface StudentMapper extends BaseMapper<Student> {
    @Select("select * from student")
    List<Student> selectAllStudent();
}

<?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.whut.multisource.mapper.db2.StudentMapper">

</mapper>

用户：

package com.whut.multisource.mapper.db1;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.whut.multisource.domain.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper extends BaseMapper<User> {
    List<User> selectAllUser();

    List<User> selectUserByName(@Param("name") String name  );
}

<?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.whut.multisource.mapper.db1.UserMapper">

    <select id="selectAllUser" resultType="com.whut.multisource.domain.User">
        select * from tb_user
    </select>


    <select id="selectUserByName" resultType="com.whut.multisource.domain.User">
        select * from tb_user where username=#{name}
    </select>
</mapper>

5. 配置类

db1配置类

package com.whut.multisource.config;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import org.apache.ibatis.logging.stdout.StdOutImpl;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.Resource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.whut.multisource.mapper.db1", sqlSessionTemplateRef  = "db1SqlSessionTemplate")
//此处的basePackages指向的是你存放数据库db1的mapper的包
public class DataSource1Config {

    @Autowired
    MybatisPlusInterceptor mybatisPlusInterceptor;



    @Bean(name = "db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")//指向yml配置文件中的数据库配置
    @Primary    //主库加这个注解，修改优先权，表示发现相同类型bean，优先使用该方法。
    public DataSource dbDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "db1SqlSessionFactory")
    @Primary
    public SqlSessionFactory dbSqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource,
        @Value("classpath*:mapper/db1/*Mapper.xml") Resource[] mapperLocations) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //这个的getResources指向的是你的mapper.xml文件，相当于在yml中配置的mapper-locations，此处配置了yml中就不用配置，或者说不会读取yml中的该配置。
        bean.setMapperLocations(mapperLocations);
        // 设置分页
        bean.setPlugins(mybatisPlusInterceptor);
        //https://blog.csdn.net/weixin_41785851/article/details/119739897
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        // 配置打印sql语句，如果不配置，多数据源下不会打印
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        return bean.getObject();
    }

    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager dbTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate dbSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

db2配置类

package com.whut.multisource.config;

import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import org.apache.ibatis.logging.stdout.StdOutImpl;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.Resource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.whut.multisource.mapper.db2", sqlSessionTemplateRef  = "db2SqlSessionTemplate")
public class DataSource2Config {
    @Autowired
    MybatisPlusInterceptor mybatisPlusInterceptor;

    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dbDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "db2SqlSessionFactory")
    public SqlSessionFactory dbSqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource,
                                                 @Value("classpath*:mapper/db2/*Mapper.xml") Resource[] mapperLocations) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(mapperLocations);
        bean.setPlugins(mybatisPlusInterceptor);
        //https://blog.csdn.net/weixin_41785851/article/details/119739897
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        // 配置打印sql语句
        configuration.setLogImpl(StdOutImpl.class);
        bean.setConfiguration(configuration);
        return bean.getObject();
    }

    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager dbTransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate dbSqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

MybatisPlus分页配置

package com.whut.multisource.config;
 
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
/**
 * MybatisPlus分页配置
 */
@Configuration
public class MybatisPlusConfig {
    /**
     * 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return interceptor;
    }
 
}

6. 控制层

package com.whut.multisource.controller;

import com.whut.multisource.mapper.db1.UserMapper;
import com.whut.multisource.mapper.db2.StudentMapper;
import com.whut.multisource.utils.ResponseHelper;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
public class TestController {
    @Resource
    private UserMapper userMapper;
    @Resource
    private StudentMapper studentMapper;

    @GetMapping("/test")
    public ResponseHelper test() {
        return ResponseHelper.ok(userMapper.selectList(null));
    }

    @GetMapping("/getAllUser")
    public ResponseHelper getAllUser() {
        return ResponseHelper.ok(userMapper.selectAllUser());
    }

    @GetMapping("/getAllStudent")
    public ResponseHelper getAllStudent() {
        return ResponseHelper.ok(studentMapper.selectAllStudent());
    }


    @GetMapping("/getUserByName")
    public ResponseHelper getUserByName(@RequestParam("name") String name) {
        return ResponseHelper.ok(userMapper.selectUserByName(name));
    }
}

工具类：

```java
package com.whut.multisource.utils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ResponseHelper<T> {
    public String msg;
    public T result;
    public String succ;

    public static  <T>ResponseHelper<T> ok(){
        return new ResponseHelper<>(null, null, "ok");
    }
    public static  <T>ResponseHelper<T> ok(T data){
        return new ResponseHelper<>(null, data, "ok");
    }
    public static  <T>ResponseHelper<T> ok(String msg, T data){
        return new ResponseHelper<>(msg, data, "ok");
    }

    public static <T> ResponseHelper<T> fail(String msg) {
        return new ResponseHelper<>(msg, null, "fail");
    }

}

7. 测试

1. http://localhost:8080/getAllStudent
   控制台输出：

JDBC Connection [HikariProxyConnection@13997946 wrapping com.mysql.jdbc.JDBC4Connection@1f619c13] will not be managed by Spring
==>  Preparing: select * from student
==> Parameters: 
<==    Columns: id, name, age, gender
<==        Row: 1, yogurt, 24, 1
<==        Row: 2, yogurt, 24, 1
<==        Row: 3, wzh, 25, 1
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c7451db]

2. http://localhost:8080/test

以上便是本文所有内容，本文部分内容参考自：
mybatis-plus 多数据源配置打印sql不生效问题
mybatis-plus多数据源配置