欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

Springboot+MyBatis实现多数据源配置笔记

程序员文章站 2024-01-27 11:52:04
...

一、简介

近年来，由于项目结构越来越大，并且经常会用到不同的数据库以满足项目的实际需求。本节就配置多数据源进行记录

二、准备条件

1.框架：Springboot+MyBatis
2.数据库：mysql的两个数据库及各自一张表（为了演示简单及结构明显）

1.(db1)kings库的tb_user表
2.(db2)peace库的tb_users表
3.下边是tb_user的创建，tb_users一样，这是表名不一样

CREATE TABLE tb_user (
id int(11) NOT NULL auto_increment COMMENT ‘id’,
name varchar(20) NOT NULL default ‘’ COMMENT ‘名字’,
age int(11) NOT NULL default ‘0’ COMMENT ‘年纪’,
password varchar(225) NOT NULL default ‘’ COMMENT ‘密码’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.先看项目结构图如下

Springboot+MyBatis实现多数据源配置笔记

三、实现

1.创建Springboot项目boot，导入核心依赖
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>${mybatis.version}</version>
</dependency>

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>${mysql.version}</version>
</dependency>
2.修改全局配置文件application.yml

注意：此处使用的是jdbc-url，不是url。

因为连接池的实际类型没有被公开，所以在您的自定义数据源的元数据中没有生成**，而且在IDE中没有完成(因为DataSource接口没有暴露属性)。另外，如果您碰巧在类路径上有Hikari，那么这个基本设置就不起作用了，因为Hikari没有url属性(但是确实有一个jdbcUrl属性)。在这种情况下，您必须重写您的配置如下

spring:
  datasource:
    db1:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/kings?allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8
      username: root
      password: mysql
    db2:
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/peace?allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8
      username: root
      password: mysql
3.对数据源进行创建配置

1.创建datasource包
2.分别创建DataSource1.java和DataSource2.java进行管理配置数据源db1和db2
1.DataSource1.java

package com.test.boot.datasource;

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;
/**
* @Description:    db1的数据源配置
* @Author:         Joe
* @CreateDate:     2020/4/21 15:15
*/
@Configuration //注册到spring容器中
@MapperScan(basePackages = "com.test.boot.db1.mapper",sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSource1 {

    /**
     * 配置db1数据库
     * @return
     */
    @Bean(name = "db1Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")//获取全局配置文件中数据
    @Primary //指定默认数据源
    public DataSource testDatasource(){
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     */
    @Bean(name = "db1SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1Datasource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //TbUserMapper.xml的位置;如果使用的是注解sql的话可以省去此行代码
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:db1/mapper/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db1Datasource")DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 封装数据库操作，线程安全的
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2.DataSource2.java

package com.test.boot.datasource;

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 //注册到spring容器中
@MapperScan(basePackages = "com.test.boot.db2.mapper",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSource2 {

    /**
     * 配置db2数据库
     * @return
     */
    @Bean(name = "db2Datasource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")//获取全局配置文件中数据
    public DataSource testDatasource(){
        DataSource build = DataSourceBuilder.create().build();
        return build;
    }

    /**
     * 创建SqlSessionFactory
     * @param dataSource
     * @return
     */
    @Bean(name = "db2SqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2Datasource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //TbUsersMapper.xml的位置;如果使用的是注解sql的话可以省去此行代码
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:db2/mapper/*.xml"));
        return bean.getObject();
    }

    /**
     * 配置事务管理
     * @param dataSource
     * @return
     */
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("db2Datasource")DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 封装数据库操作，线程安全的
     * @param sqlSessionFactory
     * @return
     */
    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory")SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

3.注意：DataSource1.java的每个方法必须加 @Primary注解意为主、默认数据源

4.使用mybatis****生成实体类、mapper、mapper.xml(过程略去)
5.在对应mapper接口上添加对应数据源注解

注意
1.mapper下的方法请忽略
2.TbUserMapper.java对应的是DataSource1.java
@Qualifier(“db1SqlSessionFactory”)指向DataSource1.java的sqlSessionFactoryRef命名
Springboot+MyBatis实现多数据源配置笔记
Springboot+MyBatis实现多数据源配置笔记
3.TbUsersMapper.java对应的是DataSource2.java
@Qualifier(“db2SqlSessionFactory”)指向DataSource2.java的sqlSessionFactoryRef命名
Springboot+MyBatis实现多数据源配置笔记
Springboot+MyBatis实现多数据源配置笔记

6.创建对应service与impl进行测试准备工作

1.UsersService.java

package com.test.boot.db2.service;

import com.test.boot.entity.TbUsers;

public interface UsersService {

    void addUser(TbUsers user);
}

2.UsersServiceImpl.java

package com.test.boot.db2.service.impl;

import com.test.boot.db2.mapper.TbUsersMapper;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUsers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private TbUsersMapper usersMapper;
    @Override
    public void addUser(TbUsers user) {
        usersMapper.insertSelective(user);
    }
}

3.UsersService.java

package com.test.boot.db2.service;

import com.test.boot.entity.TbUsers;

public interface UsersService {

    void addUser(TbUsers user);
}

4.UsersServiceImpl.java

package com.test.boot.db2.service.impl;

import com.test.boot.db2.mapper.TbUsersMapper;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUsers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private TbUsersMapper usersMapper;
    @Override
    public void addUser(TbUsers user) {
        usersMapper.insertSelective(user);
    }
}
7.创建controller进行测试

IndexController.java

package com.test.boot.controller;

import com.test.boot.db1.service.UserService;
import com.test.boot.db2.service.UsersService;
import com.test.boot.entity.TbUser;
import com.test.boot.entity.TbUsers;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

@RestController
@Slf4j
public class IndexController {

    @Autowired
    private UserService userService;
    @Autowired
    private UsersService usersService;

    @RequestMapping(value = "/addUser",method = RequestMethod.GET)
    public String addUser(){
        TbUser user1 = new TbUser();
        user1.setName("joe");
        user1.setAge(25);
        user1.setPassword("111111");
        userService.saveUser(user1);
        log.info("db1的");
        return "ok";
    }

    @RequestMapping(value = "/addUsers",method = RequestMethod.GET)
    public String addUsers(){
        TbUsers user1 = new TbUsers();
        user1.setName("an");
        user1.setAge(23);
        user1.setPassword("111111");
        usersService.addUser(user1);
        log.info("db2的");
        return "ok";
    }
}
8.启动项目，在浏览器进行测试

1.测试db1
http://localhost:8080/addUser
2.测试db2
http://localhost:8080/addUsers
注意：实体类TbUser.java和TbUsers.java就差一个s，别乱了。还请多多指正!

相关标签： 常用代码块与笔记

上一篇： 移动Oracle表空间数据文件方案

下一篇： sqlserver2008r2附加sqlserver2005创建的数据库拒绝访问怎么办

推荐阅读