MyBatis设置多数据源

文件目录

image-20201221152609871

Maven版本配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>//spring-boot 2.3.0
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

yml配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
spring:
  datasource:
  	#数据库一：主数据库
    test1:
      jdbc-url: jdbc:p6spy:mysql://xxxx:端口号/test1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
      username: xxxx
      password: xxxx
    #数据库二
    test2:
      jdbc-url: jdbc:p6spy:mysql://xxxxx:端口号/test2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
      username: xxxx
      password: xxxx
server:
  port: 2020

mybatis-plus:
  mapper-locations: classpath:/mapper/**/*.xml
  global-config: #全局配置
    db-config:
      id-type: auto #id自增
  type-aliases-package: com.springboot.dev.*.domain.dao
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

注意点：

  • 配多数据源 url 变为jdbc-url

多数据源Config配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.springboot.dev.config.dataSource;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
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;

@Configuration
@MapperScan(basePackages = {
        "com.springboot.dev.db01.mapper"},//配置Mapper接口地址
        sqlSessionFactoryRef = "Test1SqlSessionFactory",
        sqlSessionTemplateRef = "Test1SqlSessionTemplate")
public class Test1DataSourceConfig {
    /**
     * 创建数据源
     * @return
     */
    @Primary    //表示为默认数据源
    @Bean(name = "Test1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.test1")// prefix表示参数的前缀 读取yml的database参数
    public DataSource Db01DateSource(){return DataSourceBuilder.create().build(); }

    /**
     * 创建SessionFactory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Primary
    @Bean(name = "Test1SqlSessionFactory")
    public SqlSessionFactory Db01SqlSessionFactory(@Qualifier("Test1DataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean bean=new MybatisSqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().
                getResources("classpath:mapper/db01/*Mapper.xml"));
        return bean.getObject();
    }
    /**
     * 配置数据库事务
     */
    @Primary
    @Bean(name = "Test1DataSourceTransactionManager")
    public DataSourceTransactionManager Db01DataSourceTransactionManager(@Qualifier("Test1DataSource")DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }
    @Primary
    @Bean(name = "Test1SqlSessionTemplate")
    public SqlSessionTemplate Db01SqlSessionTemplate(@Qualifier("Test1SqlSessionFactory")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

代码使用

Db02数据库数据使用跟下面代码相同无需其他配置

Mapper
1
2
3
4
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    List<Student> list();
}

注意：如果使用Mybatis-Plus 需要继承BaseMapper

Service
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@Service
public class StudentServiceImpl implements StudentService {
    private StudentMapper studentMapper;

    public StudentServiceImpl(StudentMapper studentMapper) {
        this.studentMapper = studentMapper;
    }
	//使用mybatis-plus 分页功能需要配置分页config
    @Override
    public ResponseEntity list(Integer page, Integer size) {
        Page<Student> page1 = new Page<Student>(page, size,true);
        QueryWrapper<Student> queryWrapper=new QueryWrapper<>();
        Page<Student> list = studentMapper.selectPage(page1, queryWrapper);
        return ResponseEntity.ok(list.getRecords());
    }

    @Override
    public ResponseEntity getList(Integer page, Integer size) {
        return ResponseEntity.ok(studentMapper.list());
    }
}
MybatisPlus分页Config
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Configuration
public class MyBatisPlusConfig {
    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,
     * 需要设置 MybatisConfiguration#useDeprecatedExecutor = false
     * 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}
Controller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@RestController
public class StudentController {

    private StudentService studentService;

    public StudentController(StudentService studentService) {
        this.studentService = studentService;
    }

    @GetMapping("/stu/list")
    public ResponseEntity<?> list(@RequestParam(value = "page",defaultValue = "1",required = false)Integer page,@RequestParam(value = "size",defaultValue = "10",required = false) Integer size){
        return studentService.list(page,size);
    }

    @GetMapping("/stu/getList")
    public ResponseEntity<?> getList(@RequestParam(value = "page",defaultValue = "1",required = false)Integer page,@RequestParam(value = "size",defaultValue = "10",required = false) Integer size){
        return studentService.getList(page,size);
    }
}

MybatisPlus打印sql，慢sql插件

p6spy

文档链接

  • p6spy 依赖引入

  • <dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>最新版本</version>
</dependency>

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

    Gradle：compile group: 'p6spy', name: 'p6spy', version: '最新版本'

    - application.yml 配置：

    ```yaml
    spring:
      datasource:
        driver-class-name: com.p6spy.engine.spy.P6SpyDriver
        url: jdbc:p6spy:h2:mem:test
        ...

  • spy.properties 配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2

注意

  • driver-class-name 为 p6spy 提供的驱动类
  • url 前缀为 jdbc:p6spy 跟着冒号为对应数据库连接地址
  • 打印出sql为null,在excludecategories增加commit
  • 批量操作不打印sql,去除excludecategories中的batch
  • 批量操作打印重复的问题请使用MybatisPlusLogFactory (3.2.1新增）
  • 该插件有性能损耗，不建议生产环境使用。

效果如下：

image-20201221153248068

曾出现的问题

SpringBoot多数据源中使用mybatis-plus出现Invalid bound statement (not found)

mybatis-plus官方文档得知，不能使用原生的 SqlSessionFactory，正好项目里面配置的是原生的

img

改为

img

img